- csv工具类
ExcelCsvUtils
xls、xlsx、csv通用格式工具类
此处使用LinkedHashMap
链表哈希表,实现键值中值为空时仍存在数据以及保证顺序与sql顺序一致。
mapper.xml 中需要设置返回类型为:resultType="java.util.LinkedHashMap"
package com.xxx.xxx.utils;
import lombok.val;
import org.springframework.util.CollectionUtils;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
public class ExcelCsvUtils {
private static final String CSV_COLUMN_SEPARATOR = ",";
private static final String CSV_ROW_SEPARATOR = "\r\n";
public static void doExport(List<LinkedHashMap<String, String>> dataList, String titles, String keys, OutputStream os)
throws Exception {
StringBuffer buf = new StringBuffer();
String[] titleArr = null;
String[] keyArr = null;
titleArr = titles.split(",");
keyArr = keys.split(",");
for (String title : titleArr) {
buf.append(title).append(CSV_COLUMN_SEPARATOR);
}
buf.append(CSV_ROW_SEPARATOR);
if (!CollectionUtils.isEmpty(dataList)) {
for (Map<String, String> data : dataList) {
for (String key : keyArr) {
buf.append(ExcelCsvUtils.handleFormatValue(data.get(key))).append(CSV_COLUMN_SEPARATOR);
}
buf.append(CSV_ROW_SEPARATOR);
}
}
os.write(buf.toString().getBytes("UTF-8"));
os.flush();
}
public static void responseSetProperties(String fileName, HttpServletResponse response)
throws UnsupportedEncodingException {
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
String fn = fileName + sdf.format(new Date()) + ".csv";
String utf = "UTF-8";
response.setContentType("application/ms-txt.numberformat:@");
response.setCharacterEncoding(utf);
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "max-age=30");
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fn, utf));
}
private static String handleFormatValue(Object data) {
if (data == null) {
return "";
}
String val = data.toString();
if (val.contains("\"") ){
val=val.replace("\"", "\"\"");
}
if (val.contains(",")) {
val = "\""+val+"\"";
} else {
val = "\t" + val;
}
return val;
}
}
- 修改
yml
中mybatis
配置 根据实际情况修改call-setters-on-nulls
为true
即可
mybatis-plus:
# 扫描mapper.xml文件
mapper-locations: classpath:mapper*
controller
查询后导出
@RequestMapping(value = "generateHomeExcelCSV",method = {RequestMethod.POST},produces = {"text/plain;charset=UTF-8"})
public void generateHqmsCSV(HttpServletResponse response,@RequestBody Map<String,Object> data) throws Exception {
ServletOutputStream outputStream = response.getOutputStream();
String keys = "";
List<LinkedHashMap<String, String>> dataList = xxxMapper.getxxx(data);
for (Map.Entry<String, String> entry : dataList.get(0).entrySet()) {
keys += entry.getKey()+",";
}
keys = removeTrailingComma(keys);
ExcelCsvUtils.doExport(dataList,keys,keys,
outputStream);
}
public static String removeTrailingComma(String str) {
if (str.endsWith(",")) {
return str.substring(0, str.length() - 1);
}
return str;
}
- 前台正常
http
请求后台方法,并将返回数据处理为blob
后导出即可
this.$http
.post("xxx/xxx/generateHomeExcelCSV", { 'outEndTime': this.where.outEndTime, 'outStartTime': this.where.outStartTime })
.then((res) => {
/**
* 关闭加载框
*/
loading.close();
// 处理数据,并下载
const blob = new Blob([res.data]);
let url = window.URL.createObjectURL(blob)
let link = document.createElement('a')
link.href = url
link.setAttribute('download', 'xxxx'+Date.now()+'.csv')
document.body.appendChild(link)
link.click()
})
.catch((e) => {
/**
* 关闭加载框
*/
loading.close();
this.$message.error(e.message);
});
- 完工,回家睡觉🤣
- END