Apache POI processing Excel documents

Apache POI processing Excel documents

Currently, there are two popular excel Document Processing Methods: POI and JXL. Heavyweight POI Advantages and Disadvantages: Suitable for advanced features such as formulas and macros that have professional requirements on the details of excel documents. The disadvantage is that the operations are relatively cumbersome and non-pure java-compiled racks, cross-platform performance needs to be enhanced. Lightweight JXL Advantages and Disadvantages: Jxl is a pure javaAPI with superior cross-platform performance and relatively simple operations. Its disadvantage is that it does not support some advanced functions of excel documents, but can meet daily needs. Here we will introduce the basic use of POI.

1. First import the relevant rack package,

Pay attention to the JDK version of your development project. Download different POI versions based on the corresponding JDK version.

2. The ExcelReader helper class can process xls and xlsx files. The readExcelTitle (InputStream is) method reads the file title, that is, the first line of the file, and the readExcelContent (InputStream is) method reads the file content:

Import java. io. IOException;
Import java. io. InputStream;
Import java. text. DecimalFormat;
Import java. text. SimpleDateFormat;
Import java. util. Date;
Import java. util. LinkedHashMap;
Import java. util. Map;

Import org. apache. log4j. Logger;
Import org. apache. poi. ss. usermodel. Cell;
Import org. apache. poi. ss. usermodel. DateUtil;
Import org. apache. poi. ss. usermodel. Row;
Import org. apache. poi. ss. usermodel. Sheet;
Import org. apache. poi. ss. usermodel. Workbook;
Import org. apache. poi. ss. usermodel. WorkbookFactory;

/**
* Excel worksheet operations
*/
Public class ExcelReader {
Private static DecimalFormat df = new DecimalFormat ("0 ");
Private final static Logger log = Logger. getLogger (ExcelReader. class );
Private Workbook wb = null;
Private Sheet sheet = null;
Private Row row = null;

/**
* Read the content of the Excel table header.
* @ Param InputStream
* @ Return String array of the header content
* @ Throws IOException
*/
Public String [] readExcelTitle (InputStream is) throws Exception {
Try {
Wb = WorkbookFactory. create (is );
} Catch (IOException e ){
Log. error ("An error occurred while reading the Excel table Header", e );
Throw e;
}
Sheet = wb. getSheetAt (0 );
Row = sheet. getRow (0 );
// Total number of titles
Int colNum = row. getPhysicalNumberOfCells ();
String [] title = new String [colNum];
For (int I = 0; I <colNum; I ++ ){
Title [I] = getCellFormatValue (row. getCell (I ));
}
Log.info ("the content of the Excel table header is read ");
Return title;
}

/**
* Reading Excel Data
* @ Param InputStream
* @ Return Map the Map object containing the cell data content
* @ Throws IOException
*/
Public Map <Integer, String> readExcelContent (InputStream is) throws Exception {
Map <Integer, String> content = new LinkedHashMap <Integer, String> ();
String str = "";
Try {
Wb = WorkbookFactory. create (is );
} Catch (IOException e ){
Log. error ("reading Excel data content", e );
Throw e;
}
Sheet = wb. getSheetAt (0 );
// Obtain 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 "-". In the future, use the String replace () method to restore the data.
// You can also set the data of each cell to the attribute of a javabean.
// Str + = getStringCellValue (row. getCell (short) j). trim () +
//"-";
If (row! = Null ){
Str + = getCellFormatValue (row. getCell (j). trim () + ",";
} Else {
Str + = "" + ",";
}
J ++;
}
Content. put (I, str. substring (0, str. length ()-1 ));
Str = "";
}
Log.info ("reading Excel Data ");
Return content;
}

/**
* Set Data Based on Cell type
* @ Param cell
* @ Return
*/
Private String getCellFormatValue (Cell cell ){
String cellvalue = "";
If (cell! = Null ){
// Determine the Type of the current Cell
Switch (cell. getCellType ()){
// If the current Cell Type is NUMERIC
Case Cell. CELL_TYPE_NUMERIC:
Case Cell. CELL_TYPE_FORMULA :{
// Determine whether the current cell is Date
If (DateUtil. isCellDateFormatted (cell )){
// If it is of the Date type, convert it to the 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 and minute seconds: 2011-10-12
Date 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 (df. format (cell. getNumericCellValue ()));
}
Break;
}
// If the current Cell Type is STRIN
Case Cell. CELL_TYPE_STRING:
// Obtain the current Cell string
Cellvalue = cell. getRichStringCellValue (). getString ();
Break;
// Default Cell value
Default:
Cellvalue = "";
}
} Else {
Cellvalue = "";
}
Return cellvalue;

}
}

Simple instance code. The exception handling mechanism should be added in actual use:
12345678910111213 FileInputStream is = new FileInputStream (file );
ExcelReader excelReader = new ExcelReader ();
String [] title = excelReader. readExcelTitle (is); // read the file title (not the file name, but the first line of the file)
For (String str: title ){
System. out. println (str );
}
Is. close ();
Is = new FileInputStream (file );
Map <Integer, String> map = excelReader. readExcelContent (is); // read the file content
For (int I = 1; I <= map. size (); I ++ ){
System. out. println (map. get (I ));
}
Is. close ();

Related:

How to export an EXCEL file using POI

POI details: click here
POI: click here

This article permanently updates the link address: