Java POI Excel

Apache.org provides a project (poi) dedicated for office software operations. This greatly facilitates the operation and reading of the content for statistical analysis. Ingress is poi-bin-3.7-20101029.zip
Component APIs
Excel (SS = HSSF + XSSF)
Word (HWPF + XWPF)
PowerPoint (HSLF + XSLF)
OpenXML4J (OOXML)
OLE2 Filesystem (POIFS)
OLE2 Document Props (HPSF)
Outlook (HSMF)
Visio (HDGF)
Publisher (HPBF)

 

The document provides two different writing methods: Old Code and new code. If you want to be compatible with xls and xslx files, we recommend that you use new code. And the old code is not very good for the office2007 operation.

The Code is as follows:

Public static void main (String [] args) throws Exception {
HSSFWorkbook wb = new HSSFWorkbook ();
// Create a new sheet to create a new table
HSSFSheet s = wb. createSheet ();
// Declare a row object reference declares a new row
HSSFRow r = null;
// Declare a cell object reference declares a cell
HSSFCell c = null;
// Create 2 cell styles create 2 cell styles
HSSFCellStyle cs = wb. createCellStyle ();
HSSFCellStyle cs2 = wb. createCellStyle ();
HSSFDataFormat df = wb. createDataFormat ();

// Create 2 fonts objects create 2 cell fonts
HSSFFont f = wb. createFont ();
HSSFFont f2 = wb. createFont ();

// Set font 1 to 12 point type, blue and bold Set the font types ranging from 1 to 12, blue and bold
F. setFontHeightInPoints (short) 12 );
F. setColor (HSSFColor. RED. index );
F. setBoldweight (HSSFFont. BOLDWEIGHT_BOLD );

// Set font 2 to 10 point type, red and bold to Set the font types ranging from 2 to 10, red and bold
F2.setFontHeightInPoints (short) 10 );
F2.setColor (HSSFColor. RED. index );
F2.setBoldweight (HSSFFont. BOLDWEIGHT_BOLD );

// Set cell style and formatting to Set the cell style and format
Cs. setFont (f );
Cs. setDataFormat (df. getFormat ("#,## 0.0 "));

// Set the other cell style and formatting to Set other cell styles and formats
Cs2.setBorderBottom (cs2.BORDER _ THIN );
Cs2.setDataFormat (HSSFDataFormat. getBuiltinFormat ("text "));
Cs2.setFont (f2 );

// Define a few rows defines a new row
For (short rownum = (short) 0; rownum <30; rownum ++ ){
R = s. createRow (rownum );
For (short cellnum = (short) 0; cellnum <10; cellnum + = 2 ){
C = r. createCell (cellnum );
HSSFCell c2 = r. createCell (cellnum + 1 );

C. setCellValue (double) rownum + (cellnum/10 ));
C2.setCellValue (new HSSFRichTextString ("Hello! "+ Cellnum ));
}
}

// Save
FileOutputStream out = new FileOutputStream ("d: // workbook.xls ");
Wb. write (out );
Out. close ();

}

Bytes ---------------------------------------------------------------------------------------------------------

Public static void main (String [] args) throws Exception {
Workbook [] wbs = new Workbook [] {new HSSFWorkbook (),
New XSSFWorkbook ()};
For (int I = 0; I <wbs. length; I ++ ){
Workbook wb = wbs [I];
CreationHelper createHelper = wb. getCreationHelper ();

// Create a new sheet to create a new table
Sheet s = wb. createSheet ();
// Declare a row object reference declares the reference row of an object.
Row r = null;
// Declare a cell object reference declares the reference cell of the object
Cell c = null;
// Create 2 cell styles create 2 cell styles
CellStyle cs = wb. createCellStyle ();
CellStyle cs2 = wb. createCellStyle ();
DataFormat df = wb. createDataFormat ();

// Create 2 fonts objects create 2 font objects
Font f = wb. createFont ();
Font f2 = wb. createFont ();

// Set font 1 to 12 point type, blue and bold Set the font type from 1 to 12 points, blue and bold
F. setFontHeightInPoints (short) 12 );
F. setColor (IndexedColors. RED. getIndex ());
F. setBoldweight (Font. BOLDWEIGHT_BOLD );

// Set font 2 to 10 point type, red and bold Set the font from 2 to 10 point type, red and bold
F2.setFontHeightInPoints (short) 10 );
F2.setColor (IndexedColors. RED. getIndex ());
F2.setBoldweight (Font. BOLDWEIGHT_BOLD );

// Set cell style and formatting to Set the cell style and format
Cs. setFont (f );
Cs. setDataFormat (df. getFormat ("#,## 0.0 "));

