1、照例先看下效果图
2、引入jar包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
3、自定义合并的单元格和标题
package com.lelern.common.utils.poi;
import org.springframework.stereotype.Component;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author YBug
* @version 1.0
* @date 2024/8/13 16:05
* @Describe 自定义表头
*/
@Component
public class UserDefinedHead {
/**
* 组装EXCEl表头,一共三行数据
*
* @param titleStr 标题行要展示的内容
* @return
*/
public Map<String, List<CellModel>> getTitleListMap(String titleStr) {
Map<String, List<CellModel>> cellTitleMap = new HashMap<>(16);
// 标题行,我这里的业务不需要先注掉
// List<CellModel> titleRow = new ArrayList<>();
// CellModel title = new CellModel();
// title.setCellName(titleStr);
// title.setStartRow(0);
// title.setEndRow(0);
// title.setStartColumn(0);
// title.setEndColumn(19);
// titleRow.add(title);
// 创建第一行
List<CellModel> firstRow = new ArrayList<>();
CellModel a1 = new CellModel();
a1.setCellName("日期");
a1.setStartRow(0);
a1.setWidth(20);
a1.setEndRow(2);
a1.setStartColumn(0);
a1.setEndColumn(0);
firstRow.add(a1);
CellModel a2 = new CellModel();
a2.setCellName("收入明细");
a2.setStartRow(0);
a2.setWidth(20);
a2.setEndRow(0);
a2.setStartColumn(1);
a2.setEndColumn(3);
firstRow.add(a2);
CellModel a3 = new CellModel();
a3.setCellName("收入合计");
a3.setStartRow(0);
a3.setWidth(20);
a3.setEndRow(2);
a3.setStartColumn(4);
a3.setEndColumn(4);
firstRow.add(a3);
CellModel a4 = new CellModel();
a4.setCellName("代收代缴收款明细");
a4.setStartRow(0);
a4.setEndRow(0);
a4.setStartColumn(5);
a4.setEndColumn(9);
firstRow.add(a4);
CellModel a5 = new CellModel();
a5.setCellName("代收代缴款合计");
a5.setStartRow(0);
a5.setEndRow(2);
a5.setStartColumn(10);
a5.setEndColumn(10);
firstRow.add(a5);
CellModel a6 = new CellModel();
a6.setCellName("押金类");
a6.setStartRow(0);
a6.setEndRow(2);
a6.setStartColumn(11);
a6.setEndColumn(11);
firstRow.add(a6);
CellModel a7 = new CellModel();
a7.setCellName("收款总计");
a7.setStartRow(0);
a7.setWidth(20);
a7.setEndRow(2);
a7.setStartColumn(12);
a7.setEndColumn(12);
firstRow.add(a7);
CellModel a8 = new CellModel();
a8.setCellName("收款方式");
a8.setStartRow(0);
a8.setWidth(20);
a8.setEndRow(0);
a8.setStartColumn(13);
a8.setEndColumn(19);
firstRow.add(a8);
CellModel a9 = new CellModel();
a9.setCellName("日清余额");
a9.setStartRow(0);
a9.setWidth(20);
a9.setEndRow(2);
a9.setStartColumn(20);
a9.setEndColumn(20);
firstRow.add(a9);
// 第二行
List<CellModel> secondRow = new ArrayList<>();
CellModel b1 = new CellModel();
b1.setCellName("物业服务费收入");
b1.setStartRow(1);
b1.setEndRow(2);
b1.setWidth(20);
b1.setStartColumn(1);
b1.setEndColumn(1);
secondRow.add(b1);
CellModel b2 = new CellModel();
b2.setCellName("车位服务费收入");
b2.setStartRow(1);
b2.setEndRow(2);
b2.setWidth(20);
b2.setStartColumn(2);
b2.setEndColumn(2);
secondRow.add(b2);
CellModel b3 = new CellModel();
b3.setCellName("多种经营收入");
b3.setStartRow(1);
b3.setEndRow(2);
b3.setWidth(20);
b3.setStartColumn(3);
b3.setEndColumn(3);
secondRow.add(b3);
CellModel b4 = new CellModel();
b4.setCellName("垃圾清运收款");
b4.setStartRow(1);
b4.setEndRow(1);
b4.setWidth(10);
b4.setStartColumn(5);
b4.setEndColumn(6);
secondRow.add(b4);
CellModel b5 = new CellModel();
b5.setCellName("水电能耗费");
b5.setStartRow(1);
b5.setEndRow(1);
b5.setWidth(10);
b5.setStartColumn(7);
b5.setEndColumn(9);
secondRow.add(b5);
CellModel b6 = new CellModel();
b6.setCellName("银行转账");
b6.setStartRow(1);
b6.setEndRow(2);
b6.setWidth(10);
b6.setStartColumn(13);
b6.setEndColumn(13);
secondRow.add(b6);
CellModel b7 = new CellModel();
b7.setCellName("支付宝");
b7.setStartRow(1);
b7.setEndRow(2);
b7.setWidth(10);
b7.setStartColumn(14);
b7.setEndColumn(14);
secondRow.add(b7);
CellModel b8 = new CellModel();
b8.setCellName("微信");
b8.setStartRow(1);
b8.setEndRow(2);
b8.setWidth(10);
b8.setStartColumn(15);
b8.setEndColumn(15);
secondRow.add(b8);
CellModel b9 = new CellModel();
b9.setCellName("POS机");
b9.setStartRow(1);
b9.setEndRow(2);
b9.setWidth(10);
b9.setStartColumn(16);
b9.setEndColumn(16);
secondRow.add(b9);
CellModel b10 = new CellModel();
b10.setCellName("现金");
b10.setStartRow(1);
b10.setEndRow(2);
b10.setWidth(10);
b10.setStartColumn(17);
b10.setEndColumn(17);
secondRow.add(b10);
CellModel b11 = new CellModel();
b11.setCellName("运营系统平安子账户");
b11.setStartRow(1);
b11.setEndRow(2);
b11.setWidth(10);
b11.setStartColumn(18);
b11.setEndColumn(18);
secondRow.add(b11);
CellModel b12 = new CellModel();
b12.setCellName("抵");
b12.setStartRow(1);
b12.setEndRow(2);
b12.setWidth(10);
b12.setStartColumn(19);
b12.setEndColumn(19);
secondRow.add(b12);
// 第三行
List<CellModel> threeRow = new ArrayList<>();
CellModel d1 = new CellModel();
d1.setCellName("装修垃圾");
d1.setStartRow(2);
d1.setEndRow(2);
d1.setWidth(20);
d1.setStartColumn(5);
d1.setEndColumn(5);
threeRow.add(d1);
CellModel d2 = new CellModel();
d2.setCellName("建筑垃圾");
d2.setStartRow(2);
d2.setEndRow(2);
d2.setWidth(20);
d2.setStartColumn(6);
d2.setEndColumn(6);
threeRow.add(d2);
CellModel d3 = new CellModel();
d3.setCellName("水电费");
d3.setStartRow(2);
d3.setEndRow(2);
d3.setWidth(20);
d3.setStartColumn(7);
d3.setEndColumn(7);
threeRow.add(d3);
CellModel d4 = new CellModel();
d4.setCellName("电费");
d4.setStartRow(2);
d4.setEndRow(2);
d4.setWidth(20);
d4.setStartColumn(8);
d4.setEndColumn(8);
threeRow.add(d4);
CellModel d5 = new CellModel();
d5.setCellName("水费");
d5.setStartRow(2);
d5.setEndRow(2);
d5.setWidth(20);
d5.setStartColumn(9);
d5.setEndColumn(9);
threeRow.add(d5);
// 组装提头
// 组装第二行表头标题
cellTitleMap.put("0", firstRow);
// 组装第二行表头标题
cellTitleMap.put("1", secondRow);
// 组装第二行表头标题
cellTitleMap.put("2", threeRow);
return cellTitleMap;
}
}
4、excel赋值
package com.lelern.web.controller.system.bb;
import com.lelern.common.utils.StringUtils;
import com.lelern.common.utils.poi.CellModel;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.stereotype.Component;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* @author YBug
* @version 1.0
* @date 2024/8/13 17:19
* @Describe
*/
@Component
public class WorkBookDefined {
/**
* 生成表格(用于生成复杂表头)
*
* @param sheetName sheet名称
* @param wb 表对象
* @param cellTitleMap 表头数据
* @param cellRowNum 表头总占用行数
* @param exportData 行数据
* @return SXSSFWorkbook 数据表对象
*/
public SXSSFWorkbook createWorkbook(String sheetName, SXSSFWorkbook wb,
Map<String, List<CellModel>> cellTitleMap,
Integer cellRowNum, List<Map<String, Object>> exportData) {
// 设置表格名称
Sheet sheet = wb.createSheet(sheetName);
// 定义title列cell样式
CellStyle cellTitleStyle = wb.createCellStyle();
cellTitleStyle.setAlignment(HorizontalAlignment.CENTER); // 文字居中
cellTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置单元格内容垂直对齐
cellTitleStyle.setWrapText(true); // 设置自动换行
cellTitleStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex()); // 背景色
cellTitleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 必须设置 否则背景色不生效
cellTitleStyle.setBorderBottom(BorderStyle.THIN);
cellTitleStyle.setBorderLeft(BorderStyle.THIN);
cellTitleStyle.setBorderRight(BorderStyle.THIN);
cellTitleStyle.setBorderTop(BorderStyle.THIN);
// 定义title列cell字体
Font titleFont = wb.createFont();
titleFont.setFontName("Arial");
// titleFont.setColor(IndexedColors.WHITE.getIndex()); //字体颜色
titleFont.setFontHeightInPoints((short) 10);
titleFont.setBold(true);
cellTitleStyle.setFont(titleFont);
List<List<Integer>> mergeParams = new ArrayList<>(); // 需要合并的数据
for (int t = 0; t < cellRowNum; t++) {
Row row = sheet.createRow(t);
if (t == 0) {
row.setHeight((short) (2 * 256));
}
List<CellModel> cellNameList = cellTitleMap.get(String.valueOf(t));
for (CellModel cellModel : cellNameList) {
// 遍历插入表头
if (cellModel.getStartColumn() != null) {
Cell cell = row.createCell(cellModel.getStartColumn());
cell.setCellValue(cellModel.getCellName());
cell.setCellStyle(cellTitleStyle);
}
Integer startRow = cellModel.getStartRow();
Integer endRow = cellModel.getEndRow();
Integer startColumn = cellModel.getStartColumn();
Integer endColumn = cellModel.getEndColumn();
// 满足合并单元格条件,加入到合并集合
if (!startRow.equals(endRow) || !startColumn.equals(endColumn)) {
List<Integer> mergeParam = new ArrayList<>(4);
mergeParam.add(startRow);
mergeParam.add(endRow);
mergeParam.add(startColumn);
mergeParam.add(endColumn);
mergeParams.add(mergeParam);
}
// 根据标题设置单元格宽度
if (cellModel.getWidth() != null) {
sheet.setColumnWidth(startColumn, cellModel.getWidth() * 256);
} else {
sheet.setColumnWidth(startColumn, cellModel.getCellName().getBytes().length * 256);
}
}
}
// 合并单元格
if (StringUtils.isNotEmpty(mergeParams)) {
for (List<Integer> list : mergeParams) {
// 合并单元格之前设置单元格的样式,避免合并后部分失效
Integer startRow = list.get(0);
Integer endRow = list.get(1);
Integer startCell = list.get(2);
Integer endCell = list.get(3);
setStyleBeforeMerging(sheet, startRow, endRow, startCell, endCell, cellTitleStyle);
sheet.addMergedRegionUnsafe(new CellRangeAddress(startRow, endRow, startCell, endCell));
}
}
// 明细数据样式
CellStyle bodyStyle = wb.createCellStyle();
bodyStyle.setAlignment(HorizontalAlignment.CENTER); // 设置单元格内容水平对齐
bodyStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置单元格内容垂直对齐
bodyStyle.setWrapText(true); // 设置自动换行
bodyStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
bodyStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 必须设置 否则背景色不生效
bodyStyle.setBorderBottom(BorderStyle.THIN);
bodyStyle.setBorderLeft(BorderStyle.THIN);
bodyStyle.setBorderRight(BorderStyle.THIN);
bodyStyle.setBorderTop(BorderStyle.THIN);
// 明细数据样式字体样式
Font bodyFont = wb.createFont();
bodyFont.setFontName("Arial");
bodyFont.setFontHeightInPoints((short) 10);
bodyStyle.setFont(bodyFont);
fillExcelData(exportData, sheet, bodyStyle);
return wb;
}
/**
* 填充数据
*
* @param mapsList
*/
public static void fillExcelData(List<Map<String, Object>> mapsList, Sheet sheet, CellStyle cellStyle) {
int rowIndex = 3;
String[] baseInfoStrArr = {"receivableDate", "wuye", "chewei", "duozhong", "shouruTotal", "zhuangxiu", "jianzu", "shuidian",
"dian", "shui" , "daishouTotal", "yajin", "shouruTotalEnd", "yinhang", "weixinpay", "alipay",
"pospay", "cashpay", "pinanpay", "di", "riqing"};
for (Map<String, Object> baseMap : mapsList) {
// 填充明细数据
// 建立明细行
Row detailRow = sheet.createRow(rowIndex);
int cellIndex = 0;
// 基础数据
for (String value : baseInfoStrArr) {
Cell cell = detailRow.createCell(cellIndex);
setExcelValue(cell, baseMap.get(value), cellStyle);
cellIndex++;
}
rowIndex++;
}
}
/**
* 设置单元格值
*
* @param cell 单元格类
* @param value 传入的值
*/
public static void setExcelValue(Cell cell, Object value, CellStyle cellStyle) {
// 写数据
if (value == null) {
cell.setCellValue("");
} else {
if (value instanceof Integer || value instanceof Long) {
cell.setCellValue(Long.parseLong(value.toString()));
} else if (value instanceof BigDecimal) {
cell.setCellValue(((BigDecimal) value).setScale(1, RoundingMode.HALF_UP).doubleValue());
} else {
cell.setCellValue(value.toString());
}
}
cell.setCellStyle(cellStyle);
}
/**
* <p>
* Description: 合并单元格之前设置单元格的样式,避免合并后部分失效
* </p>
*
* @param sheet excel的sheet
* @param firstRow 开始行下标
* @param lastRow 结束行下标
* @param firstCol 开始列下标
* @param lastCol 结束列下标
* @param style 样式
*/
public static void setStyleBeforeMerging(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, CellStyle style) {
if (lastRow >= firstRow) {
//设置合并单元格之前,先设置一遍样式
for (int l = firstRow; l <= lastRow; l++) {
Row row = CellUtil.getRow(l, sheet);
for (int k = firstCol; k <= lastCol; k++) {
Cell cell = CellUtil.getCell(row, k);
cell.setCellStyle(style);
}
}
}
}
}
5、controller输出文件流
@PreAuthorize("@ss.hasPermi('system:month:export')")
@Log(title = "收费月报表", businessType = BusinessType.EXPORT)
@PostMapping("/export")
public void export(HttpServletResponse response)
{
List<ShouFeiMonth> list = shouFeiMonthService.selectShouFeiMonthList(new ShouFeiMonth());
List<Map<String, Object>> mapsList = new ArrayList<>();
for (int i = 0; i < list.size(); i++) {
ShouFeiMonth item = list.get(i);
Map<String, Object> res = new HashMap<>();
if(i == 0){
Map<String, Object> resT = new HashMap<>();
resT.put("receivableDate", "合计");
resT.put("wuye", list.stream().map(e -> e.getWuye()).reduce(BigDecimal.ZERO, BigDecimal::add).setScale(2, BigDecimal.ROUND_HALF_UP));
resT.put("chewei", list.stream().map(e -> e.getChewei()).reduce(BigDecimal.ZERO, BigDecimal::add).setScale(2, BigDecimal.ROUND_HALF_UP));
resT.put("duozhong", list.stream().map(e -> e.getDuozhong()).reduce(BigDecimal.ZERO, BigDecimal::add).setScale(2, BigDecimal.ROUND_HALF_UP));
resT.put("shouruTotal", list.stream().map(e -> e.getShouruTotal()).reduce(BigDecimal.ZERO, BigDecimal::add).setScale(2, BigDecimal.ROUND_HALF_UP));
resT.put("zhuangxiu", list.stream().map(e -> e.getZhuangxiu()).reduce(BigDecimal.ZERO, BigDecimal::add).setScale(2, BigDecimal.ROUND_HALF_UP));
resT.put("jianzu", list.stream().map(e -> e.getJianzu()).reduce(BigDecimal.ZERO, BigDecimal::add).setScale(2, BigDecimal.ROUND_HALF_UP));
resT.put("shuidian", list.stream().map(e -> e.getShuidian()).reduce(BigDecimal.ZERO, BigDecimal::add).setScale(2, BigDecimal.ROUND_HALF_UP));
resT.put("dian", list.stream().map(e -> e.getDian()).reduce(BigDecimal.ZERO, BigDecimal::add).setScale(2, BigDecimal.ROUND_HALF_UP));
resT.put("shui", list.stream().map(e -> e.getShui()).reduce(BigDecimal.ZERO, BigDecimal::add).setScale(2, BigDecimal.ROUND_HALF_UP));
resT.put("daishouTotal", list.stream().map(e -> e.getDaishouTotal()).reduce(BigDecimal.ZERO, BigDecimal::add).setScale(2, BigDecimal.ROUND_HALF_UP));
resT.put("yajin", list.stream().map(e -> e.getYajin()).reduce(BigDecimal.ZERO, BigDecimal::add).setScale(2, BigDecimal.ROUND_HALF_UP));
resT.put("shouruTotalEnd", list.stream().map(e -> e.getShouruTotalEnd()).reduce(BigDecimal.ZERO, BigDecimal::add).setScale(2, BigDecimal.ROUND_HALF_UP));
resT.put("yinhang", list.stream().map(e -> e.getYinhang()).reduce(BigDecimal.ZERO, BigDecimal::add).setScale(2, BigDecimal.ROUND_HALF_UP));
resT.put("weixinpay", list.stream().map(e -> e.getWeixinpay()).reduce(BigDecimal.ZERO, BigDecimal::add).setScale(2, BigDecimal.ROUND_HALF_UP));
resT.put("alipay", list.stream().map(e -> e.getAlipay()).reduce(BigDecimal.ZERO, BigDecimal::add).setScale(2, BigDecimal.ROUND_HALF_UP));
resT.put("pospay", list.stream().map(e -> e.getPospay()).reduce(BigDecimal.ZERO, BigDecimal::add).setScale(2, BigDecimal.ROUND_HALF_UP));
resT.put("cashpay", list.stream().map(e -> e.getCashpay()).reduce(BigDecimal.ZERO, BigDecimal::add).setScale(2, BigDecimal.ROUND_HALF_UP));
resT.put("pinanpay", list.stream().map(e -> e.getPinanpay()).reduce(BigDecimal.ZERO, BigDecimal::add).setScale(2, BigDecimal.ROUND_HALF_UP));
resT.put("di", list.stream().map(e -> e.getDi()).reduce(BigDecimal.ZERO, BigDecimal::add).setScale(2, BigDecimal.ROUND_HALF_UP));
resT.put("riqing", list.stream().map(e -> e.getRiqing()).reduce(BigDecimal.ZERO, BigDecimal::add).setScale(2, BigDecimal.ROUND_HALF_UP));
mapsList.add(resT);
SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
res.put("receivableDate", sf.format(item.getReceivableDate()));
res.put("wuye", item.getWuye());
res.put("chewei", item.getChewei());
res.put("duozhong", item.getDuozhong());
res.put("shouruTotal", item.getShouruTotal());
res.put("zhuangxiu", item.getZhuangxiu());
res.put("jianzu", item.getJianzu());
res.put("shuidian", item.getShuidian());
res.put("dian", item.getDian());
res.put("shui", item.getShui());
res.put("daishouTotal", item.getDaishouTotal());
res.put("yajin", item.getYajin());
res.put("shouruTotalEnd", item.getShouruTotalEnd());
res.put("yinhang", item.getYinhang());
res.put("weixinpay", item.getWeixinpay());
res.put("alipay", item.getAlipay());
res.put("pospay", item.getPospay());
res.put("cashpay", item.getCashpay());
res.put("pinanpay", item.getPinanpay());
res.put("di", item.getDi());
res.put("riqing", item.getRiqing());
mapsList.add(res);
}else{
SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
res.put("receivableDate", sf.format(item.getReceivableDate()));
res.put("wuye", item.getWuye());
res.put("chewei", item.getChewei());
res.put("duozhong", item.getDuozhong());
res.put("shouruTotal", item.getShouruTotal());
res.put("zhuangxiu", item.getZhuangxiu());
res.put("jianzu", item.getJianzu());
res.put("shuidian", item.getShuidian());
res.put("dian", item.getDian());
res.put("shui", item.getShui());
res.put("daishouTotal", item.getDaishouTotal());
res.put("yajin", item.getYajin());
res.put("shouruTotalEnd", item.getShouruTotalEnd());
res.put("yinhang", item.getYinhang());
res.put("weixinpay", item.getWeixinpay());
res.put("alipay", item.getAlipay());
res.put("pospay", item.getPospay());
res.put("cashpay", item.getCashpay());
res.put("pinanpay", item.getPinanpay());
res.put("di", item.getDi());
res.put("riqing", item.getRiqing());
mapsList.add(res);
}
}
String fileName = "GGGGG.xls";
SXSSFWorkbook workbook = workBookDefined.createWorkbook("收费月度报表", new SXSSFWorkbook(), userDefinedHead.getTitleListMap(""), 3, mapsList);
try {
OutputStream os = response.getOutputStream();// 取得输出流
response.reset();
// 下面几行是为了解决文件名乱码的问题
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-disposition","attachment;filename="+new String(fileName.getBytes(), StandardCharsets.UTF_8));
response.setHeader("wms-filename", fileName);
// 浏览器通过流下载
response.flushBuffer();
workbook.write(os);
os.flush();
} catch (Exception e) {
e.printStackTrace();
}
}
6、vue2前端
//使用vue2的axios前后端交互
export function exportMonth() {
return request({
url: '/system/month/export',
method: 'post',
responseType: 'blob',
timeout: 60000
})
}
exportMonth().then((res) => {
console.log("res..",res)
// 创建一个链接元素
const blob = new Blob([res]); // 把得到的结果用流对象转一下
var a = document.createElement("a"); //创建一个<a></a>标签
a.href = URL.createObjectURL(blob); // 将流文件写入a标签的href属性值
a.download = "收费月度报表.xlsx"; //设置文件名
a.style.display = "none"; // 障眼法藏起来a标签
document.body.appendChild(a); // 将a标签追加到文档对象中
a.click(); // 模拟点击了a标签,会触发a标签的href的读取,浏览器就会自动下载了
a.remove(); // 一次性的,用完就删除a标签
});
到此结束
注意:如果遇到跨域问题可以留言