Using EasyPoi to export a large amount of data to Excel

This project is managed by maven.
The dependencies of EasyPoi in the pom file are:

 <! - easypoi dependence - >
        The role of easypoi parent package is well known to all.
             2.easypoi-annotation basic annotation package, which functions on entity objects and facilitates dependency management of maven multi-project after disassembly
             3.easypoi-base import and export toolkit, can complete Excel export, import, Word export, Excel export function
             4.easypoi-web couples spring-mvc with AbstractView, greatly simplifying the export function under spring-mvc
             5.sax imports use the xercesImpl package (which can cause strange problems), and word exports use poi-scratchpad.
             They are all optional packages.
         -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.0.1</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.0.1</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.0.1</version>
        </dependency>

**Entity class**
package com.cenhua.dataimport.pojo;

import cn.afterturn.easypoi.excel.annotation.Excel;

public class Customer implements java.io.Serializable{
    @Excel(name = "number", width = 25)
     String id;

    @Excel(name = "Full name", width = 25)
     String name;

    @Excel(name = "Gender", width = 10)
     String sex;

    @Excel(name = "Age", width = 40)
     int age;
    @Excel(name = "n1", width = 25)
     String n1;

    @Excel(name = "n2", width = 25)
     String n2;

    @Excel(name = "n3", width = 10)
     String n3;

    @Excel(name = "n4", width = 40)
     int n4;

    @Excel(name = "n5", width = 25)
     int n5;

    @Excel(name = "n6", width = 25)
     String n6;

    @Excel(name = "n7", width = 10)
     String n7;

    @Excel(name = "n8", width = 40)
     String n8;
    @Excel(name = "n9", width = 25)
     String n9;

    @Excel(name = "n10", width = 25)
     String n10;

    @Excel(name = "n11", width = 10)
     String n11;

    @Excel(name = "n12", width = 40)
     String n12;

    @Excel(name = "n13", width = 10)
     String n13;

    @Excel(name = "n14", width = 40)
     String n14;

    @Excel(name = "n15", width = 40)
     String n15;

    //Constructor with parametric construction method
    public Customer(String id, String name, String sex, int age, String n1, String n2, String n3, int n4, int n5, String n6, String n7, String n8, String n9, String n10, String n11, String n12, String n13, String n14,String n15) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.age = age;
        this.n1 = n1;
        this.n2 = n2;
        this.n3 = n3;
        this.n4 = n4;
        this.n5 = n5;
        this.n6 = n6;
        this.n7 = n7;
        this.n8 = n8;
        this.n9 = n9;
        this.n10 = n10;
        this.n11 = n11;
        this.n12 = n12;
        this.n13 = n13;
        this.n14 = n14;
        this.n15 = n15;
    }
    public Customer(){
    }

    public String getN15() {
        return n15;
    }

    public void setN15(String n15) {
        this.n15 = n15;
    }

    public String getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public String getSex() {
        return sex;
    }

    public int getAge() {
        return age;
    }

    public String getN1() {
        return n1;
    }

    public String getN2() {
        return n2;
    }

    public String getN3() {
        return n3;
    }

    public int getN4() {
        return n4;
    }

    public int getN5() {
        return n5;
    }

    public String getN6() {
        return n6;
    }

    public String getN7() {
        return n7;
    }

    public String getN8() {
        return n8;
    }

    public String getN9() {
        return n9;
    }

    public String getN10() {
        return n10;
    }

    public String getN11() {
        return n11;
    }

    public String getN12() {
        return n12;
    }

    public String getN13() {
        return n13;
    }

    public String getN14() {
        return n14;
    }

    public void setId(String id) {
        this.id = id;
    }

    public void setName(String name) {
        this.name = name;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public void setN1(String n1) {
        this.n1 = n1;
    }

    public void setN2(String n2) {
        this.n2 = n2;
    }

    public void setN3(String n3) {
        this.n3 = n3;
    }

    public void setN4(int n4) {
        this.n4 = n4;
    }

    public void setN5(int n5) {
        this.n5 = n5;
    }

    public void setN6(String n6) {
        this.n6 = n6;
    }

    public void setN7(String n7) {
        this.n7 = n7;
    }

    public void setN8(String n8) {
        this.n8 = n8;
    }

    public void setN9(String n9) {
        this.n9 = n9;
    }

    public void setN10(String n10) {
        this.n10 = n10;
    }

    public void setN11(String n11) {
        this.n11 = n11;
    }

    public void setN12(String n12) {
        this.n12 = n12;
    }

    public void setN13(String n13) {
        this.n13 = n13;
    }

    public void setN14(String n14) {
        this.n14 = n14;
    }

}

**Control layer**
package com.cenhua.dataimport.controller;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import cn.afterturn.easypoi.excel.entity.ExportParams;

import cn.afterturn.easypoi.excel.export.ExcelBatchExportServer;
import com.cenhua.dataimport.pojo.Customer;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.sql.*;
import java.util.*;


