如何導出復雜的excel 表格

如圖表格，存在行和列的合并，邊框，樣式，顏色等。

依賴

 <!-- https://mvnrepository.com/artifact/org.apache.poi/poi --><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.2</version></dependency>

實現的示例demo

 @GetMapping("/exportExcel")public void exportExcel(HttpServletRequest request, HttpServletResponse response) {try {HSSFWorkbook workbook = new HSSFWorkbook();//創建一個excel對象HSSFSheet sheet = workbook.createSheet();//創建一個工作薄對象HSSFRow row = sheet.createRow(0);HSSFCell createCell = row.createCell(0);
//            sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$E$1"));//單元格合并HSSFCellStyle cellStyle = workbook.createCellStyle();cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//25%灰色cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//顏色cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());// 設置邊框樣式為細線cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);cellStyle.setBorderBottom(BorderStyle.THIN);//字體水平和垂直居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中HSSFFont font = workbook.createFont();//字體,大小，粗細font.setFontName("宋體");font.setFontHeight((short) 20);font.setBold(true);//加粗cellStyle.setFont(font);createCell.setCellStyle(cellStyle);createCell.setCellValue("活動統計報表");
//            CellRangeAddress mergedRegion = new CellRangeAddress(0, 0, 0, 4);CellRangeAddress mergedRegion = CellRangeAddress.valueOf("$A$1:$E$1");sheet.addMergedRegion(mergedRegion);//單元格合并RegionUtil.setBorderTop(BorderStyle.THIN, mergedRegion, sheet);// 合并 A1:E1 設置邊框粗細RegionUtil.setBorderTop(BorderStyle.THIN, mergedRegion, sheet);RegionUtil.setBorderBottom(BorderStyle.THIN, mergedRegion, sheet);RegionUtil.setBorderLeft(BorderStyle.THIN, mergedRegion, sheet);RegionUtil.setBorderRight(BorderStyle.THIN, mergedRegion, sheet);row = sheet.createRow(1);createCell = row.createCell(0);font.setFontHeightInPoints((short) 12);font.setBold(true);cellStyle.setFont(font);createCell.setCellValue("2024-01-01~2024-01-31");createCell.setCellStyle(cellStyle);sheet.autoSizeColumn(0);sheet.autoSizeColumn(1);List<String> headerList = Arrays.asList("項目", "", "活動類型", "參數人數", "經費");List<List<String>> listData = new ArrayList<>();listData.add(Arrays.asList("球類賽事", "", "籃球", "16", "158"));listData.add(Arrays.asList("", "", "羽毛球", "8", "231"));listData.add(Arrays.asList("", "", "網球", "23", "143"));font.setBold(true);cellStyle.setFont(font);for (int j = 0; j < listData.size(); j++) {row = sheet.createRow(j + 3);List<String> strings = listData.get(j);for (int k = 0; k < headerList.size(); k++) {createCell = row.createCell(k, CellType.STRING);createCell.setCellStyle(cellStyle);String text = strings.get(k);text = null == text ? "" : text;createCell.setCellValue(text);sheet.autoSizeColumn(k);}}sheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$E$2"));sheet.addMergedRegion(CellRangeAddress.valueOf("$A$3:$E$3"));sheet.addMergedRegion(CellRangeAddress.valueOf("$A$4:$B$6"));ExcelUtil.setFileDownloadHeader(request, response, "測試" + System.currentTimeMillis() + ".xls");OutputStream outputStream = response.getOutputStream();workbook.write(outputStream);workbook.close();logger.info("導出成功");} catch (Exception e) {logger.error("導出失敗", e);}}

import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.util.CellReference;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;public class ExcelUtil {private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);public static void setFileDownloadHeader(HttpServletRequest request, HttpServletResponse response, String fileName) {String userAgent = request.getHeader("USER-AGENT");try {String finalFileName = null;if (StringUtils.contains(userAgent, "MSIE") || StringUtils.contains(userAgent, "Trident")) {finalFileName = URLEncoder.encode(fileName, "UTF8");} else if (StringUtils.contains(userAgent, "Mozilla")) {finalFileName = new String(fileName.getBytes(), "ISO8859-1");} else {finalFileName = URLEncoder.encode(fileName, "UTF8");}response.setHeader("Cache-Control", "private");response.setHeader("Pragma", "private");response.setContentType("application/vnd.ms-excel;charset=utf-8");response.setHeader("Content-Type", "application/force-download");response.setHeader("Content-Disposition", "attachment;filename=\"" + finalFileName + "\"");} catch (UnsupportedEncodingException e) {logger.error("導出設置錯誤", e);}}public static String columnIndexToColumnName(int columnIndex) {String string = CellReference.convertNumToColString(16384);return string;}
}