导出 excel 多 sheet 且有跨列及背景色样式

引入依赖包，代码可直接运行 main 方法看效果。希望能帮到你

import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * 导出excel多sheet且有跨列及背景色样式
 *
 * @author Stone
 */
public class ExportExcelUtil {

    /**
     * 导出多sheet格式的excel
     *
     * @param fileSavePath 导出文件保存路径
     * @param workbook     工作薄对象
     * @param sheetIndex   sheet位置，0表示第一个表格中的第一个sheet
     * @param sheetName    sheet名称
     * @param sheetTitle   sheet标题
     * @param headers      sheet各列标题头
     * @param data         sheet数据集
     * @param weekbottomsW sheet底部合计行（外部）
     * @param weekbottomsN sheet底部合计行（内部）
     */
    public void exportExcel(String fileSavePath, HSSFWorkbook workbook, int sheetIndex, String sheetName, String sheetTitle, String[] headers, List<List<String>> data, String[] weekbottomsW, String[] weekbottomsN) {
        try {
            //创建工作薄
            HSSFSheet sheet = workbook.createSheet();
            //设置sheet位置及名称
            workbook.setSheetName(sheetIndex, sheetName);
            //设置工作薄默认列宽度，单位：字节
            sheet.setDefaultColumnWidth((short) 23);
            //创建sheet页公共标题行及样式
            Row rowTitle = sheet.createRow(0);
            //标题字体
            Font fontTitle = workbook.createFont();
            //字体字号
            fontTitle.setFontHeightInPoints((short) 11);
            //是否加粗
            fontTitle.setBold(true);
            //标题样式
            CellStyle styleTitle = workbook.createCellStyle();
            styleTitle.setBorderBottom(BorderStyle.THIN);
            styleTitle.setBorderLeft(BorderStyle.THIN);
            styleTitle.setBorderRight(BorderStyle.THIN);
            styleTitle.setBorderTop(BorderStyle.THIN);
            styleTitle.setAlignment(HorizontalAlignment.CENTER);
            styleTitle.setFont(fontTitle);
            //标题所在行号0标识当前sheet页第1行
            Cell cellTitle = rowTitle.createCell(0);
            //应用样式
            cellTitle.setCellStyle(styleTitle);
            //标题行跨列设置
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length - 1));
            cellTitle.setCellValue(sheetTitle);

            //创建表格各列标题行
            Row rowHeade = sheet.createRow(1);
            for (int i = 0; i < headers.length; i++) {
                Cell cellHeader = rowHeade.createCell((short) i);
                cellHeader.setCellStyle(styleTitle);
                HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                cellHeader.setCellValue(text.toString());
            }

            //遍历数据集合，创建数据行
            if (data != null) {
                //从第3行开始写入数据行
                int index = 2;

                //临时列内容，用于判断背景色是否应发生变化
                String code = "";
                //是否设置背景色
                boolean isColor = true;

                for (List<String> sheetData : data) {
                    //创建数据行
                    Row rowData = sheet.createRow(index);

                    String entName = sheetData.get(0);
                    if(StringUtil.isNotEmpty(code) && !entName.equals(code)){
                        if(isColor){
                            isColor= false;
                        }else{
                            isColor= true;
                        }
                    }
                    code = entName;

                    //从第1列开始写入数据列
                    int cellIndex = 0;
                    for (String sd : sheetData) {
                        Cell cellData = rowData.createCell((short) cellIndex);
                        if(isColor){
                            cellData.setCellStyle(getCellStyleBlue(workbook));
                        }else{
                            cellData.setCellStyle(getCellStyleWhite(workbook));
                        }
                        cellData.setCellValue(sd.toString());
                        cellIndex++;
                    }
                    index++;
                }
            }

