首页 1 2 3 4 5 6 7

大批量到处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);

}

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