apache poi 导出繁琐的excel表格

如何导出复杂的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;
}
}