Package lizikj.bigwheel.shop.util.excel;
Import Java.io.File;
Import Java.io.FileInputStream;
Import java.io.IOException;
Import Java.io.InputStream;
Import Java.text.DecimalFormat;
Import java.util.ArrayList;
Import java.util.List;
Import lizikj.bigwheel.common.vo.agent.Agent;
Import Org.apache.poi.hssf.usermodel.HSSFWorkbook;
Import Org.apache.poi.ss.usermodel.Cell;
Import Org.apache.poi.ss.usermodel.Row;
Import Org.apache.poi.ss.usermodel.Sheet;
Import Org.apache.poi.ss.usermodel.Workbook;
Import Org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class Readexcelutils {
/** Total number of rows */
private int totalrows = 0;
/** total number of columns */
private int totalcells = 0;
/** Error message */
Private String errorinfo;
/**
* Verify Excel Files
* @param FilePath
* @return
*/
public boolean validateexcel (String filePath) {
/** Check if the file name is empty or if it is in Excel format */
if (FilePath = = NULL | |! ( wdwutil.isexcel2003 (FilePath) | | wdwutil.isexcel2007 (FilePath))) {
errorinfo = "file name is not in Excel format";
return false;
}
/** Check if file exists */
File File = new file (FilePath);
if (file = = NULL | |!file.exists ()) {
ErrorInfo = "File does not exist";
return false;
}
return true;
}
/**
* Read Excel file according to file name
* @param FilePath
* @return
*/
Public list<agent> Read (String filePath) {
list<agent> datalst = new arraylist<agent> ();
InputStream is = null;
try{
/** Verify that the file is valid */
if (!validateexcel (FilePath)) {
System.out.println (errorinfo);
return null;
}
/** determine the type of file, is 2003 or 2007 */
Boolean isExcel2003 = true;
if (wdwutil.isexcel2007 (FilePath)) {
isExcel2003 = false;
}
/** calls the method provided by this class to read from a stream */
File File = new file (FilePath);
is = new FileInputStream (file);
Datalst = Read (is, isExcel2003);
Is.close ();
} catch (Exception ex) {
Ex.printstacktrace ();
}finally {
if (is = null) {
try{
Is.close ();
}catch (IOException e) {
is = null;
E.printstacktrace ();
}
}
}
/** returns the result of the last read */
return datalst;
}
/**
* Read Excel files based on stream
* @param inputstream
* @param isExcel2003
* @return
*/
Public list<agent> Read (InputStream inputstream, Boolean isExcel2003) {
List<agent> datalst = null;
try{
/** How to create a workbook based on version selection */
Workbook wb = null;
if (isExcel2003) {
WB = new Hssfworkbook (InputStream);
}else{
WB = new Xssfworkbook (InputStream);
}
Datalst = read (WB);
}catch (IOException e) {
E.printstacktrace ();
}
return datalst;
}
/**
* Read Data
* @param WB
* @return
*/
Private list<agent> Read (Workbook WB) {
list<agent> datalst = new arraylist<agent> ();
/** get the first shell */
Sheet Sheet = wb.getsheetat (0);
/** get the number of lines in Excel */
This.totalrows = Sheet.getphysicalnumberofrows ();
/** get the number of columns in Excel */
if (this.totalrows >= 1 && sheet.getrow (0)! = null) {
This.totalcells = Sheet.getrow (0). Getphysicalnumberofcells ();
}
Agent agent = null;
/** Loop Excel Line */
for (int r = 1; r < This.totalrows; r++) {
Row Hssfrow = Sheet.getrow (R);
if (Hssfrow = = null) {
Continue
}
Agent = new agent ();
Cell name = Hssfrow.getcell (0);
Cell pname = Hssfrow.getcell (1);
Cell cname = Hssfrow.getcell (2);
Cell rname = Hssfrow.getcell (3);
Cell address = Hssfrow.getcell (4);
Cell contact = Hssfrow.getcell (5);
Cell mobile = Hssfrow.getcell (6);
Cell companyName = Hssfrow.getcell (7);
Agent.setparentagentid (0);
Agent.setagentname (GetValue (name));
Agent.setagentrole ("K1");
Agent.setprovinceid (GetValue (pname));
Agent.setcityid (GetValue (CNAME));
Agent.setregionid (GetValue (rname));
Agent.setcontact (GetValue (contact));
Agent.setaddress (GetValue (address));
Agent.setmobile (GetValue (mobile));
Agent.setcontactname (GetValue (contact));
Agent.setcompanyname (GetValue (companyName));
Datalst.add (agent);
}
return datalst;
}
@SuppressWarnings ("Static-access")
Private String GetValue (Cell Hssfcell) {
DecimalFormat df = new DecimalFormat ("#");
if (hssfcell.getcelltype () = = Hssfcell.cell_type_boolean) {
Returns the value of a Boolean type
Return string.valueof (Hssfcell.getbooleancellvalue ());
} else if (hssfcell.getcelltype () = = Hssfcell.cell_type_numeric) {
Returns the value of a numeric type
Return string.valueof (Df.format (Hssfcell.getnumericcellvalue ()));
} else {
Returns the value of a string type
Return string.valueof (Hssfcell.getstringcellvalue ());
}
}
}
Class Wdwutil {
/**
* is 2003 Excel, return True is 2003
* @param FilePath
* @return
*/
public static Boolean isExcel2003 (String filePath) {
Return filepath.matches ("^.+\\" (? i) (XLS) $ ");
}
/**
* is 2007 Excel, return True is 2007
* @param FilePath
* @return
*/
public static Boolean isExcel2007 (String filePath) {
Return filepath.matches ("^.+\\" (? i) (xlsx) $ ");
}
}
From: http://blog.csdn.net/maxu12345/article/details/47977811
http://blog.csdn.net/mmm333zzz/article/details/7962377
Http://www.cnblogs.com/hongten/p/java_poi_excel.html
Java POI reads the exc file