            //创建表格底部计算行（外部）
            Row bottomsW = sheet.createRow(data.size() + 2);
            Cell cellBottom = null;
            for (int i = 0; i < weekbottomsW.length; i++) {
                cellBottom = bottomsW.createCell((short) i);
                cellBottom.setCellStyle(styleTitle);
                HSSFRichTextString text = new HSSFRichTextString(weekbottomsW[i]);
                if (i == 0) {
                    sheet.addMergedRegion(new CellRangeAddress(data.size() + 2, data.size() + 2, 0, 1));
                }
                cellBottom.setCellValue(text.toString());
            }
            //创建表格底部计算行（内部）
            Row bottomsN = sheet.createRow(data.size() + 3);
            for (int i = 0; i < weekbottomsN.length; i++) {
                cellBottom = bottomsN.createCell((short) i);
                cellBottom.setCellStyle(styleTitle);
                HSSFRichTextString text = new HSSFRichTextString(weekbottomsN[i]);
                if (i == 0) {
                    sheet.addMergedRegion(new CellRangeAddress(data.size() + 3, data.size() + 3, 0, 1));
                }
                cellBottom.setCellValue(text.toString());
            }

            //初始化文件流对象
            OutputStream file = new FileOutputStream(fileSavePath);
            //将数据写入工作薄
            workbook.write(file);
            //关闭文件流
            file.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public CellStyle getCellStyleBlue (HSSFWorkbook workbook){
        //数据行字体设置
        Font fontData = workbook.createFont();
        fontData.setFontHeightInPoints((short) 11);
        //数据行样式设置
        CellStyle cellStyle = workbook.createCellStyle();
        //背景色
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setFont(fontData);
        //设置背景色为浅蓝色
        cellStyle.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        return cellStyle;
    }

    public CellStyle getCellStyleWhite (HSSFWorkbook workbook){
        //数据行字体设置
        Font fontData = workbook.createFont();
        fontData.setFontHeightInPoints((short) 11);
        //数据行样式设置
        CellStyle cellStyle = workbook.createCellStyle();
        //背景色
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setFont(fontData);
        cellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
        return cellStyle;
    }

