Java中使用 poi 导出复合表头（合并表头）
1、导出示例
2、需要导入的包 poi 坐标
<!--核心jar包--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <!--支持xlsx读取--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.17</version> </dependency>
3、代码
package org.alanlau.common.util; import org.apache.commons.fileupload.disk.DiskFileItem; import org.apache.commons.fileupload.disk.DiskFileItemFactory; import org.apache.commons.lang3.time.DateFormatUtils; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.RegionUtil; import org.apache.poi.xssf.usermodel.XSSFClientAnchor; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.apache.tomcat.util.http.fileupload.IOUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.web.multipart.MultipartFile; import org.springframework.web.multipart.commons.CommonsMultipartFile; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.io.UnsupportedEncodingException; import java.net.URLEncoder; import java.time.LocalDateTime; import java.time.format.DateTimeFormatter; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; /** * 支持复合表头导出的Excel工具类 * * @author liukun * @version 1.0 * @date 2020/2/18 20:22 */ public class ExcelUtils { private ExcelUtils() { throw new UnsupportedOperationException("initialization is prohibited..."); } private static Logger logger = LoggerFactory.getLogger(ExcelUtils.class); /** * 图片 */ public static class Image { private byte[] imageData; public Image(byte[] imageData) { this.imageData = imageData; } public byte[] getImageData() { return imageData; } } /** * 导出（目前仅支持小于两行表头的合并操作） * @param realName 导出文件名 * @param data 导出数据 * @param fields 导出字段,比如 date|时间,firstHeader|表头1,secondHeader|表头2,sub001|编号-0001|子标题1,sub002|编号-0001|子标题2,sub003|编号-0001|子标题3,sub004|编号-0001|子标题4,remark|备注 */ public static void export(HttpServletResponse response, String realName, List<Map<String, Object>> data, String fields) { String fileName = realName + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd_HHmmss")) + ".xlsx"; writeFileToResponse(construct(fields, data, fileName), response); } /** * 导出并转换时间格式（目前仅支持小于两行表头的合并操作） * @param realName 导出文件名 * @param data 导出数据 * @param fields 导出字段,比如 date|时间,firstHeader|表头1,secondHeader|表头2,sub001|编号-0001|子标题1,sub002|编号-0001|子标题2,sub003|编号-0001|子标题3,sub004|编号-0001|子标题4,remark|备注 * @param formatter 日期格式化字符串 */ public static void export(HttpServletResponse response, String realName, List<Map<String, Object>> data, String fields, String formatter) { convertDateToStr(data, formatter); export(response, realName, data, fields); } /** * 将文件写入响应流 * @param file 文件 * @param response 响应 * @throws UnsupportedEncodingException */ public static void writeFileToResponse(MultipartFile file, HttpServletResponse response) { // 设置文件ContentType类型，这样设置，会自动判断下载文件类型 response.setContentType("multipart/form-data"); InputStream inputStream = null; try { String fileName = URLEncoder.encode(((CommonsMultipartFile) file).getFileItem().getName(), "UTF-8"); response.setHeader("Content-Disposition", "attachment;filename=" + fileName); response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0"); response.setHeader("Pragma", "public"); response.setDateHeader("Expires", (System.currentTimeMillis() + 1000)); inputStream = file.getInputStream(); OutputStream out = response.getOutputStream(); byte[] buffer = new byte[1024]; int count = 0; while ((count = inputStream.read(buffer)) != -1) { out.write(buffer, 0, count); } out.close(); out.flush(); } catch (IOException e) { logger.error("文件下载异常：文件名【%s】", file.getName(), e); } finally { IOUtils.closeQuietly(inputStream); } } /** * 创建生成 excel 文档 * @param fields 导出字段 * @param data 导出数据 * @param fileName 导出文档名称 * @return 生成的 excel 文档 */ private static MultipartFile construct(String fields, List<Map<String, Object>> data, String fileName) { Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet(); CellStyle cellStyle = createCellStyle(workbook); Map<String, Integer> fieldOrder = getFieldOrder(fields); // 头部占的行数 int headerRows = calculateHeaderRows(fields); initHeader(sheet, cellStyle, fields, headerRows); fillData(workbook, sheet, cellStyle, data, fieldOrder, headerRows); DiskFileItem fileItem = (DiskFileItem)(new DiskFileItemFactory()).createItem("file", "text/plain", true, fileName); try { OutputStream os = fileItem.getOutputStream(); Throwable var8 = null; try { workbook.write(os); } catch (Throwable var18) { var8 = var18; throw var18; } finally { if (os != null) { if (var8 != null) { try { os.close(); } catch (Throwable var17) { var8.addSuppressed(var17); } } else { os.close(); } } } } catch (Exception var20) { throw new IllegalArgumentException("Invalid file: " + var20, var20); } return new CommonsMultipartFile(fileItem); } /** * 初始化表头 * @param sheet sheet页 * @param cellStyle 单元格样式 * @param fields 导出字段 * @param headerRows 头部所占的行数 */ private static void initHeader(Sheet sheet, CellStyle cellStyle, String fields, int headerRows) { // 构造表头 Row row1 = sheet.createRow(0); Row row2 = null; if (headerRows > 1) { row2 = sheet.createRow(1); } String[] field = fields.split(","); // 上一个合并的表头名称 String preMergeName = null; // 合并的起止索引 int startIdx = 0,endIdx = -1; for (int i = 0; i < field.length; i++) { String[] f = field[i].split("\\|"); Cell c1 = row1.createCell(i); c1.setCellStyle(cellStyle); c1.setCellValue(f[1]); if (f.length > 2) { Cell c2 = row2.createCell(i); c2.setCellStyle(cellStyle); c2.setCellValue(f[f.length - 1]); if (preMergeName == null) { preMergeName = f[1]; startIdx = i; endIdx = startIdx; } else if (preMergeName.equals(f[1])){ endIdx++; } else { mergeRegion(sheet, 0, 0, startIdx, endIdx); preMergeName = f[1]; startIdx = i; endIdx = startIdx; } } else { if (headerRows > 1) { mergeRegion(sheet, 0, 1, i, i); } if (preMergeName != null) { mergeRegion(sheet, 0, 0, startIdx, endIdx); preMergeName = null; } } // 处理最后一次循环 if (i == field.length -1) { if (preMergeName != null) { mergeRegion(sheet, 0, 0, startIdx, endIdx); } } } } /** * 计算表头需要占用的行数 * @param fields 导出字段 * @return 表头需要占用的行数 */ private static int calculateHeaderRows(String fields) { int rowMax = 0; int rowCount = 0; char[] chars = fields.toCharArray(); for (char c : chars) { if (c == '|') { rowCount ++; } else if (c == ',') { if (rowCount > rowMax) { rowMax = rowCount; } rowCount = 0; } } return rowMax; } /** * 填充数据 * @param workbook workbook文档 * @param sheet sheet页 * @param cellStyle 单元格样式 * @param data 待填充的数据 * @param fieldOrder 字段填充顺序 * @param headerRows 头部占的行数 */ private static void fillData(Workbook workbook, Sheet sheet, CellStyle cellStyle, List<Map<String, Object>> data, Map<String, Integer> fieldOrder, int headerRows) { for (int i = 0; i < data.size(); i++) { Row row = sheet.createRow(headerRows + i); data.get(i).forEach((key, value) -> { // 为了保证只导出 fields 中的字段，这里必须加这个判断 if (fieldOrder.get(key) != null) { Cell cell = row.createCell(fieldOrder.get(key)); cell.setCellStyle(cellStyle); if (value != null) { if (value instanceof Image) { setCellPicture(workbook, sheet, cell, (Image) value); } else { cell.setCellValue(String.valueOf(value)); } } else { cell.setCellValue(""); } } }); } } /** * 设置单元格图片（图片是悬浮的） * @param workbook workbook文档 * @param sheet sheet页 * @param cell 单元格 * @param image 图片 */ private static void setCellPicture(Workbook workbook, Sheet sheet, Cell cell, Image image) { int rowIndex = cell.getRowIndex(); int columnIndex = cell.getColumnIndex(); // 绘图对象 Drawing<?> patriarch = sheet.createDrawingPatriarch(); // 创建锚点 XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, columnIndex, rowIndex, columnIndex + 1, rowIndex + 1); patriarch.createPicture(anchor, workbook.addPicture(image.getImageData(), XSSFWorkbook.PICTURE_TYPE_JPEG)); } /** * 得到字段的行索引 * @param fields 字段 * @return 字段索引map */ private static Map<String, Integer> getFieldOrder(String fields) { Map<String, Integer> fieldOrder = new HashMap<>(16); String[] field = fields.split(","); for (int i = 0; i < field.length; ++i) { String[] f = field[i].split("\\|"); fieldOrder.put(f[0], i); } return fieldOrder; } /** * 创建自定义单元格样式 * @param workbook 工作簿 */ private static CellStyle createCellStyle(Workbook workbook) { // 为单元格设置边框线 CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); // 居中显示 cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); return cellStyle; } /** * 合并区域 * @param sheet sheet页 * @param firstRow 起始行 * @param lastRow 结束行 * @param firstCol 起始列 * @param lastCol 结束列 */ private static void mergeRegion(Sheet sheet,int firstRow, int lastRow, int firstCol, int lastCol) { if (firstRow == lastRow && firstCol == lastCol) { return; } CellRangeAddress cra = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol); sheet.addMergedRegion(cra); // 为合并后的单元格添加边框线 RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet); RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet); RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet); RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet); } /** * 转换时间格式 * @param data 导出数据 * @param formatter 转换格式 formatter */ private static void convertDateToStr(List<Map<String, Object>> data, String formatter) { for (Map<String, Object> datum : data) { for (Map.Entry<String, Object> stringObjectEntry : datum.entrySet()) { String key = stringObjectEntry.getKey(); Object value = stringObjectEntry.getValue(); if (value != null) { if (value instanceof Date) { datum.put(key, DateFormatUtils.format((Date) value, formatter)); } else if (value instanceof LocalDateTime) { datum.put(key, ((LocalDateTime) value).format(DateTimeFormatter.ofPattern(formatter))); } } } } } }
4、使用示例
@GetMapping("export-test")
public void exportTest(HttpServletResponse response) {
List<Map<String, Object>> mapList = new ArrayList<>();
// ... 这里忽略了得到数据的过程
String exportFields = "date|时间,firstHeader|表头1,secondHeader|表头2," +
"sub001|编号-0001|子标题1,sub002|编号-0001|子标题2,sub003|编号-0001|子标题3,sub004|编号-0001|子标题4,remark|备注";
ExcelUtils.export(response, "导出的文件名", mapList, exportFields, "yyyy-MM-dd HH:mm");
}
5、导出图片的示例
@GetMapping("/export")
public void export(HttpServletResponse response) throws IOException {
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
File file = new File("E:\\桌面壁纸\\yoona1.jpg");
BufferedImage bufferImg = ImageIO.read(file);
ImageIO.write(bufferImg, file.getName().substring(file.getName().lastIndexOf(".") + 1), byteArrayOut);
// 得到数据
List<SysUser> list = sysUserService.list(Wrappers.<SysUser>lambdaQuery().isNotNull(SysUser::getCreatedTime));
List<Map<String, Object>> mapList = BeanUtils.beansToMaps(list, SysUser.class);
// 导出的字段
String exportFields = "name|姓名,loginName|登录名,password|密码,pic|图片";
// 往导出的数据中加入图片
mapList.get(0).put("pic", new ExcelUtils.Image(byteArrayOut.toByteArray()));
mapList.get(1).put("pic", null);
ExcelUtils.export(response, "测试", mapList, exportFields);
byteArrayOut.close();
}
导出截图：
