CSV|SpringBoot JAVA导出数据到CSV文件 Excel|Java

1.CSV工具类

import org.apache.commons.csv.CSVFormat; import org.apache.commons.csv.CSVParser; import org.apache.commons.csv.CSVPrinter; import org.apache.commons.csv.CSVRecord; import org.springframework.http.HttpHeaders; import org.springframework.http.MediaType; import java.io.*; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.Map; import java.util.concurrent.CountDownLatch; import java.util.concurrent.ExecutorService; import java.util.concurrent.Executors; /** * 导出至csv文件 * */ public class CsvUtil { //CSV文件分隔符 private final static String NEW_LINE_SEPARATOR="\n"; /** CSV文件列分隔符 */ private static final String CSV_COLUMN_SEPARATOR = ","; /** CSV文件列分隔符 */ private static final String CSV_RN = "\r\n"; /**写入csv文件 * @param headers 列头 * @param data 数据内容 * @param filePath 创建的csv文件路径 * @throws IOException **/ public static void writeCsvWithHeader(String[] headers, List data, String filePath) { //初始化csvformat CSVFormat format = CSVFormat.DEFAULT.withHeader(headers); try { //根据路径创建文件，并设置编码格式 FileOutputStream fos = new FileOutputStream(filePath); OutputStreamWriter osw = new OutputStreamWriter(fos, "GBK"); //创建CSVPrinter对象 CSVPrinter printer = new CSVPrinter(osw, format); if(null!=data){ //循环写入数据 for(Object[] lineData:data){ printer.printRecord(lineData); } } printer.flush(); printer.close(); } catch (IOException e) { e.printStackTrace(); } }/**写入csv文件 * @param headers 列头 * @param data 数据内容 * @param filePath 创建的csv文件路径 * @throws IOException **/ public static void writeCsvWithRecordSeparator(Object[] headers, List data, String filePath){ //初始化csvformat CSVFormat format = CSVFormat.DEFAULT.withRecordSeparator(NEW_LINE_SEPARATOR); try { //根据路径创建文件，并设置编码格式 FileOutputStream fos = new FileOutputStream(filePath); OutputStreamWriter osw = new OutputStreamWriter(fos, "GBK"); //创建CSVPrinter对象 CSVPrinter printer = new CSVPrinter(osw,format); //写入列头数据 printer.printRecord(headers); if(null!=data){ //循环写入数据 for(Object[] lineData:data){ printer.printRecord(lineData); } } printer.flush(); printer.close(); System.out.println("CSV文件创建成功,文件路径:"+filePath); } catch (IOException e) { e.printStackTrace(); } }/** * @filePath 文件路径 */ public static List readCsvParse(String filePath){ List records = new ArrayList<>(); try { FileInputStream in = new FileInputStream(filePath); BufferedReader reader = new BufferedReader (new InputStreamReader(in,"GBK")); CSVParser parser = CSVFormat.EXCEL.parse(reader); records = parser.getRecords(); parser.close(); } catch (IOException e) { e.printStackTrace(); }finally { return records; } }/** * 自定义字段 * @filePath 文件路径 */ public static List readCsvParseWithHeader(String filePath,String[] headers){ List records = new ArrayList<>(); try { FileInputStream in = new FileInputStream(filePath); BufferedReaderreader = new BufferedReader (new InputStreamReader(in,"GBK")); CSVParser parser = CSVFormat.EXCEL.withHeader(headers).parse(reader); records = parser.getRecords(); /*for (CSVRecord record : parser.getRecords()) { System.out.println(record.get("id") + "," + record.get("name") + "," + record.get("code")); }*/ parser.close(); }catch (IOException e){ e.printStackTrace(); }finally { return records; } }/** * 导出至多个csv文件 * */ public void writeMuti() throws InterruptedException { ExecutorService executorService = Executors.newFixedThreadPool(3); CountDownLatch doneSignal = new CountDownLatch(2); List recordList = new ArrayList<>(); executorService.submit(new CsvExportThread("E:/0.csv", recordList, doneSignal)); executorService.submit(new CsvExportThread("E:/1.csv", recordList, doneSignal)); doneSignal.await(); System.out.println("Finish!!!"); }/** * @param colNames 表头部数据 * @param dataList 集合数据 * @param mapKeys 查找的对应数据 */ public static ByteArrayOutputStream doExport(String[] colNames, String[] mapKeys, List dataList) { try { StringBuffer buf = new StringBuffer(); // 完成数据csv文件的封装 // 输出列头 for (int i = 0; i < colNames.length; i++) { buf.append(colNames[i]).append(CSV_COLUMN_SEPARATOR); } buf.append(CSV_RN); if (null != dataList) { // 输出数据 for (int i = 0; i < dataList.size(); i++) { for (int j = 0; j < mapKeys.length; j++) { buf.append(dataList.get(i).get(mapKeys[j])).append(CSV_COLUMN_SEPARATOR); } buf.append(CSV_RN); } } // 写出响应 ByteArrayOutputStream os = new ByteArrayOutputStream(); //OutputStream os = new ByteArrayOutputStream(); os.write(buf.toString().getBytes("GBK")); os.flush(); os.close(); return os; } catch (Exception e) { LogUtils.error("doExport错误...", e); e.printStackTrace(); } return null; }public static HttpHeaders setCsvHeader(String fileName) { HttpHeaders headers = new HttpHeaders(); try { // 设置文件后缀 SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss"); String filename = new String(fileName.getBytes("gbk"), "iso8859-1") + sdf.format(new Date()) + ".csv"; headers.add("Pragma", "public"); headers.add("Cache-Control", "max-age=30"); headers.add("Content-Disposition", "attachment; filename="+filename); headers.setContentType(MediaType.valueOf("application/vnd.ms-excel; charset=UTF-8")); }catch (Exception e){ e.printStackTrace(); } return headers; }}

2.定义接口

public interface DemoService { /*导出csv文件*/ byte[] exportCsv(); }

3.接口实现类

@Service public class DemoServiceImpl implements DemoService { @Autowired private SysToolsMapper toolsMapper; @Override public byte[] exportCsv() { byte[] content = null; try { String[] sTitles = new String[]{"名称","链接","图标"}; String[] mapKeys = new String[]{"toolName","toolLink","toolIcon"}; List dataList = this.toolsMapper.selectAllTools(); ByteArrayOutputStream os = CsvUtil.doExport(sTitles,mapKeys,dataList); content = os.toByteArray(); }catch (Exception e){ e.printStackTrace(); } return content; } }

4.springboot调用接口

@Controller @RequestMapping("/demo") public class DemoController {@Autowired private DemoService demoService; @RequestMapping("/exportCsv") public ResponseEntity exportCsv(){ //设置excel文件名 String fileName="信息报表"; //设置HttpHeaders，设置fileName编码，排除导出文档名称乱码问题 HttpHeaders headers = CsvUtil.setCsvHeader(fileName); byte[] value = https://www.it610.com/article/null; try { //获取要导出的数据 value = this.demoService.exportCsv(); }catch (Exception e){ e.printStackTrace(); } return new ResponseEntity(value,headers, HttpStatus.OK); } }

【CSV|SpringBoot JAVA导出数据到CSV文件】4.maven引入jar包