Excel solution for Java operations

Recently, I used the Excel Import and Export function. The main difficulty is how to operate Excel documents in Java. Now we will introduce how to use Apache poi to Operate Excel.

1. Preparation: Import jar packages related to Apache POI. the poi web site is http://poi.apache.org /.

2. Create an Excel document:

Example 1 demonstrates how to use the Jakarta poi API to create an Excel document.

Example 1 program is as follows:
Import org. Apache. Poi. hssf. usermodel. hssfworkbook;
Import org. Apache. Poi. hssf. usermodel. hssfsheet;
Import org. Apache. Poi. hssf. usermodel. hssfrow;
Import org. Apache. Poi. hssf. usermodel. hssfcell;
Import java. Io. fileoutputstream;
Public class createexcel {

/** The location where the Excel file is to be stored. Assume that the file is in the test directory of the E disk */

Public static string outputfile = "E:/test/model.xls ";
Public static void main (string ARGs []) {

Try {

// Create a new Excel Workbook
Hssfworkbook workbook = new hssfworkbook ();

// Create a worksheet in an Excel worksheet named the default value
// To create a worksheet named "model", the statement is:
// Hssfsheet sheet = Workbook. createsheet ("model ");

Hssfsheet sheet = Workbook. createsheet ();

// Create a row (the top row) at index 0)
Hssfrow ROW = sheet. createrow (short) 0 );

// Create a cell at index 0 (upper left)
Hssfcell cell = row. createcell (short) 0 );

// Define cells as strings
Cell. setcelltype (hssfcell. cell_type_string );

// Enter some content in the cell
Cell. setcellvalue ("added value ");

// Create an output file stream
Fileoutputstream fileout = new fileoutputstream (outputfile );

// Save the corresponding Excel Workbook
Workbook. Write (fileout );
Fileout. Flush ();

// The operation ends. close the file.
Fileout. Close ();
System. Out. println ("file generation ...");

} Catch (exception e ){
System. Out. println ("xlcreate ():" + E );
}
}
}

 

2. Read data in the Excel document:

Example 2 demonstrates how to read data from an Excel document. Assume that an Excel file named model.xls is under the e-disk test directory.

Example 2:

Import org. Apache. Poi. hssf. usermodel. hssfworkbook;
Import org. Apache. Poi. hssf. usermodel. hssfsheet;
Import org. Apache. Poi. hssf. usermodel. hssfrow;
Import org. Apache. Poi. hssf. usermodel. hssfcell;
Import java. Io. fileinputstream;
Public class readexcel {

/** Storage location of Excel files. Note that it is a forward slash */
Public static string filepath = "E:/test/model.xls ";
Public static void main (string ARGs []) {
Try {
// Create a reference to an Excel Workbook File
Hssfworkbook workbook = new hssfworkbook (New fileinputstream (filepath ));

// Create a reference to the worksheet.
// In this example, reference by name (let's assume that the table has the default name "sheet1 ")
Hssfsheet sheet = Workbook. getsheet ("sheet1 ");

// You can also use getsheetat (INT index) to reference data by index,
// In the Excel document, the default index of the first worksheet is 0,
// The statement is: hssfsheet sheet = Workbook. getsheetat (0 );

// Read the upper left Unit
Hssfrow ROW = sheet. getrow (0 );
Hssfcell cell = row. getcell (short) 0 );

// Output unit content. Cell. getstringcellvalue () is used to obtain the value of the unit.
System. Out. println ("upper left Unit:" + cell. getstringcellvalue ());
} Catch (exception e ){
System. Out. println ("xlread ():" + E );
}
}
}

 

3. Set the cell format:

Here, we will only introduce some statements related to format settings. We assume that the workbook is a reference to a workbook. In Java, the first step is to create and set the font and cell format, and then apply these formats:

 

3.1 create a font and set it to red or bold:
Hssffont font = Workbook. createfont ();
Font. setcolor (hssffont. color_red );
Font. setboldweight (hssffont. boldweight_bold );

 

3.2 Create a format
Hssfcellstyle cellstyle = Workbook. createcellstyle ();
Cellstyle. setfont (font );

 

3.3 application format

Hssfcell cell = row. createcell (short) 0 );
Cell. setcellstyle (cellstyle );
Cell. setcelltype (hssfcell. cell_type_string );
Cell. setcellvalue ("title ");

4. Type of Excel file cells in poi
When reading the value of each cell, you can use the getcelltype method to obtain the type of the current cell. There are 6 types of cells in Excel, as shown below.
1) cell_type_blank: NULL
2) cell_type_boolean: Boolean
3) cell_type_error: Error
4) cell_type_formula: formula type
5) cell_type_string: string type
6) cell_type_numeric: numeric type
If the cell type is cell_type_numeric, You need to further judge the data format of the cell, because it may be of the date type, and the date type in Excel is also stored in a double type number. The date in Excel indicates the number of days between the current time and January 1, January 1, 1900. Therefore, you need to call the iscelldateformatted method of hssfdateutil to determine whether the data format of the cell is of the Excel date type. If yes, call the getdatecellvalue method to return a date of the Java type.

Therefore, when reading data from cells in Excel, you must determine the cell format type. This is what I wrote. refer:
Private object getcelldata (cell, formulaevaluator formula ){
If (cell = NULL ){
Return NULL;
}
Switch (cell. getcelltype ()){
Case cell. cell_type_string:
Return cell. getrichstringcellvalue (). getstring ();
Case cell. cell_type_numeric:
If (dateutil. iscelldateformatted (cell )){
Return cell. getdatecellvalue ();
} Else {
Return cell. getnumericcellvalue ();
}
Case cell. cell_type_boolean:
Return cell. getbooleancellvalue ();
Case cell. cell_type_formula:
Return formula. Evaluate (cell). getnumbervalue ();
Default:
Return NULL;
}
}