Spring导出CSV文件

• 由于Csv格式简单，占内存少，生成的文件相对Excel小，可用各种编辑器打开等优点，导出类需求多用Csv格式

• 经测试导出10列40W行数据大小的表格大小约80M，还可以，若有更大数据量级可考虑分割打包下载

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>

Util方法

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);
		}
    }
 
}

方法1：将List转化成byte[]然后OutputStream.write()

    @RequestMapping(value = "/export")
    public void export1(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";
		// 直接将一个list<String>类型的参数变成byte[]，然后再OutputStream.write()到本地文件
		byte[] bytes = ExportCSVUtil.writeDataAfterToBytes(tableHeaderArr, cellList);
		ExportCSVUtil.responseSetProperties(fileName,bytes, response);
    }

方法2：将List<Object[]>转化成byte[]然后OutputStream.write()

    @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);
    	// 注意参数类型为List<Object[]> 形式和方法1的List<String>区别开
    	String[] tableHeaderArr = {"id","姓名","年龄"};
    	String fileName = "导出文件.csv";
		byte[] bytes = ExportCSVUtil.writeCsvAfterToBytes(tableHeaderArr, cellList);
		ExportCSVUtil.responseSetProperties(fileName,bytes, response);
    }

转自：https://blog.csdn.net/lzxlfly/article/details/107753891