该博客介绍了如何使用Apache POI库来实现复杂动态表头和合并单元格的Excel导出功能。通过创建workbook、设置列宽、定义样式以及处理多行表头，实现了自定义的月度业务明细报表。代码示例详细展示了创建标题、表头和内容样式的步骤，包括行高、字体样式、单元格合并等细节。
做项目的时候一个导出的需求，使用框架封装的导出功能不能满足复杂动态表头的导出，因此，使用POI原生导出，借鉴了一些网上的导出demo。
本文介绍 HSSFWorkbook 导出Excel多行表头、合并单元格的表格。
表头样式如下：
直接上代码：
**
* 导出excel(月度业务明细)
*/
@GetMapping("/export")
public void exportExcel(String startTime,BusinessDetail businessDetail){
//表头动态日期
String year= startTime.substring(0,4);
String month = startTime.substring(5,7);
String day = startTime.substring(8,10);
Integer srcyear = (Integer.parseInt(year)-1);
//1.创建一个workbook,对应一个excel文件
HSSFWorkbook wb = new HSSFWorkbook();
//2.在workbook中添加一个sheet,对应Excel中的sheet
HSSFSheet sheet = wb.createSheet("月度业务明细");
//设置每一列的列宽
sheet.setColumnWidth(0,256*15);
sheet.setColumnWidth(1,256*15);
sheet.setColumnWidth(2,256*15);
sheet.setColumnWidth(3,256*15);
sheet.setColumnWidth(4,256*15);
sheet.setColumnWidth(5,256*15);
sheet.setColumnWidth(6,256*15);
sheet.setColumnWidth(7,256*15);
sheet.setColumnWidth(8,256*15);
sheet.setColumnWidth(9,256*15);
sheet.setColumnWidth(10,256*15);
sheet.setColumnWidth(11,256*15);
sheet.setColumnWidth(12,256*15);
sheet.setColumnWidth(13,256*15);
sheet.setColumnWidth(14,256*15);
sheet.setColumnWidth(15,256*15);
//3.设置样式以及字体样式
HSSFCellStyle titleStyle = ExcelUtils.createTitleCellStyle(wb);
HSSFCellStyle headerStyle = ExcelUtils.createHeadCellStyle(wb);
HSSFCellStyle contentStyle = ExcelUtils.createContentCellStyle(wb);
//4.创建标题，合并标题单元格
//行号
int rowNum = 0;
//创建第一行，索引从0开始(标题行)
HSSFRow row0 = sheet.createRow(rowNum++);
row0.setHeight((short) 800);// 设置行高
String title = "月度业务明细";
HSSFCell c00 = row0.createCell(0);
c00.setCellValue(title);
c00.setCellStyle(titleStyle);
// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 15));//标题合并单元格操作，总列数为16
//第二行
HSSFRow row1 = sheet.createRow(rowNum++);
row1.setHeight((short)500);
String[] row_first = {"客户",month+"月发车",srcyear+"年12月31日前货款回收(期前欠款)",year+"年货款回收","截止"+year+"年"+month+"月"+day+"日业务明细","","","","","","","","","",month+"月份新增欠款",month+"月收款合计"};
for (int i = 0; i < row_first.length; i++) {
HSSFCell tempCell = row1.createCell(i);
tempCell.setCellValue(row_first[i]);
tempCell.setCellStyle(headerStyle);
}
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(1, 3, 0, 0));//经销商或客户
sheet.addMergedRegion(new CellRangeAddress(1, 3, 1, 1));//12月发车
sheet.addMergedRegion(new CellRangeAddress(1, 3, 2, 2));//期前欠款
sheet.addMergedRegion(new CellRangeAddress(1, 3, 3, 3));//2019年货款回收
sheet.addMergedRegion(new CellRangeAddress(1, 1, 4, 13));//截止2019年12月27日业务明细
sheet.addMergedRegion(new CellRangeAddress(1, 3, 14, 14));//12月份新增加欠款
sheet.addMergedRegion(new CellRangeAddress(1, 3, 15, 15));//12月份收款合计
//第三行
HSSFRow row2 = sheet.createRow(rowNum++);
row2.setHeight((short)500);
String[] row_second = {"","","","",month+"月合同发车","","",month+"月分期管理费及利息","","",month+"月代收代付运费","","",month+"月预付款","",""};
for (int i = 0; i < row_second.length; i++) {
HSSFCell tempCell = row2.createCell(i);
tempCell.setCellValue(row_second[i]);
tempCell.setCellStyle(headerStyle);
}
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(2, 2, 4, 6));
sheet.addMergedRegion(new CellRangeAddress(2, 2, 7, 9));
sheet.addMergedRegion(new CellRangeAddress(2, 2, 10, 12));
sheet.addMergedRegion(new CellRangeAddress(2, 3, 13, 13));
//第四行
HSSFRow row3 = sheet.createRow(rowNum++);
row3.setHeight((short)500);
String[] row_third = {"","","","","应收","实收","欠款","应收","实收","欠款","应收","实收","欠款","","",""};
for (int i = 0; i < row_third.length; i++) {
HSSFCell tempCell = row3.createCell(i);
tempCell.setCellValue(row_third[i]);
tempCell.setCellStyle(headerStyle);
}
//查询月度明细列表
List<BusinessDetail> list = businessService.selectMonthBusinessList(businessDetail);
for(int i = 0;i<list.size();i++){
HSSFRow tempRow = sheet.createRow(rowNum++);
tempRow.setHeight((short)500);
//循环单元格填入数据
for(int j=0;j<16;j++){
HSSFCell tempCell = tempRow.createCell(j);
tempCell.setCellStyle(contentStyle);
String cellValue = "";
if(j ==0){
//经销商
cellValue = list.get(i).getClientName();
}else if(j == 1){
cellValue = list.get(i).getDespatchAmount();
}else if(j ==2){
if(list.get(i).getBalance() ==null){
cellValue = "0";
}else{
cellValue = list.get(i).getBalance().toString();
}
}else if(j == 3){
if(list.get(i).getLoanRecovery() ==null){
cellValue = "0";
}else{
cellValue = list.get(i).getLoanRecovery().toString();
}
}else if(j ==4){
if(list.get(i).getTotalMoney() ==null){
cellValue = "0";
}else{
cellValue = list.get(i).getTotalMoney().toString();
}
}else if(j == 5){
if(list.get(i).getRepayTotal() ==null){
cellValue = "0";
}else{
cellValue = list.get(i).getRepayTotal().toString();
}
}else if(j == 6){
if(list.get(i).getRestTotal() ==null){
cellValue = "0";
}else{
cellValue = list.get(i).getRestTotal().toString();
}
}else if(j ==7){
if(list.get(i).getTotalManage() ==null){
cellValue = "0";
}else{
cellValue = list.get(i).getTotalManage().toString();
}
}else if(j == 8){
if(list.get(i).getRepayManage() ==null){
cellValue = "0";
}else{
cellValue = list.get(i).getRepayManage().toString();
}
}else if(j == 9){
if(list.get(i).getRestManage() ==null){
cellValue = "0";
}else{
cellValue = list.get(i).getRestManage().toString();
}
}else if(j == 10){
if(list.get(i).getTotalFreight() ==null){
cellValue = "0";
}else{
cellValue = list.get(i).getTotalFreight().toString();
}
}else if(j == 11){
if(list.get(i).getRepayFreight() ==null){
cellValue = "0";
}else{
cellValue = list.get(i).getRepayFreight().toString();
}
}else if( j == 12){
if(list.get(i).getRestFreight() ==null){
cellValue = "0";
}else{
cellValue = list.get(i).getRestFreight().toString();
}
}else if(j == 13){
if(list.get(i).getImprest() ==null){
cellValue = "0";
}else{
cellValue = list.get(i).getImprest().toString();
}
}else if(j ==14){
if(list.get(i).getNewBalance() ==null){
cellValue = "0";
}else{
cellValue = list.get(i).getNewBalance().toString();
}
}else if(j == 15){
if(list.get(i).getRepayTotal() ==null){
cellValue = "0";
}else{
cellValue = list.get(i).getRepayTotal().toString();
}
}
tempCell.setCellValue(cellValue);
}
}
//导出excel
HttpServletResponse response = this.getResponse();
String fileName = "月度业务明细.xls";
try {
fileName = new String(fileName.getBytes("UTF-8"),"ISO-8859-1");
response.setHeader("Content-disposition", "attachment;filename=\"" + fileName + "\"");
OutputStream stream = response.getOutputStream();
if(null != wb && null != stream){
wb.write(stream);
wb.close();
stream.close();
}
}catch (Exception e){
e.printStackTrace();
}
}
设置标题样式、表头样式以及内容样式：
单独写了一个ExcelUtils类，代码如下：
/**
* 创建标题样式
* @param wb
* @return
*/
public static HSSFCellStyle createTitleCellStyle(HSSFWorkbook wb){
HSSFCellStyle cellStyle = wb.createCellStyle();
//水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//垂直对齐
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//背景颜色
cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
HSSFFont headerFont1 = (HSSFFont) wb.createFont();
//字体加粗
headerFont1.setBold(true);
//字体类型
headerFont1.setFontName("黑体");
//字体大小
headerFont1.setFontHeightInPoints((short)15);
cellStyle.setFont(headerFont1);
return cellStyle;
}
/**
* 创建表头样式
* @param wb
* @return
*/
public static HSSFCellStyle createHeadCellStyle(HSSFWorkbook wb){
HSSFCellStyle cellStyle = wb.createCellStyle();
//设置自动换行
cellStyle.setWrapText(true);
//设置背景颜色
cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
//水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//垂直对齐
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
//下边框
cellStyle.setBorderBottom(BorderStyle.THIN);
//左边框
cellStyle.setBorderLeft(BorderStyle.THIN);
//右边框
cellStyle.setBorderRight(BorderStyle.THIN);
//上边框
cellStyle.setBorderTop(BorderStyle.THIN);
//创建字体样式
HSSFFont headerFont = (HSSFFont)wb.createFont();
//字体加粗
headerFont.setBold(true);
//字体类型
headerFont.setFontName("黑体");
//字体大小
headerFont.setFontHeightInPoints((short)12);
//为标题样式添加字体样式
cellStyle.setFont(headerFont);
return cellStyle;
}
/**
* 设置表格内容样式
* @param wb
* @return
*/
public static HSSFCellStyle createContentCellStyle(HSSFWorkbook wb){
HSSFCellStyle cellStyle = wb.createCellStyle();
//水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//垂直居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//设置自动换行
cellStyle.setWrapText(true);
//上边框
cellStyle.setBorderTop(BorderStyle.THIN);
//下边框
cellStyle.setBorderBottom(BorderStyle.THIN);
//左边框
cellStyle.setBorderLeft(BorderStyle.THIN);
//右边框
cellStyle.setBorderRight(BorderStyle.THIN);
//设置字体
HSSFFont font = (HSSFFont)wb.createFont();
font.setColor((short)8);
font.setFontHeightInPoints((short)12);
return cellStyle;
}
扫一扫
2880
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
