一、需求：
下载列表，在没有过滤之前下载列表所有数据，点击过滤之后，下载过滤之后对数据，生成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会导致数据全部在一行，出现问题。
