Recently, because of the needs of the project, a Java read an example of reading an Excel file is written, there is a lot of works on the date and the formula. When starting, I really cost a lot of work, huh, huh. Later, after I found it, I finally had a little smaller, and I posted it with everyone.
However, there is still a WARING: That is to read Merge data [Warning] Unknown PTG 2D (45) I don't know who has a solution, I will throw a brick first, and I have jade, although I'm coming over.
Package com.zzxy.model;
Import java.io.fileinputstream;
Import java.io.fileoutputstream;
Import java.text.decimalformat;
Import java.util.date;
Import org.apache.poi.hssf.record.formularcord;
Import org.apache.poi.hssf.record.formula.areaptg;
Import org.apache.poi.hssf.usermodel.hssfcell;
Import org.apache.poi.hssf.usermodel.hssfcellstyle;
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.hssf.usermodel.hssfdataFormat;
Import org.apache.poi.hssf.record.calccountRecord;
/ **
*
* Author
*
* Java reads the Excel file
*
* Level of an Excel file: Excel file -> Worksheet -> Row -> cells correspond to POI, for: Workbook-> Sheet-> Row-> Cell
*
* /
Public class poitest {
Public static string outputfile = "c: //temp/test.xls";
Public static string filetoberead = "c: //temp/test.xls";
Public void createexcel () {
Try {
// Create a new Excel workbook
HSSFWORKBOOK WORKBOOK = New HSSFWORKBOOK ();
// Construct a work table in the Excel workbook, named the default
// To create a worksheet that is "benefit indicator", its statement is:
// HSSFSHEET Sheet = Workbook.createsheet ("Benefits Indicators"
HSSFSHEET Sheet = Workbook.createsheet ();
/ / Create a line in the location of index 0 (top row)
HSSFrow Row = Sheet.createrow ((Short) 0);
/ / Create a single element in the position of index 0 (upper left)
HSSfcell Cell = row.createcell ((Short) 0);
// Define the cell is a string type
Cell.SetCellType (hssfcell.cell_type_string); // Enter some content in cell
Cell.setcellValue ("SWEATER");
// New output file stream
FileOutputStream Fout = New FileOutputStream (OutputFile);
// Put the corresponding Excel workbook storage
Workbook.write (fout);
Fout.flush ();
// End, close the file
Fout.close ();
System.out.println ("File Generate ...");
} catch (exception e) {
System.out.Println ("is running xlcreate ():" E);
}
}
/ **
* Read the Excel, traverse each clenching information, and determine if it is a mobile phone number, and display the correct mobile number
*
* Note: 1. SHEET, start with 0 to workbook.getnumberofsheets () - 1
* 2.Row, start with 0 (GetFirstrownum), ending with GetLastrownum
* 3.Cell, starting with 0, endlastcenum, ending, the number of ends, unknown, different lengths, may be as long as the length of the display
* /
Public void readExcel () {
// will be expressed as the number of 1.3922433397E10 to 13922433397
// decimalformat df = new decimalformat ("#");
Try {
// Create a reference to the Excel workbook file
HSSFWORKBOOK WORKBOOK = New HSSFWORKBOOK (New FileInputStream
FiletobeRead));
//System.out.println ("===sheetsnum=== " Workbook.getNumberofsheets ()); // Get the number of Sheet
For (int Numsheets = 0; Numsheets
IF (NULL! = Workbook.getsheetat (Numsheets)) {
Hssfsheet asheet = workbook.getsheetat (numsheets); // get a Sheet
if (! asheet.isdisplayformulas ()) // Judgment is the value of the formula in Asheet
Asheet.SetDisplayFormulas (TRUE);
//System.out.println (" Getfirstrownum asheet.getfirstrownum ());
//System.out.println (" Getlastrownum asheet.getlastrownum ());
For (int rownumofsheet = 0); rownumofsheet <= asheet
.getlastrownum (); rownumofsheet ) {
IF (null! = asheet.Getrow (rownumofsheet)) {hssfrow is = asheet.getrow (rownumofsheet);
//System.out.println (">>> getFirstCellnum <<<" zym ());
//System.out.println (">> getLastcellnum <<< " arow.getlastCellnum ());
For (Short Cellnumofrow = 0; Cellnumofrow <= AROW
.GetlastCellnum (); cellnumofrow ) {
//System.out.println (">>Rownumofsheet ";" "" "
//System.out.println (">>>cellnumofrow<<<" cellnumofrow);
IF (NULL! = Arow.getcell (Cellnumofrow) {
HSSfcell Acell = Arow.getcell (Cellnumofrow);
INT CELLTYPE = Acell.getcellType ();
Switch (CellType) {
Case hssfcell.cell_type_numeric: // numeric
String strCell = String.Valueof (Acell.getNumericCellvalue ());
IF (HSSFDateutil.iscellDateFormatted (ACell))
{
// double sa = acell.getNumericCellValue ();
// Date DD = HSSFDateutil.getjavadate (SA);
// hssfdataformat tt = new hssfdataformat (Workbook);
//dd.tostring ();
System.out.println (Acell.getdateCellValue ());
}
Else
System.out.println (Strcell);
Break;
Case hssfcell.cell_type_string: // String
Strcell = Acell.getstringcellValue ();
System.out.println (Strcell);
Break;
Case hssfcell.cell_type_formula: // Formula
// strcell = acell.getcellformula ();
Strcell = String.Valueof (Acell.getNumericcellValue ());
System.out.println (Strcell);
Break;
Case hssfcell.cell_type_blank: // blank
Strcell = Acell.getstringcellValue ();
System.out.println (Strcell);
Break;
DEFAULT:
System.out.println ("-------------- format reading is incorrect!"); // Other format data
}
}
}
}
}
}
System.out.println ("// Sheet End ///);
}
} catch (Exception E) {system.out.println ("Readexcelerror" E);
}
}
/ / Read the value of the cell specified row and column, pay attention: line, column subscript starts from 0
Public void getSpecial (Short Numrow, Short Numcol) {
Try {
// Create a reference to the Excel workbook file
HSSFWORKBOOK WORKBOOK = New HSSFWORKBOOK (New FileInputStream (FiletobeRead);
IF (null! = Workbook.getsheetat (0)) {
HSSFSHEET Asheet = Workbook.getsheetat (0); // Get a Sheet
IF (NULL! = asheet.getrow) {
HSSFROW AROW = asheet.Getrow (Numrow);
IF (NULL! = Arow.getcell (Numcol) {
HSSfcell Acell = AROW.GETCELL (NUMCOL);
INT CELLTYPE = Acell.getcellType ();
Switch (CellType) {
Case 0: // numeric
String strCell = String.Valueof (Acell.getNumericCellvalue ());
IF (HSSFDateutil.iscellDateFormatted (ACell))
System.out.println ("00000000000000000" acell.getdatecellValue ());
Else
System.out.println (Strcell);
Break;
Case 1: // String
Strcell = Acell.getstringcellValue ();
System.out.println ("111111111111111111" strCell);
Break;
Case 2: // formula
Asheet.SetDisplayFormulas (TRUE);
Strcell = String.Valueof (Acell.getNumericcellValue ());
System.out.println ("2222222222222222222" STRCELL);
Break;
Case 3: // blank
Strcell = Acell.getstringcellValue ();
System.out.println ("33333333" STRCELL);
Break;
DEFAULT:
System.out.println ("-------------- format reading is incorrect!"); // Other format data
}
}
}
}
} catch (exception e) {
System.out.println ("Readexcelerror" E);
}
}
Public static void main (String [] args) {
Poitest POI = New Poitest ();
//poi.createexcel ();
POI.Readexcel ();
POI.GETSPECIAL ((Short) 4, (Short) 4); // The data value of the fifth column of the fifth line
}
}
