POI XSSFWorkbook 导出excel合并单元格
一，背景介绍
需求，需要导出一个excel，带单元格合并，类似于：
二，代码
网上找了一份代码，自己做了一些更新和修改。
核心代码：
// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 1));
实例：
package com.madorangecat.artmartxt.utils.excel;import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import java.util.ArrayList;import java.util.List;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.usermodel.*;/*** @Author:刘德安* @Date: /1/11 10:37*/public class TestExcelDemo {public static void main(String[] args) {Student student = new Student();student.setName("XXX ");student.setResult("95");student.setClazz("二班");student.setGrade("五年级");student.setNumber("66");List<Student> list = new ArrayList<>();list.add(student);list.add(student);list.add(student);// testExcelDemo(list);testExcelDemoPlus(list);}/*** @param list 需要写入excel的数据 从数据库或者其他途径读取*/public static void testExcelDemoPlus(List<Student> list) {/** 第一步，创建一个Workbook，对应一个Excel文件 */XSSFWorkbook wb = new XSSFWorkbook();/** 第二步，在Workbook中添加一个sheet,对应Excel文件中的sheet */XSSFSheet sheet = wb.createSheet("excel导出标题sheet");/** 第三步，设置样式以及字体样式*/XSSFCellStyle titleStyle = createTitleCellStyle(wb);XSSFCellStyle headerStyle = createHeadCellStyle(wb);XSSFCellStyle contentStyle = createContentCellStyle(wb);/** 第四步，创建标题 ,合并标题单元格 */// 行号int rowNum = 0;// 创建第一页的第一行，索引从0开始XSSFRow row0 = sheet.createRow(rowNum);row0.setHeight((short) 800);// 设置行高String title = "excel导出标题01";XSSFCell c00 = row0.createCell(0);c00.setCellValue(title);c00.setCellStyle(titleStyle);// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 1));//标题合并单元格操作，6为总列数String title1 = "excel导出标题02";XSSFCell c001 = row0.createCell(2);c001.setCellValue(title1);c001.setCellStyle(titleStyle);// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）sheet.addMergedRegion(new CellRangeAddress(0, 0, 2, 3));//标题合并单元格操作，6为总列数// rowNum = 1;// XSSFRow row1 = sheet.createRow(rowNum);// row1.setHeight((short) 800);// 设置行高String title3 = "excel导出标题03";XSSFCell c003 = row0.createCell(4);c003.setCellValue(title3);c003.setCellStyle(titleStyle);// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）sheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4));//标题合并单元格操作，6为总列数//第二行rowNum = 2;XSSFRow row2 = sheet.createRow(rowNum);row2.setHeight((short) 700);String[] row_third = {"学号", "姓名", "年级", "班级", "成绩"};for (int i = 0; i < row_third.length; i++) {//sheet.setColumnWidth(i, 256*30); //设置列宽度XSSFCell tempCell = row2.createCell(i);tempCell.setCellValue(row_third[i]);tempCell.setCellStyle(headerStyle);}rowNum = 3;for (Student student : list) {XSSFRow tempRow = sheet.createRow(rowNum);rowNum++;tempRow.setHeight((short) 500);// 循环单元格填入数据for (int j = 0; j < 5; j++) {XSSFCell tempCell = tempRow.createCell(j);tempCell.setCellStyle(contentStyle);String tempValue = "";if (j == 0) {// 学号tempValue = student.getNumber();} else if (j == 1) {// 姓名tempValue = student.getName();} else if (j == 2) {// 年级tempValue = student.getGrade();} else if (j == 3) {// 班级tempValue = student.getClazz();} else if (j == 4) {// 成绩tempValue = student.getResult();}tempCell.setCellValue(tempValue);}}String filePath = "D:\\Demo\\";String fileName = "testExcelDemo.xlsx";File file = new File(filePath + fileName);FileOutputStream fos = null;try {fos = new FileOutputStream(file);// 写入磁盘wb.write(fos);fos.close();//记得关闭} catch (IOException e) {e.printStackTrace();}}/*** @param list 需要写入excel的数据 从数据库或者其他途径读取*/public static void testExcelDemo(List<Student> list) {/** 第一步，创建一个Workbook，对应一个Excel文件 */XSSFWorkbook wb = new XSSFWorkbook();/** 第二步，在Workbook中添加一个sheet,对应Excel文件中的sheet */XSSFSheet sheet = wb.createSheet("excel导出标题");/** 第三步，设置样式以及字体样式*/XSSFCellStyle titleStyle = createTitleCellStyle(wb);XSSFCellStyle headerStyle = createHeadCellStyle(wb);XSSFCellStyle contentStyle = createContentCellStyle(wb);/** 第四步，创建标题 ,合并标题单元格 */// 行号int rowNum = 0;// 创建第一页的第一行，索引从0开始XSSFRow row0 = sheet.createRow(rowNum++);row0.setHeight((short) 800);// 设置行高String title = "excel导出标题";XSSFCell c00 = row0.createCell(0);c00.setCellValue(title);c00.setCellStyle(titleStyle);// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 2));//标题合并单元格操作，6为总列数//第二行XSSFRow row2 = sheet.createRow(rowNum++);row2.setHeight((short) 700);String[] row_third = {"学号", "姓名", "年级", "班级", "成绩"};for (int i = 0; i < row_third.length; i++) {//sheet.setColumnWidth(i, 256*30); //设置列宽度XSSFCell tempCell = row2.createCell(i);tempCell.setCellValue(row_third[i]);tempCell.setCellStyle(headerStyle);}for (Student student : list) {XSSFRow tempRow = sheet.createRow(rowNum++);tempRow.setHeight((short) 500);// 循环单元格填入数据for (int j = 0; j < 5; j++) {XSSFCell tempCell = tempRow.createCell(j);tempCell.setCellStyle(contentStyle);String tempValue = "";if (j == 0) {// 学号tempValue = student.getNumber();} else if (j == 1) {// 姓名tempValue = student.getName();} else if (j == 2) {// 年级tempValue = student.getGrade();} else if (j == 3) {// 班级tempValue = student.getClazz();} else if (j == 4) {// 成绩tempValue = student.getResult();}tempCell.setCellValue(tempValue);}}String filePath = "D:\\Demo\\";String fileName = "testExcelDemo.xlsx";File file = new File(filePath + fileName);FileOutputStream fos = null;try {fos = new FileOutputStream(file);// 写入磁盘wb.write(fos);fos.close();//记得关闭} catch (IOException e) {e.printStackTrace();}}/*** 创建标题样式** @param wb* @return*/private static XSSFCellStyle createTitleCellStyle(XSSFWorkbook wb) {XSSFCellStyle cellStyle = wb.createCellStyle();cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());//背景颜色XSSFFont headerFont1 = (XSSFFont) wb.createFont(); // 创建字体样式headerFont1.setBold(true); //字体加粗headerFont1.setFontName("黑体"); // 设置字体类型headerFont1.setFontHeightInPoints((short) 15); // 设置字体大小cellStyle.setFont(headerFont1); // 为标题样式设置字体样式return cellStyle;}/*** 创建表头样式** @param wb* @return*/private static XSSFCellStyle createHeadCellStyle(XSSFWorkbook wb) {XSSFCellStyle 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); //上边框XSSFFont headerFont = (XSSFFont) wb.createFont(); // 创建字体样式headerFont.setBold(true); //字体加粗headerFont.setFontName("黑体"); // 设置字体类型headerFont.setFontHeightInPoints((short) 12); // 设置字体大小cellStyle.setFont(headerFont); // 为标题样式设置字体样式return cellStyle;}/*** 创建内容样式** @param wb* @return*/private static XSSFCellStyle createContentCellStyle(XSSFWorkbook wb) {XSSFCellStyle cellStyle = wb.createCellStyle();cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中cellStyle.setWrapText(true);// 设置自动换行cellStyle.setBorderBottom(BorderStyle.THIN); //下边框cellStyle.setBorderLeft(BorderStyle.THIN); //左边框cellStyle.setBorderRight(BorderStyle.THIN); //右边框cellStyle.setBorderTop(BorderStyle.THIN); //上边框// 生成12号字体XSSFFont font = wb.createFont();font.setColor((short) 8);font.setFontHeightInPoints((short) 12);cellStyle.setFont(font);return cellStyle;}}
实体类：
package com.madorangecat.artmartxt.utils.excel;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;/*** @description:* @author: LCM* @create: -10-07 16:50**/@Data@AllArgsConstructor@NoArgsConstructorpublic class Student {private String name;private String result;private String clazz;private String grade;private String number;}
