不依赖模型.
获取的结果按列存储,有类型和长度,方便拼接自定义sql
/**
* poi读取excel流(2G内存环境下,大于5M有OOM风险)
*
* @param is 输入流
* @param filename 文件名称
* @return 返回表格数据
* @Describe 从输入流读取文件, 解析内容
*/
public static List<ExcelSheetBean> readExcelBean(InputStream is, String filename) {
List<ExcelSheetBean> excel = Lists.newArrayList();
// ByteArrayOutputStream baos = null;
try {
// baos = getOutputStream(is);
// is = new ByteArrayInputStream(baos.toByteArray());
Workbook workbook;
// 根据不同类型初始化
if (filename.endsWith(xls)) {
workbook = new HSSFWorkbook(is);
} else if (filename.endsWith(xlsx)) {
workbook = new XSSFWorkbook(is);
} else {
throw new IOException("读取失败");
}
// 遍历sheet
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
Sheet sheetAt = workbook.getSheetAt(i);
ExcelSheetBean _sheet = new ExcelSheetBean();
_sheet.setTableName(filename);
_sheet.setSheetName(sheetAt.getSheetName());
List<ExcelColBean> colValues = Lists.newLinkedList();
int physicalNumberOfRows = sheetAt.getPhysicalNumberOfRows();
// 遍历所有行
for (int j = 0; j < physicalNumberOfRows; j++) {
Row row = sheetAt.getRow(j);
if (row == null) {
continue;
}
// 第一行不做类型判断,初始化list大小
if (j == 0) {
int physicalNumberOfCells = row.getPhysicalNumberOfCells();
for (int k = 0; k < physicalNumberOfCells; k++) {
Cell cell = row.getCell(k);
ExcelColBean excelColBean = new ExcelColBean();
excelColBean.getColValues().add(cell.toString());
colValues.add(excelColBean);
}
continue;
}
// 遍历单元格
for (int k = 0; k < colValues.size(); k++) {
// 设置列类型
Cell cell = row.getCell(k);
if (cell == null || StringUtil.isEmpty(cell.toString()) || cell.getCellTypeEnum() == BLANK) {
colValues.get(k).getColValues().add("");
continue;
} else if (colValues.get(k).getType() == null) {
colValues.get(k).setType(getCellType(cell));
} else if (colValues.get(k).getType() != ExcelCellType.STRING && getCellType(cell) != colValues.get(k).getType()) {
colValues.get(k).setType(ExcelCellType.STRING);
}
// 根据类型赋值
switch (getCellType(cell)) {
case NUMBER:
Double value;
try {
value = cell.getNumericCellValue();
} catch (Exception e) {
value = Double.valueOf(cell.getStringCellValue());
}
boolean isInt = false;
if (Math.round(value) == value) {
isInt = true;
}
// BigDecimal处理精度问题
if (isInt && value < Long.MAX_VALUE) {
colValues.get(k).getColValues().add(new BigDecimal(value.toString()).toPlainString());
Integer len = Long.valueOf(Math.round(value)).toString().length();
if (colValues.get(k).getType() == ExcelCellType.NUMBER
&& len > colValues.get(k).getPreQuantity()) {
colValues.get(k).setPreQuantity(len);
}
} else {
colValues.get(k).getColValues().add(new BigDecimal(cell.toString()).toPlainString());
if (colValues.get(k).getType() == ExcelCellType.NUMBER) {
String[] split = new BigDecimal(cell.toString()).toPlainString().split("\\.");
if (split[0].length() > colValues.get(k).getPreQuantity()) {
colValues.get(k).setPreQuantity(split[0].length());
}
if (!isInt && split.length > 1 && split[1].length() > colValues.get(k).getSufQuantity()) {
colValues.get(k).setSufQuantity(split[1].length());
}
}
}
break;
case STRING:
if (cell.toString().contains(("'"))) {
colValues.get(k).getColValues().add(cell.toString().replace("'", "\\'"));
} else {
colValues.get(k).getColValues().add(cell.toString());
}
if (cell.toString().length() > colValues.get(k).getStrQuantity()) {
colValues.get(k).setStrQuantity(cell.toString().length());
}
break;
case DATE:
SimpleDateFormat sdf = new SimpleDateFormat(DEFAULTDATAFORMAT);
try {
colValues.get(k).getColValues().add(sdf.format(getJavaDate(cell.getNumericCellValue())));
} catch (IllegalStateException e) {
colValues.get(k).getColValues().add(sdf.format(getDate(cell.toString())));
}
break;
}
}
}
_sheet.setCols(colValues);
excel.add(_sheet);
}
} catch (IOException e) {
e.printStackTrace();
} finally {
// closeInputStream(baos);
}
return excel;
}
下面是自定义类:
/**
* excel 表
*/
@Data
@NoArgsConstructor
public static class ExcelSheetBean {
private String tableName;
private String sheetName;
private List<ExcelColBean> cols = Lists.newArrayList();
}
/**
* excel 列
*/
@Data
@NoArgsConstructor
public static class ExcelColBean {
// 类型
private ExcelCellType type;
// 整数最大位数
private Integer preQuantity = 0;
// 小数最大位数
private Integer sufQuantity = 0;
// 字符最大值
private Integer strQuantity = 255;
// 值集
private List<Object> colValues = Lists.newArrayList();
}
/**
* 单元格格式
*/
public enum ExcelCellType {
// 数字
NUMBER,
// 时间
DATE,
// 字符
STRING
}
