Java POI reads Excel files

Poi-3.9-20121203.jar

Poi-ooxml-3.9-20121203.jar

Poi-ooxml-schemas-3.9-20121203.jar

Stax-api-1.0.1.jar

Xmlbeans-2.3.0.jar

The above several jar package is indispensable, sometimes there will be classnotfound and other errors, because the jar is missing or incompatible reasons, the above jar package has been tested, can be used.

Read the Excel file code as follows:

public class Excelutils {
Provides an interface for reading Excel files externally
public static list<list<object>> readexcel (file file) throws IOException {
String fName = File.getname ();
String extension = Fname.lastindexof (".") = =-1? "": FName
. substring (Fname.lastindexof (".") + 1);
if ("xls". Equals (extension)) {//2003
System.err.println ("Read excel2003 file contents");
return Read2003excel (file);
} else if ("xlsx". Equals (extension)) {//2007
System.err.println ("Read excel2007 file contents");
return Read2007excel (file);
} else {
throw new IOException ("Unsupported file type:" + extension);
}
}

/**
* Read 2003excel
*
* @param file
* @return
*/
private static list<list<object>> read2003excel (file file)
Throws IOException {
list<list<object>> dataList = new arraylist<list<object>> ();
Hssfworkbook wb = new Hssfworkbook (new FileInputStream (file));
Hssfsheet sheet = wb.getsheetat (0);
Hssfrow row = null;
Hssfcell cell = null;
Object val = null;
DecimalFormat df = new DecimalFormat ("0");//Format number
SimpleDateFormat SDF = new SimpleDateFormat ("Yyyy-mm-dd HH:mm:ss");//Format Date string
for (int i = Sheet.getfirstrownum (); I < sheet
. Getphysicalnumberofrows (); i++) {
row = Sheet.getrow (i);
if (row = = null) {
Continue
}
List<object> objlist = new arraylist<object> ();
for (int j = Row.getfirstcellnum (); J < Row.getlastcellnum (); j + +) {
Cell = Row.getcell (j);
if (cell = = null) {
val = null;
Objlist.add (Val);
Continue
}
Switch (Cell.getcelltype ()) {
Case hssfcell.cell_type_string:
val = Cell.getstringcellvalue ();
Break
Case Hssfcell.cell_type_numeric:
if ("@". Equals (Cell.getcellstyle (). getdataformatstring ())) {
val = Df.format (Cell.getnumericcellvalue ());
} else if ("General". Equals (Cell.getcellstyle ()
. getdataformatstring ())) {
val = Df.format (Cell.getnumericcellvalue ());
} else {
val = Sdf.format (hssfdateutil.getjavadate (cell
. Getnumericcellvalue ()));
}
Break
Case Hssfcell.cell_type_boolean:
val = Cell.getbooleancellvalue ();
Break
Case Hssfcell.cell_type_blank:
val = "";
Break
Default
val = cell.tostring ();
Break
}
Objlist.add (Val);
}
Datalist.add (objlist);
}
return dataList;
}

/**
* Read the Excel table header
*
* @param file
* @return
* @throws IOException
*/
public static string[] Readexcelhead (file file) throws IOException {
Hssfworkbook wb = new Hssfworkbook (new FileInputStream (file));
Hssfsheet sheet = wb.getsheetat (0);
Hssfrow row = null;
Hssfcell cell = null;
row = Sheet.getrow (0);
string[] Buff = new String[row.getlastcellnum ()];
for (int i = Row.getfirstcellnum (); I < Row.getlastcellnum (); i++) {
Cell = Row.getcell (i);
Buff[i] = Cell.getstringcellvalue ();
}
return buff;
}

/**
* Read 2007excel
*
* @param file
* @return
*/

private static list<list<object>> read2007excel (file file)
Throws IOException {
list<list<object>> dataList = new arraylist<list<object>> ();
Xssfworkbook XWB = new Xssfworkbook (new FileInputStream (file));
Xssfsheet sheet = xwb.getsheetat (0);
Xssfrow row = null;
Xssfcell cell = null;
Object val = null;
DecimalFormat df = new DecimalFormat ("0");//Format number
SimpleDateFormat SDF = new SimpleDateFormat ("Yyyy-mm-dd HH:mm:ss");//Format Date string
for (int i = Sheet.getfirstrownum (); I < sheet
. Getphysicalnumberofrows (); i++) {
row = Sheet.getrow (i);
if (row = = null) {
Continue
}
List<object> objlist = new arraylist<object> ();
for (int j = Row.getfirstcellnum (); J < Row.getlastcellnum (); j + +) {
Cell = Row.getcell (j);
if (cell = = null) {
val = null;
Objlist.add (Val);
Continue
}
Switch (Cell.getcelltype ()) {
Case xssfcell.cell_type_string:
val = Cell.getstringcellvalue ();
Break
Case Xssfcell.cell_type_numeric:
if ("@". Equals (Cell.getcellstyle (). getdataformatstring ())) {
val = Df.format (Cell.getnumericcellvalue ());
} else if ("General". Equals (Cell.getcellstyle ()
. getdataformatstring ())) {
val = Df.format (Cell.getnumericcellvalue ());
} else {
val = Sdf.format (hssfdateutil.getjavadate (cell
. Getnumericcellvalue ()));
}
Break
Case Xssfcell.cell_type_boolean:
val = Cell.getbooleancellvalue ();
Break
Case Xssfcell.cell_type_blank:
val = "";
Break
Default
val = cell.tostring ();
Break
}
Objlist.add (Val);
}
Datalist.add (objlist);
}
return dataList;
}
}

Java POI reads Excel files