About the system grooming of POI

Java operation Excel file, there are two tools can be used, one is JXL, one is poi. Recently made the requirement to use to POI, so this article describes the use of POI (currently only describes writing Excel files).

First, Introduction

Jakarta POI is a subproject of Apache, with the goal of processing Ole2 objects. It provides a set of Java APIs that manipulate Windows documents

At present the more mature is the HSSF interface, processing MS Excel object. It's not like we're just using CSV-generated unformatted things that can be translated by Excel, but real Excel objects, you can control some properties like Sheet,cell and so on. Poi, with HSSF, you can read, write, and modify Excel files in plain Java code.

HSSF provides two types of Api:usermodel and Eventusermodel for read operations, the user model and the event-user model. The former is very well understood, the latter is more abstract, but the operation efficiency is much higher.

Ii. Creating an Excel document using the HSSF API

1,HSSF of several entity classes :

Hssfworkbook: Entire Excel file

Hssfsheet: Worksheets

Hssfrow: Yes

Hssfcell: Cell

Hssfcellstyle: Cell style

Hssffont: Cell Font

Hssfdataformat: Cell content formatting

Hssfrichtextstring: Cell text content

There are other entities that are not listed here

2,Hssfworkbook

Workbook is created by using the new Hssfworkbook instance.

Hssfworkbook WorkBook = new Hssfworkbook ();

3.hssfsheet sheet is created by the Createsheet () function of the Hssfworkbook instance. Hssfsheet sheet = Workbook.createsheet (); The newly created multiple sheet are automatically added to workbook in order. Sheet created without specifying a name (the name of the bottom tab display), you can call Hssfworkbook's Setsheetname function to set it manually. such as Workbook.setsheetname (Sheetindex, "sheetname"); Parameter Sheetindex starting from 0

4.Hssfrow Row is created by the CreateRow (rowNumber) function of the Hssfsheet instance. The parameter RowNumber starts at 0. Hssfrow row = sheet.createrow (0); You can call the SetHeight (height) function to set the height of the row, where height is Twip, which is 1/20 point. The height can also be set by the Setheightinpoints function.

5. TheHssfcell cell is created by the Createcell (column) or Createcell (column, type) function of the Hssfrow instance. The TYPE of CELL Hssfcell.cell_type_numeric

Hssfcell.cell_type_string

Hssfcell.cell_type_formula

Hssfcell.cell_type_blank Default Value

Hssfcell.cell_type_boolean

The value of the cell calls the Setcellvalue (para) function to set.       The para parameter is hssfrichtextstring, double, Date, Calander. A single cell does not have a width value and must be set Hssfsheet the Setcolumnwidth (colindex, width) function of the instance, which is 1/256 character. For example, if the first column cell width is set to 8 character widths, use: Sheet.setcolumnwidth (0, 8*256);

In addition, Hssfsheet provides the Autosizecolumn (short column) method to automatically adjust the width of the column based on the contents of the cell. This method is inefficient and not recommended (especially when the data is large).

6,Hssffont

Hssffont is created by the CreateFont () method of the Hssfworkbook instance, namely:

Hssffont font = Workbook.createfont ();

Font.setfontheightinpoints ((short) 11); Font Size font.setfontname ("Song Body");

7,Hssfdataformat

A hssfdataformat that is used to format cells, such as numbers and date formats. The object is created by the Createdataformat () method of the Hssfworkbook instance, which is:

Hssfdataformat DataFormat = Workbook.createdataformat ();

After creation, use the style to set it:

Style.setdataformat (Dataformat.getformat ("Yyyy-mm-dd hh:mm:ss"));

8,Hssfcellstyle

Hssfcellstyle instances are created through the Createcellstyle () method of the Hssfworkbook instance, namely:

