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 } }
