导出CSV
Util代码:
package com.mmall.util;
import lombok.extern.slf4j.Slf4j;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.List;
import java.util.Map;
/**
* 导出CSV 工具类
* @date 2019/5/6
*/
@Slf4j
public class CSVUtil {
/**
* CSV文件列分隔符
*/
private static final String CSV_COLUMN_SEPARATOR = ",";
/**
* CSV文件列分隔符
*/
private static final String CSV_RN = "\r\n";
/**
* 数据初始化
*
* @param data 数据库查出来的数据
* @param displayColNames csv表头
* @param matchColNames data中的key ,可以说是数据库字段了,原本为”0001”类型的数据在excel中打开会被默认改变为”1”的数据。 解决方法 :key前加"'"用于特殊处理;
* 例如 输入列名为"num"数字为 001,则传入的key值为"-num",保证输出为字符串
* @return
*/
public static String formatCsvData(List<Map<String, Object>> data,
String displayColNames, String matchColNames) {
StringBuilder builder = new StringBuilder();
String[] displayColNamesArr;
String[] matchColNamesMapArr;
displayColNamesArr = displayColNames.split(",");
matchColNamesMapArr = matchColNames.split(",");
// 输出列头
for (String s : displayColNamesArr) {
builder.append(s).append(CSV_COLUMN_SEPARATOR);
}
builder.append(CSV_RN);
if (null != data) {
// 输出数据
for (Map<String, Object> datum : data) {
for (String s : matchColNamesMapArr) {
//处理list<Map>中 value=null的数据
Object object = datum.get(s);
if (object == null) {
object = datum.get(s.substring(1));
}
if (object == null) {
builder.append(CSV_COLUMN_SEPARATOR);
} else {
if (s.startsWith("-")) {
builder.append("\t").append(object.toString()).append(CSV_COLUMN_SEPARATOR);
} else {
builder.append(object).append(CSV_COLUMN_SEPARATOR);
}
}
}
builder.append(CSV_RN);
}
}
log.info("csv file Initialize successfully");
return builder.toString();
}
/**
* 导出
*
* @param fileName 文件名
* @param content 内容
* @param request
* @param response
* @throws IOException
*/
public static void exportCsv(String fileName, String content, HttpServletRequest request,
HttpServletResponse response) throws IOException {
// 读取字符编码
String csvEncoding = StandardCharsets.UTF_8.toString();
// 设置响应
response.setCharacterEncoding(csvEncoding);
response.setContentType("text/csv; charset=" + csvEncoding);
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "max-age=30");
fileName = URLEncoder.encode(fileName, csvEncoding);
response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
// 写出响应
OutputStream os = response.getOutputStream();
os.write(content.getBytes(csvEncoding));
os.flush();
os.close();
log.info("csv file download completed");
}
}
代码:
public void exportSeq(HttpServletResponse response) throws IOException {
List<SeqNoVo> seqNoVos = (List<SeqNoVo>) session.getAttribute("seqNoVos");
List<Map<String, Object>> data = seqNoVos.stream().map(seqNoVo -> {
Map<String, Object> map = new HashMap<>(16);
map.put("id", seqNoVo.getId());
map.put("seq_no", seqNoVo.getSeqNo());
map.put("integral", seqNoVo.getIntegral());
map.put("status", seqNoVo.getStatus().equals(0) ? "否" : "是");
map.put("remarks", seqNoVo.getRemarks());
return map;
}).collect(Collectors.toList());
//csv表头
String header = "序号,序列号,对应积分,是否被使用,备注";
//对应的数据库字段
String key = "id,seq_no,integral,status,remarks";
//csv文件名
String fileName = "序列号.csv";
String content = CSVUtil.formatCsvData(data, header, key);
CSVUtil.exportCsv(fileName, content, request, response);
log.info("导出完成");
}
HTML:
<div class="hidden-xs" id="SeqnoTableToolbar" role="group">
<#button name="生成领奖码" icon="fa-plus" clickFun="Seqno.openAddSeqno()"/>
<a class="btn btn-primary button-margin" id="updown" onclick="updown()">
<i class="fa "></i> 导出CSV
</a>
</div>
<script>
function updown() {
// 下载链接需要添加当前时间戳 以防浏览器缓存导致下载内容不一致
$("#updown").attr("href",Feng.ctxPath + "/seqno/exportSeq?curr="+new Date().getTime());
}
</script>