项目中遇到一个问题,正常运行的项目中,一个excel数据上传,解析数据后生成json文件保存在服务器上,某天突然出现故障,排查后发现是用户数据了超多导致,记录此次解决方案。
当exel的数据量为几万或者十几万时,普通的poi读取即可,但是如果excel数据量为几十万或者几百万时,就会导致读取速度慢,出现读取超时,就需要使用流处理方式解决。
对于excel表格的处理,根据后缀不同处理方式不同,且两者存在区别需要注意:
- HSSFworkbook对象可读取/导出的xls格式的excel
- XSSFworkbook对象可读取/导出的xlsx格式的excel
- xls导出最大行数为65535,xlsx最大行数为104万
- xlsx和xls在数据量相同的情况下xlsx文件更小
1.添加依赖
<properties>
<poi.version>4.0.1</poi.version>
</properties>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.version}</version>
</dependency>
2.常规POI上传方式
@PostMapping(value = "/uploadJsonData")
public ResultBody uploadJsonData(@RequestParam("file") MultipartFile file, String name, String city) {
FileOutputStream fileOutputStream=null;
OutputStreamWriter outputStreamWriter=null;
BufferedWriter writer=null;
try {
if(!file.isEmpty()) {
InputStream inputStream = file.getInputStream();
Workbook book=null;
//2003
if(fileName.matches("^.+\\.(?i)(xls)$")) {
book=new HSSFWorkbook(inputStream);
}
//2007
if(fileName.matches("^.+\\.(?i)(xlsx)$")) {
book = new XSSFWorkbook(inputStream);
}
Sheet sheet = book.getSheetAt(0);
int allRowNum = sheet.getLastRowNum();
if(allRowNum==0) {
return ResultBody.error("导入文件数据为空");
}
SimpleDateFormat dformat = new SimpleDateFormat("yyyy-MM-dd");
List<FlowChart> flowChartList=new ArrayList<>();
for(int i=1;i<=allRowNum;i++) {
Row row = sheet.getRow(i);
if(row!=null) {
//读取数据
Cell cell1 = row.getCell(1);
Cell cell2 = row.getCell(2);
if(cell1==null){
//判断数据是否为空,为空停止本次循环,开始下次循环
break;
}
//定义对象,保存数据
FlowChart flowChart=new FlowChart();
flowChart.setAcquisitionDate(dformat.parse(cell1.toString()));
flowChart.setBrodband(new BigDecimal(cell2.toString()));
flowChartList.add(flowChart);
}
}
//数据保存到新文件
File jsonFile = new File("D:/1.json");
if(!jsonFile.exists()){
jsonFile.createNewFile();
}
fileOutputStream=new FileOutputStream(jsonFile);
outputStreamWriter=new OutputStreamWriter(fileOutputStream,"utf-8");
writer = new BufferedWriter(outputStreamWriter);
writer.write(JSONArray.toJSONString(flowChartList));
writer.flush();
writer.close();
outputStreamWriter.close();
fileOutputStream.close();
return ResultBody.success("导入成功");
}else{
return ResultBody.error("导入失败,文件不存在");
}
} catch (Exception e) {
e.printStackTrace();
return ResultBody.error("导入失败");
}finally {
try{
writer.flush();
writer.close();
outputStreamWriter.close();
fileOutputStream.close();
}catch (Exception e){
e.printStackTrace();
}
}
}
3.超大POI上传方式
@PostMapping(value = "/uploadJsonData")
public ResultBody uploadJsonData(@RequestParam("file") MultipartFile file) {
FileOutputStream fileOutputStream=null;
OutputStreamWriter outputStreamWriter=null;
BufferedWriter writer=null;
try {
if(!file.isEmpty()) {
InputStream inputStream = file.getInputStream();
//1.根据excel报表获取OPCpackage
OPCPackage opcPackage = OPCPackage.open(inputStream);
// 2.创建XSSFReader
XSSFReader xssfReader = new XSSFReader(opcPackage);
// 3.获取SharedStringTable对象
SharedStringsTable sharedStringsTable = xssfReader.getSharedStringsTable();
// 4.获取styleTable对象
StylesTable stylesTable = xssfReader.getStylesTable();
// 5.创建sax xmlReader对象
XMLReader xmlReader = XMLReaderFactory.createXMLReader();
// 6.注册事件驱动处理器
SheetHandler sheetHandler = new SheetHandler();
XSSFSheetXMLHandler xssfSheetXMLHandler = new XSSFSheetXMLHandler(stylesTable, sharedStringsTable, sheetHandler, false);
xmlReader.setContentHandler(xssfSheetXMLHandler);
// 7.逐行读取
XSSFReader.SheetIterator sheetIterator = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
while (sheetIterator.hasNext()) {
InputStream in = sheetIterator.next();
InputSource is = new InputSource(in);
xmlReader.parse(is);
}
//8.读取集合
List<Map<String, String>> employeeList = sheetHandler.getList();
if(employeeList.size()==0) {
return ResultBody.error("导入文件数据为空");
}
//9.循环解析数据
List<FlowChart> flowChartList=new ArrayList<>();
SimpleDateFormat dformat = new SimpleDateFormat("yyyy-MM-dd");
for(int i=0;i<employeeList.size();i++) {
int rownum=i+2;
String dayTime=employeeList.get(i).get("F"+rownum);
String bandwidth=employeeList.get(i).get("I"+rownum);
if(dayTime==null||bandwidth==null){
continue;
}
//保存数据
FlowChart flowChart=new FlowChart();
flowChart.setAcquisitionDate(dformat.parse(dayTime));
flowChart.setBrodband(new BigDecimal(bandwidth));
flowChartList.add(flowChart);
}
//保存新文件
File jsonFile = new File("D:/1.json");
if(!jsonFile.exists()){
jsonFile.createNewFile();
}
fileOutputStream=new FileOutputStream(jsonFile);
outputStreamWriter=new OutputStreamWriter(fileOutputStream,"utf-8");
writer = new BufferedWriter(outputStreamWriter);
writer.write(JSONArray.toJSONString(flowChartList));
writer.flush();
writer.close();
outputStreamWriter.close();
fileOutputStream.close();
return ResultBody.success("导入成功");
}else{
return ResultBody.error("导入失败,文件不存在");
}
} catch (Exception e) {
e.printStackTrace();
return ResultBody.error("导入失败");
}finally {
try{
writer.flush();
writer.close();
outputStreamWriter.close();
fileOutputStream.close();
}catch (Exception e){
e.printStackTrace();
}
}
}