Use poi to read Excel files

ArticleDirectory

• Getphysicalnumberofcells

1. Preface

The project requires that the content of the Excel file be read and converted to XML format. Generally, poi and jexcelapi are used to read Excel files. Here we will introduce how to use poi to read Excel documents.

2. Code Instance:

 Package Edu. SJTU. erplab. Poi;

 Import Java. Io. fileinputstream;
 Import Java. Io. filenotfoundexception;
 Import Java. Io. ioexception;
 Import Java. Io. inputstream;
Import Java. Text. simpledateformat;
 Import Java. util. date;
 Import Java. util. hashmap;
 Import Java. util. Map;

 Import Org. Apache. Poi. hssf. usermodel. hssfcell;
 Import Org. Apache. Poi. hssf. usermodel. hssfdateutil;
 Import Org. Apache. Poi. hssf. usermodel. hssfrow;
 Import Org. Apache. Poi. hssf. usermodel. hssfsheet;
Import Org. Apache. Poi. hssf. usermodel. hssfworkbook;
 Import Org. Apache. Poi. poifs. filesystem. poifsfilesystem;

 /**  
* Excel worksheet operations
  */ 
 Public   Class Excelreader {
 Private Poifsfilesystem FS;
 Private Hssfworkbook WB;
 Private Hssfsheet sheet;
Private Hssfrow row;

 /**  
* Read the content of the Excel table header.
*  @ Param  Inputstream
*  @ Return  Array of string header content
  */ 
 Public String [] readexceltitle (inputstream is ){
 Try {
FS = New Poifsfilesystem (is );
WB =New Hssfworkbook (FS );
} Catch (Ioexception e ){
E. printstacktrace ();
}
Sheet = WB. getsheetat (0 );
Row = sheet. getrow (0 );
 //  Total title Columns  
          Int Colnum = row. getphysicalnumberofcells ();
System. Out. println ("colnum:" + colnum );
String [] Title = New String [colnum];
For ( Int I = 0; I <colnum; I ++ ){
 //  Title [I] = getstringcellvalue (row. getcell (short) I ));  
 Title [I] = getcellformatvalue (row. getcell (( Short ) I ));
}
 Return Title;
}

 /**  
* Reading Excel Data
*  @ Param Inputstream
*  @ Return  Map Objects that contain cell data
  */ 
 Public Map <integer, string> readexcelcontent (inputstream is ){
Map <integer, string> content = New Hashmap <integer, string> ();
String STR = "";
 Try {
FS = New Poifsfilesystem (is );
WB = New Hssfworkbook (FS );
} Catch (Ioexception e ){
E. printstacktrace ();
}
Sheet = WB. getsheetat (0 );
 //  Get the total number of rows  
          Int Rownum = sheet. getlastrownum ();
Row = sheet. getrow (0 );
 Int Colnum = row. getphysicalnumberofcells ();
 //  The body content should start from the second line and the title of the first row Header 
          For ( Int I = 1; I <= rownum; I ++ ){
Row = sheet. getrow (I );
 Int J = 0;
 While (J <colnum ){
 //  Split the data content of each cell with "-", and use the string Replace () method to restore data later.
  //  You can also set the data of each cell to the attribute of a JavaBean.
  //  STR + = getstringcellvalue (row. getcell (short) J). Trim () +
 //  "-";  
 STR + = getcellformatvalue (row. getcell (( Short ) J). Trim () + "";
J ++;
}
Content. Put (I, STR );
STR = "";
}
 Return Content;
}

 /**  
* Get the data whose cell data content is string type
*
*  @ Param Cell Excel Cell
*  @ Return  String cell data content
  */ 
 Private String getstringcellvalue (hssfcell cell ){
String strcell = "";
 Switch (Cell. getcelltype ()){
 Case Hssfcell. cell_type_string:
Strcell = cell. getstringcellvalue ();
 Break ;
 Case Hssfcell. cell_type_numeric:
Strcell = string. valueof (cell. getnumericcellvalue ());
 Break ;
 Case Hssfcell. cell_type_boolean:
Strcell = string. valueof (cell. getbooleancellvalue ());
 Break ;
 Case Hssfcell. cell_type_blank:
Strcell = "";
 Break ;
 Default :
Strcell = "";
 Break ;
}
 If (Strcell. Equals ("") | strcell = Null ){
 Return "";
}
 If (Cell = Null ){
 Return "";
}
 Return Strcell;
}

 /**  
* Obtain the data whose cell data content is of the date type.
*
* @ Param  Cell
* Excel Cells
*  @ Return  String cell data content
  */ 
 Private String getdatecellvalue (hssfcell cell ){
String result = "";
 Try {
 Int Celltype = cell. getcelltype ();
 If (Celltype = hssfcell. cell_type_numeric ){
Date = cell. getdatecellvalue ();
Result = (date. getyear () + 1900) + "-" + (date. getmonth () + 1)
+ "-" + Date. getdate ();
} Else   If (Celltype = hssfcell. cell_type_string ){
String date = getstringcellvalue (cell );
Result = date. replaceall ("[year and month]", "-"). Replace ("day", ""). Trim ();
} Else   If (Celltype = hssfcell. cell_type_blank ){
Result = "";
}
} Catch (Exception e ){
System. Out. println ("the date format is incorrect! ");
E. printstacktrace ();
}
 Return Result;
}

 /**  
* Set Data Based on the hssfcell type
*  @ Param  Cell
*  @ Return  
  */ 
 Private String getcellformatvalue (hssfcell cell ){
String cellvalue = "";
 If (Cell! = Null ){
 //  Determine the type of the current cell  
              Switch (Cell. getcelltype ()){
 //  If the current cell type is Numeric  
              Case Hssfcell. cell_type_numeric:
 Case Hssfcell. cell_type_formula :{
 // Judge whether the current cell is date  
                  If (Hssfdateutil. iscelldateformatted (cell )){
 //  If the data type is date, convert it to data format

  //  Method 1: the data format is time, minute, and second: 0:00:00
  //  Cellvalue = cell. getdatecellvalue (). tolocalestring ();

  //  Method 2: This data format does not contain time, minute, and second: 2011-10-12  
 Date = cell. getdatecellvalue ();
Simpledateformat SDF = New Simpledateformat ("yyyy-mm-dd ");
Cellvalue = SDF. Format (date );

}
 //  If it is a pure number  
                  Else {
 //  Obtains the value of the current cell.  
 Cellvalue = string. valueof (cell. getnumericcellvalue ());
}
 Break ;
}
 //  If the current cell type is strin  
              Case Hssfcell. cell_type_string:
 //  Obtains the current cell string.  
 Cellvalue = cell. getrichstringcellvalue (). getstring ();
 Break ;
 //  Default cell value  
              Default :
Cellvalue = "";
}
} Else {
Cellvalue = "";
}
 Return Cellvalue;

}

 Public   Static   Void Main (string [] ARGs ){
 Try {
 //  Test the title of an Excel worksheet  
 Inputstream is = New Fileinputstream ("D: \ test2.xls ");
Excelreader = New Excelreader ();
String [] Title = excelreader. readexceltitle (is );
System. Out. println ("obtain the title of an Excel table :");
 For (String S: Title ){
System. Out. Print (S + "");
}

 //  Test the content of an Excel worksheet.  
 Inputstream is2 =New Fileinputstream ("D: \ test2.xls ");
Map <integer, string> map = excelreader. readexcelcontent (is2 );
System. Out. println ("obtain the content of the Excel table :");
 For ( Int I = 1; I <= map. Size (); I ++ ){
System. Out. println (Map. Get (I ));
}

} Catch (Filenotfoundexception e ){
System. Out. println ("the specified path file is not found! ");
E. printstacktrace ();
}
}
}

3. Summary

Because the content in an Excel cell usually has a certain format, such as the date type, number type, and string type, it is necessary to judge the format when reading, otherwise there will be an error. Generally, the date cannot be read normally. There is a method in the Code instance:

Getcellformatvalue (hssfcell cell)

Input an Excel cell in this method to identify the cell format and convert it to the correct format.

PS: 2012-2-23

There is a piece of code in the Code instance:

 
IntColnum = row. getphysicalnumberofcells ();
 

TheHssfrow. getphysicalnumberofcells ();This method is used to obtain the number of cells in a row.GetphysicalnumberofcellsExplanation of methods

Getphysicalnumberofcells

 
Public intGetphysicalnumberofcells()
 

Gets the number of defined cells (not number of cells in the actual row !). That is to say if only columns 0, 4, 5 have values then there wocould be 3.

Specified:

GetphysicalnumberofcellsIn Interface Row

Returns:

Int representing the number of defined cells in the row.