import io.swagger.annotations.ApiModelProperty;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
/**
* CSV文件，全称Comma-separated values，就是逗号分隔的数据文件。
* CSV格式规范：
* MS-DOS-style lines that end with (CR/LF) characters (optional for the last line)
* Jiger: {使用回车换行（两个字符）作为行分隔符，最后一行数据可以没有这两个字符。}
* An optional header record (there is no sure way to detect whether it is present, so care is required when importing).
* Jiger:{标题行是否需要，要双方显示约定}.
* Each record "should" contain the same number of comma-separated fields.
* Jiger:{每行记录的字段数要相同，使用逗号分隔。} 逗号是默认使用的值，双方可以约定别的。
* Any field may be quoted (with double quotes).
* Jiger:{任何字段的值都可以使用双引号括起来}. 为简单期间，可以要求都使用双引号。
* Fields containing a line-break, double-quote, and/or commas should be quoted. (If they are not, the file will likely be impossible to process correctly).
* Jiger:{字段值中如果有换行符，双引号，逗号的，必须要使用双引号括起来。这是必须的。}
* A (double) quote character in a field must be represented by two (double) quote characters.
* Jiger:{如果值中有双引号，使用一对双引号来表示原来的一个双引号}
*
* 根据swagger注解生成csv文件
* @Author: xuebin.liu
* @Date: 2021/5/30 14:17
*
*/
public class CsvUtil {
/**
* 使用Map作为缓存（提高对象解析速度），ThreadLocal 处理缓存的线程安全问题
*/
private static ThreadLocal<ConcurrentHashMap<Class ,Field[]>> cacheHelper = new ThreadLocal();
/**
* 根据csv格式规范处理csv单元格中特殊字符 " and ,
* @param singleVal
* @return
*/
public static Object dealSpecialObject(Object singleVal) {
if (singleVal == null) {
return null;
}
if (singleVal instanceof String) {
boolean hasSpecialChar = false;
String val = (String) singleVal;
if (val.contains("\"")) {
val = val.replaceAll("\"", "\"\"");
hasSpecialChar = true;
} else if (val.contains(",")) {
hasSpecialChar = true;
} else if (val.contains("\r") || val.contains("\n")) {
hasSpecialChar = true;
}
return hasSpecialChar ? "\"".concat(val).concat("\"") : val;
} else {
return singleVal;
}
}
/**
* 自动追加换行符
* @param sb
*/
public static void line (StringBuilder sb ,String lineContent){
sb.append(lineContent).append("\r\n");
}
/**
* 新增空白行
* @param sb
*/
public static void line (StringBuilder sb ){
sb.append(",,").append("\r\n");
}
/**
* 新增单元格
* @param sb
* @param data
*/
public static void cell (StringBuilder sb ,Object data){
sb.append(dealSpecialObject(data)).append(",");
}
/**
* 列表数据为空，展示空白行
* @param sb
*/
private static void lineAsEmptyList(StringBuilder sb){
sb.append(",列表无数据,").append("\r\n");
}
/**
* 创建表头
* @param clazz
* @param sb
*/
private static void buildHeader(Class<?> clazz ,StringBuilder sb){
Field[] fields = clazz.getDeclaredFields();
CsvUtil.buildHeader(Arrays.asList(fields) , sb);
}
/**
* 创建表头
* @param fields
* @param sb
*/
private static void buildHeader(List<Field> fields ,StringBuilder sb){
StringBuilder headerLine = new StringBuilder();
for (Field objField : fields) {
ApiModelProperty properties = objField.getAnnotation(ApiModelProperty.class);
String propertyName = properties.value();
CsvUtil.cell(headerLine, propertyName);
}
CsvUtil.line(sb, headerLine.toString());
}
/**
* 获取对象的字段，并进行列表字段优先排序
* @param value
* @return
*/
private static Field[] getObjFields (Object value){
Class clazz = value.getClass();
if(cacheHelper.get().containsKey(clazz)){
return cacheHelper.get().get(clazz);
}else{
Field[] fields = value.getClass().getDeclaredFields();
Arrays.sort(fields,((o1, o2) -> {
if(o1.getType().getClass().equals(List.class)){
return 1 ;
}
return 0;
}));
cacheHelper.get().putIfAbsent(clazz,fields);
return fields;
}
}
/**
* 处理普通对象,普通对象的字段中包含List也可处理
* @param value
* @param sb
* @param isNeedHeader
*/
private static void processObj(Object value,StringBuilder sb,boolean isNeedHeader){
Field[] fields = getObjFields(value);
StringBuilder bodyLine = new StringBuilder();
List<Field> noListField = new ArrayList<>();
for(Field objField :fields){
String fieldName = objField.getName();
Object fieldVal = ReflectUtil.getFieldValueByName(value,fieldName);
if(fieldVal instanceof List){
processList( fieldVal, sb);
continue;
}
noListField.add(objField);
CsvUtil.cell(bodyLine,fieldVal);
}
if(isNeedHeader){
buildHeader(noListField , sb);
}
CsvUtil.line(sb,bodyLine.toString());
}
/**
* 处理列表对象
* @param value
* @param sb
*/
private static void processList(Object value,StringBuilder sb){
int count = 0;
if(value==null ||((List)value).size()<=0){
CsvUtil.lineAsEmptyList(sb);
}
for( Object instance :(List)value){
if(count == 0){
buildHeader(instance.getClass() , sb);
}
processObj( instance, sb,false);
count ++;
}
}
/**
* 处理Map里面的数据
* @param value
* @param sb
*/
private static void processVal(Object value,StringBuilder sb){
if(value instanceof List){
processList( value, sb);
// todo
}else{
processObj( value, sb,true);
}
}
/**
* 根据数据集配合swagger注解获取CSV文件内容
* @param data
* @return
*/
public static String getCsvContent(LinkedHashMap<String,Object> data) {
StringBuilder content = new StringBuilder();
try {
CsvUtil.initCache();
data.entrySet().forEach((entry) -> {
CsvUtil.line(content, (String) dealSpecialObject(entry.getKey()));
processVal(entry.getValue(), content);
CsvUtil.line(content);
});
} finally {
CsvUtil.releaseCache();
}
return content.toString();
}
/**
* 下载csv文件，文件名不需要.csv后缀,字符编码处理成GBK,防止在MS-EXCEL中字符乱码
* @param fileName
* @param content
* @param response
*/
public static void downLoadCsv(String fileName,String content, HttpServletResponse response) {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type","application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode( fileName + ".csv", "UTF-8"));
response.getOutputStream().write(content.getBytes("GBK"));
response.getOutputStream().flush();
response.getOutputStream().close();
} catch (IOException e) {
throw new RuntimeException(e.getMessage());
}
}
/**
* 初始化当前线程的ThreadLocal变量
*/
private static void initCache(){
cacheHelper.set(new ConcurrentHashMap<Class ,Field[]>());
}
/**
* 释放内存数据,防止OOM
*/
private static void releaseCache(){
cacheHelper.get().clear();
cacheHelper.remove();
}
使用示例
List<TimeNodeVo> getGmvTrend(DashboardQueryDto dto);
@Data
@ApiModel(value="时间节点通用")
@Builder
public class TimeNodeVo {
@ApiModelProperty(value = "时间")
private String time ;
@ApiModelProperty(value = "数值")
private BigDecimal num ;
}
LinkedHashMap<String ,Object> kvs = new LinkedHashMap<>(16);
kvs.put("4.流水趋势",learnService.getGmvTrend(dto));
String content = CsvUtil.getCsvContent(kvs);
CsvUtil.downLoadCsv(fileName,content,response);
结果
屏幕截图 2021-06-02 145156.jpg
