Excel导出含二级表头

代码是学习此链接
代码大致流程如下（用的是POI）：

1. 拿到所有表头放到二维数组，其中有一级表头的列：创建一维新数组，把一级放数组的[0]，二级放数组的[1][2]这样的，内容放到list

6. 把数据写入，也分二级和一级的情况

代码：

import com.iceolive.util.StringUtil;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
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.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;

import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: gongPing
 * ExportData迭代更新版本，增加了添加二级表头功能
 *传参1 String[][] headers {{一级表头,key},{一级表头,key},{一级表头,二级表头1,二级表头2...}}这里二级表头的key就是它的名称
 *传参2 title 标题 为空默认sheet
 *传参3 String[] mergeLine 要合并的列，这里是将指定的列全部合并
 *
 * 参考方法：/centerMeetingDayReport/excel
 * @Date: 2020/11/15 10:26
 */
public class ExportDataNew {
    private XSSFCellStyle headStyle;
    private SXSSFWorkbook workbook;
    private SXSSFSheet sheet;
    /**
     * 创建一行
     */
    private SXSSFRow row = null;
    private SXSSFCell cell = null;
    private int currentRow = 0;
    private XSSFCellStyle stringStyle;
    private String[][] headers;
    private String sheetName;
    private String sheetTitle;
    private Integer[] mergeLines;
    /**
     *
     * @param headers  表头列
     * @param title   标题
     * @param mergeLine  要合并单元格的列
     */
    public ExportDataNew(String[][] headers,String title,Integer[] mergeLine) throws FileNotFoundException {
        this.headers = headers;//表头
        if(StringUtil.isBlank(title)){
            this.sheetTitle ="sheet";
        }else{
            this.sheetTitle = title;
        }

        this.sheetName = "sheet1";
        this.mergeLines = mergeLine;
        try {
            workbook = new SXSSFWorkbook(1000);
            this.headStyle = (XSSFCellStyle) this.workbook.createCellStyle();
            //边框样式
            headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
            headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
            headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
            headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
            //前景填充颜色设置为有索引的颜色
            headStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
            headStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
            //对齐对样式
            headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
            XSSFFont headFont = (XSSFFont) workbook.createFont();
            // 设置头部字体为宋体
            headFont.setFontName("宋体");
            // 粗体
            headFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
            headFont.setFontHeightInPoints((short) 11);
            // 单元格样式使用字体
            this.headStyle.setFont(headFont);

            createSheet(sheetName, headers,sheetTitle);
        } catch (Exception exc) {
            exc.printStackTrace();
        }

    }

