public static List<String[]> read(InputStream inputStream) throws Exception {
List<String[]> result = new ArrayList<String[]>();
try {
Workbook wb = create(inputStream);
// for (int sheetCount = 0; sheetCount < wb.getNumberOfSheets(); sheetCount++) {
Sheet sheet = wb.getSheetAt(0);
//新加
//sheet.setForceFormulaRecalculation(true);
if (sheet.getSheetName() != null
&& !"".equals(sheet.getSheetName().trim())) {
int rows = sheet.getPhysicalNumberOfRows();
if (rows <= 1)
throw new Exception("Excel表单中没数据!");
Row tabHeaderInExcel = sheet.getRow(0);
if (tabHeaderInExcel == null)
throw new Exception("Excel表单中没有找到表头!");
int cells = tabHeaderInExcel.getPhysicalNumberOfCells();
for (int r = 1; r < rows; r++) {
Row row = sheet.getRow(r);
if (row == null)
continue;
String[] excelCols = new String[cells];
for (int c = 0; c < cells; c++) {
Cell cell = row.getCell(c);
if (cell == null)
continue;
Object value = null;
int type = cell.getCellType();
switch (type) {
case Cell.CELL_TYPE_NUMERIC:
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
value = simpleDateFormat.format(cell.getDateCellValue());
break;
// m月d日
}
else if (cell.getCellStyle().getDataFormat() == 58) {
value = simpleDateFormat.format(cell.getDateCellValue());
break;
// yyyy-MM-dd
}else if(cell.getCellStyle().getDataFormat() == 14) {
value = simpleDateFormat.format(cell.getDateCellValue());
break;
// yyyy年m月d日
}else if(cell.getCellStyle().getDataFormat() == 31) {
value = simpleDateFormat.format(cell.getDateCellValue());
break;
// yyyy年m月
}else if(cell.getCellStyle().getDataFormat() == 57) {
value = simpleDateFormat.format(cell.getDateCellValue());
break;
// HH:mm
}else if(cell.getCellStyle().getDataFormat() == 20) {
value = simpleDateFormat.format(cell.getDateCellValue());
break;
// h时mm分
}else if(cell.getCellStyle().getDataFormat() == 32) {
value = simpleDateFormat.format(cell.getDateCellValue());
break;
}else {
value = cell.getNumericCellValue();
//这里处理 数字的科学计算法
if(value.toString().contains("E")){
DecimalFormat bdf=new DecimalFormat("#");
value = bdf.format(value);
}
break;
}
case Cell.CELL_TYPE_STRING:
value = cell.getStringCellValue().trim();
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_ERROR:
value = "";
case Cell.CELL_TYPE_FORMULA:
try{//获取excel单元格中的计算公式(这里获取的是excel单元格中计算出来的值)
value=String.valueOf(cell.getNumericCellValue());
}catch(IllegalStateException e){
value=String.valueOf(cell.getRichStringCellValue());
}
break;
case Cell.CELL_TYPE_BLANK:
value = "";
default:
continue;
}
if (value == null)
continue;
excelCols[c] = value.toString();
}
result.add(excelCols);
}
}
// }
return result;
} catch (IOException e) {
throw new Exception(e.getMessage());
} catch (InvalidFormatException e) {
throw new Exception(e.getMessage());
}
}
读取excel辅助工具类
最新推荐文章于 2023-02-01 10:24:40 发布