JAVA导出数据到excel中⼤数据量的解决⽅法
最近在做项⽬功能时，发现有20万以上的数据。要求导出时直接导出成压缩包。原来的逻辑是使⽤poi导出到excel，他是操作对象集合然后将结果写到excel中。
使⽤poi等导出时，没有考虑数据量的问题，量⽆法满⾜，有个⼏千⾏jvm就哭了。更别提⼏万⾏⼏百万⾏数据了。
jdbctemplate查询一条数据经过⼀天的研究发现⼀种不会消耗过多内存的⽅法：
导出成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”;
@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 = uteQuery();
try {
CsvUtil.de,SysConstent.path) + "\\VehicleRepairDetail.zip",
fileName, header, rs);//Value()是为了获取你导出到服务器的路径
} 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.String(i));
}
}
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 = de,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[]>(adFileToByteArray(file), headers, HttpStatus.OK);
}
总结：以上只是关键代码。使⽤时只需要稍加改变就可以运⾏。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。