在做贵阳信用项目时，需要同部里进行数据共享，文件格式CVS。业务层方面展示的是数据库的表名，选择需要导出的表的数据。做过导出POI的，导出CVS查了一些资料，做个笔记。
展示图：数据表名
controller层需要传入的参数有：表名，库名，HttpServletRequest，HttpServletResponse
Controller.java:
exportTableService.getExportData(codeList, dataname, resp, req);
service层主要是获取cvs表头、key、导出的数据，并通过utils实现导出cvs文件。
Service.java:
public void getExportData(List<String> codes, String tableSchema, HttpServletResponse resp, HttpServletRequest req){
//通过getTableName()获取表头名称，即数据库中的字段注释
Map<String, String> nameMap = getTableName(codes, tableSchema);
//通过getKeyName()获取Key，即数据库的字段名
Map<String, String> keyMap = getKeyName(codes, tableSchema);
if (codes!=null && codes.size()>0){
for (String code : codes){
//csv表头
String header = nameMap.get(code);
//key
String key = keyMap.get(code);
//获取数据
Map<String, List<Map<String,Object>>> dataMap = self.getData(code);
List<Map<String, Object>> data = dataMap.get(code);
String content = CSVUtils.formatCsvData(data,header,key);
String fileName = "XXX.csv";
CSVUtils.exportCsv(fileName,content,resp, req);
}
}
}
因为用的是JPA，所以获取表名、key、数据都使用的jdbcTemplate来实现。
getTableName()、getKeyName()：
public Map<String, String> getTableName(List<String> codes, String tableSchema){
Map<String, String> map = new HashMap<>();
if (codes!=null){
for (String code : codes){
StringBuilder sb = new StringBuilder();
sb.append("select COLUMN_NAME,column_comment from information_schema.COLUMNS where table_name = '");
sb.append(code);
sb.append("' AND table_schema = '");
sb.append(tableSchema);
sb.append("'");
List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sb.toString());
if (mapList!=null && mapList.size()>0){
StringBuilder headerSB = new StringBuilder();
for (Map<String, Object> m : mapList){
if (headerSB!=null && StringUtils.isNotBlank(headerSB.toString())){
headerSB.append(",");
}
headerSB.append(m.get("column_comment").toString());
}
map.put(code, headerSB==null?"":headerSB.toString());
}
}
}
return map;
}
public Map<String, String> getKeyName(List<String> codes, String tableSchema){
Map<String, String> map = new HashMap<>();
if (codes!=null){
for (String code : codes){
StringBuilder sb = new StringBuilder();
sb.append("select COLUMN_NAME,column_comment from information_schema.COLUMNS where table_name = '");
sb.append(code);
sb.append("' AND table_schema = '");
sb.append(tableSchema);
sb.append("'");
List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sb.toString());
if (mapList!=null && mapList.size()>0){
StringBuilder keySB = new StringBuilder();
for (Map<String, Object> m : mapList){
if (keySB!=null && StringUtils.isNotBlank(keySB.toString())){
keySB.append(",");
}
keySB.append(m.get("COLUMN_NAME").toString());
}
map.put(code, keySB==null?"":keySB.toString());
}
}
}
return map;
}
tableNames跟keyNames存的都是K-V格式，K为表名，V为查询出来的数据加“,”拼接而成的字符串。
getData():
public Map<String, List<Map<String,Object>>> getData(String code){
Map<String, List<Map<String,Object>>> map = new HashMap<>();
StringBuilder sb = new StringBuilder();
sb.append("select * from ");
sb.append(code);
List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sb.toString());
map.put(code, mapList);
return map;
}
datas同样是map格式，K为表名，V为数据list。
接下来将这三个数据进行初始化，并导出成cvs文件。
我将这个封装成CVSUtils.java：
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 表头
* @param matchColNames data中的key
* @return
*/
public static String formatCsvData(List<Map<String, Object>> data, String displayColNames, String matchColNames){
StringBuffer buf = new StringBuffer();
String[] displayColNamesArr1 = null;
String[] matchColNamesMapArr1 = null;
//筛选key和name
displayColNamesArr1 = displayColNames.split(",");
List<String> list1 = new ArrayList<>();
for (int i=0; i<displayColNamesArr1.length; i++){
list1.add(displayColNamesArr1[i]);
}
String[] displayColNamesArr = new String[list1.size()];
displayColNamesArr = list1.toArray(displayColNamesArr);
matchColNamesMapArr1 = matchColNames.split(",");
List<String> list2 = new ArrayList<>();
for (int i=0; i<matchColNamesMapArr1.length; i++){
list2.add(matchColNamesMapArr1[i]);
}
String[] matchColNamesMapArr = new String[list2.size()];
matchColNamesMapArr = list2.toArray(matchColNamesMapArr);
// 输出列头
for (int i = 0; i < displayColNamesArr.length; i++) {
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){
buf.append(CSV_COLUMN_SEPARATOR);
}else{
if(matchColNamesMapArr[j].startsWith("-")){
buf.append("\t" +object.toString()).append(CSV_COLUMN_SEPARATOR);
}else{
buf.append(object).append(CSV_COLUMN_SEPARATOR);
}
}
}
buf.append(CSV_RN);
}
}
logger.info("csv file Initialize successfully");
return buf.toString();
}
/**
* 导出
*
* @param fileName 文件名
* @param content 内容
* @param request
* @param response
* @throws IOException
*/
public static void exportCsv(String fileName, String content,HttpServletResponse response, HttpServletRequest request) {
// 读取字符编码
String csvEncoding = "UTF-8";
// 设置响应
response.setCharacterEncoding(csvEncoding);
response.setContentType("text/csv; charset=" + csvEncoding);
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "max-age=30");
OutputStream os = null;
try {
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 + "\"");
// 写出响应
os = response.getOutputStream();
os.write(content.getBytes("GBK"));
logger.info("csv file download completed");
} catch (UnsupportedEncodingException e) {
throw new IllegalArgumentException(e.getMessage());
} catch (IOException e) {
throw new IllegalArgumentException(e.getMessage());
}finally {
try {
os.flush();
os.close();
}catch (IOException e){
throw new IllegalArgumentException(e.getMessage());
}
}
}
}
Service.java通过调用 “CSVUtils.formatCsvData(List<Map<String, Object>> data, String displayColNames, String matchColNames)” 来初始化数据，数据初始化完成后嗲用 “exportCsv(String fileName, String content,HttpServletResponse response, HttpServletRequest request)” 导出cvs文件。
至此数据导出成CVS文件就完成了，之前写过POI导出Excel，其实两者可以借鉴一下。
