import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
public class ExcelUtils {
public static List readExcel(File file, Integer rowCount, Integer columnCount)
throws FileNotFoundException, IOException {
// 根据后缀名称判断excel的版本
String fileName = file.getName();
String extName = fileName.substring(fileName.lastIndexOf("."));
Workbook wb = null;
if (".xls".equals(extName)) {
wb = new HSSFWorkbook(new FileInputStream(file));
} else if (".xlsx".equals(extName)) {
wb = new XSSFWorkbook(new FileInputStream(file));
} else {
// 无效后缀名称,这里之能保证excel的后缀名称,不能保证文件类型正确,不过没关系,在创建Workbook的时候会校验文件格式
throw new IllegalArgumentException("Invalid excel version");
}
// 开始读取数据
List<ExcelSheet> sheetPOs = new ArrayList<>();
// 解析sheet
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Sheet sheet = wb.getSheetAt(i);
List<List<Object>> dataList = new ArrayList<>();
ExcelSheet sheetPO = new ExcelSheet();
sheetPO.setSheetName(sheet.getSheetName());
sheetPO.setDataList(dataList);
int readRowCount = 0;
if (rowCount == null || rowCount > sheet.getPhysicalNumberOfRows()) {
readRowCount = sheet.getPhysicalNumberOfRows();
} else {
readRowCount = rowCount;
}
// 解析sheet 的行
for (int j = sheet.getFirstRowNum(); j < readRowCount; j++) {
Row row = sheet.getRow(j);
if (row == null) {
continue;
}
if (row.getFirstCellNum() < 0) {
continue;
}
int readColumnCount = 0;
if (columnCount == null || columnCount > row.getLastCellNum()) {
readColumnCount = (int) row.getLastCellNum();
} else {
readColumnCount = columnCount;
}
List<Object> rowValue = new LinkedList<Object>();
// 解析sheet 的列
for (int k = 0; k < readColumnCount; k++) {
Cell cell = row.getCell(k);
rowValue.add(getCellValue(wb, cell));
}
dataList.add(rowValue);
}
sheetPOs.add(sheetPO);
}
return sheetPOs;
}
private static Object getCellValue(Workbook wb, Cell cell) {
Object columnValue = null;
if (cell != null) {
DecimalFormat df = new DecimalFormat("0");// 格式化 number
// String
// 字符
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
columnValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
columnValue = df.format(cell.getNumericCellValue());
} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
columnValue = nf.format(cell.getNumericCellValue());
} else {
columnValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
}
break;
case Cell.CELL_TYPE_BOOLEAN:
columnValue = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
columnValue = "";
break;
case Cell.CELL_TYPE_FORMULA:
// 格式单元格
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
evaluator.evaluateFormulaCell(cell);
CellValue cellValue = evaluator.evaluate(cell);
columnValue = cellValue.getNumberValue();
break;
default:
columnValue = cell.toString();
}
}
return columnValue;
}
}