java poi 对表头导出DEMO

导出可以直接使用，需要替换list(数据来源);有问题留言 

注意：使用时 handClum 最好一个一个单元格调试；

package com.slecc.pdd.ssm.controller;

/**
 * Created By: hdx
 * Date: 2020-12-10 09:53
 */
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import com.baomidou.mybatisplus.mapper.EntityWrapper;
import com.slecc.pdd.ssm.bean.PmPlanCompleteV;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class SXSSFExecelPOITest {

    public static void main(String[] args) {
        try {
            boolean createExcelFile = createExcelFile();
            if (createExcelFile) {
                System.out.println("SXSSF_excel导出成功");
            } else {
                System.out.println("SXSSFexcel导出失败");
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    private static boolean createExcelFile() throws Exception {

        boolean state = false;
        OutputStream os = null;
        FileOutputStream fos = null;
        String resultFileName = null;
        String fileName = "";
        try {
            fileName = "aaa.xlsx";
            SXSSFWorkbook wb = excelFile();

            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
            resultFileName = "E:\\" + File.separator + "excel" + File.separator + sdf.format(new Date())
                    + File.separator + fileName;
            resultFileName = resultFileName.replaceAll("\\\\", "/");
            File file = new File(resultFileName);
            if (!file.exists()) {
                file.getParentFile().mkdirs();
            }
            fos = new FileOutputStream(file);
            os = new BufferedOutputStream(fos, 1024);
            wb.write(os);
            os.flush();
            state = true;
        } finally {
            if (os != null)
                os.close();
        }
        return state;
    }

    private static SXSSFWorkbook excelFile() throws Exception {

        String[] titles = { "项目名称","可研批文 时间、文号", "批复总投资（万元）","开工建设许可情况", "开工建设许可情况", "开工建设许可情况", "开工建设许可情况","开工建设许可情况","专项验收完成情况","专项验收完成情况", "专项验收完成情况","专项验收完成情况","专项验收完成情况","专项验收完成情况","专项验收完成情况","专项验收完成情况","计划/要求完成竣工验收时间","备注" };
        String[] handClum = { "0,1,0,0", "0,1,1,1","0,1,2,2","0,0,3,7","0,0,8,15","0,1,16,16","0,1,17,17" };//起始行，起始列，结束行，结束列

        String[] titles2 = { "项目名称","可研批文 时间、文号", "批复总投资（万元）","环评批文时间、文号", "安全审查批文时间、文号", "职业病危害预评价批文时间、文号", "安全设施设计审查批文时间、文号","职业病防护设施设计审查批文时间、文号","完成生产考核或标定（是/否）","环保验收批文时间、文号","安全设施竣工验收批文时间、文号","职业病防护设施验收批文时间、文号","消防竣工验收批文时间、文号", "防雷装置验收批文时间、文号","档案验收批文时间、文号","竣工决算审计批文时间、文号","备注" };

        // 100 指定Excel在屏幕尺寸下可滑动数据为100条
        SXSSFWorkbook wb = new SXSSFWorkbook(100);
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);// 设置单元格水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);// 设置单元格垂直居中
        Sheet sheet = wb.createSheet("aaa.xlsx");
        for (int i = 0; i < titles.length; i++) {
            sheet.setColumnWidth(i, 10000);
        }

        Row row = sheet.createRow(0);// 创建表头1
        for (int i = 0; i < titles.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellStyle(style);
            cell.setCellValue(titles[i]);
        }

        // 动态合并单元格
        for (int i = 0; i < handClum.length; i++) {

            // sheet.autoSizeColumn(i, true);
            String[] temp = handClum[i].split(",");
            Integer startrow = Integer.parseInt(temp[0]);
            Integer overrow = Integer.parseInt(temp[1]);
            Integer startcol = Integer.parseInt(temp[2]);
            Integer overcol = Integer.parseInt(temp[3]);
            sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol));//起始行，起始列，结束行，结束列
        }

        row = sheet.createRow(1);// 创建表头2

        for (int i = 0; i < titles2.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellStyle(style);
            cell.setCellValue(titles2[i]);
        }

        // 填充数据  list 需要插入的数据来源
        if (list != null && list.size() > 0) {
            for (int i = 0; i < list.size(); i++) {
                Row contentRow = sheet.createRow(i + 2);// 填充类容,从第2行开始,0行给表头
                if (i % 100 == 0) {
                    try {
                        ((SXSSFSheet) sheet).flushRows(100);
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }

                for (int j = 0; j < titles2.length; j++) {
                    Cell cell = contentRow.createCell(j);
                    cell.setCellStyle(style);
                    PmPlanCompleteV content = list.get(i);
                    switch (j) {
                        case 0:
                            cell.setCellValue(content.getXmmc());
                            break;
                        case 1:
                            cell.setCellValue(content.getKypfsjwh());
                            break;
                        case 2:
                            cell.setCellValue(content.getPfje());
                            break;
                        case 3:
                            cell.setCellValue(content.getHppfsjwh());
                            break;
                        case 4:
                            cell.setCellValue(content.getAppfsjwh());
                            break;
                        case 5:
                            cell.setCellValue(content.getZxfsjwhXf());
                            break;
                        case 6:
                            cell.setCellValue(content.getAqsjpfsjwh());
                            break;
                        case 7:
                            cell.setCellValue(content.getZppfsjwh());
                            break;
                        case 8:
                            cell.setCellValue(content.getSckh());
                            break;
                        case 9:
                            cell.setCellValue(content.getZxfsjwhHb());
                            break;
                        case 10:
                            cell.setCellValue(content.getZxfsjwhAb());
                            break;
                        case 11:
                            cell.setCellValue(content.getZxfsjwhZy());
                            break;
                        case 12:
                            cell.setCellValue(content.getZxfsjwhXf());
                            break;
                        case 13:
                            cell.setCellValue(content.getZxfsjwhFl());
                            break;
                        case 14:
                            cell.setCellValue(content.getZxfsjwhDa());
                            break;
                        case 15:
                            cell.setCellValue(content.getZxfsjwhJgjs());
                            break;
                        case 16:
                            cell.setCellValue(content.getJhjgny());
                            break;
                        case 17:
                            cell.setCellValue(content.getBz());
                            break;
                    }
                }
            }
        }
        return wb;
    }

}