需要动态生成如下的表头,涉及到横向、纵向单元格合并,效果图:
使用的是SXSSFWorkbook,可用于数据量很大的情景,会将数据放在硬盘上,防止堆内存溢出
代码如下:
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.junit.jupiter.api.Test;
import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
/**
* ExcelTest
*
* @author zhouxy
* @date 2022/5/11
**/
@Slf4j
public class ExcelTest {
/**
* 导出excel (HSSFWorkbook)
*/
@Test
public void exportExcel() {
/** 第一步,创建一个Workbook,对应一个Excel文件 */
SXSSFWorkbook wb = new SXSSFWorkbook();
/** 第二步,在Workbook中添加一个sheet,对应Excel文件中的sheet */
SXSSFSheet sheet = wb.createSheet("报表");
/** 第三步,创建标题 ,合并标题单元格 */
// 行号
int rowNum = 0;
// 创建第一页的第一行,索引从0开始
SXSSFRow row0 = sheet.createRow(rowNum++);
row0.setHeight((short) 800);// 设置行高
String title = "报表";
SXSSFCell c00 = row0.createCell(0);
c00.setCellValue(title);
CellStyle titleStyle = createTitleCellStyle(wb);
CellStyle headerStyle = createHeadCellStyle(wb);
c00.setCellStyle(titleStyle);
// 合并单元格,参数依次为起始行,结束行,起始列,结束列 (索引0开始)
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));//标题合并单元格操作,6为总列数
// 第二行
SXSSFRow row1 = sheet.createRow(rowNum++);
row1.setHeight((short) 500);
String[] row_first = {"姓名", "年龄", "年级", "成绩", "", "", "", "", ""};
for (int i = 0; i < row_first.length; i++) {
SXSSFCell tempCell = row1.createCell(i);
tempCell.setCellValue(row_first[i]);
tempCell.setCellStyle(headerStyle);
// 合并
if (i < 3) {
sheet.addMergedRegion(new CellRangeAddress(1, 2, i, i));
}
}
sheet.addMergedRegion(new CellRangeAddress(1, 1, 3, 8));
//设置第三行标题
String[] titles_row2 = {"语文", "数学", "英语", "生物", "物理",
"化学"};
SXSSFRow title_row2 = sheet.createRow(2);
for (int j = 0; j <= 8; j++) {
SXSSFCell tempCell = title_row2.createCell(j);
tempCell.setCellStyle(headerStyle);
if(j >= 3){
tempCell.setCellValue(titles_row2[j - 3]);
}
}
//导出
try (FileOutputStream excel = new FileOutputStream("excel.xls");
BufferedOutputStream bos = new BufferedOutputStream(excel)) {
wb.write(bos);
System.out.println("导出完成");
} catch (IOException e) {
System.out.println("导出失败:" + e.getMessage());
}
}
/**
* 创建标题样式
*
* @param wb
* @return
*/
private static CellStyle createTitleCellStyle(SXSSFWorkbook wb) {
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());//背景颜色
Font headerFont1 = wb.createFont(); // 创建字体样式
headerFont1.setFontName("黑体"); // 设置字体类型
headerFont1.setFontHeightInPoints((short) 15); // 设置字体大小
cellStyle.setFont(headerFont1); // 为标题样式设置字体样式
return cellStyle;
}
/**
* 创建表头样式
*
* @param wb
* @return
*/
private static CellStyle createHeadCellStyle(SXSSFWorkbook wb) {
CellStyle 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); //上边框
Font headerFont = wb.createFont(); // 创建字体样式
headerFont.setFontName("黑体"); // 设置字体类型
headerFont.setFontHeightInPoints((short) 12); // 设置字体大小
cellStyle.setFont(headerFont); // 为标题样式设置字体样式
return cellStyle;
}
}