@Controller
@ExcelTarget("courseDomain")
public class DownloadController implements Serializable {
    @RequestMapping(value = "/downloadeasypoi")
    public void download(HttpServletRequest httpServletRequest, HttpServletResponse httpServletResponse) {

        DownloadController d = new DownloadController();
        try {
            d.bigDataExport(httpServletRequest,httpServletResponse);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //Create EXCEL file
    public static Workbook creatExcel(List<List<String>> lists, String[] titles, String name) throws IOException {
        System.out.println(lists);
        //Create a new workbook
        Workbook wb = new HSSFWorkbook();
        // Create the first sheet (page) and name it
        Sheet sheet = wb.createSheet(name);
        // Set column width manually. The first parameter denotes the column to be set; the second parameter denotes the width of the column, and n is the number of pixels of the column height.
        for(int i=0;i<titles.length;i++){
            sheet.setColumnWidth((short) i, (short) (35.7 * 150));
        }
        // Create the first line
        Row row = sheet.createRow((short) 0);

        // Create two cell formats
        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();

        // Create two fonts
        Font f = wb.createFont();
        Font f2 = wb.createFont();

        // Create the first font style (for column names)
        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);

        // Create a second font style (for values)
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());

        // Set the style of the first cell (for column names)
        cs.setFont(f);
        cs.setBorderLeft(CellStyle.BORDER_THIN);
        cs.setBorderRight(CellStyle.BORDER_THIN);
        cs.setBorderTop(CellStyle.BORDER_THIN);
        cs.setBorderBottom(CellStyle.BORDER_THIN);
        cs.setAlignment(CellStyle.ALIGN_CENTER);

        // Set the style of the second cell (for values)
        cs2.setFont(f2);
        cs2.setBorderLeft(CellStyle.BORDER_THIN);
        cs2.setBorderRight(CellStyle.BORDER_THIN);
        cs2.setBorderTop(CellStyle.BORDER_THIN);
        cs2.setBorderBottom(CellStyle.BORDER_THIN);
        cs2.setAlignment(CellStyle.ALIGN_CENTER);
        //Column name
        for(int i=0;i<titles.length;i++){
            Cell cell = row.createCell(i);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(cs);
        }
        if(lists == null || lists.size() == 0){
            return wb;
        }
        //Set the values for each row and column
        for (int i1 = 1; i1 <= lists.size(); i1++) {
            // Row rows, Cell squares, Row and Cell are counted from zero.
            // Create a row on the page sheet
            Row row1 = sheet.createRow(i1);
            for(int j1=0;j1<titles.length;j1++){
                // Create a grid on the row line
                Cell cell = row1.createCell(j1);
                cell.setCellValue(lists.get(i1-1).get(j1));
                cell.setCellStyle(cs2);
            }
        }
        return wb;
    }
    public static Workbook exportBigExcel(ExportParams entity, Class<?> pojoClass,
                                          Collection<?> dataSet) {
        ExcelBatchExportServer batachServer = ExcelBatchExportServer
                .getExcelBatchExportServer(entity, pojoClass);
        return batachServer.appendData(dataSet);
    }

    public static void closeExportBigExcel() {
        ExcelBatchExportServer batachServer = ExcelBatchExportServer.getExcelBatchExportServer(null,
                null);
        batachServer.closeExportBigExcel();
    }
    public void bigDataExport(HttpServletRequest httpServletRequest, HttpServletResponse httpServletResponse) throws Exception {
        // Tell the browser what software to open the file.
        httpServletResponse.setHeader("content-Type", "application/vnd.ms-excel");
        // Default name of download file
        httpServletResponse.setHeader("Content-Disposition", "attachment;filename=user.xls");
        List<Customer> list = new ArrayList<Customer>();
        Workbook workbook = null;
        ExportParams params = new ExportParams("Big Data Testing", "test");
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
           Connection c = DriverManager.getConnection( "Own url", "Account number", "Password");
            String sql= "select * from xyz_copy1";
            Statement ps = c.createStatement();
            ResultSet rs = ps.executeQuery(sql);

            System.out.println("Print database data");
            while(rs.next()) {
                Customer cu = new Customer();//Creating objects in a loop avoids adding values to list s when values are added
                //map=new HashMap<String, Customer>();
                String id = rs.getString("id");
                String name =rs.getString("name");
                String sex =rs.getString("sex");
                int age = rs.getInt("age");
                String  n1 = rs.getString("n1");
                String n2 = rs.getString("n2");
                String n3 = rs.getString("n3");
                int n4 = rs.getInt("n4");
                int n5 = rs.getInt("n5");
                String n6 = rs.getString("n6");
                String n7 = rs.getString("n7");
                String n8 = rs.getString("n8");
                String n9 = rs.getString("n9");
                String n10 = rs.getString("n10");
                String n11 = rs.getString("n11");
                String n12 = rs.getString("n12");
                String n13 = rs.getString("n13");
                String n14 = rs.getString("n14");
                String n15 = rs.getString("n15");
                cu.setId(id);
                cu.setName(name);
                cu.setSex(sex);
                cu.setAge(age);
                cu.setN1(n1);
                cu.setN2(n2);
                cu.setN3(n3);
                cu.setN4(n4);
                cu.setN5(n5);
                cu.setN6(n6);
                cu.setN7(n7);
                cu.setN8(n8);
                cu.setN9(n9);
                cu.setN10(n10);
                cu.setN11(n11);
                cu.setN12(n12);
                cu.setN13(n13);
                cu.setN14(n14);
                cu.setN15(n15);
                list.add(cu);
                //In the for cycle, you can set the number of read bars according to your own computer.
                if(list.size() == 10000){
                    workbook = ExcelExportUtil.exportBigExcel(params, Customer.class, list);
                    list.clear();
                }
            }
            workbook.write(httpServletResponse.getOutputStream());
            ExcelExportUtil.closeExportBigExcel();
            File savefile = new File("D:/excel/");
            if (!savefile.exists()) {
                savefile.mkdirs();
            }
            FileOutputStream fos = new FileOutputStream("D:/excel/ExcelExportBigData.bigDataExport.xls");
            workbook.write(fos);
            fos.close();
           } catch (Exception e) {
            e.printStackTrace();
        }

    }

}

/ / in the for cycle, you can set the number of read bar according to your computer. This is the key to control the memory not overflow.
if(list.size() == 10000){
workbook = ExcelExportUtil.exportBigExcel(params, Customer.class, list);
list.clear();
}