一. 业务场景,Excel导出合并单元格,效果如下!
二。导入所需依赖
Apache POI是一个开源的Java库,用于读取和写入Microsoft Office格式的文件,如Word文档(.docx)、Excel表格(.xlsx)和PowerPoint幻灯片(.pptx)等。要在项目中使用Apache POI,你通常需要添加相应的依赖。
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>你的版本号</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>你的版本号</version>
</dependency>
三。解决思路
1.获取到需要导出的数据,对需要合并的数据进行分组,需要合并的数据根据开始行,到分组后的数据作为结束行,进行合并!
1.1需要则创建表头,不需要则直接在本地获取模板进行写入!
/**
* 创建表头信息
*/
public void createHeader(Sheet sheet, Workbook workbook) {
Row row = sheet.createRow(0);
row.setHeightInPoints(50);
for (int i = 0; i < headList.size(); i++) {
CellStyle cellStyle = WorkbookUtil.addExcelStyle(workbook, IndexedColors.GREY_25_PERCENT.index);
Cell cell = row.createCell(i);
cell.setCellValue(headList.get(i));
cell.setCellStyle(cellStyle);
sheet.setColumnWidth(i, (headList.get(i).length() + 10) * 256);
}
// 自动调整列宽以适应内容 setColumnWidth(int columnIndex, int width)
sheet.setColumnWidth(2, 40 * 256); //每个字符*256
sheet.setColumnWidth(3, 20 * 256);
sheet.setColumnWidth(9, 30 * 256);
sheet.autoSizeColumn(0);
}
/**
* 以下是我自己的一个导入案例
*/
@Override
public void exportExcel(YwCostTask costTask, HttpServletResponse response) {
String year = costTask.getYear();
try {
List<YwCostTask> list = this.list(Condition.getLikeQueryWrapper(costTask));
// 创建一个新的工作簿
Workbook workbook = new XSSFWorkbook();
//配置样式
CellStyle cellStyle = WorkbookUtil.addCellStyle(workbook);
Font font = WorkbookUtil.addFontStyle(workbook);
Map<String, Map<String, List<YwCostTask>>> collect = list.stream().collect(Collectors.groupingBy(YwCostTask::getYear, Collectors.groupingBy(YwCostTask::getContractNo)));
List<String> contractNos = collect.values().stream().flatMap(map -> map.keySet().stream()).collect(Collectors.toList()); //获取出所有的合同编号
//通过合同编号查询其他年份的子项目
Map<String, List<YwCostTask>> otherData = this.list(Wrappers.<YwCostTask>lambdaQuery().in(YwCostTask::getContractNo, contractNos).notIn(YwCostTask::getYear, year))
.stream().collect(Collectors.groupingBy(YwCostTask::getContractNo));
for (Map.Entry<String, Map<String, List<YwCostTask>>> entry : collect.entrySet()) {
// 创建一个新的工作表
Sheet sheet = workbook.createSheet(entry.getKey());
//创建表头
createHeader(sheet, workbook);
int i = 1;
int serial = 1; //序号
for (Map.Entry<String, List<YwCostTask>> listEntry : entry.getValue().entrySet()) {
// 创建行和单元格样式
List<YwCostTask> value = listEntry.getValue();
List<YwCostTask> taskList = otherData.get(listEntry.getKey());
if (ObjectUtil.isNotEmpty(taskList)) {
value.addAll(taskList);
}
for (int j = 0; j < value.size(); j++) {
YwCostTask task = value.get(j);
Row row = sheet.createRow(i);
row.createCell(0).setCellValue(serial);
row.createCell(1).setCellValue(task.getContractNo());
row.createCell(2).setCellValue(task.getProjectName());
row.createCell(3).setCellValue(task.getProjectOwner());
row.createCell(4).setCellValue(task.getTaskType());
row.createCell(5).setCellValue(WorkbookUtil.convertDateToStringWith(task.getSelectBookDate()));
row.createCell(6).setCellValue(WorkbookUtil.convertDateToStringWith(task.getContractPrintDate()));
row.createCell(7).setCellValue(WorkbookUtil.convertDateToStringWith(task.getContractSignDate()));
row.createCell(8).setCellValue(task.getIsSave().equals("1") ? "是" : "否");
row.createCell(9).setCellValue(task.getIntermediary());
row.createCell(10).setCellValue(task.getFinalCost());
row.createCell(11).setCellValue(WorkbookUtil.convertDateToStringWith(task.getProjectPrintDate()));
row.createCell(12).setCellValue(task.getAuditorName());
row.createCell(13).setCellValue(task.getIsReport().equals("1") ? "是" : "否");
row.createCell(14).setCellValue(task.getRemark());
row.createCell(15).setCellValue(String.valueOf(Optional.ofNullable(task.getAgencyAmount()).orElse(BigDecimal.ZERO)));
row.createCell(16).setCellValue(String.valueOf(Optional.ofNullable(task.getBidderAmount()).orElse(BigDecimal.ZERO)));
row.createCell(17).setCellValue(String.valueOf(Optional.ofNullable(task.getPayout()).orElse(BigDecimal.ZERO)));
row.createCell(18).setCellValue(WorkbookUtil.convertDateToStringWith(task.getPayDate()));
row.createCell(19).setCellValue(String.valueOf(Optional.ofNullable(task.getServiceAmount()).orElse(BigDecimal.ZERO)));
row.createCell(20).setCellValue(task.getRatio());
row.createCell(21).setCellValue(WorkbookUtil.convertDateToStringWith(task.getInterPayDate()));
row.createCell(22).setCellValue(WorkbookUtil.convertDateToStringWith(task.getSelectDate()));
row.createCell(23).setCellValue(WorkbookUtil.convertDateToStringWith(task.getValidDate()));
row.createCell(24).setCellValue(WorkbookUtil.convertDateToStringWith(task.getServerEndDate()));
row.createCell(25).setCellValue(task.getProjectScore());
row.createCell(26).setCellValue(task.getSupermarket());
row.createCell(27).setCellValue(task.getOtherRemark());
row.createCell(28).setCellValue(task.getSource().equals("1") ? "招投标" : "中介超市");
i = i + 1;
WorkbookUtil.addProjectNameStyle(row, font, cellStyle); //配置样式和加边框、字体
}
//判断list<T>长度大于1的就需要进行合并单元格
if (value.size() > 1) {
//处理需要合并的单元格
WorkbookUtil.setMergeCell(sheet, i - value.size(), i - 1, 0, 0);
WorkbookUtil.setMergeCell(sheet, i - value.size(), i - 1, 1, 1);
WorkbookUtil.setMergeCell(sheet, i - value.size(), i - 1, 2, 2);
WorkbookUtil.setMergeCell(sheet, i - value.size(), i - 1, 3, 3);
WorkbookUtil.setMergeCell(sheet, i - value.size(), i - 1, 4, 4);
WorkbookUtil.setMergeCell(sheet, i - value.size(), i - 1, 5, 5);
WorkbookUtil.setMergeCell(sheet, i - value.size(), i - 1, 6, 6);
WorkbookUtil.setMergeCell(sheet, i - value.size(), i - 1, 7, 7);
WorkbookUtil.setMergeCell(sheet, i - value.size(), i - 1, 8, 8);
WorkbookUtil.setMergeCell(sheet, i - value.size(), i - 1, 15, 15);
WorkbookUtil.setMergeCell(sheet, i - value.size(), i - 1, 16, 16);
WorkbookUtil.setMergeCell(sheet, i - value.size(), i - 1, 17, 17);
WorkbookUtil.setMergeCell(sheet, i - value.size(), i - 1, 18, 18);
}
serial++;
}
sheet.autoSizeColumn(0);
}
//设置导出浏览器响应信息
WorkbookUtil.createExportExcelResponse(response, workbook);
} catch (Exception e) {
e.printStackTrace();
}
/**
* 设置合并单元格
*
* @param sheet sheet页对象
* @param startRowIndex 开始行号
* @param endRowIndex 结束行号
* @param startColumnIndex 开始列号
* @param endColumnIndex 结束列号
*/
public static void setMergeCell(Sheet sheet, int startRowIndex, int endRowIndex, int startColumnIndex, int endColumnIndex) {
//合并单元格区域只有一个单元格时,不合并
if (endRowIndex == startRowIndex && endColumnIndex == startColumnIndex) {
return;
}
if (startRowIndex > endRowIndex) {
return;
}
//添加合并单元格区域
CellRangeAddress cellRangeAddress = new CellRangeAddress(startRowIndex, endRowIndex, startColumnIndex, endColumnIndex);
sheet.addMergedRegionUnsafe(cellRangeAddress);
}
/**
* 自定义导出Excel,设置浏览器响应信息
*/
public static void createExportExcelResponse(HttpServletResponse response, Workbook workBook) {
// response.setContentType("application/vnd.ms-excel");
// response.setCharacterEncoding("utf-8");
// 创建文件输出流,准备输出电子表格:这个必须有,否则你在sheet上做的任何操作都不会有效
OutputStream output;
try {
output = response.getOutputStream();
//清空缓存
response.reset();
//定义浏览器响应表头,顺带定义下载名,比如students(中文名需要转义)
String s = "" + System.currentTimeMillis();
response.setHeader("Content-disposition", "attachment;filename=" + new String(s.getBytes(), "iso-8859-1") + ".xls");
//定义下载的类型,标明是excel文件
response.setContentType("application/vnd.ms-excel");
//这时候把创建好的excel写入到输出流
workBook.write(output);
//养成好习惯,出门记得随手关门
output.close();
workBook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
*处理时间为空导出
*/
public static String convertDateToStringWith(Date date) {
if (date == null) {
// 如果Date是null,返回一个表示空值的字符串
return "";
} else {
// 使用Java 8的日期时间API将Date转换为LocalDateTime
Instant instant = Instant.ofEpochMilli(date.getTime());
LocalDateTime localDateTime = LocalDateTime.ofInstant(instant, ZoneId.systemDefault());
// 定义一个日期格式
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd").withLocale(Locale.getDefault());
// 将LocalDateTime格式化为字符串
return localDateTime.format(formatter);
}
}
/**
* 设置单元格样式
* @param workbook
* @param color
* @return
*/
public static CellStyle addExcelStyle(Workbook workbook, Short color) {
CellStyle style = workbook.createCellStyle();
style.setWrapText(true);
Font font = addFontStyle(workbook);
font.setBold(true);
style.setFont(font);
// 设置背景色
style.setFillForegroundColor(color);
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 设置垂直对齐为居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置边框
style.setBorderBottom(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
return style;
}
/**
* 设置Excel字体样式
* 字体:黑体 大小:10
*/
public static Font addFontStyle(Workbook workBook) {
Font font = workBook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 14);
return font;
}