Hssfcellstyle style =Workbook.createcellstyle (); Style.setfont (font); style.setalignment (Hssfcellstyle.align_left);  Style.setverticalalignment (Hssfcellstyle.vertical_center); Style.setwraptext (false);//whether to break lineStyle.setdataformat (Dataformat.getformat ("Yyyy-mm-dd Hh:mm:ss"));ImportJava.io.BufferedOutputStream;ImportJava.io.FileOutputStream;Importjava.io.IOException;Importjava.util.Date;ImportOrg.apache.poi.hssf.usermodel.HSSFCell;ImportOrg.apache.poi.hssf.usermodel.HSSFCellStyle;ImportOrg.apache.poi.hssf.usermodel.HSSFDataFormat;ImportOrg.apache.poi.hssf.usermodel.HSSFFont;Importorg.apache.poi.hssf.usermodel.HSSFRichTextString;ImportOrg.apache.poi.hssf.usermodel.HSSFRow;ImportOrg.apache.poi.hssf.usermodel.HSSFSheet;ImportOrg.apache.poi.hssf.usermodel.HSSFWorkbook; Public classWriteexcel {Private Static FinalString date_format = "Yyyy-mm-dd hh:mm:ss"; Private Static FinalString Money_format = "###,# #0.00";  Public Static voidMain (string[] args)throwsIOException {export (); }           Public StaticHssfcellstyle Createcellstyle (hssfworkbook workBook, Hssffont font, Shortalign) {Hssfcellstyle style=Workbook.createcellstyle ();         Style.setfont (font);         Style.setalignment (align);         Style.setverticalalignment (Hssfcellstyle.vertical_center); returnstyle; }           Public StaticHssfcellstyle Createwrapcellstyle (hssfworkbook workBook, Hssffont font, ShortAlignBooleanwrapped) {Hssfcellstyle style=Workbook.createcellstyle ();         Style.setfont (font);         Style.setalignment (align);         Style.setverticalalignment (Hssfcellstyle.vertical_center);         Style.setwraptext (wrapped); returnstyle; }           Public StaticHssfcellstyle Createformatcellstyle (hssfworkbook workBook, Hssffont font, ShortAlign, Hssfdataformat dataformat, String format) {Hssfcellstyle style=Workbook.createcellstyle ();         Style.setfont (font);         Style.setalignment (align);         Style.setverticalalignment (Hssfcellstyle.vertical_center);         Style.setdataformat (Dataformat.getformat (format)); returnstyle; }           Public Static voidCreatestringcell (Hssfrow Row, Shortcolumn, Hssfcellstyle cellstyle, String value) {Hssfcell cell=row.createcell (column);         Cell.setcellstyle (CellStyle); Cell.setcellvalue (Newhssfrichtextstring (value)); }           Public Static voidCreatenumbercell (Hssfrow Row, Shortcolumn, Hssfcellstyle CellStyle,Doublevalue) {Hssfcell cell=row.createcell (column);         Cell.setcellstyle (CellStyle);     Cell.setcellvalue (value); }           Public Static voidCreatedatecell (Hssfrow Row, Shortcolumn, Hssfcellstyle cellstyle, Date value) {Hssfcell cell=row.createcell (column);         Cell.setcellstyle (CellStyle);     Cell.setcellvalue (value); }           Public Static voidExport ()throwsIOException {hssfworkbook WorkBook=NewHssfworkbook (); Hssfsheet sheet=Workbook.createsheet (); Workbook.setsheetname (0, "Sheet1"); //FontHssffont Titlefont =Workbook.createfont (); Titlefont.setfontheightinpoints (( Short) 11); Titlefont.setfontname ("The song Body"); Hssfdataformat DataFormat=Workbook.createdataformat (); //cell styleHssfcellstyle Leftstyle =Createcellstyle (WorkBook, Titlefont, hssfcellstyle.align_left); Hssfcellstyle Rightstyle=Createcellstyle (WorkBook, Titlefont, hssfcellstyle.align_right); Hssfcellstyle Wrapstyle= Createwrapcellstyle (WorkBook, Titlefont, Hssfcellstyle.align_left,true); Hssfcellstyle Datestyle=Createformatcellstyle (WorkBook, Titlefont, Hssfcellstyle.align_right, DataFormat, date_format); Hssfcellstyle Moneystyle=Createformatcellstyle (WorkBook, Titlefont, Hssfcellstyle.align_right, DataFormat, Money_format); intRowIndex = 0; //Excel header rowHssfrow row =Sheet.createrow (RowIndex);  Shortindex = ( Short) 0;  for(inti = 0; I < 6; i + +) {Createstringcell (row, index, Leftstyle,"Column Header" +i); Sheet.setcolumnwidth (index, ( Short) (20 * 256));//20-Character widthindex++; }        //content         for(inti = 0; I < 10; i++) {row= Sheet.createrow (+ +RowIndex); Createstringcell (Row, ( Short) 0, Leftstyle, "26688135"); Createstringcell (Row, ( Short) 1, Wrapstyle, "This is the introduction"); Createnumbercell (Row, ( Short) 2, Moneystyle, 100.05); Createdatecell (Row, ( Short) 3, Datestyle,NewDate ()); Createstringcell (Row, ( Short) 4, Wrapstyle, "first line content/r/n second line content/r/n third line content");//force a line break using/r/nCreatenumbercell (Row, ( Short) 5, Rightstyle, 100); }                  //OutputBufferedoutputstream BOS =NewBufferedoutputstream (NewFileOutputStream ("Abc.xls"));         Workbook.write (BOS);     Bos.close (); } }

Reprinted from: http://blog.csdn.net/zhutulang/article/details/6885637

Common cell settings include the following:

1. Merging cells;

2. The cell is centered horizontally and vertically;

3. Define the date format:

　　1. The "how to create date cells" example in the Poi "QuickGuide" shows how to create a date cell, with the following code:

　　

    Hssfcellstyle CellStyle = wb.createcellstyle ();    Cellstyle.setdataformat (Hssfdataformat.getbuiltinformat ("M/d/yy h:mm"));     = Row.createcell ((short) 1);    Cell.setcellvalue (new  Date ());    Cell.setcellstyle (CellStyle);

This method can only create several date formats built into the Hssfdataformat method, rather than the actual fully custom format, if only the built-in format is required, refer to the source code.

　　2. For a true date custom format, you should referto the "Create User defined data Formats" example in the "Quick Guide" code as follows:

　　

    New Hssfworkbook ();     = Wb.createsheet ("format sheet");     = Wb.createdataformat ();     = Sheet.createrow (0);     = Row.createcell (0);     = Wb.createcellstyle ();    Style.setdataformat (Format.getformat"yyyy mm month DD Day"));    Cell.setcellvalue (new  Date ());    Cell.setcellstyle (style);

Reference Documentation:

Official homepage:http://poi.apache.org/

Very good tutorial:http://poi.apache.org/spreadsheet/quick-guide.html

About the system grooming of POI