前言
今天项目上要求要导出一个账单报表，记录一下如何使用POI导出exl文件。导出用到的方法主要是xssf。
一、创建导出接口
@GetMapping("/v1.0/organ/exportBillList")
@ResponseBody
public ResponseBean exportEbdOrgan(HttpServletResponse response,
@RequestParam(value = "centerId", defaultValue = "") String centerId,
@RequestParam(value = "organId", defaultValue = "") String organId,
@RequestParam(value = "startMonth") String startMonth,
@RequestParam(value = "endMonth") String endMonth){
return billReportService.exportEbdOrgan(response,centerId,organId,startMonth,endMonth);
}
二、创建导出方法
1.账单导出方法
代码如下：
此处用了两个封装好的方法
getBillHead()获取动态表头
getBillData()获取动态账单数据
public ResponseBean exportEbdOrgan(HttpServletResponse response,String centerId, String organId, String startMonth, String endMonth){
ResponseBean responseBean = new ResponseBean();
responseBean.setCode(ResponseConstant.SUCCESS_CODE);
responseBean.setMessage(ResponseConstant.SUCCESS_MESSAGE);
try{
//解析月份
//入参2020-11、2021-07 ==》11、12、1、2、3、4、5、6、7
List<String> months = monthUtil.getMonthWithoutZero(startMonth,endMonth);
if(months.size()>12){
responseBean.setCode(ResponseConstant.FAILED_CODE);
responseBean.setMessage("输入月份间隔超过12个月！");
return responseBean;
}else if(months.size()<1){
responseBean.setCode(ResponseConstant.FAILED_CODE);
responseBean.setMessage("输入月份间隔小于1个月！");
return responseBean;
}
//获取账单日期
//2020-11、2020-12、2021-01、2021-02、2021-03、2021-04、2021-05、2021-06、2021-07
List<String> yearAndMonths = monthUtil.getYearAndMonth(startMonth,endMonth);
//动态总列数
int columnNum = months.size() + 3;
//动态表头
ColumnsNameVoEum columnsNameVoEum = new ColumnsNameVoEum();
ColumnsNameVo columnsNameVo = getBillHead(columnsNameVoEum,months);
//动态数据
BillListDto billListDto = new BillListDto();
billListDto.setBillMonthList(yearAndMonths);
billListDto.setCenterId(centerId);
billListDto.setOrganId(organId);
List<BillReportDto> billReport = billReportMapper.getBillList(billListDto);
List<BillReportVo> billReportVos = getBillData(billReport,yearAndMonths);
if(billReportVos == null || billReportVos.size() == 0){
responseBean.setCode(ResponseBeanConst.FAILED_CODE);
responseBean.setMessage("未查询到账单数据！");
return responseBean;
}
//获取数据集合对象
PageInfo<BillReportDto> pageInfo = new PageInfo<>(billReport);
//单位：万元，租户数：54个，总合计：1992.98万元
long organNum = pageInfo.getTotal();
String allTotal = String.format("%.2f",
(billReport.stream().mapToDouble(BillReportDto::getSum).sum())/10000);
Map<Object, String> params = new HashMap<>();
//单位+租户数+总合计
params.put("combineMsg", "单位：万元，租户数：" + organNum + "个，总合计："+allTotal+"万元");
//账单账期
params.put("billMonth", "账期："+startMonth+"至"+endMonth);
//获取response流
ServletOutputStream out = response.getOutputStream();
try {
//租户账单报表-20211123
String filename = "租户账单报表-"+ DateUtil.getDateString(new Date()).replace("-","")+".xlsx";
response.setContentType("application/vnd.ms-excel;charset=utf-8");
//生成的xlsx名字
response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(filename, "UTF-8"));
//跨域
response.setHeader("Access-Control-Allow-Origin", "*");
//网页缓存
response.setHeader("Cache-Control", "no-cache");
} catch (UnsupportedEncodingException e1) {
e1.printStackTrace();
}
// 导出excel XSSF
billOrganExportExcelService.getWorkbook(billReportVos, columnsNameVo,
columnNum, params, out);
} catch (Exception e) {
log.error("导出账单excel出现错误:{}", e.getMessage());
responseBean.setCode(ResponseBeanConst.FAILED_CODE);
responseBean.setMessage("导出账单excel出现错误：" + e);
}
return responseBean;
}
2.导出工具类
代码如下：有部分样式直接写在了此方法中，还有一部分单独写了方法进行调用。
public void getWorkbook(List<BillReportVo> reportVos, ColumnsNameVo columnsNameVo, int columnNum,
Map<Object, String> params, ServletOutputStream out) throws Exception {
try {
// 创建XSSFWorkbook，一个XSSFWorkbook对应一个Excel文件
XSSFWorkbook workbook = new XSSFWorkbook();
// 在workbook中添加一个sheet,对应Excel文件中的sheet
XSSFSheet sheet = workbook.createSheet("账单汇总表");
// 设置表头，即每个列的列名
String[] title = new String[columnNum];
title[0] = columnsNameVo.getCenterName();
title[1] = columnsNameVo.getOrganName();
if(columnsNameVo.getMonth01() != null){
title[2] = columnsNameVo.getMonth01();
}
...
if(columnsNameVo.getMonth12() != null){
title[13] = columnsNameVo.getMonth12();
}
title[columnNum - 1] = "合计";
//第一行表名 row行，cell列
// 1.创建一个合并单元格
CellRangeAddress regionRow00 = new CellRangeAddress(0, 0, 0, columnNum);
sheet.addMergedRegion(regionRow00);
// 2.设置合并单元格内容
Cell cellRow00 = sheet.createRow(0).createCell(0);
cellRow00.setCellValue("租户账单报表");
// 3.设置表名样式
setBorderStyle(sheet, regionRow00, workbook, cellRow00);
//第二行统计数据展示
// 1.创建一个合并单元格
CellRangeAddress regionRow01Cell00 = new CellRangeAddress(1, 1, 0, 2);
CellRangeAddress regionRow01Cell01 = new CellRangeAddress(1, 1, 3, columnNum);
sheet.addMergedRegion(regionRow01Cell00);
sheet.addMergedRegion(regionRow01Cell01);
// 2.设置合并单元格内容
// (1.前3列内容 账期
Cell cellRow01Cell00 = sheet.createRow(1).createCell(0);
cellRow01Cell00.setCellValue(params.get("billMonth"));
// (2.后几列内容 金额单位总和
Cell cellRow01Cell01 = sheet.getRow(1).createCell(3);
cellRow01Cell01.setCellValue(params.get("combineMsg"));
// 3.设置样式
// (1.设置账期样式
XSSFCellStyle cellStyleR01C00 = workbook.createCellStyle();
XSSFFont font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 12);
cellStyleR01C00.setFont(font);
cellStyleR01C00.setAlignment(HorizontalAlignment.LEFT);
cellStyleR01C00.setVerticalAlignment(VerticalAlignment.CENTER);
cellRow01Cell00.setCellStyle(cellStyleR01C00);
// (2.设置金额单位总和样式
XSSFCellStyle cellStyleR01C01 = workbook.createCellStyle();
cellStyleR01C01.setFont(font);
cellStyleR01C01.setAlignment(HorizontalAlignment.RIGHT);
cellStyleR01C01.setVerticalAlignment(VerticalAlignment.CENTER);
cellRow01Cell01.setCellStyle(cellStyleR01C01);
//最后一行 制表时间
// 1.创建一个合并单元格
CellRangeAddress regionRowLast = new CellRangeAddress(reportVos.size() + 3, reportVos.size() + 3, 0, columnNum);
sheet.addMergedRegion(regionRowLast);
// 2.设置合并单元格内容
Cell cellRowLast = sheet.createRow(reportVos.size() + 3).createCell(0);
SimpleDateFormat sdf = new SimpleDateFormat( " yyyy年MM月dd日" );
cellRowLast.setCellValue("制表时间：" + sdf.format(new Date()));
// 3.设置字体格式
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFont(font);
cellStyle.setAlignment(HorizontalAlignment.RIGHT);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellRowLast.setCellStyle(cellStyle);
//第三行 表头
XSSFRow row = sheet.createRow(2);
// 此处创建一个序号列
row.createCell(0).setCellValue("序号");
setHeadStyle(workbook, row.getCell(0));
// 将列名写入
for (int i = 0; i < title.length; i++) {
// 给列写入数据,创建单元格，写入数据
row.createCell(i+1).setCellValue(title[i]);
Cell cell = row.getCell(i+1);
setHeadStyle(workbook, cell);
}
// 写入表格数据
for (int i = 0; i < reportVos.size(); i++) {
// 创建行
row = sheet.createRow(i+3);
// 序号第1列数据
row.createCell(0).setCellValue(i+1);
setBodyStyle(workbook,row.getCell(0));
// 第2列数据
row.createCell(1).setCellValue(reportVos.get(i).getCenterName());
setBodyStyle(workbook,row.getCell(1));
// 第3列数据
row.createCell(2).setCellValue(reportVos.get(i).getOrganName());
setBodyStyle(workbook,row.getCell(2));
// 月份账单
if(reportVos.get(i).getMonth01() != null ){
row.createCell(3)
.setCellValue(new Double(reportVos.get(i).getMonth01()));
setBodyStyle(workbook,row.getCell(3));
}
...
if(reportVos.get(i).getMonth12() != null ){
row.createCell(14)
.setCellValue(new Double(reportVos.get(i).getMonth12()));
setBodyStyle(workbook,row.getCell(14));
}
// 合计
row.createCell(columnNum)
.setCellValue(new Double(reportVos.get(i).getSum()));
setBodyStyle(workbook,row.getCell(columnNum));
}
// 设置单元格宽度
int curColWidth = 0;
for (int i = 0; i <= title.length; i++) {
// 列自适应宽度
sheet.autoSizeColumn(i, true);
// 为每一列设置一个最小值，方便中文显示
curColWidth = sheet.getColumnWidth(i);
if(curColWidth<2500){
sheet.setColumnWidth(i, 2500);
}
}
try {
workbook.write(out);
out.flush();
out.close();
} catch (Exception e) {
log.error("导出账单写入/关闭流出现错误:{}", e.getMessage());
}
} catch (Exception e) {
log.error("导出账单出现异常:{}", e.getMessage(),e);
}
}
3.导出样式设计
代码如下：
/**
* 设置表格名称样式
* @author ZYQ
* */
private static void setBorderStyle(Sheet sheet, CellRangeAddress region,XSSFWorkbook workbook, Cell cell) {
CellStyle cellStyle = workbook.createCellStyle();
XSSFFont font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 22);
font.setBold(true);
cellStyle.setFont(font);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cell.setCellStyle(cellStyle);
// 合并单元格左边框样式
RegionUtil.setBorderLeft(1, region, sheet);
RegionUtil.setLeftBorderColor(IndexedColors.BLACK.getIndex(), region, sheet);
// 合并单元格上边框样式
RegionUtil.setBorderTop(1, region, sheet);
RegionUtil.setTopBorderColor(IndexedColors.BLACK.getIndex(), region, sheet);
// 合并单元格右边框样式
RegionUtil.setBorderRight(1, region, sheet);
RegionUtil.setRightBorderColor(IndexedColors.BLACK.getIndex(), region, sheet);
// 合并单元格下边框样式
RegionUtil.setBorderBottom(1, region, sheet);
RegionUtil.setBottomBorderColor(IndexedColors.BLACK.getIndex(), region, sheet);
}
/**
* 设置表头样式
* @author ZYQ
*/
private static void setHeadStyle(XSSFWorkbook workbook, Cell cell){
XSSFCellStyle cellStyle = workbook.createCellStyle();
XSSFFont font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 11);
font.setBold(true);
cellStyle.setFont(font);
cellStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(214, 220, 228)));// 设置背景色
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//填充模式
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cell.setCellStyle(cellStyle);
}
/**
* 设置表格样式
* @author ZYQ
*/
private static void setBodyStyle(XSSFWorkbook workbook, Cell cell){
XSSFCellStyle cellStyle = workbook.createCellStyle();
XSSFFont font = workbook.createFont();
font.setFontName("等线");
font.setFontHeightInPoints((short) 11);
cellStyle.setFont(font);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
if(cell.getColumnIndex() != 0 && cell.getColumnIndex() != 1 && cell.getColumnIndex() != 2){
//设置 列 数据格式-保留两位小数
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
}
cell.setCellStyle(cellStyle);
}
3.导出exl效果图
总结
数据导出为exl并不难，稍微在网上找找就都有了。要导出一个动态的表单，只要对应的行和列规定好即可。
1553
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
