csv工具类CsvUtils
此处使用LinkedHashMap
链表哈希表,实现键值中值为空时仍存在数据以及保证顺序与sql顺序一致。
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 CsvUtils {
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 ( CsvUtils . handleFormatValue ( data. get ( key) ) ) . append ( CSV_COLUMN_SEPARATOR ) ;
}
buf. append ( CSV_ROW_SEPARATOR ) ;
}
}
os. write ( buf. toString ( ) . getBytes ( "GBK" ) ) ;
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 ( "generateCSV" )
@CrossOrigin
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) ;
CsvUtils . doExport ( dataList, keys, keys,
outputStream) ;
}
public static String removeTrailingComma ( String str) {
if ( str. endsWith ( "," ) ) {
return str. substring ( 0 , str. length ( ) - 1 ) ;
}
return str;
}
前台调用(需要先跨域后使用,详见跨域问题解决(后端Java+前端Vue2)) END