如果excel里面的数据量太多,内存溢出,容易卡死,用下面的工具
引入依赖包
<!-- 读取大量excel数据时使用-->
<dependency>
<groupId>com.monitorjbl</groupId>
<artifactId>xlsx-streamer</artifactId>
//如果这个版本是2.1.0的,下面两个就选4.0.1的。如果这个是1.2.0的 下面2个就选择3.15版本的
<!-- <version>2.1.0</version>-->
<version>1.2.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<!-- <version>4.0.1</version>-->
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<!-- <version>4.0.1</version>-->
<version>3.15</version>
</dependency>
//这个可能会报错,找不到依赖的时候,引入这个
<dependency>
<groupId>xml-apis</groupId>
<artifactId>xml-apis</artifactId>
<version>1.4.01</version>
</dependency>
String fileName = "d:/opt/测试.xlsx";
FileInputStream in = new FileInputStream(fileName);
Workbook wk = StreamingReader.builder()
//缓存到内存中的行数
.rowCacheSize(1000)
//读取资源时,缓存到内存的字节大小
.bufferSize(8192)
.open(in);
Sheet sheet = wk.getSheetAt(0);
List<ReportFileDTO> listFile = new ArrayList<>();
for (Row row : sheet) {
//第0行表头跳过
int rowNum = row.getRowNum();
if (0 == rowNum) {
//判断模板是否标准 包含关键字段
if (!ExcelUtil.getStringValue(row.getCell(0)).contains("记录") ||
!ExcelUtil.getStringValue(row.getCell(1)).contains("时间") ||
!ExcelUtil.getStringValue(row.getCell(2)).contains("案件号") ||
!ExcelUtil.getStringValue(row.getCell(3)).contains("客户号") ||
!ExcelUtil.getStringValue(row.getCell(4)).contains("对象") ||
!ExcelUtil.getStringValue(row.getCell(5)).contains("关系") ||
!ExcelUtil.getStringValue(row.getCell(6)).contains("号码")) {
logger.info("模板不标准:{},fileName:{},{},{},{},{},{},{},{}", trackId, fileName, ExcelUtil.getStringValue(row.getCell(0)),
ExcelUtil.getStringValue(row.getCell(1)), ExcelUtil.getStringValue(row.getCell(2)), ExcelUtil.getStringValue(row.getCell(3)), ExcelUtil.getStringValue(row.getCell(4)), ExcelUtil.getStringValue(row.getCell(5)), ExcelUtil.getStringValue(row.getCell(6)));
break;
}
continue;
}
ReportFileDTO db = new ReportFileDTO();
db.setEventHistoriesId(UUID.randomUUID().toString().replaceAll("-", ""));
db.setCallId(ExcelUtil.getStringValue(row.getCell(0)));
//时间 年-月-日 时:分:秒
try {
//yyyy-MM-dd HH:mm:ss
Date date = kz.greetgo.gcoll.common.utils.DateUtil.parseYYYY_MM_dd_HHmmss(ExcelUtil.getStringValue(row.getCell(1)));
db.setInsertTime(date);
db.setFollowDate(date);
} catch (Exception e) {
try {
//yyyy/MM/dd HH:mm:ss
Date date = kz.greetgo.gcoll.common.utils.DateUtil.parseYYYYMMDD_HHmmss(ExcelUtil.getStringValue(row.getCell(1)));
db.setInsertTime(date);
db.setFollowDate(date);
} catch (Exception ex) {
//时间格式
Cell cell = row.getCell(1);
Date dateCellValue = cell.getDateCellValue();
db.setInsertTime(dateCellValue);
db.setFollowDate(dateCellValue);
}
}
//导入时间
String year = businessDate.substring(0, 4);
String month = businessDate.substring(4, 6);
String ymd = year + "-" + month + "-01" + " 00:00:00";
db.setImportDate(kz.greetgo.gcoll.common.utils.DateUtil.parseYYYY_MM_dd_HHmmss(ymd));
db.setCustomerCode(ExcelUtil.getStringValue(row.getCell(2)));
db.setCustomerNo(ExcelUtil.getStringValue(row.getCell(3)));
//联系对象(姓名)
db.setContactName(ExcelUtil.getStringValue(row.getCell(4)));
String stringValue = ExcelUtil.getStringValue(row.getCell(5));
db.setIsSelf(stringValue.contains("本人") ? "Y" : "N");
//联络号码
db.setContactNumber(ExcelUtil.getStringValue(row.getCell(6)));
db.setRemark(ExcelUtil.getStringValue(row.getCell(7)));
db.setCollectionUnit(ftpTemplateVO.getCode());
//导入批次 机构号+businessDate(202405)
db.setBatchNo(ftpTemplateVO.getCode() + businessDate);
//导入者 system
db.setImportPerson("system");
//操作类型
db.setActionCode("manualCall");
//区分数据来源, 1:历史数据,2:委外机构
db.setSourceType("2");
listFile.add(db);
}
//分批入库
if (listFile.size() > 0) {
logger.info("机构号code:{},size:{},trackId:{}", ftpTemplateVO.getCode(), listFile.size(), trackId);
//分批入库 Lists.partition(listFile, 300); 每次入300条数据
List<List<ReportFileDTO>> partition = Lists.partition(listFile, 300);
for (int i = 0; i < partition.size(); i++) {
List<ReportFileDTO> list = partition.get(i);
reportRegister.get().batchSaveEventHistoryData(list);
}
}
}
/**
* 获取单元格的值的字符串
*
* @param cell 单元格对象
* @return cell单元格的值的字符串
*/
public static String getStringValue(org.apache.poi.ss.usermodel.Cell cell) {
if (cell == null) {
return null;
}
CellType cellType = CellType.forInt(cell.getCellType());
switch (cellType) {
case STRING:
return cell.getStringCellValue();
case NUMERIC:
double value = cell.getNumericCellValue();
return String.valueOf(Math.round(value));
case BOOLEAN:
return String.valueOf(cell.getBooleanCellValue());
default:
return null;
}
}