场景:前台展示数据信息,添加导出按键,将显示出的数据(可能导出的数据比显示的更丰富)导出为csv文件;
考虑:因为场景不太涉及到大批量数据,因此直接考虑用stringbuilder(不存在线程安全问题)拼接为csv格式,只需要将列信息输入,查询到的数据信息以,隔开,\r\n作为换行保存即可。
controller中代码:
/**
* 导出csv
*/
@RequestMapping(value = "/log/exportAloneCsv", method = RequestMethod.POST)
@ResponseBody
public void exportAloneHotQueryCsv(HttpServletRequest request, HttpServletResponse response) throws Exception {
Map<String, Object> params = RequestUtils.convertRequestToMap(request);
Long startTime = getLong(params, "startTime");
Long endTime = getLong(params, "endTime");
String query = getString(params, "query");
String type = getString(params, "type"); //
if (type == null) {
type = "Boe";
}
String appId = getAppId(params);
List<SearchHistoryVO> searchHistoryVOS = logAnalysisService.getHqOneByTN(query,startTime, endTime);
String[] titles = {"来源", "用户名", "词","del","搜索时间"};
String[] propertys = {"appId", "name", "word","del","queryTime"};
CsvExportUtil.responseSetProperties(query+"热词搜索", response);
OutputStream outputStream = null;
try {
outputStream = response.getOutputStream();
CsvExportUtil.doExport(searchHistoryVOS, titles, propertys, outputStream);
} catch (IOException e) {
e.printStackTrace();
} finally {
if (outputStream != null) {
outputStream.close();
}
}
}
}
工具类代码:
/**
* 导出查询结果为csv文件
*/
public class CsvExportUtil {
/**
* CSV文件列分隔符
*/
private static final String CSV_COLUMN_SEPARATOR = ",";
/**
* CSV文件行分隔符
*/
private static final String CSV_ROW_SEPARATOR = "\r\n";
/**
* @param dataList 集合数据
* @param titles 表头部数据
* @param propertys 数据实体的属性
* @param os 输出流
*/
public static <T> void doExport(List<T> dataList, String[] titles, String[] propertys, OutputStream os) throws Exception {
StringBuilder bud = new StringBuilder();
// 组装表头
for (String title : titles) {
bud.append(title).append(CSV_COLUMN_SEPARATOR);
}
bud.append(CSV_ROW_SEPARATOR);
// 组装数据
if (CollectionUtils.isNotEmpty(dataList)) {
for (Object obj : dataList) {
Field[] fields = obj.getClass().getDeclaredFields();
for (String property: propertys){
for (Field field : fields) {
field.setAccessible(true);
if(property.equalsIgnoreCase(field.getName())){
bud.append(field.get(obj).toString().replace(":","-").replace("/","-"));
bud.append(CSV_COLUMN_SEPARATOR);
continue;
}
}
}
bud.append(CSV_ROW_SEPARATOR);
}
}
// 写出响应
os.write(bud.toString().getBytes("GBK")); //windows下防止中文乱码
os.flush();
}
/**
* 设置Header
*
* @param fileName
* @param response
* @throws UnsupportedEncodingException
*/
public static void responseSetProperties(String fileName, HttpServletResponse response) throws UnsupportedEncodingException {
// 设置文件后缀
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
String fn = fileName + sdf.format(new Date());
response.setContentType("application/octet-stream");
response.setContentType("Content-type:application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition","attachment;filename=\"" + fn + ".csv\"");
}
}
可以经过postman请求并下载测试,记录一下,以备以后随时拿来用。