    /**
     * 创建表头
     *
     * @param sheetName
     * @param headers
     */
    private void createSheet(String sheetName, String[][] headers,String sheetTitle) {
        sheet = (SXSSFSheet) workbook.createSheet(sheetName);
        //创建第一行表头
        row = (SXSSFRow) sheet.createRow(currentRow);
        int realLen = 0;
        int startLen = 0;
        cell = (SXSSFCell) row.createCell(realLen);
        //讲列设置为字符串类型
        cell.setCellType(XSSFCell.CELL_TYPE_STRING);
        //设置单元格背景颜色
        headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        cell.setCellStyle(headStyle);//设置样式
        cell.setCellValue(sheetTitle);//设置标题
        int titleLen = getTitleLen(headers);//总列数
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titleLen-1)); // 合并单元格
        currentRow++;
        row = (SXSSFRow) sheet.createRow(currentRow);
        for (int i = 0; i < headers.length; i++) {
            String[] headerT = headers[i];
            if (headerT.length > 2) {
                //一级表头
                Integer len = headerT.length;
                for (int j = 0; j < len - 1; j++) {
                    cell = (SXSSFCell) row.createCell(realLen);
                    realLen++;
                    cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                    cell.setCellStyle(headStyle);
                    cell.setCellValue(headers[i][0]);//设置一级表头（每次都从数组[0]里拿）
                }
                sheet.addMergedRegion(new CellRangeAddress(1, 1, startLen, realLen - 1)); // 合并一级表头需要的单元格
                startLen = realLen;//更新列到一级表头所占位置后对第一个

            } else {
                cell = (SXSSFCell) row.createCell(i);
                cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(headers[i][0]);
                cell.setCellStyle(headStyle);
                realLen++;
                startLen++;
            }

        }
        currentRow++;
        //创建第二行表头
        realLen = 0;
        row = (SXSSFRow) sheet.createRow(currentRow);
        for (int i = 0; i < headers.length; i++) {

            String[] headerT = headers[i];
            if (headerT.length > 2) {
                //二级表头
                Integer len = headerT.length;
                for (int j = 1; j < len; j++) {
                    String title = headerT[j];
                    cell = (SXSSFCell) row.createCell(realLen);
                    realLen++;
                    cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                    cell.setCellStyle(headStyle);
                    cell.setCellValue(title);
                }

            } else {
                cell = (SXSSFCell) row.createCell(i);
                cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                cell.setCellStyle(headStyle);
                cell.setCellValue("");
                //合并1-2行
                sheet.addMergedRegion(new CellRangeAddress(1, 2, i, i));
                realLen++;
            }
        }
        currentRow++;
    }

    /**
     * 获取表格宽度
     *
     * @param headers
     * @return
     */
    private int getTitleLen(String[][] headers) {
        int realLen = 0;
        for (int i = 0; i < headers.length; i++) {//length是总列数
            String[] headerT = headers[i];
            if (headerT.length > 2) {
                //一级表头
                Integer len = headerT.length;
                for (int j = 0; j < len - 1; j++) {
                    realLen++;
                }
            } else {
                realLen++;
            }
        }return realLen;

    }

    /**
     * 导出excel
     *
     * @param listRows
     * @throws ParseException
     */
    private void poiWriteExcelTo2007(List<Map<String, String>> listRows, OutputStream out)
            throws ParseException {
        for (int i = 0; i < listRows.size(); i++) {
            row = (SXSSFRow) sheet.createRow(currentRow);
            Map<String, String> listCells = listRows.get(i);
            int realLen=0;
            for (int j = 0; j < this.headers.length; j++) {
                String[] headerT=this.headers[j];
                if(headerT.length>2){
                    for(int k=1;k<headerT.length;k++){
                        String obj = listCells.get(headerT[k]) == null ? "" : String.valueOf(listCells.get(headerT[k]));
                        cell = (SXSSFCell) row.createCell(realLen);
                        realLen++;
                        cell.setCellValue(obj);
                        cell.setCellStyle(stringStyle);
                    }
                }else{
                    String obj = listCells.get(this.headers[j][1]) == null ? "" : String.valueOf(listCells.get(this.headers[j][1]));
                    cell = (SXSSFCell) row.createCell(j);
                    realLen++;
                    cell.setCellValue(obj);
                    cell.setCellStyle(stringStyle);
                }

            }
            currentRow++;
        }
        // sheet.addMergedRegion(new CellRangeAddress(3, 5, 0, 0)); // 合并单元格
        //合并列
        if(mergeLines!=null){
            System.out.println("合并列==========================");
            for(int  i=0 ;i<mergeLines.length;i++){
                int line= mergeLines[i];
                sheet.addMergedRegion(new CellRangeAddress(3, listRows.size()+2, line, line)); // 合并单元格
            }
        }

        try {
            workbook.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出
     *
     * @throws IOException
     * @throws ParseException
     */
    public void exportData(List<Map<String, String>> listRows, OutputStream out)
            throws IOException, ParseException {
        ExportDataNew exportData = new ExportDataNew(this.headers,this.sheetTitle,this.mergeLines);
        exportData.poiWriteExcelTo2007(listRows, out);
    }



    /**
     * 导出
     *
     * @throws IOException
     * @throws ParseException
     */
    public void exportData(String fileName, List<Map<String, String>> listRows, HttpServletResponse response)
            throws IOException, ParseException {
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;Filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));
        exportData(listRows, response.getOutputStream());
    }



    public static void main(String[] args) throws IOException, ParseException {
        //定义一个对象数组！
        String[][] headers=new String[15][];
        headers[0]= new String[]{"日期", "DATE"};
        headers[1]= new String[]{"星期","DATE_STR"};
        headers[2]= new String[]{"时间","TIME"};
        headers[3]= new String[]{"会议室","NAME"};
        headers[4]= new String[]{"订会部门","RESERVE_DEPT_NAME"};
        headers[5]= new String[]{"订会人","MEETING_RESERVATION"};
        headers[6]= new String[]{"订会人电话","MOBILE"};
        headers[7]= new String[]{"会议名称","THEME"};
        headers[8]= new String[]{"参会领导","LEADERS"};
        headers[9]= new String[]{"会议等级","LEVELS"};
        headers[10]= new String[]{"会议人数","NUM"};
        headers[11]= new String[]{"预定是否符合流程","IS_TRUE"};
        //会议设备
        String[] eqment=new String[6];eqment[0]="会议设备";
        //会议物资
        String[] materials=new String[6];materials[0]="会议物资";
        //会议服务
        String[] server=new String[6];server[0]="会议服务";
        for(int i =0;i<5;i++){
            eqment[i+1]="测试服务A"+i+1;
            materials[i+1]="测试服务B"+i+1;
            server[i+1]="测试服务C"+i+1;
        }
        headers[12]= eqment;
        headers[13]= materials;
        headers[14]= server;
        //数据集合
        List<Map<String,String>> listMap =new ArrayList<Map<String,String>>();
        for (int i=0;i<5;i++){
            Map<String,String> map= new HashMap<String,String>();
            map.put("TIME","vo.getTime()");
            map.put("DATE","vo.getDate()");
            map.put("DATE_STR","vo.getDateStr()");
            map.put("IS_TRUE","vo.getIsTrue()");
            map.put("LEADERS","vo.getLeaders()");
            map.put("LEVELS","vo.getLevels()");
            map.put("MEETING_RESERVATION","vo.getMeetingReservation()");
            map.put("MOBILE","vo.getMobile()");
            map.put("THEME","vo.getTheme()");
            map.put("NUM","vo.getNum().toString()");
            map.put("RESERVE_DEPT_NAME","vo.getReserveDeptName()");
            map.put("NAME","vo.getName()");
            //二级表头数据赋值
            for(int j=0;j<eqment.length;j++){
                map.put(eqment[j],"1");
            }
            listMap.add(map);
        }

        //指定要合并的列，全部合并
        Integer[] mergeLine={0,1};
        OutputStream out = new FileOutputStream("/Users/zengxu/Desktop/临时/temp.xlsx");
        ExportDataNew exportDataNew = new ExportDataNew(headers,"接待中心每日会议汇总表",mergeLine);
        exportDataNew.exportData(listMap,out);
    }
}