由于Csv格式简单,占内存少,生成的文件相对Excel小,可用各种编辑器打开等优点,导出类需求多用Csv格式
经测试导出10列40W行数据大小的表格大小约80M,还可以,若有更大数据量级可考虑分割打包下载
Java直接导出 (方法一)
Controller代码如下:
@RequestMapping(value = "/export")
public void getSkuList1(HttpServletResponse response){
String[] tableHeaderArr = {"id","姓名","年龄"};
List<String> cellList = new ArrayList<>();
cellList.add("1,小明,13");
cellList.add("2,小强,14");
cellList.add("3,小红,15");
String fileName = "导出文件.csv";
byte[] bytes = ExportCSVUtil.writeDataAfterToBytes(tableHeaderArr, cellList);
ExportCSVUtil.responseSetProperties(fileName,bytes, response);
}
第三方jar导出 (方法二)
maven依赖
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-csv</artifactId>
<version>1.8</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.8.0</version>
</dependency>
Controller代码如下:
@RequestMapping(value = "/export")
public void getSkuList1(HttpServletResponse response){
List<Object[]> cellList = new ArrayList<>();
Object[] obj1 = {1,"小明",13};
Object[] obj2 = {2,"小强",14};
Object[] obj3 = {3,"小红",15};
cellList.add(obj1);
cellList.add(obj2);
cellList.add(obj3);
String[] tableHeaderArr = {"id","姓名","年龄"};
String fileName = "导出文件.csv";
byte[] bytes = ExportCSVUtil.writeCsvAfterToBytes(tableHeaderArr, cellList);
ExportCSVUtil.responseSetProperties(fileName,bytes, response);
}
导出工具类
import java.io.BufferedWriter;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;
import org.apache.commons.io.ByteOrderMark;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* 写csv文件
* @Author lizian
* @Date 2020-07-26
*/
public class ExportCSVUtil {
private static final Logger logger = LoggerFactory.getLogger(ExportCSVUtil.class);
/**
* 写CSV并转换为字节流
* @param tableHeaderArr 表头
* @param cellList 数据
* @return
*/
public static byte[] writeDataAfterToBytes(String[] tableHeaderArr, List<String> cellList) {
byte[] bytes = new byte[0];
ByteArrayOutputStream byteArrayOutputStream = null;
OutputStreamWriter outputStreamWriter = null;
BufferedWriter bufferedWriter = null;
try {
byteArrayOutputStream = new ByteArrayOutputStream();
outputStreamWriter = new OutputStreamWriter(byteArrayOutputStream,StandardCharsets.UTF_8);
bufferedWriter = new BufferedWriter(outputStreamWriter);
//excel文件需要通过文件头的bom来识别编码,而CSV文件格式不自带bom,所以写文件时,需要先写入bom头,否则excel打开乱码
bufferedWriter.write(new String(ByteOrderMark.UTF_8.getBytes()));
//写表头
StringBuilder sb = new StringBuilder();
String tableHeader = String.join(",", tableHeaderArr);
sb.append(tableHeader + StringUtils.CR + StringUtils.LF);
for (String rowCell : cellList) {
sb.append(rowCell + StringUtils.CR + StringUtils.LF);
}
bufferedWriter.write(sb.toString());
bufferedWriter.flush();
//把输出流转换字节流
bytes = byteArrayOutputStream.toString(StandardCharsets.UTF_8.name()).getBytes();
return bytes;
} catch (IOException e) {
logger.error("writeDataAfterToBytes IOException:{}", e.getMessage(), e);
} finally {
try {
if (bufferedWriter != null) {
bufferedWriter.close();
}
if (outputStreamWriter != null) {
outputStreamWriter.close();
}
if (byteArrayOutputStream != null) {
byteArrayOutputStream.close();
}
} catch (IOException e) {
logger.error("iostream close IOException:{}", e.getMessage(), e);
}
}
return bytes;
}
/**
* 写CSV并转换为字节流
* @param headers 表头
* @param cellList 表数据
* @return
*/
public static byte[] writeCsvAfterToBytes(String[] headers,List<Object[]> cellList) {
byte[] bytes = new byte[0];
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
OutputStreamWriter outputStreamWriter = new OutputStreamWriter(byteArrayOutputStream, StandardCharsets.UTF_8);
BufferedWriter bufferedWriter = new BufferedWriter(outputStreamWriter);
CSVPrinter csvPrinter = null;
try {
//创建csvPrinter并设置表格头
csvPrinter = new CSVPrinter(bufferedWriter, CSVFormat.DEFAULT.withHeader(headers));
//写数据
csvPrinter.printRecords(cellList);
csvPrinter.flush();
bytes = byteArrayOutputStream.toString(StandardCharsets.UTF_8.name()).getBytes();
} catch (IOException e) {
logger.error("writeCsv IOException:{}", e.getMessage(), e);
} finally {
try {
if (csvPrinter != null) {
csvPrinter.close();
}
if (bufferedWriter != null) {
bufferedWriter.close();
}
if (outputStreamWriter != null) {
outputStreamWriter.close();
}
if (byteArrayOutputStream != null) {
byteArrayOutputStream.close();
}
} catch (IOException e) {
logger.error("iostream close IOException:{}", e.getMessage(), e);
}
}
return bytes;
}
/**
* 设置下载响应
* @param fileName
* @param bytes
* @param response
*/
public static void responseSetProperties(String fileName, byte[] bytes, HttpServletResponse response) {
try {
fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
response.setContentType("application/csv");
response.setCharacterEncoding(StandardCharsets.UTF_8.name());
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "max-age=30");
response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
OutputStream outputStream = response.getOutputStream();
outputStream.write(bytes);
outputStream.flush();
} catch (IOException e) {
logger.error("iostream error:{}", e.getMessage(), e);
}
}
}