1.导入依赖

<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>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.9</version>
</dependency>

2.导出

//获取模板文件  自己建一个excel模板
File file = new File(rootPath + "中间测算数据.xlsx");
FileInputStream inputStream = new FileInputStream(file);
//创建一个webbook，对应一个Excel文件
XSSFWorkbook wb = new XSSFWorkbook(inputStream);
//创建大数据导出book
SXSSFWorkbook wb2 = new SXSSFWorkbook(wb, 500);
//创建单元格样式
CellStyle cellStyle = wb2.createCellStyle();
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
//获取模板总的sheet表
Sheet sheet = wb2.getSheet("第一张表")
//如果需要改变模板中的表头，需要自己创建sheet.createSheet("sheet001")  记得添加表头
//添加数据
int i= 1;
for (HospitalCostHistory costHistory : hospitalCosts) {
    //封装单元格数据
    Row row1 = sheet.createRow(i);

    Cell cell = row1.createCell(0);
    cell.setCellValue(costHistory.getMedicalNum());
    cell.setCellStyle(cellStyle);
}

//导出
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("测算中间数据导出", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
OutputStream out = response.getOutputStream();
wb2.write(out);
out.close();

• 数据量大的话，建议使用多线程，分成多个sheet导出 能加快速度
• 例如： 有多个sheet

ExecutorService executorService = Executors.newFixedThreadPool(5);
for (String sheet:sheetMap.keySet()) {
    executorService.execute(()->{
        importData(List<HospitalProject> list, Sheet sheet)
    });
//拆分list  40000条封装一个
public void imporptData(List<HospitalProject> lsit){
    ConcurrentHashMap<Integer,CopyOnWriteArrayList<HospitalProject>> itemMap = batchList(list, 40000);
    wb.removeSheetAt(1);
    Iterator<Map.Entry<Integer,CopyOnWriteArrayList<HospitalProject>>> iterator=itemMap.entrySet().iterator();
    while (iterator.hasNext()){
        Map.Entry<Integer,CopyOnWriteArrayList<HospitalProject>> next = iterator.next();
        int key = next.getKey();
        CopyOnWriteArrayList<HospitalProject>  list1= next.getValue();
        int i =1;//自定义下标
        Sheet sheet = wb.createSheet(CollectionTaskConstant.ZJSJ_XMMXB + "(" + key + ")");
        //创建头部
        creatHeardCalulateDate02(sheet, cellStyle);
        //封装数据
        for (HospitalProject hospitalProject : list1) {
            //封装单元格数据
            Row row1 = sheet.createRow(i);

            Cell cell33 = row1.createCell(0);
            cell33.setCellValue((String)CollectionTaskConstant.deptTypeMap.get(hospitalProject.getServType()));
            cell33.setCellStyle(cellStyle);
        }
    }

  i++;//下标自增
}
iterator.remove();
}
           

• 建议使用Alibaba的 # EsayExcle