Excel读取poi:兼容2003和2007并读取公式
代码
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
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;
import com.github.pagehelper.util.StringUtil;
public class ExcelUtils {
/** 错误信息 */
private static String errorInfo;
public static String getErrorInfo() {
return errorInfo;
}
private static boolean validateExcel(String filePath) {
/** 检查文件名是否为空或者是否是Excel格式的文件 */
if (!filePath.endsWith(".xls") && !filePath.endsWith(".xlsx")) {
errorInfo = "文件名不是excel格式";
return false;
}
/** 检查文件是否存在 */
File file = new File(filePath);
if (filePath == null || file == null || !file.exists()) {
errorInfo = "文件不存在";
return false;
}
return true;
}
public static List<List<String>> read(String filePath) {
List<List<String>> dataLst = new ArrayList<List<String>>();
InputStream is = null;
try {
/** 验证文件是否合法 */
if (!validateExcel(filePath)) {
System.out.println(errorInfo);
return null;
}
/** 判断文件的类型,是2003还是2007 */
boolean isExcel2003 = true;
if (filePath.endsWith(".xlsx")) {
isExcel2003 = false;
}
/** 调用本类提供的根据流读取的方法 */
File file = new File(filePath);
is = new FileInputStream(file);
dataLst = readBook(is, isExcel2003);
is.close();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (is != null) {
try {
is.close();
} catch (IOException e) {
is = null;
e.printStackTrace();
}
}
}
/** 返回最后读取的结果 */
return dataLst;
}
public static List<List<String>> readBook(InputStream inputStream, boolean isExcel2003) {
List<List<String>> dataLst = null;
try {
/** 根据版本选择创建Workbook的方式 */
Workbook wb = null;
if (isExcel2003) {
wb = new HSSFWorkbook(inputStream);
} else {
wb = new XSSFWorkbook(inputStream);
}
dataLst = readExcel(wb);
} catch (IOException e) {
e.printStackTrace();
}
return dataLst;
}
private static List<List<String>> readExcel(Workbook wb) {
List<List<String>> dataLst = new ArrayList<List<String>>();
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
/** 得到第一个shell */
Sheet sheet = wb.getSheetAt(0);
/** 得到Excel的行数 */
int totalRows = sheet.getPhysicalNumberOfRows();
/** 得到Excel的列数 */
int totalCells = 0;
if (totalRows >= 1 && sheet.getRow(0) != null) {
totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
/** 循环Excel的行 不取第一行 */
for (int r = 0; r < totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
List<String> rowLst = new ArrayList<String>();
/** 循环Excel的列 */
for (int c = 0; c <= totalCells; c++) {
Cell cell = row.getCell(c);
String cellValue = "";
if (null != cell) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
if (date != null) {
cellValue = new SimpleDateFormat("yyyy-MM-dd")
.format(date);
} else {
cellValue = "";
}
} else {
cellValue = cell.getNumericCellValue() + "";
}
break;
case HSSFCell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
cellValue = cell.getBooleanCellValue() + "";
break;
case HSSFCell.CELL_TYPE_FORMULA:
try {
CellValue value;
value = evaluator.evaluate(cell);
switch (value.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
cellValue = value.getBooleanValue() + "";
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
Date date = cell.getDateCellValue();
cellValue = format.format(date);
} else {
cellValue = value.getNumberValue() + "";
}
break;
case Cell.CELL_TYPE_STRING:
cellValue = value.getStringValue();
break;
case Cell.CELL_TYPE_BLANK:
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR:
cellValue = "";
break;
case Cell.CELL_TYPE_FORMULA:
cellValue = "";
break;
}
} catch (Exception e) {
cellValue = cell.getStringCellValue().toString();
cell.getCellFormula();
}
break;
case HSSFCell.CELL_TYPE_BLANK:
break;
case HSSFCell.CELL_TYPE_ERROR:
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
}
if (StringUtil.isNotEmpty(cellValue) ) {
rowLst.add(cellValue);
}
}
if (rowLst != null && rowLst.size() > 0) {
/** 保存第r行的第c列 */
dataLst.add(rowLst);
}
}
return dataLst;
}
}