通用
需要poi-*,xmlbean-XXX.jar。没有xmlbean对xlsx解析不正常。在下载的poi包里有。
目前公式、小数解析正常,公式的日期解析不正常。
public String[][] readFromInputStream(InputStream is,int index) throws EncryptedDocumentException, InvalidFormatException, IOException{
Workbook wb=WorkbookFactory.create(is);
Sheet sheet=wb.getSheetAt(index);
String[][] returnValue=null;
evaluator=wb.getCreationHelper().createFormulaEvaluator();
if(sheet!=null){
int row=sheet.getLastRowNum()-sheet.getFirstRowNum();
int col=sheet.getRow(0).getLastCellNum()-sheet.getRow(0).getFirstCellNum();
returnValue=new String[row][col];
for(int x=0;x<row;x++){
for(int y=0;y<col;y++)
{
returnValue[x][y]=getCellStringValue(sheet.getRow(x).getCell(y));
}
}
}
return returnValue;
}
private String getCellStringValue(Cell c)
{
String returnValue = null;
switch(c.getCellType()){
case Cell.CELL_TYPE_BLANK:
returnValue="";
break;
case Cell.CELL_TYPE_BOOLEAN:
returnValue=Boolean.toString(c.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
returnValue="格子错误";
break;
case Cell.CELL_TYPE_FORMULA:
returnValue=stringFromFormulaCell(c);
break;
case Cell.CELL_TYPE_NUMERIC:
returnValue=stringFromNumericCell(c);
break;
case Cell.CELL_TYPE_STRING:
returnValue=c.getStringCellValue();
break;
default:
break;
}
System.out.println(returnValue);
return returnValue;
}
/*
* 获取公式的值
*/
private String stringFromFormulaCell(Cell c){
String value=null;
CellValue cv=evaluator.evaluate(c);
switch (cv.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
value=Boolean.toString(cv.getBooleanValue());
break;
case Cell.CELL_TYPE_NUMERIC:
value=stringFromNumericDouble(cv.getNumberValue());
break;
case Cell.CELL_TYPE_STRING:
value=cv.getStringValue();
break;
case Cell.CELL_TYPE_BLANK:
value="";
break;
case Cell.CELL_TYPE_ERROR:
value="错误!";
break;
// CELL_TYPE_FORMULA will never happen
case Cell.CELL_TYPE_FORMULA:
break;
}
return value;
}
/*
* 获取数的值
*/
private String stringFromNumericCell(Cell c){
String value=null;
if (DateUtil.isCellDateFormatted(c)) {
//如果是date类型则 ,获取该cell的date值
Date date = DateUtil.getJavaDate(c.getNumericCellValue());
SimpleDateFormat format = new SimpleDateFormat("yyyyMMdd");
value = format.format(date);
}else{
//纯数字
value=stringFromNumericDouble(c.getNumericCellValue());
}
return value;
}
/*
* 小数的转化
*/
private String stringFromNumericDouble(double cv){
String value=null;
BigDecimal big=new BigDecimal(cv);
value = big.toString();
//解决1234.0 去掉后面的.0
if(null!=value&&!"".equals(value.trim())){
String[] item = value.split("[.]");
if(1<item.length){
if("0".equals(item[1])){
value=item[0];
}else{
value= big.setScale(2, BigDecimal.ROUND_HALF_DOWN).toString();
}
}
}
return value;
}