利用EasyPoi导出大量数据到Excel

利用EasyPoi导出大量数据到Excel

本项目是利用maven管理。
pom文件中EasyPoi的依赖为：

 <!--easypoi依赖-->
        <!-- 1.easypoi 父包作用大家都懂得
             2.easypoi-annotation 基础注解包,作用与实体对象上,拆分后方便maven多工程的依赖管理
             3.easypoi-base 导入导出的工具包,可以完成Excel导出,导入,Word的导出,Excel的导出功能
             4.easypoi-web 耦合了spring-mvc 基于AbstractView,极大的简化spring-mvc下的导出功能
             5.sax 导入使用xercesImpl这个包(这个包可能造成奇怪的问题哈),word导出使用poi-scratchpad,
             都作为可选包了
         -->
        <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>

**实体类**
package com.cenhua.dataimport.pojo;

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

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

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

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

    @Excel(name = "年龄", 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
    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;
    }

}

**控制层**
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();
        }
    }

    //创建EXCEL文件
    public static Workbook creatExcel(List<List<String>> lists, String[] titles, String name) throws IOException {
        System.out.println(lists);
        //创建新的工作薄
        Workbook wb = new HSSFWorkbook();
        // 创建第一个sheet（页），并命名
        Sheet sheet = wb.createSheet(name);
        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
        for(int i=0;i<titles.length;i++){
            sheet.setColumnWidth((short) i, (short) (35.7 * 150));
        }
        // 创建第一行
        Row row = sheet.createRow((short) 0);

        // 创建两种单元格格式
        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();

        // 创建两种字体
        Font f = wb.createFont();
        Font f2 = wb.createFont();

        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);

        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());

        // 设置第一种单元格的样式（用于列名）
        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);

        // 设置第二种单元格的样式（用于值）
        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);
        //设置列名
        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;
        }
        //设置每行每列的值
        for (int i1 = 1; i1 <= lists.size(); i1++) {
            // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
            // 创建一行，在页sheet上
            Row row1 = sheet.createRow(i1);
            for(int j1=0;j1<titles.length;j1++){
                // 在row行上创建一个方格
                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 {
        // 告诉浏览器用什么软件可以打开此文件
        httpServletResponse.setHeader("content-Type", "application/vnd.ms-excel");
        // 下载文件的默认名称
        httpServletResponse.setHeader("Content-Disposition", "attachment;filename=user.xls");
        List<Customer> list = new ArrayList<Customer>();
        Workbook workbook = null;
        ExportParams params = new ExportParams("大数据测试", "测试");
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
           Connection c = DriverManager.getConnection( "自己的url", "账号", "密码");
            String sql= "select * from xyz_copy1";
            Statement ps = c.createStatement();
            ResultSet rs = ps.executeQuery(sql);

            System.out.println("打印数据库数据");
            while(rs.next()) {
                Customer cu = new Customer();//创建对象 在循环内创建对象能够避免list添加值时会出现值被
                //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);
                //在for循环之内  根据自己电脑可以设置读取条数
                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();
        }

    }

}

//在for循环之内 根据自己电脑可以设置读取条数 此处为控制内存不溢出的关键
if(list.size() == 10000){
workbook = ExcelExportUtil.exportBigExcel(params, Customer.class, list);
list.clear();
}