Java实现生成csv文件并导入数据

一、需求：

        下载列表，在没有过滤之前下载列表所有数据，点击过滤之后，下载过滤之后对数据，生成csv文件。

二、思路：

        先根据条件（是否过滤了数据）筛选出数据，将数据导入csv文件，生成文件并返回。

三、代码实现：

1、controller层

 /**
     * 文件下载（失败了会返回一个有部分数据的Excel）
     * <p>
     * 1. 创建excel对应的实体对象
     * <p>
     * 2. 设置返回的 参数
     * <p>
     * 3. 直接写，这里注意，finish的时候会自动关闭OutputStream,当然外面再关闭流问题不大
     */
    @ApiOperation(value = "downloadCSV", notes = "download solution")
    @CactusAction(url = "api/downloadCSV", method = HttpMethod.POST)
    @PostMapping("/downloadCSV")
    public void downloadAndJudgeFilter(HttpServletResponse response, HttpServletRequest request
            ,@ApiIgnore CactusContext context, @Validated @RequestBody OrderSolutionDownloadDTO downloadDTO) throws IOException {
        //-------传入参数根据自己的传  CactusContext为获取到用户信息-------OrderSolutionDownloadDTO为查询的参数dto--------
        //-------这一块------ 根据自己的条件筛选出需要导出的数据 ---------------
        JSONObject criteria = null;
        if(downloadDTO.getFilterId() > 0){
            Filters filter = filtersService.getOne(Wrappers.<Filters>lambdaQuery().eq(Filters::getAccountId, context.getAccountId())
                    .eq(Filters::getCreatorId, context.getUserId()).eq(Filters::getId, downloadDTO.getFilterId()));
            if (Objects.nonNull(filter)){
                criteria = JSONObject.parseObject(filter.getCriteria());
            }
        }else{
            criteria = JSONObject.parseObject(downloadDTO.getF());
        }
        List<SolutionDocument> solutionDocuments = returnAndRefundListService.downloadAndJudgeFilter(context, criteria);
        // --------------数据为List集合solutionDocuments------------------------------
        // ----------将数据传入ExportCsvUtils中
        ExportCsvUtils.exportCsv(response,request,solutionDocuments);

    }

2、ExportCsvUtils

import com.shulex.cloud.platform.ticket.consts.SolutionListConstants;
import com.shulex.cloud.platform.ticket.es.document.SolutionDocument;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

/**
  * @Author: c
  * @Description:
  * @Date:  2022/4/13
  */
public class ExportCsvUtils {

    /** CSV文件列分隔符 */
    private static final String CSV_COLUMN_SEPARATOR = ",";

    /** CSV文件列分隔符 */
    private static final String CSV_RN = "\r\n";

