Java poi 3.9 实现多sheet 多列头合并单元格动态导出

skvmeimei

已于 2022-07-10 10:16:46 修改

阅读量843

点赞数

分类专栏： java java 导入/导出文章标签： java Java POI实现导入导出

于 2022-07-10 10:07:32 首次发布

本文链接：https://blog.csdn.net/skvmeimei/article/details/125702392

版权

1.maven 引用jar

<!--依赖的jar包-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>

2.ExcelSheetTools 创建excel sheet 工具类

package smm.excel.muti;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.*;

public class ExcelSheetTools {
    /**
     * 生成单个工作表 多列头合并单元格
     * @param workbook
     * @param sheetName
     * @param dataMap
     */
    public static void createExcelSheet(HSSFWorkbook workbook , String sheetName, Map dataMap,int type){
        try {
            HSSFSheet sheet = workbook.createSheet(sheetName);
            int columnSize = 7;
            //设置列宽
            for(int i = 0;i< columnSize;i++ ){
                sheet.setColumnWidth(i, 4000);
            }

            // 设置字体
            HSSFFont headfont = workbook.createFont();
            headfont.setFontName("宋体");
            // 字体大小
            headfont.setFontHeightInPoints((short) 22);
            // 加粗
            headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            //头部样式
            HSSFCellStyle headstyle = workbook.createCellStyle();
            headstyle.setFont(headfont);
            // 左右居中
            headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            // 上下居中
            headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            headstyle.setLocked(true);
            // 自动换行
            headstyle.setWrapText(true);
            headstyle.setBorderLeft((short) 1);
            headstyle.setLeftBorderColor(HSSFColor.BLACK.index);
            headstyle.setRightBorderColor(HSSFColor.BLACK.index);
            headstyle.setBottomBorderColor(HSSFColor.BLACK.index);
            headstyle.setBorderBottom((short) 1);
            headstyle.setBorderRight((short) 1);
            // 加粗字体样式
            HSSFFont columnHeadFont = workbook.createFont();
            columnHeadFont.setFontName("宋体");
            columnHeadFont.setFontHeightInPoints((short) 12);
            columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

            // 列头的样式
            HSSFCellStyle columnHeadStyle = workbook.createCellStyle();
            columnHeadStyle.setFont(columnHeadFont);
            columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            columnHeadStyle.setLocked(true);
            columnHeadStyle.setWrapText(true);
            columnHeadStyle.setTopBorderColor(HSSFColor.BLACK.index);
            columnHeadStyle.setBorderTop((short) 1);
            columnHeadStyle.setLeftBorderColor(HSSFColor.BLACK.index);
            columnHeadStyle.setBorderLeft((short) 1);
            columnHeadStyle.setRightBorderColor(HSSFColor.BLACK.index);
            columnHeadStyle.setBorderRight((short) 1);
            columnHeadStyle.setBottomBorderColor(HSSFColor.BLACK.index);
            columnHeadStyle.setBorderBottom((short) 1);
            HSSFFont font = workbook.createFont();
            font.setFontName("宋体");
            font.setFontHeightInPoints((short) 12);
            /**内容居中单元格样式*/
            HSSFCellStyle centerstyle = workbook.createCellStyle();
            centerstyle.setFont(font);
            centerstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            centerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            centerstyle.setWrapText(true);
            centerstyle.setLeftBorderColor(HSSFColor.BLACK.index);
            centerstyle.setBorderLeft((short) 1);
            centerstyle.setTopBorderColor(HSSFColor.BLACK.index);
            centerstyle.setBorderTop((short) 1);
            centerstyle.setRightBorderColor(HSSFColor.BLACK.index);
            centerstyle.setBorderRight((short) 1);
            centerstyle.setBottomBorderColor(HSSFColor.BLACK.index);
            centerstyle.setBorderBottom((short) 1);
            centerstyle.setFillForegroundColor(HSSFColor.WHITE.index);
            /**内容居左单元格样式*/
            HSSFCellStyle leftstyle = workbook.createCellStyle();
            leftstyle.setFont(font);
            leftstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
            leftstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            leftstyle.setWrapText(true);
            leftstyle.setLeftBorderColor(HSSFColor.BLACK.index);
            leftstyle.setBorderLeft((short) 1);
            leftstyle.setTopBorderColor(HSSFColor.BLACK.index);
            leftstyle.setBorderTop((short) 1);
            leftstyle.setRightBorderColor(HSSFColor.BLACK.index);
            leftstyle.setBorderRight((short) 1);
            leftstyle.setBottomBorderColor(HSSFColor.BLACK.index);
            leftstyle.setBorderBottom((short) 1);
            leftstyle.setFillForegroundColor(HSSFColor.WHITE.index);
            /**跨行跨列无边框线样式*/
            HSSFCellStyle rowColstyle = workbook.createCellStyle();
            rowColstyle.setFont(font);
            rowColstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
            rowColstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            rowColstyle.setWrapText(true);
            rowColstyle.setLeftBorderColor(HSSFColor.WHITE.index);
            rowColstyle.setBorderLeft((short) 1);
            rowColstyle.setTopBorderColor(HSSFColor.WHITE.index);
            rowColstyle.setBorderTop((short) 1);
            rowColstyle.setRightBorderColor(HSSFColor.BLACK.index);
            rowColstyle.setBorderRight((short) 1);
            rowColstyle.setBottomBorderColor(HSSFColor.WHITE.index);
            rowColstyle.setBorderBottom((short) 1);
            rowColstyle.setFillForegroundColor(HSSFColor.WHITE.index);


            HSSFCellStyle rowColstyle1 = workbook.createCellStyle();
            rowColstyle1.setFont(font);
            rowColstyle1.setAlignment(HSSFCellStyle.ALIGN_LEFT);
            rowColstyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            rowColstyle1.setWrapText(true);

            rowColstyle1.setRightBorderColor(HSSFColor.BLACK.index);
            rowColstyle1.setBorderRight((short) 1);
            rowColstyle1.setBottomBorderColor(HSSFColor.WHITE.index);

            rowColstyle1.setFillForegroundColor(IndexedColors.TAN.getIndex());
            rowColstyle1.setFillPattern(CellStyle.SOLID_FOREGROUND);
            /*//**字体红色无边框居左*//*
            HSSFFont redFont = workbook.createFont();
            redFont.setFontName("宋体");
            redFont.setFontHeightInPoints((short) 12);
            redFont.setColor(HSSFColor.RED.index);
            HSSFCellStyle redStyle = workbook.createCellStyle();
            redStyle.setFont(redFont);
            redStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
            redStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            redStyle.setWrapText(true);
            redStyle.setLeftBorderColor(HSSFColor.WHITE.index);
            redStyle.setBorderLeft((short) 1);
            redStyle.setTopBorderColor(HSSFColor.WHITE.index);
            redStyle.setBorderTop((short) 1);
            redStyle.setRightBorderColor(HSSFColor.BLACK.index);
            redStyle.setBorderRight((short) 1);
            redStyle.setBottomBorderColor(HSSFColor.WHITE.index);
            redStyle.setBorderBottom((short) 1);
            redStyle.setFillForegroundColor(HSSFColor.WHITE.index);
            *//**字体红色有边框居中*//*
            HSSFCellStyle redCenterStyle = workbook.createCellStyle();
            redCenterStyle.setFont(redFont);
            redCenterStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            redCenterStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            redCenterStyle.setWrapText(true);
            redCenterStyle.setLeftBorderColor(HSSFColor.BLACK.index);
            redCenterStyle.setBorderLeft((short) 1);
            redCenterStyle.setTopBorderColor(HSSFColor.BLACK.index);
            redCenterStyle.setBorderTop((short) 1);
            redCenterStyle.setRightBorderColor(HSSFColor.BLACK.index);
            redCenterStyle.setBorderRight((short) 1);
            redCenterStyle.setBottomBorderColor(HSSFColor.BLACK.index);
            redCenterStyle.setBorderBottom((short) 1);
            redCenterStyle.setFillForegroundColor(HSSFColor.WHITE.index);
            *//**字体红色加粗有边框居中*//*
            HSSFFont redBoldFont = workbook.createFont();
            redBoldFont.setFontName("宋体");
            redBoldFont.setFontHeightInPoints((short) 12);
            redBoldFont.setColor(HSSFColor.RED.index);
            redBoldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            HSSFCellStyle redBoldStyle = workbook.createCellStyle();
            redBoldStyle.setFont(redBoldFont);
            redBoldStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            redBoldStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CE