1、需求是客户想要对每日核对的账务金额进行一个数据导出功能,但是每一个账户最终核对的金额是由多个金额组合而来,每个账户组成的金额列个数和key的中文名都不一致,而且数据是存在数据库的一个大字段中。数据存在数据库中如下图格式。
2、客户需要导出Excel的格式如下
3、最开始我是采用easy Excel的poi接口进行一个导出,因为最后一个列是动态的存在数据库一个字段中,用easy Excel可以不创建对象的写,但是和续写功能无法一起套用(个人技术能力原因),后来选择了SXSSFWorkbook进行导出,自定义每一行数据的格式。
{
LambdaQueryWrapper<InnerBean> lqw = new LambdaQueryWrapper<>();
lqw.ge(InnerBean::getId, 1);
List<InnerBean> innerBeans = this.baseMapper.selectList(lqw);
//输出路径
String filePath = "/Users/hzf-olo/Downloads/Idea/JAVA/Test.xlsx";
//创建SXSSFWorkbook对象,这样表示SXSSFWorkbook只会保留100条数据在内存中,其它的数据都会写到磁盘里,这样的话占用的内存就会不多
SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(getXSSFWorkbook(filePath),100);
//获取第一个Sheet页
SXSSFSheet sheet = sxssfWorkbook.getSheetAt(0);
CellStyle style = sxssfWorkbook.createCellStyle();
//设置边框
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
//行对象
SXSSFRow row = null;
//输出流
BufferedOutputStream outputStream = null;
try {
outputStream = new BufferedOutputStream(new FileOutputStream(filePath));
//行数
int rowNum = 0;
//列数
int columnNum = 0;
for (InnerBean innerBean : innerBeans) {
//创建第0行,0列,设置值为内部户账号
row = sheet.createRow(rowNum++);
SXSSFCell rowCell0 = row.createCell(columnNum);
rowCell0.setCellValue("内部户账号");
rowCell0.setCellStyle(style);
sheet.setColumnWidth(columnNum++, 5120);
//0行,1列设置为内部户户名
SXSSFCell rowCell1 = row.createCell(columnNum);
rowCell1.setCellValue("内部户户名");
rowCell1.setCellStyle(style);
sheet.setColumnWidth(columnNum++, 5120);
String[] splitRowData = innerBean.getComplexList().split(",");
for (String rowData : splitRowData) {
String[] data = rowData.split("=");
SXSSFCell rowCellN = row.createCell(columnNum);
rowCellN.setCellValue(data[0]);
rowCellN.setCellStyle(style);
sheet.setColumnWidth(columnNum++, 5120);
}
//重置列数值
columnNum = 0;
row = sheet.createRow(rowNum++);
SXSSFCell cell0 = row.createCell(columnNum++);
cell0.setCellValue(innerBean.getInnerAcc());
cell0.setCellStyle(style);
SXSSFCell cell1 = row.createCell(columnNum++);
cell1.setCellValue(innerBean.getInnerAccName());
cell1.setCellStyle(style);
for (String rowData : splitRowData) {
String[] data = rowData.split("=");
SXSSFCell cellN = row.createCell(columnNum++);
cellN.setCellValue(data[1]);
cellN.setCellStyle(style);
}
//创建一个新行,用于区分两条数据
columnNum = 0;
row = sheet.createRow(rowNum++);
row.createCell(0).setCellValue("");
}
//进行一个写入数据
sxssfWorkbook.write(outputStream);
outputStream.flush();
//释放workbook所占用的全部资源
sxssfWorkbook.dispose();
} catch (Exception e) {
e.printStackTrace();
} finally {
if(outputStream!=null) {
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
/**
* 先建立一个XSSFWorkbook对象
*/
public static XSSFWorkbook getXSSFWorkbook(String filePath) {
XSSFWorkbook workbook = null;
BufferedOutputStream outputStream = null;
try {
File fileXlsxPath = new File(filePath);
outputStream = new BufferedOutputStream(new FileOutputStream(fileXlsxPath));
workbook = new XSSFWorkbook();
workbook.createSheet("Data");
workbook.write(outputStream);
} catch (Exception e) {
e.printStackTrace();
}finally {
if(outputStream!=null) {
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return workbook;
}