/*
* Created on 2004-11-5
*
* Reading Excel files in Java
*
*/
Package readxls;
Import java. Io. fileinputstream;
Import java. Io. fileoutputstream;
Import java. Text. decimalformat;
Import org. Apache. Poi. hssf. usermodel. hssfcell;
Import org. Apache. Poi. hssf. usermodel. hssfrow;
Import org. Apache. Poi. hssf. usermodel. hssfsheet;
Import org. Apache. Poi. hssf. usermodel. hssfworkbook;
/**
*
* @ Author shiwt
*
* Reading Excel files in Java
*
* Level of an Excel file: Excel file-> worksheet-> row-> cell corresponds to poi, which is: Workbook-> Sheet-> row-> Cell
*
*/
Public class poitest {
Public static string outputfile = "E: // test // tels.xls ";
Public static string filetoberead = "E: // test // tels.xls ";
Public void createexcel (){
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 "benefit indicator", the statement is:
// Hssfsheet sheet = Workbook. createsheet ("benefit indicator ");
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 ("sweater ");
// Create an output file stream
Fileoutputstream fout = new fileoutputstream (outputfile );
// Save the corresponding Excel Workbook
Workbook. Write (fout );
Fout. Flush ();
// The operation ends. close the file.
Fout. Close ();
System. Out. println ("file generation ...");
} Catch (exception e ){
System. Out. println ("xlcreate ():" + E );
}
}
/**
*
* Read the Excel file, traverse each cell to obtain the information, determine whether it is a mobile phone number, and display the correct mobile phone number.
*
*
* Note: 1. Sheet starts with 0 and ends 2.rowwith Workbook. getnumberofsheets ()-1,
* Start with 0 (getfirstrownum) and end with getlastrownum 3. cell,
* Start with 0 (getfirstcellnum) and end with getlastcellnum. The number of ended records may be too long due to different display lengths.
*
*/
Public void readexcel (){
// Convert the phone number expressed as 1.3922433397e10 to 13922433397, which is not necessarily the best conversion method.
Decimalformat df = new decimalformat ("#");
Try {
// Create a reference to an Excel Workbook File
Hssfworkbook workbook = new hssfworkbook (New fileinputstream (
Filetoberead ));
// System. Out
//. Println ("=== sheetsnum =" + Workbook. getnumberofsheets (); // obtain the number of sheets
For (INT numsheets = 0; numsheets <Workbook. getnumberofsheets (); numsheets ++ ){
If (null! = Workbook. getsheetat (numsheets )){
Hssfsheet asheet = Workbook. getsheetat (numsheets); // obtain a sheet
// 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 Arow = asheet. getrow (rownumofsheet );
// System. Out. println (">>> getfirstcellnum <" +
// Arow. getfirstcellnum ());
// System. Out. println (">>> getlastcellnum <" +
// Arow. getlastcellnum ());
For (short cellnumofrow = 0; cellnumofrow <= Arow
. Getlastcellnum (); cellnumofrow ++ ){
If (null! = Arow. getcell (cellnumofrow )){
Hssfcell acell = Arow. getcell (cellnumofrow );
Int celltype = acell. getcelltype ();
// System. Out. println (celltype );
Switch (celltype ){
Case 0: // numeric
String strcell = DF. Format (acell
. Getnumericcellvalue ());
System. Out. println (strcell );
Break;
Case 1: // string
Strcell = acell. getstringcellvalue ();
System. Out. println (strcell );
Break;
Default:
// System. Out. println ("the format is not correct and cannot be read"); // data in other formats
}
}
}
}
}
}
}
} Catch (exception e ){
System. Out. println ("readexcelerror" + E );
}
}
Public static void main (string [] ARGs ){
Poitest poi = new poitest ();
// POI. createexcel ();
Poi. readexcel ();
}
}