    /**
     * export csv
     * @param response
     * @param request
     * @param  list
     * SolutionListConstants为自定义的表头常量类
     */
    public static void exportCsv(HttpServletResponse response, HttpServletRequest request
            , List<SolutionDocument> list){
        // 设置表格头
        Object[] head = {SolutionListConstants.TICKET_NUMBER,SolutionListConstants.TICKET_CREATE_DATE,
                SolutionListConstants.CUSTOMER_EMAIL,SolutionListConstants.CUSTOMER_NAME,
                SolutionListConstants.PLATFORM,SolutionListConstants.MARKET,SolutionListConstants.SELLER,
                SolutionListConstants.ORDER_PURCHASE_DATE,SolutionListConstants.ORDER_NUMBER,
                SolutionListConstants.PRODUCT_CATEGORY,SolutionListConstants.SKU,SolutionListConstants.ITEMS,
                SolutionListConstants.SKU_QUANTITY,SolutionListConstants.SKU_AMOUNT,
                SolutionListConstants.SOLUTION_CREATE_DATE,SolutionListConstants.SOLUTION_TYPE,
                SolutionListConstants.SOLUTION_QUANTITY,SolutionListConstants.SOLUTION_AMOUNT,
                SolutionListConstants.CURRENCY,SolutionListConstants.SOLUTION_NOTE};
        List<Object> headList = Arrays.asList(head);

        List<List<Object>> dataList = getNovel(list);
        // 导出文件路径
        String downloadFilePath = request.getSession().getServletContext().getRealPath("");
        // 导出文件名称
        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("HH:mm:ss");

        String fileName = "solution_List_"+ LocalDate.now()+"_"+dtf.format(LocalDateTime.now());

        // 导出CSV文件
        File csvFile = ExportCsvUtils.createCSVFile(headList, dataList, downloadFilePath, fileName);

        try {
            // 取得文件名。
            String filename = csvFile.getName();
            // 取得文件的后缀名。
            String ext = filename.substring(filename.lastIndexOf(".") + 1).toUpperCase();
            // 以流的形式下载文件。
            FileInputStream fis = new FileInputStream(csvFile);
            // 设置response的Header
            String userAgent = request.getHeader("User-Agent");
            // 针对IE或者以IE为内核的浏览器：
            if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
                filename = java.net.URLEncoder.encode(filename, "UTF-8");
            } else {
                // 非IE浏览器的处理：
                filename = new String(filename.getBytes("UTF-8"), "ISO-8859-1");
            }
            response.setHeader("Content-disposition",String.format("attachment; filename=\"%s\"", filename));
            response.setContentType("multipart/form-data");
            response.setCharacterEncoding("UTF-8");

            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/octet-stream");

            int content = 0;
            while ((content = fis.read()) != -1) {
                toClient.write(content);
            }
            fis.close();
            toClient.flush();
            toClient.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }

    }

    /**
     * 导入的数据
     * @param list
     * @return
     */
    private static List<List<Object>> getNovel(List<SolutionDocument> list) {
        List<List<Object>> dataList = new ArrayList<List<Object>>();
        List<Object> rowList = null;
        // 1000000 data spent 13s
        if (list != null && list.size() > 0) {
            for (int i = 0; i < list.size(); i++) {
                rowList = new ArrayList<Object>();
                Object[] row = new Object[20];
                // 根据表头列数对应相应的数据
                row[0] = list.get(i).getTicketNumber();
                row[1] = list.get(i).getTicketCreateDate();
                row[2] = list.get(i).getCustomerEmail();
                row[3] = list.get(i).getCustomerName();
                row[4] = list.get(i).getPlatform();
                row[5] = list.get(i).getMarket();
                row[6] = list.get(i).getSeller();

                row[7] = list.get(i).getOrderPurchaseDate();
                row[8] = list.get(i).getOrderNumber();
                row[9] = list.get(i).getProductCategory();
                row[10] = list.get(i).getSku();
                row[11] = list.get(i).getItems();
                row[12] = list.get(i).getSkuQuantity();
                row[13] = list.get(i).getSkuAmount();

                row[14] = list.get(i).getSolutionCreateDate();
                row[15] = list.get(i).getSolutionType();
                row[16] = list.get(i).getSolutionQuantity();
                row[17] = list.get(i).getSolutionAmount();
                row[18] = list.get(i).getCurrency();
                row[19] = list.get(i).getSolutionNote();

                for(int j=0;j<row.length;j++){
                    rowList.add(row[j]);
                }
                dataList.add(rowList);
            }

        }

        return dataList;
    }


    /**
     *  new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
     * CSV文件生成方法
     * @param head
     * @param dataList
     * @param outPutPath
     * @param filename
     * @return
     */
    public static File createCSVFile(List<Object> head, List<List<Object>> dataList,String outPutPath, String filename) {
        File csvFile = null;
        BufferedWriter csvWriter = null;
        try {
            csvFile = new File(outPutPath + File.separator + filename + ".csv");
            File parent = csvFile.getParentFile();
            if (parent != null && !parent.exists()) {
                parent.mkdirs();
            }
            csvFile.createNewFile();

            // GB2312使正确读取分隔符","
            csvWriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(csvFile),
                    "GB2312"), 1024);

            // 测试乱码-------
//            csvWriter.write('\ufeff');

//            csvWriter.write(new String(new byte[] { (byte) 0xEF, (byte) 0xBB,(byte) 0xBF }));
            // --------------

            // 写入文件头部
            writeRow(head, csvWriter);
            // 写入文件内容
            for (List<Object> row : dataList) {
                writeRow(row, csvWriter);
            }
            csvWriter.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                csvWriter.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return csvFile;
    }

    /**
     * 写一行数据方法
     * @param row
     * @param csvWriter
     * @throws IOException
     */
    private static void writeRow(List<Object> row, BufferedWriter csvWriter) throws IOException {
        // 写入文件头部
        for (Object data : row) {
            StringBuffer buf = new StringBuffer();
            String rowStr =  buf.append("\"").append(data).append("\t\",").toString();
            csvWriter.write(rowStr);
        }
        csvWriter.newLine();
    }

}

3、SolutionListConstants常量类

/**
  * @Author: c
  * @Description:
  * @Date:  2022/4/13
  */
public class SolutionListConstants {

    private SolutionListConstants() {
    }

    public static final String TICKET_NUMBER = "Ticket Number";
    public static final String TICKET_CREATE_DATE = "Ticket Create Date";
    public static final String CUSTOMER_EMAIL = "Customer Email";
    public static final String CUSTOMER_NAME = "Customer Name";
    public static final String PLATFORM = "Platform";
    public static final String MARKET = "Market";
    public static final String SELLER = "Seller";

    public static final String ORDER_PURCHASE_DATE = "Order Purchase Date";
    public static final String ORDER_NUMBER = "Order Number";
    public static final String PRODUCT_CATEGORY = "Product Category";
    public static final String SKU = "SKU";
    public static final String ITEMS = "Items";
    public static final String SKU_QUANTITY = "SKU Quantity";
    public static final String SKU_AMOUNT = "SKU Amount";

    public static final String SOLUTION_CREATE_DATE = "Solution Create Date";
    public static final String SOLUTION_TYPE = "Solution Type";
    public static final String SOLUTION_QUANTITY = "Solution Quantity";
    public static final String SOLUTION_AMOUNT = "Solution Amount";
    public static final String CURRENCY = "Currency";
    public static final String SOLUTION_NOTE = "Solution Note";
}

四、总结

        由于需要传入参数，使用的是post请求，但前端弄了一天下载的数据会出现中文乱码（后端提供的这个接口通过postman测试下载的文件是完好的）遇到这个问题，不要再使用这个方法，请查看我使用的easyExcel生成excel文件的方法，强行将本文中导出的csv文件格式改成.xlsx会导致数据全部在一行，出现问题。