一、读取Excel数据，防止内存溢出
1、pom.xml导入依赖包
<dependency>
<groupId>com.monitorjbl</groupId>
<artifactId>xlsx-streamer</artifactId>
<version>2.1.0</version>
</dependency>
2、核心代码如下：
public void importFinanceCostByExcel(File file) {
InputStream is = null;
//数据库关键是这几步
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
FinanceCostMapper financeCostBatchMapper = sqlSession.getMapper(FinanceCostMapper.class);
int BATCH = 1000;
try {
is = new FileInputStream(file);
Workbook workbook = StreamingReader.builder()
.rowCacheSize(1000)// number of rows to keep in memory (defaults to 10)
.bufferSize(1024) // buffer size to use when reading InputStream to file (defaults to 1024)
.open(is); // InputStream or File for XLSX file (required)
Sheet sheet = workbook.getSheet("成本到箱表格");
logger.info("Import Finance Cost Begin!");
List<FinanceCost> financeCostList = new ArrayList<>();
Date nowDate = new Date();
int i = -1;
for (Row row : sheet) {
i++;
if(i< 1)
continue;
FinanceCost financeCost = this.excelRowToFinanceCost(row);
financeCost.setAcreatedatetime(nowDate);
financeCost.setAlastupdatetime(nowDate);
financeCostBatchMapper.insert(financeCost);
if(i != 0 && i % 1000 == 0) {
logger.info("Import Data : {} " ,i);
//批量新增
sqlSession .commit();
sqlSession.flushStatements();//这里还要研究一下，要不然数据库还是撑不住
}
}
sqlSession.commit();
sqlSession.close();
} catch (FileNotFoundException e1) {
e1.printStackTrace();
} finally {
if(is != null) {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
logger.info("Import Finance cost End!");
}
