自定义表头导出（ExcelKit）

POIUtil为ExcelKit包的工具类

package com.mjm.demo.pojo.test;

import com.mjm.DemoApplication;
import com.mjm.demo.pojo.DecTest;
import com.mjm.demo.service.DecTestService;
import com.wuwenze.poi.util.Const;
import com.wuwenze.poi.util.POIUtil;
import lombok.Data;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

@RestController
@RequestMapping("/g")
 public class exportTest {
//    关于test注入为空对象,需要加的注解
    //@RunWith(SpringRunner.class)
    //@SpringBootTest(classes = Application.class)

    //获得Excel对象
    @Autowired
    private  DecTestService service;
    @GetMapping("/g")
    public void writeBigData(HttpServletResponse response) throws IOException {

        System.out.println("service = " + service);
//        String[] titles = {"时间", "科目", "科目", "科目", "科目", "备注"};
        String[] titles = {"信息表"};
//        String[] handClum = {"0,1,0,0", "0,0,1,4", "0,1,5,5"};
        //起始行号，终止行号， 起始列号，终止列号
        String[] handClum = { "0,0,0,9"};

//        String[] titles2 = {"时间", "科目一", "科目二", "科目三", "科目四", "备注"};
        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("sheet1");
        for (int i = 0; i < titles.length; i++) {
            sheet.setColumnWidth(i, 4000);
        }

        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<DecTest> newList = new ArrayList<DecTest>();
        List<DecTest> list = service.list();
        for (DecTest dec : list) {
            DecTest decTest = new DecTest();
            decTest.setId(dec.getId());
            decTest.setCreateTime(dec.getCreateTime());
            decTest.setName(dec.getName());
            decTest.setStatus(dec.getStatus());
            newList.add(dec);
        }
        //newlist
        if (newList != null && newList.size() > 0) {
            for (int i = 0; i < newList.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);
                    DecTest content = newList.get(i);
                    switch (j) {
                        case 0:
                            cell.setCellValue(i+1);
                            break;
                        case 1:
                            cell.setCellValue(content.getName());
                            break;
                        case 2:
                            cell.setCellValue(content.getStatus());
                            break;
                        case 3:
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM.dd HH:mm:ss");
                            String format = sdf.format(content.getCreateTime());
                            cell.setCellValue(format);
                            break;
                    }
                }
            }
        }
        POIUtil.download(wb, response, URLEncoder.encode("test-导出结果.xlsx", Const.ENCODING));
    /*
            long startTime = System.currentTimeMillis();    //获取开始时间,单位毫秒
                FileInputStream inputStream = new FileInputStream("D:\\QR\\00.xlsx");
                XSSFWorkbook wb_template = new XSSFWorkbook(inputStream);

                inputStream.close();

                SXSSFWorkbook wb = new SXSSFWorkbook(wb_template,1000);
           *//* SXSSFSheet sheet = wb.createSheet("sheetName");
        sheet.setRandomAccessWindowSize(-1);*//*
            wb.setCompressTempFiles(true);
            SXSSFSheet sh = (SXSSFSheet) wb.createSheet("数据导出");
//            sh.setDefaultColumnWidth(4);
//            sh.setRandomAccessWindowSize(100);// keep 100 rows in memory, exceeding rows will be flushed to disk，每次加载到内存100条
            for(int rowNum = 0; rowNum < 200; rowNum++){ //500000为行数
                Row row = sh.createRow(rowNum);
                for(int cellNum = 0; cellNum < 10; cellNum++){ //10为列数
                    Cell cell = row.createCell(cellNum);
                    String address = new CellReference(cell).formatAsString();
                    cell.setCellValue(address);
                }
            }*/
/*    FileOutputStream outFile = new FileOutputStream("D:\\QR\\cpssfx.xlsx");
            wb.write(outFile);
            outFile.close();
            wb.dispose();*/
            // dispose of temporary files backing this workbook on disk
    long endTime = System.currentTimeMillis(); //获取开始时间,单位毫秒
          /*  long duration = endTime - startTime;
            System.out.println("程序运行时长：" + Double.valueOf(duration)/1000 + "秒");*/
}

    /*public static void main(String[] args) throws Throwable {
        writeBigData();
    }*/
}

@Data
class keMu {
    private String createDate;
    private String ke1;
    private String ke2;
    private String ke3;
    private String ke4;
    private String remark;
}

00