private void getCsv(List<ExcelDto> list, HttpServletRequest request, HttpServletResponse response) throws IOException {
List<Map<String, Object>> listDto = ListUtils.newArrayList();
for (ExcelDto dto : list) {
Map<String, Object> map = BeanUtil.beanToMap(dto);
listDto.add(map);
}
//csv表头
String header = "client_acc_id,id_code,phone,remark";
//下面 data里的key,可以说是数据库字段了
String key = "client_acc_id,id_code,phone,remark";
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
String fileName = sdf.format(new Date()).toString() + "-ttl.csv";
//从数据库加载 你的数据
String content = CSVUtils.formatCsvData(listDto, header, key);
try {
CSVUtils.exportCsv(fileName, content,request, response);
return;
} catch (IOException e) {
e.printStackTrace();
}
}
package com.jeesite.modules.util;
import java.io.*;
import java.net.URLEncoder;
import java.util.*;
import java.util.regex.Pattern;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.log4j.Logger;
import com.jeesite.common.lang.StringUtils;
/**
* @describe 导出csv并弹出下载框提示~ 可以下载2W数据没问题
*/
public class CSVUtils {
/** CSV文件列分隔符 */
private static final String CSV_COLUMN_SEPARATOR = ",";
/** CSV文件列分隔符 */
private static final String CSV_RN = "\r\n";
private final static Logger logger = Logger.getLogger(CSVUtils.class);
/**
* 数据初始化
* @param data 数据库查出来的数据
* @param displayColNames csv表头
* @param matchColNames data中的key ,可以说是数据库字段了,原本为”0001”类型的数据在excel中打开会被默认改变为”1”的数据。 解决方法 :key前加"'"用于特殊处理;
* @return
*/
public static String formatCsvData(List<Map<String, Object>> data,
String displayColNames, String matchColNames) {
StringBuffer buf = new StringBuffer();
String[] displayColNamesArr = null;
String[] matchColNamesMapArr = null;
displayColNamesArr = displayColNames.split(",");
matchColNamesMapArr = matchColNames.split(",");
// 输出列头
for (int i = 0; i < displayColNamesArr.length; i++) {
if (i==displayColNamesArr.length-1) {
buf.append(displayColNamesArr[i]);
}else{
buf.append(displayColNamesArr[i]).append(CSV_COLUMN_SEPARATOR);
}
}
buf.append(CSV_RN);
if (null != data) {
// 输出数据
for (int i = 0; i < data.size(); i++) {
for (int j = 0; j < matchColNamesMapArr.length; j++) {
//处理list<Map>中 value=null的数据
Object object = data.get(i).get(matchColNamesMapArr[j]);
if(object==null){
object = data.get(i).get(matchColNamesMapArr[j].substring(1));
}
if(object==null){
if (j!=matchColNamesMapArr.length-1) {
buf.append(CSV_COLUMN_SEPARATOR);
}
}else{
String value = object.toString();
if (isInteger(value)) {
value = "\t"+value;
}
if(value.contains(",")){
//如果还有双引号,先将双引号转义,避免两边加了双引号后转义错误
if(value.contains("\"")){
value=value.replace("\"", "\"\"");
}
//将逗号转义
value="\""+value+"\"";
}
if (!value.contains("@")) {
value = value.toUpperCase();
}
if(matchColNamesMapArr[j].startsWith("-")){
// buf.append("\t" +object.toString()).append(CSV_COLUMN_SEPARATOR);
if (j==matchColNamesMapArr.length-1) {
buf.append("" +value);
}else{
buf.append("" +value).append(CSV_COLUMN_SEPARATOR);
}
}else{
if (j==matchColNamesMapArr.length-1) {
buf.append(value);
}else{
buf.append(value).append(CSV_COLUMN_SEPARATOR);
}
}
}
}
buf.append(CSV_RN);
}
}
logger.info("csv file Initialize successfully");
return buf.toString();
}
public static boolean isInteger(String str) {
Pattern pattern = Pattern.compile("^[-\\+]?[\\d]*$");
return pattern.matcher(str).matches();
}
/**
* 导出
* @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 = "UTF-8";
// 设置响应
response.setCharacterEncoding(csvEncoding);
response.setContentType("text/csv; charset=" + csvEncoding);
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "max-age=30");
final String userAgent = request.getHeader("USER-AGENT");
if(StringUtils.contains(userAgent, "MSIE")){//IE浏览器
fileName = URLEncoder.encode(fileName,"UTF8");
}else if(StringUtils.contains(userAgent, "Mozilla")){//google,火狐浏览器
fileName = new String(fileName.getBytes(), "ISO8859-1");
}else{
fileName = URLEncoder.encode(fileName,"UTF8");//其他浏览器
}
response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
// 写出响应
OutputStream os = response.getOutputStream();
os.write(content.getBytes("UTF8"));
os.flush();
os.close();
logger.info("csv file download completed");
}
}