// Set the other cell style and formatting to Set other cell styles and formats
Cs2.setBorderBottom (cs2.BORDER _ THIN );
Cs2.setDataFormat (df. getFormat ("text "));
Cs2.setFont (f2 );

// Define a few rows defines several rows
For (int rownum = 0; rownum <30; rownum ++ ){
R = s. createRow (rownum );
For (int cellnum = 0; cellnum <10; cellnum + = 2 ){
C = r. createCell (cellnum );
Cell c2 = r. createCell (cellnum + 1 );

C. setCellValue (double) rownum + (cellnum/10 ));
C2.setCellValue (createHelper. createRichTextString ("Hello! "
+ Cellnum ));
}
}

// Save
String filename = "d: // workbook.xls ";
If (wb instanceof XSSFWorkbook ){
Filename = filename + "x ";
}

FileOutputStream out = new FileOutputStream (filename );
Wb. write (out );
Out. close ();
}

}
Bytes ---------------------------------------------------------------------------------------------------------

Java reads Excel and is compatible with versions 2003 and earlier and 2007:

Public class PoiReadExcel {

Public void readExcel (String filePath ){

Try {

Workbook workBook = null;
Try {
WorkBook = new XSSFWorkbook (filePath); // supports 2007
} Catch (Exception ex ){
WorkBook = new HSSFWorkbook (new FileInputStream (filePath); // 2003 or earlier versions are supported
}

// Obtain the number of worksheets in Excel
System. out. println ("Number of worksheets:" + workBook. getNumberOfSheets ());

// Cycle each Worksheet
For (int I = 0; I <workBook. getNumberOfSheets (); I ++ ){
// Create a worksheet
Sheet sheet = workBook. getSheetAt (I );

Int rows = sheet. getPhysicalNumberOfRows (); // obtain the number of rows
If (rows> 0 ){
Sheet. getMargin (Sheet. TopMargin );
For (int r = 0; r <rows; r ++) {// row Loop
Row row = sheet. getRow (r );
If (row! = Null ){

Int cells = row. getLastCellNum (); // obtain the number of Columns
For (short c = 0; c <cells; c ++) {// column Loop
Cell cell = row. getCell (c );

If (cell! = Null ){
String value = getValue (cell );
System. out. println ("th" + r + "row" + "th" + c
+ "Column:" + value );
}
}
}
}
}

// Query merged Cells
For (I = 0; I <sheet. getNumMergedRegions (); I ++ ){
System. out. println ("no." + I + "merge cells ");
CellRangeAddress region = sheet. getMergedRegion (I );
Int row = region. getLastRow ()-region. getFirstRow () + 1;
Int col = region. getLastColumn ()-region. getFirstColumn () + 1;
System. out. println ("starting line:" + region. getFirstRow ());
System. out. println ("Starting column:" + region. getFirstColumn ());
System. out. println ("occupied rows:" + row );
System. out. println ("occupied columns:" + col );
}
}

} Catch (Exception ex ){
Ex. printStackTrace ();
}
}

Public String getValue (Cell cell ){

String value = "";
Switch (cell. getCellType ()){

Case Cell. CELL_TYPE_NUMERIC: // numeric type
If (DateUtil. isCellDateFormatted (cell )){
// If it is of the date type, obtain the date value of the cell.
Value = DateUtil. getJavaDate (cell. getNumericCellValue (). toString ();
} Else {// pure number
Value = String. valueOf (cell. getNumericCellValue ());
}
Break;
/* This row indicates that the cell content is of the string type */
Case Cell. CELL_TYPE_STRING: // string type
Value = cell. getRichStringCellValue (). toString ();
Break;
Case Cell. CELL_TYPE_FORMULA: // Formula
// Read the formula calculation value
Value = String. valueOf (cell. getNumericCellValue ());
If (value. equals ("NaN") {// if the obtained data value is invalid, convert it to the obtained string
Value = cell. getRichStringCellValue (). toString ();
}
// Cell. getCellFormula (); read Formula
Break;
Case Cell. CELL_TYPE_BOOLEAN: // Boolean
Value = "" + cell. getBooleanCellValue ();
Break;
/* This row indicates that the cell value is empty */
Case Cell. CELL_TYPE_BLANK: // null
Value = "";
Break;
Case Cell. CELL_TYPE_ERROR: // fault
Value = "";
Break;
Default:
Value = cell. getRichStringCellValue (). toString ();
}

Return value;
}

Public static void main (String args []) {

PoiReadExcel im = new PoiReadExcel ();

Im. readExcel ("F:/2003.xls ");
// Im. readExcel ("F:/2007.xlsx ");
// Im. readExcel ("F:/2007.xls"); // save as 2007

}

}