完美兼容excel2003 和excel2007的读取，处理了所有excel所有的类型，依赖包如下：
poi-3.10-FNAL.jar
poi-ooxml-3.10-FNAL.jar
poi-ooxml-schemas-3.10-FNAL.jar
xmlbeans-2.6.0.jar
dom4j-1.6.1.jar
不多说了，直接上代码。
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date; import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
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 Utils { /**
* 兼容2003 or 2007，不是根据后缀xls or xlsx
* Creates the appropriate HSSFWorkbook / XSSFWorkbook from
* the given InputStream.
* Your input stream MUST either support mark/reset, or
* be wrapped as a {@link PushbackInputStream}!
*/
public static Workbook create(InputStream inp) throws IOException, InvalidFormatException {
// If clearly doesn't do mark/reset, wrap up
if(! inp.markSupported()) {
inp = new PushbackInputStream(inp, 8);
} if(POIFSFileSystem.hasPOIFSHeader(inp)) {
return new HSSFWorkbook(inp);
}
if(POIXMLDocument.hasOOXMLHeader(inp)) {
return new XSSFWorkbook(OPCPackage.open(inp));
}
throw new IllegalArgumentException("Your InputStream was neither an OLE2 stream, nor an OOXML stream");
} public static void main(String[] args) {
try {
InputStream stream = new FileInputStream("c:/1.xlsx");
Workbook wb = create(stream);
Sheet sheet = wb.getSheetAt(0);
// 行数(从0开始,相当于最后一行的索引),列数
int count_row=sheet.getLastRowNum();
int count_cell=sheet.getRow(0).getPhysicalNumberOfCells();
System.out.println(count_row + " + " + count_cell);
String[][] str=new String[count_row][count_cell];
Cell cell = null;
Row row=null;
for (int i = 0; i < count_row; i++) {
for (int j = 0; j < count_cell; j++) {
row=sheet.getRow(i+1);
cell=row.getCell(j);
String obj_content="";
if (cell == null) {
continue;
}
System.out.println(cell.getCellType() + " " + cell.getCellStyle().getDataFormat() + " " + cell.getCellStyle().getDataFormatString());
// + cell.getCellFormula() + " " + cell.getCachedFormulaResultType() if(cell.getCellType()==cell.CELL_TYPE_BLANK) {
obj_content="";
}else if(cell.getCellType()==cell.CELL_TYPE_NUMERIC) { /**
* yyyy-MM-dd----- 14
yyyy年m月d日--- 31
yyyy年m月------- 57
m月d日 ---------- 58
HH:mm----------- 20
h时mm分 ------- 32
* Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
*/
System.out.println(HSSFDateUtil.isCellDateFormatted(cell));
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
SimpleDateFormat dateFormat = null;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("m/d/yy")) {
dateFormat = new SimpleDateFormat("yyyy-MM-dd");
} else if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm:ss")) {
dateFormat = new SimpleDateFormat("HH:mm:ss");
}
obj_content = dateFormat.format(date);
System.out.println(date + " " + dateFormat.format(date));
} else {
obj_content=(int)cell.getNumericCellValue()+"";
}
} else if(cell.getCellType()==cell.CELL_TYPE_STRING) {
obj_content=cell.getStringCellValue();
} else if (cell.getCellType()==cell.CELL_TYPE_FORMULA ) {
System.out.println("============is fuc" + cell.getCellFormula());
} else if (cell.getCellType()==cell.CELL_TYPE_BOOLEAN) {
obj_content = String.valueOf(cell.getBooleanCellValue());
}
else if(cell.getCellType()==cell.CELL_TYPE_ERROR) { } str[i][j]=obj_content; }
}
System.out.println(Arrays.deepToString(str));
} catch (InvalidFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}