    public static void main(String[] args) {
        try {
            //导出文件名
            String fileName = new StringBuilder("导出Excel文件名").append(".xls").toString();
            List<List<String>> weekSheet1Data = new ArrayList<List<String>>();
            String weekSheet1bottomsWSumA = "-";
            String weekSheet1bottomsWSumB = "-";
            String weekSheet1bottomsWSumC = "-";
            String weekSheet1bottomsNSumA = "-";
            String weekSheet1bottomsNSumB = "-";
            String weekSheet1bottomsNSumC = "-";
            String[] weekSheet1bottomsW = {"合计（外部）", "", weekSheet1bottomsWSumA, weekSheet1bottomsWSumB, weekSheet1bottomsWSumC};
            String[] weekSheet1bottomsN = {"合计（内部）", "", weekSheet1bottomsNSumA, weekSheet1bottomsNSumB, weekSheet1bottomsNSumC};
            List<List<String>> weekSheet2Data = new ArrayList<List<String>>();
            String weekSheet2bottomsWSumA = "-";
            String weekSheet2bottomsWSumB = "-";
            String weekSheet2bottomsWSumC = "-";
            String weekSheet2bottomsNSumA = "-";
            String weekSheet2bottomsNSumB = "-";
            String weekSheet2bottomsNSumC = "-";
            String[] weekSheet2bottomsW = {"合计（外部）", "", weekSheet2bottomsWSumA, weekSheet2bottomsWSumB, weekSheet2bottomsWSumC};
            String[] weekSheet2bottomsN = {"合计（内部）", "", weekSheet2bottomsNSumA, weekSheet2bottomsNSumB, weekSheet2bottomsNSumC};
            List<List<String>> weekSheet3Data = new ArrayList<List<String>>();
            String weekSheet3bottomsWSumA = "-";
            String weekSheet3bottomsWSumB = "-";
            String weekSheet3bottomsWSumC = "-";
            String weekSheet3bottomsNSumA = "-";
            String weekSheet3bottomsNSumB = "-";
            String weekSheet3bottomsNSumC = "-";
            String[] weekSheet3bottomsW = {"合计（外部）", "", weekSheet3bottomsWSumA, weekSheet3bottomsWSumB, weekSheet3bottomsWSumC};
            String[] weekSheet3bottomsN = {"合计（内部）", "", weekSheet3bottomsNSumA, weekSheet3bottomsNSumB, weekSheet3bottomsNSumC};

            List rowData1 = new ArrayList<Object>() {{
                this.add("北京市");
                this.add("某公司");
                this.add("1234");
                this.add("567");
                this.add("89");
            }};
            List rowData2 = new ArrayList<Object>() {{
                this.add("北京市");
                this.add("某某公司");
                this.add("2334");
                this.add("677");
                this.add("90");
            }};
            List rowData3 = new ArrayList<Object>() {{
                this.add("河北省");
                this.add("X公司");
                this.add("8934");
                this.add("547");
                this.add("69");
            }};
            List rowData4 = new ArrayList<Object>() {{
                this.add("河北省");
                this.add("XX公司");
                this.add("3934");
                this.add("647");
                this.add("93");
            }};
            List rowData5 = new ArrayList<Object>() {{
                this.add("山东省");
                this.add("公司名称");
                this.add("5234");
                this.add("787");
                this.add("83");
            }};
            List rowData6 = new ArrayList<Object>() {{
                this.add("陕西省");
                this.add("公司公司");
                this.add("9034");
                this.add("737");
                this.add("59");
            }};
            List rowData7 = new ArrayList<Object>() {{
                this.add("陕西省");
                this.add("某有限公司");
                this.add("8834");
                this.add("667");
                this.add("73");
            }};
            List rowData8 = new ArrayList<Object>() {{
                this.add("青海省");
                this.add("0.0有限责任公司");
                this.add("7634");
                this.add("337");
                this.add("82");
            }};
            List rowData9 = new ArrayList<Object>() {{
                this.add("青海省");
                this.add("青海X有限责任公司");
                this.add("6334");
                this.add("737");
                this.add("66");
            }};

            weekSheet1Data.add(rowData1);
            weekSheet1Data.add(rowData2);
            weekSheet1Data.add(rowData3);
            weekSheet1Data.add(rowData4);
            weekSheet1Data.add(rowData5);
            weekSheet1Data.add(rowData6);
            weekSheet1Data.add(rowData7);
            weekSheet1Data.add(rowData8);
            weekSheet1Data.add(rowData9);

            weekSheet2Data.add(rowData1);
            weekSheet2Data.add(rowData2);
            weekSheet2Data.add(rowData3);
            weekSheet2Data.add(rowData4);
            weekSheet2Data.add(rowData5);
            weekSheet2Data.add(rowData6);
            weekSheet2Data.add(rowData7);
            weekSheet2Data.add(rowData8);
            weekSheet2Data.add(rowData9);

            weekSheet3Data.add(rowData1);
            weekSheet3Data.add(rowData2);
            weekSheet3Data.add(rowData3);
            weekSheet3Data.add(rowData4);
            weekSheet3Data.add(rowData5);
            weekSheet3Data.add(rowData6);
            weekSheet3Data.add(rowData7);
            weekSheet3Data.add(rowData8);
            weekSheet3Data.add(rowData9);


            //导出文件保存路径
            String fileSavePath = new StringBuilder("D:\\").append(fileName).toString();
            //工作薄标题头，假如有5列
            String[] weekHeaders = {"省", "公司", "Num1", "Num2", "Num3"};

            ExportExcelUtil util = new ExportExcelUtil();
            HSSFWorkbook workbook = new HSSFWorkbook();
            //假如需要写入三个Sheet工作薄
            util.exportExcel(fileSavePath, workbook, 0, "一区", "统计", weekHeaders, weekSheet1Data, weekSheet1bottomsW, weekSheet1bottomsN);
            util.exportExcel(fileSavePath, workbook, 1, "二区", "统计", weekHeaders, weekSheet2Data, weekSheet2bottomsW, weekSheet2bottomsN);
            util.exportExcel(fileSavePath, workbook, 2, "三区", "统计", weekHeaders, weekSheet3Data, weekSheet3bottomsW, weekSheet3bottomsN);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

​

英雄留步！走过路过不要错过，既然来了就恭请加入 平等 • 自由 • 奔放 的 黑客派社区，一起学习，相互分享，共同进步吧 😄
注册邀请链接：https://hacpai.com/register?r=Stone