当数据量较大时,使用模板方式进行数据的写入,容易造成内存溢出,这时候就要考虑用SXSSFWorkbook进行大数据的导出,主要实现代码如下:
@Override
@Async
public void exportFpsj(ExportExcelDto dto, Long id) throws BusinessException {
//list数据总量
int fpsjSl=0;
//数据list集合
List<VO> fpsj;
String hdfsFileName;
try {
if (fpsjSl > maxRow) {
ByteArrayOutputStream tempByteOStream;
BufferedOutputStream tempBufferOStream;
ZipOutputStream tempZStream;
ZipEntry tempEntry;
byte[] tempBytes;
tempByteOStream = new ByteArrayOutputStream();
InputStream inputStream;
CheckedOutputStream csum1 = new CheckedOutputStream(tempByteOStream, new Adler32());
tempZStream = new ZipOutputStream(csum1);
tempBufferOStream = new BufferedOutputStream(tempZStream);
int pageSize = fpsjSl / maxRow + 1;
for (int i = 0; i < pageSize; i++) {
//数量合计 所以加1
List<ExportFpsjVO> list = fpsj.subList(i * maxRow, (i + 1) * maxRow > fpsjSl+1 ? fpsjSl+1 : (i + 1) * maxRow);
String fileName = dto.getNsrsbh() + "(" + i + ")" + ".xlsx";
if (i == pageSize - 1) {
inputStream = readExcel(list, 1);
} else {
inputStream = readExcel(list, 0);
}
byte[] buff = new byte[1024];
tempEntry = new ZipEntry(fileName);
tempZStream.putNextEntry(tempEntry);
int len;
while ((len = inputStream.read(buff)) != -1) {
tempZStream.write(buff, 0, len);
}
}
tempBufferOStream.flush();
tempByteOStream.flush();
tempZStream.closeEntry();
tempZStream.close();
tempBytes = tempByteOStream.toByteArray();
InputStream in = new ByteArrayInputStream(tempBytes);
tempByteOStream.close();
tempBufferOStream.close();
hdfsFileName = hdfsPath + UUID.randomUUID() + "/" + dto.getNsrsbh() + ".zip";
HdfsUtil.createFile(in, hdfsFileName);
} else {
InputStream in = readExcel(fpsj, 1);
hdfsFileName = hdfsPath + UUID.randomUUID() + "/" + dto.getNsrsbh() + ".xlsx";
HdfsUtil.createFile(in, hdfsFileName);
}
//创建文件存储路径
ExportFpxxDTO fpxxDTO = new ExportFpxxDTO();
fpxxDTO.setId(id);
fpxxDTO.setFlag(1);
fpxxDTO.setSrc(hdfsFileName);
updateFile(fpxxDTO);
} catch (IOException i) {
ExportFpxxDTO fpxxDTO = new ExportFpxxDTO();
fpxxDTO.setId(id);
fpxxDTO.setFlag(2);
updateFile(fpxxDTO);
}
}
/**
* @param data 数据
* @param flag 是否为最后一个Excel
* @return
*/
public InputStream readExcel(List<ExportFpsjVO> data, int flag) {
try {
ClassPathResource cpr = new ClassPathResource("file/fpxx.xlsx");
InputStream in = cpr.getInputStream();
// URL url = CompanyExportServiceImpl.class.getClassLoader().getResource("file/fpxx.xlsx");
// log.info("文件路径为{}", url.getPath());
XSSFWorkbook workbook = new XSSFWorkbook(in);
SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workbook, 100);
Sheet sheet = sxssfWorkbook.getSheetAt(0);
//设置列宽
sheet.setColumnWidth(1, 4000);
sheet.setColumnWidth(2, 4000);
sheet.setColumnWidth(3, 6000);
sheet.setColumnWidth(4, 8000);
sheet.setColumnWidth(5, 7000);
sheet.setColumnWidth(6, 16000);
sheet.setColumnWidth(7, 15000);
sheet.setColumnWidth(8, 8000);
sheet.setColumnWidth(12, 3000);
sheet.setColumnWidth(13, 3000);
sheet.setColumnWidth(14, 3000);
sheet.setColumnWidth(15, 3000);
sheet.setColumnWidth(16, 3000);
sheet.setColumnWidth(17, 6000);
sheet.setColumnWidth(18, 6000);
sheet.setColumnWidth(19, 14000);
sheet.setColumnWidth(20, 11000);
//标题样式
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 在sheet里创建第一行,参数为行索引(excel的行),可以是0~65535之间的任何一个
Row row = sheet.createRow(0);
// 创建单元格(excel的单元格,参数为列索引,可以是0~255之间的任何一个
Cell cell = row.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 21));
// 设置单元格内容
cell.setCellValue("表头名");
cell.setCellStyle(cellStyle);
//第一行
row = sheet.createRow(1);
cell = row.createCell(1);
cell.setCellValue("列名1");
cell.setCellStyle(cellStyle);
cell = row.createCell(5);
cell.setCellValue("列名1");
cell.setCellStyle(cellStyle);
cell = row.createCell(8);
cell.setCellValue("列名1");
cell.setCellStyle(cellStyle);
cell = row.createCell(17);
cell.setCellValue("列名1");
cell.setCellStyle(cellStyle);
//设置单元格合并
sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 4));
sheet.addMergedRegion(new CellRangeAddress(1, 1, 5, 7));
sheet.addMergedRegion(new CellRangeAddress(1, 1, 8, 16));
sheet.addMergedRegion(new CellRangeAddress(1, 1, 17, 20));
// 生成一个样式
XSSFCellStyle style = workbook.createCellStyle();
// 居中
style.setAlignment(HorizontalAlignment.CENTER);
style.setBorderLeft(BorderStyle.MEDIUM);
style.setLeftBorderColor(IndexedColors.BLACK.index);
style.setBorderBottom(BorderStyle.MEDIUM);
style.setBottomBorderColor(IndexedColors.BLACK.index);
style.setBorderTop(BorderStyle.MEDIUM);
style.setTopBorderColor(IndexedColors.BLACK.index);
style.setBorderRight(BorderStyle.MEDIUM);
style.setRightBorderColor(IndexedColors.BLACK.index);
for (int i = 0; i < data.size() + 1; i++) {
row = sheet.createRow(i + 2);
if (i == 0) {
cell = row.createCell(0);
cell.setCellValue("列名2");
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue("列名2");
cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue("列名2");
cell.setCellStyle(style);
cell = row.createCell(3);
cell.setCellValue("列名2");
cell.setCellStyle(style);
cell = row.createCell(4);
cell.setCellValue("列名2");
cell.setCellStyle(style);
cell = row.createCell(5);
cell.setCellValue("列名2");
cell.setCellStyle(style);
cell = row.createCell(6);
cell.setCellValue("列名2");
cell.setCellStyle(style);
cell = row.createCell(7);
cell.setCellValue("列名2");
cell.setCellStyle(style);
cell = row.createCell(8);
cell.setCellValue("列名2");
cell.setCellStyle(style);
cell = row.createCell(9);
cell.setCellValue("列名2");
cell.setCellStyle(style);
cell = row.createCell(10);
cell.setCellValue("列名2");
cell.setCellStyle(style);
cell = row.createCell(11);
cell.setCellValue("列名2");
cell.setCellStyle(style);
cell = row.createCell(12);
cell.setCellValue("列名2");
cell.setCellStyle(style);
cell = row.createCell(13);
cell.setCellValue("列名2");
cell.setCellStyle(style);
cell = row.createCell(14);
cell.setCellValue("列名2");
cell.setCellStyle(style);
cell = row.createCell(15);
cell.setCellValue("列名2");
cell.setCellStyle(style);
cell = row.createCell(16);
cell.setCellValue("列名2");
cell.setCellStyle(style);
cell = row.createCell(17);
cell.setCellValue("列名2");
cell.setCellStyle(style);
cell = row.createCell(18);
cell.setCellValue("列名2");
cell.setCellStyle(style);
cell = row.createCell(19);
cell.setCellValue("列名2");
cell.setCellStyle(style);
cell = row.createCell(20);
cell.setCellValue("列名2");
cell.setCellStyle(style);
} else {
ExportFpsjVO exportFpsjVO = data.get(i - 1);
if (0 == flag) {
cell = row.createCell(0);
cell.setCellValue(String.valueOf(i));
cell.setCellStyle(style);
} else {
if (i == data.size()) {
cell = row.createCell(0);
cell.setCellValue("合计");
cell.setCellStyle(style);
} else {
cell = row.createCell(0);
cell.setCellValue(String.valueOf(i));
cell.setCellStyle(style);
}
}
cell = row.createCell(1);
cell.setCellValue(exportFpsjVO.getFpdm());
cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue(exportFpsjVO.getFphm());
cell.setCellStyle(style);
cell = row.createCell(3);
cell.setCellValue(exportFpsjVO.getKprq());
cell.setCellStyle(style);
cell = row.createCell(4);
cell.setCellValue(exportFpsjVO.getGfmc());
cell.setCellStyle(style);
cell = row.createCell(5);
cell.setCellValue(exportFpsjVO.getGfsbh());
cell.setCellStyle(style);
cell = row.createCell(6);
cell.setCellValue(exportFpsjVO.getGfdzdh());
cell.setCellStyle(style);
cell = row.createCell(7);
cell.setCellValue(exportFpsjVO.getGfyhzh());
cell.setCellStyle(style);
cell = row.createCell(8);
cell.setCellValue(exportFpsjVO.getHwmc());
cell.setCellStyle(style);
cell = row.createCell(9);
cell.setCellValue(exportFpsjVO.getGgxh());
cell.setCellStyle(style);
cell = row.createCell(10);
cell.setCellValue(exportFpsjVO.getDw());
cell.setCellStyle(style);
cell = row.createCell(11);
cell.setCellValue(exportFpsjVO.getSl());
cell.setCellStyle(style);
cell = row.createCell(12);
cell.setCellValue(exportFpsjVO.getDj());
cell.setCellStyle(style);
cell = row.createCell(13);
cell.setCellValue(exportFpsjVO.getJe());
cell.setCellStyle(style);
cell = row.createCell(14);
cell.setCellValue(exportFpsjVO.getSlv());
cell.setCellStyle(style);
cell = row.createCell(15);
cell.setCellValue(exportFpsjVO.getSe());
cell.setCellStyle(style);
cell = row.createCell(16);
cell.setCellValue(exportFpsjVO.getJshj());
cell.setCellStyle(style);
cell = row.createCell(17);
cell.setCellValue(exportFpsjVO.getXfmc());
cell.setCellStyle(style);
cell = row.createCell(18);
cell.setCellValue(exportFpsjVO.getXfsbh());
cell.setCellStyle(style);
cell = row.createCell(19);
cell.setCellValue(exportFpsjVO.getXfdzdh());
cell.setCellStyle(style);
cell = row.createCell(20);
cell.setCellValue(exportFpsjVO.getXfyhzh());
cell.setCellStyle(style);
}
}
ByteArrayOutputStream os = new ByteArrayOutputStream();
sxssfWorkbook.write(os);
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
os.flush();
os.close();
is.close();
return is;
} catch (Exception i) {
return null;
}
}