JAVA导出数据到excel中大数据量的解决方法

最近在做项目功能时 ，发现有20万以上的数据。要求导出时直接导出成压缩包。原来的逻辑是使用poi导出到excel，他是操作对象集合然后将结果写到excel中。

使用poi等导出时，没有考虑数据量的问题，大数据量无法满足，有个几千行jvm就哭了。更别提几万行几百万行数据了。

经过一天的研究发现一种不会消耗过多内存的方法：

导出成csv格式

大数据量的导出成csv格式分为以下几步：

1.首先引入需要的jar包 一下是我maven的配置方式

	<dependency>
			<groupId>org.mvel</groupId>
			<artifactId>mvel2</artifactId>
			<version>2.2.8.Final</version>
		</dependency>
		<dependency>
			<groupId>net.sourceforge.javacsv</groupId>
			<artifactId>javacsv</artifactId>
			<version>2.0</version>
		</dependency>

 2.以下是具体的执行代码，我是用的是jdbcTemplate

public class DownloadVehicleRepair extends AbstractJob {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    protected void executeBusiness(Long aLong) {
        System.out.println("开始执行！！！！！！！！！！");
        final String fileName = "车辆维修清单.csv";//压缩包里面的文件
        final String[] header = {"序号", "第三方机构代码", "机构名称", "分公司", "合作机构", "单位类别", "主品牌", "品牌名称",
                "被投诉", "涉及欺诈", "黑名单", "审核状态", "维护时间", "维护人员代码"};
        final String sql = "您需要执行sql”;

        jdbcTemplate.execute(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement pstmt = connection.prepareStatement(sql);
                return pstmt;
            }
        }, new PreparedStatementCallback<Integer>() {
            @Override
            public Integer doInPreparedStatement(PreparedStatement preparedStatement) throws SQLException, DataAccessException {
                ResultSet rs = preparedStatement.executeQuery();
                try {
                    CsvUtil.writeCsv(RuntimeEnvironmentUtil.getValue(SysConstent.code,SysConstent.path) + "\\VehicleRepairDetail.zip",
                            fileName, header, rs);//RuntimeEnvironmentUtil.getValue()是为了获取你导出到服务器的路径
                } catch (Exception e) {
                    e.printStackTrace();
                }
                return 0;
            }
        });
        System.out.println("导出完成！！！！！！！！！！！");

    }
}

 3.以下是帮助类

public class CsvUtil {
    // 编码类型
    public static final Charset CHARSET = Charset.forName("GBK");

    // 分隔符
    public static final char DELIMITER = ',';

    // 文件后缀
    public static final String SUFFIX = ".csv";


    public static void writeCsv(OutputStream out, String[] header, ResultSet rs)
            throws IOException, SQLException {
        CsvWriter writer = null;
        try {
            writer = new CsvWriter(out, CsvUtil.DELIMITER, CsvUtil.CHARSET);
            writeCsv(writer, header, rs);
        } finally {
            if (writer != null)
                writer.close();
        }
    }

    public static void writeCsv(CsvWriter writer, String[] header, ResultSet rs)
            throws IOException, SQLException {
        if (header != null)
            writer.writeRecord(header);
        ResultSetMetaData md = rs.getMetaData();
        int columnCount = md.getColumnCount();
        while (rs.next()) {
            for (int i = 1; i <= columnCount; i++)
                writer.write(rs.getString(i));
            writer.endRecord();
        }
    }

    public static void writeCsv(File file, String[] header, ResultSet rs)
            throws IOException, SQLException {
        BufferedOutputStream out = null;
        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(file);
            out = new BufferedOutputStream(fileOutputStream);
            writeCsv(out, header, rs);
        } finally {
            if (out != null) {
                out.flush();
                out.close();
            }
            if (fileOutputStream != null) {
                fileOutputStream.close();
            }
        }
    }

    public static void writeCsv(String csvFilePath, String[] header,
                                ResultSet rs) throws IOException, SQLException {
        writeCsv(new File(csvFilePath), header, rs);
    }

    public static void writeCsv(String zipFilePath, String csvName, String[] header, ResultSet rs)
            throws IOException, SQLException {
        FileOutputStream fos = null;
        BufferedOutputStream bos = null;
        ZipOutputStream zos = null;
        try {
            fos = new FileOutputStream(zipFilePath);
            bos = new BufferedOutputStream(fos);
            zos = new ZipOutputStream(bos);
            zos.putNextEntry(new ZipEntry(csvName));
            writeCsv(zos, header, rs);
        } finally {
            StreamUtil.flush(zos);
            StreamUtil.close(zos);
            //StreamUtil.flush(bos);
            StreamUtil.close(bos);
            //StreamUtil.flush(fos);
            StreamUtil.close(fos);
        }
    }

}

 

public class StreamUtil {
    public static void flush(Flushable flushable) {
        if (flushable != null) {
            try {
                flushable.flush();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    public static void close(Closeable closeable){
        if(closeable!=null){
            try {
                closeable.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

 4.下面是下载时的action

 @RequestMapping(value = "/downloadVehicleRepair", method = RequestMethod.POST)
    public ResponseEntity<byte[]> download() throws IOException {
        String path = RuntimeEnvironmentUtil.getValue(SysConstent.code,SysConstent.path)+"\\VehicleRepairDetail.zip";
        File file = new File(path);
        HttpHeaders headers = new HttpHeaders();
        String fileName = new String("车辆维修清单.zip".getBytes("UTF-8"), "iso-8859-1");//为了解决中文名称乱码问题
        headers.setContentDispositionFormData("attachment", fileName);
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        return new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(file), headers, HttpStatus.OK);
    }

 总结：以上只是关键代码。使用时只需要稍加改变就可以运行。