1、创建XSSFWorkbook对象（也就是excel文档对象）
2、通过XSSFWorkbook对象创建sheet对象（也就是excel中的sheet）
3、通过sheet对象创建XSSFRow对象（row行对象）
4、通过XSSFRow对象创建列cell并set值（列名）
5、处理数据循环表头(业务需要)
service impl层
@Override
public StatisticalReportResultDTO getStatisticalExcelReport(StatisticalReportQuery query) {
// 查询条件
StatisticalReportPageVO queryVO = StatisticalReportConverter.queryToDO(query);
LambdaQueryWrapper<StatisticalReportPageVO> queryWrapper = new LambdaQueryWrapper<StatisticalReportPageVO>();
// 年月 转换为 时间段
if (query.getStatisticalPeriodStartTime() != null && query.getStatisticalPeriodEndTime() != null) {
queryVO.setStatisticalPeriodStartTime(query.getStatisticalPeriodStartTime());
queryVO.setStatisticalPeriodEndTime(query.getStatisticalPeriodEndTime());
}
if (StringUtils.isNotBlank(query.getTaxId())) {
String[] taxIds = query.getTaxId().split(",");
queryVO.setTaxId(taxIds);
}
queryWrapper.setEntity(queryVO);
// 列表
List<StatisticalReportDTO> list = statisticalReportMapper.getStatisticalExcelReport(queryWrapper);
StatisticalReportResultDTO statisticalReportResultDTO = new StatisticalReportResultDTO();
StatisticalReportDTO total = new StatisticalReportDTO();
if (CollectionUtils.isNotEmpty(list)) {
BigDecimal zero = BigDecimal.ZERO;
BigDecimal amount = zero, districtRetainedAmount = zero, districtIncentiveAmountReceivable = zero, districtRewardAmountActuallyReceived = zero, tnIncome = zero,
intermediaryIncome = zero, enterpriseIncome = zero;
for (StatisticalReportDTO vo : list) {
//比例回显
if (vo.getDistrictRetentionRatio() != null) {
BigDecimal districtRetentionRatio = vo.getDistrictRetentionRatio();
BigDecimal percentage = new BigDecimal(100);
BigDecimal newDistrictRetentionRatio = districtRetentionRatio.multiply(percentage).setScale(2, RoundingMode.HALF_UP);//保留两位小数;
vo.setDistrictRetentionRatio(newDistrictRetentionRatio);
}
if (vo.getDistrictRewardRatio() != null) {
BigDecimal districtRewardRatio = vo.getDistrictRewardRatio();
BigDecimal percentage = new BigDecimal(100);
BigDecimal newDistrictRewardRatio = districtRewardRatio.multiply(percentage).setScale(2, RoundingMode.HALF_UP);//保留两位小数;
vo.setDistrictRewardRatio(newDistrictRewardRatio);
}
// 计算总合计
amount = amount.add(vo.getAmount() != null ? vo.getAmount() : zero);
districtRetainedAmount = districtRetainedAmount.add(vo.getDistrictRetainedAmount() != null ? vo.getDistrictRetainedAmount() : zero);
districtIncentiveAmountReceivable = districtIncentiveAmountReceivable
.add(vo.getDistrictIncentiveAmountReceivable() != null ? vo.getDistrictIncentiveAmountReceivable() : zero);
districtRewardAmountActuallyReceived = districtRewardAmountActuallyReceived
.add(vo.getDistrictRewardAmountActuallyReceived() != null ? vo.getDistrictRewardAmountActuallyReceived() : zero);
tnIncome = tnIncome.add(vo.getTnIncome() != null ? vo.getTnIncome() : zero);
intermediaryIncome = intermediaryIncome.add(vo.getIntermediaryIncome() != null ? vo.getIntermediaryIncome() : zero);
enterpriseIncome = enterpriseIncome.add(vo.getEnterpriseIncome() != null ? vo.getEnterpriseIncome() : zero);
}
total.setAmount(amount);
total.setDistrictRewardAmountActuallyReceived(districtRewardAmountActuallyReceived);
total.setDistrictRetainedAmount(districtRetainedAmount);
total.setDistrictIncentiveAmountReceivable(districtIncentiveAmountReceivable);
total.setTnIncome(tnIncome);
total.setIntermediaryIncome(intermediaryIncome);
total.setEnterpriseIncome(enterpriseIncome);
}
statisticalReportResultDTO.setList(list);
statisticalReportResultDTO.setTotal(total);
return statisticalReportResultDTO;
}controller 层
/**
* @description excel导出数据
* @version 1.0
* @author jlk
* @date 2022年01月27日
* @param query
*/
@ApiOperation(value = "获取Excel导出数据 作者：jlk 日期： 2022年01月27日")
@GetMapping(value = "/getExcelExport")
public void getExcelExport(HttpServletRequest request, HttpServletResponse response, StatisticalReportQuery query) {
StatisticalReportResultDTO statisticalReportResultDTO = statisticalReportService.getStatisticalExcelReport(query);
List<StatisticalReportConditionDTO> statisticalReportConditionDTOList = statisticalReportService.getExcelReportCondition(query);
String fileName = "总部经济-统计报表" + System.currentTimeMillis();
//调用poi导出Excel
ExcelUtil.poiExportExcel(statisticalReportResultDTO, statisticalReportConditionDTOList, query, fileName, response);
}ExcelUtil工具类
@SuppressWarnings("resource")
public static void poiExportExcel(StatisticalReportResultDTO statisticalReportResultDTO, List<StatisticalReportConditionDTO> statisticalReportConditionDTOList,
StatisticalReportQuery query, String fileName, HttpServletResponse response) {
try {
List<StatisticalReportDTO> statisticalReportList = statisticalReportResultDTO.getList();
XSSFWorkbook wb = new XSSFWorkbook();
XSSFRow row = null;
XSSFCell cell = null;
// 建立新的sheet对象（excel的表单） 并设置sheet名字
XSSFSheet sheet = wb.createSheet("统计报表");
sheet.setDefaultRowHeightInPoints(20);
sheet.setDefaultColumnWidth(12);
//----------------标题样式--------------------------------
XSSFCellStyle titleStyle = wb.createCellStyle(); //标题样式
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
Font ztFont = wb.createFont();
ztFont.setItalic(false); // 设置字体为斜体字
//ztFont.setColor(Font.COLOR_NORMAL); // 将字体设置为“红色”
ztFont.setFontHeightInPoints((short) 16); // 将字体大小设置为18px
ztFont.setFontName("宋体"); // 将“宋体”字体应用到当前单元格上
ztFont.setBold(true); //加粗
//ztFont.setUnderline(Font.U_DOUBLE);// 添加（Font.U_SINGLE单条下划线/Font.U_DOUBLE双条下划线）
//ztFont.setStrikeout(true);// 是否添加删除线
titleStyle.setFont(ztFont);
//------------------------------------------------------------
//----------------二级标题格样式----------------------------------
XSSFCellStyle titleStyle2 = wb.createCellStyle(); //表格样式
titleStyle2.setAlignment(HorizontalAlignment.CENTER);
titleStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
titleStyle2.setBorderBottom(BorderStyle.THIN);//下边框
titleStyle2.setBorderLeft(BorderStyle.THIN);//左边框
titleStyle2.setBorderRight(BorderStyle.THIN);//右边框
titleStyle2.setBorderTop(BorderStyle.THIN);//上边框
Font ztFont2 = wb.createFont();
ztFont2.setItalic(false); // 设置字体为斜体字
ztFont2.setColor(Font.COLOR_NORMAL); // 将字体设置为“红色”
ztFont2.setFontHeightInPoints((short) 10); // 将字体大小设置为18px
ztFont2.setFontName("宋体"); // 字体应用到当前单元格上
ztFont2.setBold(true); //加粗
//ztFont.setUnderline(Font.U_DOUBLE);// 添加（Font.U_SINGLE单条下划线/Font.U_DOUBLE双条下划线）
//ztFont.setStrikeout(true);// 是否添加删除线
titleStyle2.setFont(ztFont2);
titleStyle2.setWrapText(true);//设置自动换行
//----------------------------------------------------------
//----------------单元格样式-----------------------------------
XSSFCellStyle cellStyle = wb.createCellStyle(); //表格样式
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
cellStyle.setBorderRight(BorderStyle.THIN);//右边框
cellStyle.setBorderTop(BorderStyle.THIN);//上边框
Font cellFont = wb.createFont();
cellFont.setItalic(false); // 设置字体为斜体字
cellFont.setColor(Font.COLOR_NORMAL); // 将字体设置为“红色”
cellFont.setFontHeightInPoints((short) 9); // 将字体大小设置为18px
cellFont.setFontName("宋体"); // 字体应用到当前单元格上
cellStyle.setFont(cellFont);
cellStyle.setWrapText(true);//设置自动换行
//----------------------------------------------------------
// ----------------------创建第一行----------------------------
// 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
row = sheet.createRow(0);
// 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
cell = row.createCell(0);
// 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
// 设置单元格内容
String titleName = "天能招引企业财政奖励及分配明细";
if (query.getStatisticalPeriodStartTime() != null && query.getStatisticalPeriodEndTime() != null) {
LocalDateTime startTime = query.getStatisticalPeriodStartTime();
LocalDateTime endTime = query.getStatisticalPeriodEndTime();
String startY = String.valueOf(startTime.getYear());
String startM = String.valueOf(startTime.getMonthValue());
String endY = String.valueOf(endTime.getYear());
String endM = String.valueOf(endTime.getMonthValue());
titleName = titleName + "(" + startY + "年" + startM + "月-" + endY + "年" + endM + "月)";
}
cell.setCellValue(titleName);
cell.setCellStyle(titleStyle);
// --------------------------------------------------
// ------------------处理数据start---------------------
if (CollectionUtils.isNotEmpty(statisticalReportConditionDTOList)) {
int rowNum = 1;
for (int i = 0; i < statisticalReportConditionDTOList.size(); i++) {
StatisticalReportConditionDTO statisticalReportConditionDTO = statisticalReportConditionDTOList.get(i);
String name = "";
if (!StringUtils.isEmpty(statisticalReportConditionDTO.getDealerName())) {
name = statisticalReportConditionDTO.getDealerName();
}
rowNum = rowNum + i;
creatCell(sheet, row, cell, titleStyle2, rowNum, name);
rowNum = rowNum + 1;
if (CollectionUtils.isNotEmpty(statisticalReportList)) {
BigDecimal zero = BigDecimal.ZERO;
BigDecimal amount = zero, districtRetainedAmount = zero, districtIncentiveAmountReceivable = zero, districtRewardAmountActuallyReceived = zero,
tnIncome = zero, intermediaryIncome = zero, enterpriseIncome = zero;
int mergeSize = 0;
String dealerId = "";
for (int j = 0; j < statisticalReportList.size(); j++) {
List<Object> data = new ArrayList<>();
int size = 0;
StatisticalReportDTO statisticalReportDTO = statisticalReportList.get(j);
if (statisticalReportConditionDTO.getEnterpriseId().equals(statisticalReportDTO.getEnterpriseId())
&& statisticalReportConditionDTO.getStatisticalYear().equals(statisticalReportDTO.getStatisticalYear())
&& statisticalReportConditionDTO.getStatisticsQuarter().equals(statisticalReportDTO.getStatisticsQuarter())) {
dealerId = statisticalReportDTO.getDealerId();
// 计算总合计
amount = amount.add(statisticalReportDTO.getAmount() != null ? statisticalReportDTO.getAmount() : zero);
districtRetainedAmount = districtRetainedAmount
.add(statisticalReportDTO.getDistrictRetainedAmount() != null ? statisticalReportDTO.getDistrictRetainedAmount() : zero);
districtIncentiveAmountReceivable = districtIncentiveAmountReceivable.add(
statisticalReportDTO.getDistrictIncentiveAmountReceivable() != null ? statisticalReportDTO.getDistrictIncentiveAmountReceivable() : zero);
districtRewardAmountActuallyReceived = districtRewardAmountActuallyReceived.add(
statisticalReportDTO.getDistrictRewardAmountActuallyReceived() != null ? statisticalReportDTO.getDistrictRewardAmountActuallyReceived() : zero);
tnIncome = tnIncome.add(statisticalReportDTO.getTnIncome() != null ? statisticalReportDTO.getTnIncome() : zero);
intermediaryIncome = intermediaryIncome
.add(statisticalReportDTO.getIntermediaryIncome() != null ? statisticalReportDTO.getIntermediaryIncome() : zero);
enterpriseIncome = enterpriseIncome.add(statisticalReportDTO.getEnterpriseIncome() != null ? statisticalReportDTO.getEnterpriseIncome() : zero);
data.add(statisticalReportDTO.getEnterpriseName());
data.add(statisticalReportDTO.getTaxName());
data.add(retain2Decimals(statisticalReportDTO.getAmount()));
data.add(statisticalReportDTO.getDistrictRetentionRatio() + "%");
data.add(retain2Decimals(statisticalReportDTO.getDistrictRetainedAmount()));
data.add(statisticalReportDTO.getDistrictRewardRatio() + "%");
data.add(retain2Decimals(statisticalReportDTO.getDistrictIncentiveAmountReceivable()));
data.add(retain2Decimals(statisticalReportDTO.getTnIncome()));
if(!StringUtils.isEmpty(statisticalReportDTO.getDealerId())){
data.add(retain2Decimals(statisticalReportDTO.getIntermediaryIncome()));
}else{
data.add("");
}
data.add(retain2Decimals(statisticalReportDTO.getEnterpriseIncome()));
if (!StringUtils.isEmpty(statisticalReportDTO.getRemark())) {
data.add(statisticalReportDTO.getRemark());
} else {
data.add("");
}
mergeSize++;
size++;
rowNum = rowNum + size; //从第三行开始
row = sheet.createRow(rowNum);
for (int k = 0; k < data.size(); k++) { //将数据添加到单元格中
cell = row.createCell(k);
cell.setCellValue("" + data.get(k) + "");
cell.setCellStyle(cellStyle);
}
}
}
if (mergeSize > 0) {
rowNum = rowNum + 1;
row = sheet.createRow(rowNum);
cell = row.createCell(0);
cell.setCellStyle(cellStyle);
cell = row.createCell(1);
cell.setCellValue("小计(元)");
cell.setCellStyle(cellStyle);
cell = row.createCell(2);
cell.setCellValue("" + retain2Decimals(amount) + "");
cell.setCellStyle(cellStyle);
cell = row.createCell(3);
cell.setCellStyle(cellStyle);
cell = row.createCell(4);
cell.setCellValue("" + retain2Decimals(districtRetainedAmount) + "");
cell.setCellStyle(cellStyle);
cell = row.createCell(5);
cell.setCellStyle(cellStyle);
cell = row.createCell(6);
cell.setCellValue("" + retain2Decimals(districtIncentiveAmountReceivable) + "");
cell.setCellStyle(cellStyle);
cell = row.createCell(7);
cell.setCellValue("" + retain2Decimals(tnIncome) + "");
cell.setCellStyle(cellStyle);
cell = row.createCell(8);
if(!StringUtils.isEmpty(dealerId)){
cell.setCellValue("" + retain2Decimals(intermediaryIncome) + "");
}
cell.setCellStyle(cellStyle);
cell = row.createCell(9);
cell.setCellValue("" + retain2Decimals(enterpriseIncome) + "");
cell.setCellStyle(cellStyle);
cell = row.createCell(10);
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(rowNum - mergeSize, rowNum, 0, 0));
sheet.addMergedRegion(new CellRangeAddress(rowNum - mergeSize, rowNum, 10, 10));
}
}
}
StatisticalReportDTO total = statisticalReportResultDTO.getTotal();
if (total != null) {
rowNum = rowNum + 1;
row = sheet.createRow(rowNum);
XSSFCellStyle cellStyleTotal = wb.createCellStyle(); //表格样式
cellStyleTotal.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
cellStyleTotal.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyleTotal.setAlignment(HorizontalAlignment.CENTER);
cellStyleTotal.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyleTotal.setBorderBottom(BorderStyle.THIN);//下边框
cellStyleTotal.setBorderLeft(BorderStyle.THIN);//左边框
cellStyleTotal.setBorderRight(BorderStyle.THIN);//右边框
cellStyleTotal.setBorderTop(BorderStyle.THIN);//上边框
cellStyleTotal.setFont(cellFont);
cellStyleTotal.setWrapText(true);//设置自动换行
sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, 1));
cell = row.createCell(0);
cell.setCellValue("合计");
cell.setCellStyle(cellStyleTotal);
cell = row.createCell(1);
cell.setCellStyle(cellStyleTotal);
cell = row.createCell(2);
cell.setCellValue("" + retain2Decimals(total.getAmount()) + "");
cell.setCellStyle(cellStyleTotal);
cell = row.createCell(3);
cell.setCellStyle(cellStyleTotal);
cell = row.createCell(4);
cell.setCellValue("" + retain2Decimals(total.getDistrictRetainedAmount()) + "");
cell.setCellStyle(cellStyleTotal);
cell = row.createCell(5);
cell.setCellStyle(cellStyleTotal);
cell = row.createCell(6);
cell.setCellValue("" + retain2Decimals(total.getDistrictIncentiveAmountReceivable()) + "");
cell.setCellStyle(cellStyleTotal);
cell = row.createCell(7);
cell.setCellValue("" + retain2Decimals(total.getTnIncome()) + "");
cell.setCellStyle(cellStyleTotal);
cell = row.createCell(8);
cell.setCellValue("" + retain2Decimals(total.getIntermediaryIncome()) + "");
cell.setCellStyle(cellStyleTotal);
cell = row.createCell(9);
cell.setCellValue("" + retain2Decimals(total.getEnterpriseIncome()) + "");
cell.setCellStyle(cellStyleTotal);
cell = row.createCell(10);
cell.setCellStyle(cellStyleTotal);
}
}
// ------------------处理数据end--------------------------------------
// 输出Excel文件
response.reset();
OutputStream output = response.getOutputStream();
response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1") + ".xlsx"); //filename = 文件名
response.setContentType("application/msexcel");
wb.write(output);
output.close();
} catch (Exception e) {
e.printStackTrace();
}
}循环插入表头方法
private static void creatCell(XSSFSheet sheet, XSSFRow row, XSSFCell cell, XSSFCellStyle cellStyle, Integer rowNum, String name) {
// ------------------创建表头start---------------------
row = sheet.createRow(rowNum); // 创建第二行
sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum + 1, 0, 0));
cell = row.createCell(0);
cell.setCellValue("招引企业");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum + 1, 1, 1));
cell = row.createCell(1);
cell.setCellValue("税种");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum + 1, 2, 2));
cell = row.createCell(2);
cell.setCellValue("金额");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum + 1, 3, 3));
cell = row.createCell(3);
cell.setCellValue("区留存比例");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum + 1, 4, 4));
cell = row.createCell(4);
cell.setCellValue("区留存金额");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum + 1, 5, 5));
cell = row.createCell(5);
cell.setCellValue("区奖励比例");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum + 1, 6, 6));
cell = row.createCell(6);
cell.setCellValue("区奖励金额");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 7, 9));
cell = row.createCell(7);
cell.setCellValue("奖励分配");
cell.setCellStyle(cellStyle);
cell = row.createCell(8);
cell.setCellStyle(cellStyle);
cell = row.createCell(9);
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum + 1, 10, 10));
cell = row.createCell(10);
cell.setCellValue("备注");
cell.setCellStyle(cellStyle);
//--------------------------- 创建第三行--------------------
row = sheet.createRow(rowNum + 1);
cell = row.createCell(0);
cell.setCellStyle(cellStyle);
cell = row.createCell(1);
cell.setCellStyle(cellStyle);
cell = row.createCell(2);
cell.setCellStyle(cellStyle);
cell = row.createCell(3);
cell.setCellStyle(cellStyle);
cell = row.createCell(4);
cell.setCellStyle(cellStyle);
cell = row.createCell(5);
cell.setCellStyle(cellStyle);
cell = row.createCell(6);
cell.setCellStyle(cellStyle);
cell = row.createCell(7);
cell.setCellValue("天能所得");
cell.setCellStyle(cellStyle);
cell = row.createCell(8);
cell.setCellValue(name + "(中介)");
cell.setCellStyle(cellStyle);
cell = row.createCell(9);
cell.setCellValue("公司所得");
cell.setCellStyle(cellStyle);
cell = row.createCell(10);
cell.setCellStyle(cellStyle);
//-------------------------表头end---------------------
}业务需要 保留2位小数方法
private static BigDecimal retain2Decimals(BigDecimal num) {
BigDecimal result = num.setScale(2, RoundingMode.HALF_UP);//保留两位小数
return result;
}excel导出模版如下：
2674
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
