ExcelParser, Excel parsing tool class (parsing xlsx)

Package cn.com.css. common. util;

Import java. io. File;
Import java. io. FileInputStream;
Import java. io. IOException;
Import java. io. InputStream;
Import java. math. BigDecimal;
Import java. util. HashMap;
Import java. util. Map;

Import org. apache. poi. hssf. usermodel. HSSFCell;
Import org. apache. poi. xssf. usermodel. XSSFCell;
Import org. apache. poi. xssf. usermodel. XSSFRow;
Import org. apache. poi. xssf. usermodel. XSSFSheet;
Import org. apache. poi. xssf. usermodel. XSSFWorkbook;

Public class ExcelParser {
Private XSSFWorkbook xssfWorkbook;
Private XSSFSheet xssfSheet;
Private XSSFRow xssfRow;
Private int columnNumbers;
Private int totalRows;

/**
* @ Param args
* @ Throws Exception
*/
Private String getCellFormatValue (XSSFCell cell ){
String cellvalue = "";
If (cell! = Null ){
// Determine the Type of the current Cell
Switch (cell. getCellType ()){
// If the current Cell Type is NUMERIC
Case XSSFCell. CELL_TYPE_NUMERIC :{
BigDecimal big = new BigDecimal (cell. getNumericCellValue ());
Cellvalue = big. toString ();
Break;
}
Case XSSFCell. CELL_TYPE_FORMULA :{
// Determine whether the current cell is Date
/*
* If (XSSFDateUtil. isCellDateFormatted (cell )){//
* If it is of the Date type, it is converted to the Data format.
*
* // Method 1: the data format is time/minute/Second: 2011-10-12 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 {// obtain the current Cell value cellvalue =
* String. valueOf (cell. getNumericCellValue ());}
*/
BigDecimal bigula = new BigDecimal (cell
. GetCachedFormulaResultType ());
Cellvalue = bigula. toString ();
Break;
}
// If the current Cell Type is STRIN
Case HSSFCell. CELL_TYPE_STRING:
// Obtain the current Cell string
Cellvalue = cell. getRichStringCellValue (). getString ();
Break;
// Default Cell value
Default:
Cellvalue = "";
}
} Else {
Cellvalue = "";
}
Return cellvalue;

}

/**
* \ Brief obtains the name of the column in Excel and some excel initialization information, such as the number of rows and number of columns.
*
* @ Param is
* @ Return
* @ Attention
* @ Author permission assignment
* @ Date 2014-5-27
* @ Note begin modify by null
*/
Public String [] getTitle (InputStream is ){
Try {
XssfWorkbook = new XSSFWorkbook (is );
} Catch (IOException e ){
E. printStackTrace ();
}
XssfSheet = xssfWorkbook. getSheetAt (0 );
System. out. println ("xssfSheet. getSheetName () =" + xssfSheet. getSheetName ());

// Obtain the number of rows
TotalRows = xssfSheet. getLastRowNum ();
System. out. println ("totalRows =" + totalRows );
XssfRow = xssfSheet. getRow (0); // first line: title
// Obtain the number of Columns
ColumnNumbers = xssfRow. getPhysicalNumberOfCells ();
System. out. println ("columnNumbers =" + columnNumbers );

// A set of column names
String [] title = new String [columnNumbers];
For (int I = 0; I <columnNumbers; I ++ ){
Title [I] = getCellFormatValue (xssfRow. getCell (I ));
}

// Returns an array of column names.
Return title;
}

Public Map ReadExcelContent (InputStream is ){
GetTitle (is );
Map Content = new HashMap ();
String str = "";
// The body content should start from the second line and the title of the first row Header
For (int I = 1; I <= totalRows; I ++ ){
XssfRow = xssfSheet. getRow (I );
Int j = 0;

While (j <= columnNumbers ){
String cell = getCellFormatValue (xssfRow. getCell (j). trim ();
If (cell = ""){
J ++;
Continue; // cell = "null ";
}
Str + = cell + "\ t ";
J ++;
}
Str. trim ();
Content. put (I, str );
Str = "";
}
Return content;
}

Public XSSFWorkbook getXssfWorkbook (){
Return xssfWorkbook;
}

Public void setXssfWorkbook (XSSFWorkbook xssfWorkbook ){
This. xssfWorkbook = xssfWorkbook;
}

Public XSSFSheet getXssfSheet (){
Return xssfSheet;
}

Public void setXssfSheet (XSSFSheet xssfSheet ){
This. xssfSheet = xssfSheet;
}

Public XSSFRow getXssfRow (){
Return xssfRow;
}

Public void setXssfRow (XSSFRow xssfRow ){
This. xssfRow = xssfRow;
}

Public int getColumnNumbers (){
Return columnNumbers;
}

Public void setColumnNumbers (int columnNumbers ){
This. columnNumbers = columnNumbers;
}

Public int getTotalRows (){
Return totalRows;
}

Public void setTotalRows (int totalRows ){
This. totalRows = totalRows;
}

Public static void main (String [] args) throws Exception {
// Construct the XSSFWorkbook object, and pass the strPath to the file path
ExcelParser ep = new ExcelParser ();
InputStream is = new FileInputStream (new File ("D: \ product management template.xlsx "));
// String [] titles = ep. getTitle (is );
Map Map = ep. readExcelContent (is );

For (int I = 1; I <= map. size (); I ++ ){
String row = map. get (I );
String rowArr [] = row. split ("");
// If there are more than three columns in each row
String cell0 = rowArr [0]; // Product Name
String cell1 = rowArr [1]; // Country Code
System. out. println (cell0 + "\ t" + cell1 );
// System. out. println (map. size ());
}

Is. close ();
Is = null;
}
}