先上传连个图片看看效果，这是界面效果dwz框架（springmvc开发）
点击导出csv效果图
js部分的代码（带条件查询的csv导出）：
function exportReportCsv(){ exportAgencyProfitList1(); } function exportAgencyProfitList1($pageNum,$numPerPage){ var $valueArray={ 'CUST_ID':"${PD.CUST_ID}"==' '?'':"${PD.CUST_ID}", 'MYSTS':"${PD.MYSTS}"==' '?'':"${PD.MYSTS}", 'AGENTID':"${PD.AGENTID}"==' '?'':"${PD.AGENTID}", 'APPLRDAT1':"${PD.APPLRDAT1}"==' '?'':"${PD.APPLRDAT1}", 'APPLRDAT2':"${PD.APPLRDAT2}"==' '?'':"${PD.APPLRDAT2}" }; window.location.href="<%=path%>/customer/exportCsv?"+jQuery.param($valueArray); return; }
jsp 代码（其实就是一个onclick事件）：
<li><a id="csv" class="edit" href="#" onclick="return exportReportCsv();"><span>导出CSV</span></a></li>
controller部分代码：
@RequestMapping("/customer/exportCsv")
@ResponseBody
public void exportCsv1(Page page,HttpServletResponse response) throws Exception{
//map中装了两个list集合，titles:List<String> varList ： List<PageData>
logger.info("客户信息csv表格导出");
pd=this.getPageData(page);
//csv表数据
List<Map<String, String>> exportData = null;
exportData = customerService.findCsvAgt1(pd);
//csv表头
LinkedHashMap<String, String> map =customerService.findCsvTabTitle1();
File file = CSVUtil.createCSVFile(response,exportData, map,CSVUtil.path,CSVUtil.name);//生成CSV文件保存在c:/customer/也就是CSVUtil.path下
String fileName = file.getName();
CSVUtil.exportFile(response, CSVUtil.path + fileName, fileName);//将c:/customer/下的文件读取成CSV文件
CSVUtil.deleteFiles(CSVUtil.path);//删除c:/customer/下的文件,只保留界面生成文件
}
interface部分代码：
/** * csv导出 */ public LinkedHashMap<String, String> findCsvTabTitle(); /** * csv导出查数据 */ public List<Map<String, String>> findCsvAgt(PageData pd) throws Exception;
接口实现类代码：
/* * csv导出表头 */ @Override//设置表头信息 public LinkedHashMap<String, String> findCsvTabTitle1() { logger.info("执行服务，查询客户账户CSV表头：findCsvTabTitle"); LinkedHashMap<String, String> map = new LinkedHashMap<>(); map.put("1", "客户ID"); map.put("2", "客户姓名"); map.put("3", "账户编号"); map.put("4", "账户类型"); map.put("5", "货币类型"); map.put("6", "账户余额"); map.put("7", "账户冻结金额"); map.put("8", "账户带清算金额"); map.put("9", "账户状态"); map.put("10", "账户标识"); map.put("11", "最后一次交易日期"); map.put("12", "最后一次交易时间"); return map; } /* * csv导出内容 */ @Override public List findCsvAgt1(PageData pd) throws Exception { logger.info("执行服务，查询客户账户CSV表数据：findCsvAgt1"); List exportData = new ArrayList<Map>(); List<PageData> dataList = (List<PageData>) dao.findForList("customerMapper.findCSVAgt1",pd);//从数据库查询数据，mybaits框架 for (PageData pdd : dataList) {//数据库数据解析，与表头对应起来 LinkedHashMap map = new LinkedHashMap(); map.put("1",pdd.getString("CUST_ID")); map.put("2",pdd.getString("CUST_NAM") ); map.put("3",pdd.getString("PAY_AC_NO")); map.put("4",pdd.getString("ACC_TYPE") ); map.put("5",pdd.getString("CCY")); map.put("6",pdd.getString("ACC_BALANCE") ); map.put("7",pdd.getString("ACC_FROZEN_AMT") ); map.put("8",pdd.getString("ACC_CLEAR_AMT") ); map.put("9",pdd.getString("AC_STATUS") ); map.put("10",pdd.getString("LIST_STS_FLG") ); map.put("11",pdd.getString("LST_TXN_DATE") ); map.put("12",pdd.getString("LST_TXN_TIME") ); exportData.add(map); } return exportData; }
sql样板：
<!-- csv导出查询========================================================--> <!-- 账户浏览EXCEL表格导出 --> <select id="findExcelAgt2" parameterType="pd" resultType="pd"> select a.CUST_ID as var1,a.CUST_NAM as var2,b.PAY_AC_NO as var3,b.ACC_TYPE as var4,b.ACC_BALANCE as var5, CASE b.AC_STATUS WHEN '0' THEN '正常' WHEN '1' THEN '未激活' WHEN '2' THEN '冻结' WHEN '9' THEN '已销毁' ELSE NULL END var6, b.LST_TXN_DATE as var7 from ARP_CUST_INFO a join ARP_AC_REL c on c.LINK_ID = a.CUST_ID join ARP_AC_PROFILE b on c.PAY_AC_NO = b.PAY_AC_NO where 1 = 1 <if test="PAY_AC_NO!=null and PAY_AC_NO!=''"> <![CDATA[AND b.PAY_AC_NO like concat(concat('%',#{PAY_AC_NO}),'%')]]> </if> <if test="CUST_ID!=null and CUST_ID!=''"> <![CDATA[AND a.CUST_ID like concat(concat('%',#{CUST_ID}),'%')]]> </if> <if test="AC_STATUS!=null and AC_STATUS!=''"> <![CDATA[AND b.AC_STATUS like concat(concat('%',#{AC_STATUS}),'%')]]> </if> <if test="LST_TXN_DATE1!=null and LST_TXN_DATE1!=''"> and to_date(b.LST_TXN_DATE,'YYYYMMDD') >= to_date(#{LST_TXN_DATE1},'YYYY-MM-DD') </if> <if test="LST_TXN_DATE2!=null and LST_TXN_DATE2!=''"> and to_date(b.LST_TXN_DATE,'YYYYMMDD') <= to_date(#{LST_TXN_DATE2},'YYYY-MM-DD') </if> order by b.LST_TXN_DATE desc,b.LST_TXN_TIME desc </select>
工具类********这才是核心的部分：
csv导出工具类 /** * */ package com.common.util; /** * 描述： * @author GK * @date 创建时间：2016年7月14日 下午3:18:02 * @version 1.0 */ /** * @author Administrator * */ import java.io.BufferedWriter; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.io.OutputStreamWriter; import java.net.URLEncoder; import java.util.ArrayList; import java.util.Iterator; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletResponse; import org.apache.commons.beanutils.BeanUtils; public class CSVUtil { public static final String name="customer";//生成文件的名的前缀，如customer89809808080.csv public static final String path="c:/customer/";//生成csv文件的保存路径 /** * 生成为CVS文件 * @param exportData * 源数据List * @param map * csv文件的列表头map * @param outPutPath * 文件路径 * @param fileName * 文件名称 * @return */ @SuppressWarnings("rawtypes") public static File createCSVFile(HttpServletResponse response,List exportData, LinkedHashMap map,String outPutPath, String fileName ) { response.setContentType("application/csv;charset=UTF-8"); File csvFile = null; BufferedWriter csvFileOutputStream = null; try { File file = new File(outPutPath); if (!file.exists()) { file.mkdir(); } //定义文件名格式并创建 csvFile = File.createTempFile(fileName, ".csv", new File(outPutPath)); System.out.println("csvFile：" + csvFile); // UTF-8使正确读取分隔符"," csvFileOutputStream = new BufferedWriter(new OutputStreamWriter(new FileOutputStream( csvFile), "UTF-8"), 1024); System.out.println("csvFileOutputStream：" + csvFileOutputStream); // 写入文件头部 for (Iterator propertyIterator = map.entrySet().iterator(); propertyIterator.hasNext();) { java.util.Map.Entry propertyEntry = (java.util.Map.Entry) propertyIterator.next(); if((String) propertyEntry.getValue() != null) { csvFileOutputStream.write((String) propertyEntry.getValue()); }else{ csvFileOutputStream.write(""); } if (propertyIterator.hasNext()) { csvFileOutputStream.write(","); } } csvFileOutputStream.newLine(); // 写入文件内容 for (Iterator iterator = exportData.iterator(); iterator.hasNext();) { Object row = (Object) iterator.next(); for (Iterator propertyIterator = map.entrySet().iterator(); propertyIterator .hasNext();) { java.util.Map.Entry propertyEntry = (java.util.Map.Entry) propertyIterator .next(); csvFileOutputStream.write((String) BeanUtils.getProperty(row, (String) propertyEntry.getKey())); if (propertyIterator.hasNext()) { csvFileOutputStream.write(","); } } if (iterator.hasNext()) { csvFileOutputStream.newLine(); } } csvFileOutputStream.flush(); } catch (Exception e) { e.printStackTrace(); } finally { try { csvFileOutputStream.close(); } catch (IOException e) { e.printStackTrace(); } } return csvFile; } /** * 下载文件 * @param response * @param csvFilePath * 文件路径 * @param fileName * 文件名称 * @throws IOException */ public static void exportFile(HttpServletResponse response, String csvFilePath, String fileName) throws IOException { response.setContentType("application/csv;charset=UTF-8"); response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8")); InputStream in = null; try { in = new FileInputStream(csvFilePath); int len = 0; byte[] buffer = new byte[1024]; response.setCharacterEncoding("UTF-8"); OutputStream out = response.getOutputStream(); while ((len = in.read(buffer)) > 0) { out.write(new byte[] { (byte) 0xEF, (byte) 0xBB, (byte) 0xBF }); out.write(buffer, 0, len); } } catch (FileNotFoundException e) { System.out.println(e); } finally { if (in != null) { try { in.close(); } catch (Exception e) { throw new RuntimeException(e); } } } } /** * 删除该目录filePath下的所有文件 * @param filePath * 文件目录路径 */ public static void deleteFiles(String filePath) { File file = new File(filePath); if (file.exists()) { File[] files = file.listFiles(); for (int i = 0; i < files.length; i++) { if (files[i].isFile()) { files[i].delete(); } } } } /** * 删除单个文件 * @param filePath * 文件目录路径 * @param fileName * 文件名称 */ public static void deleteFile(String filePath, String fileName) { File file = new File(filePath); if (file.exists()) { File[] files = file.listFiles(); for (int i = 0; i < files.length; i++) { if (files[i].isFile()) { if (files[i].getName().equals(fileName)) { files[i].delete(); return; } } } } } /** * 测试数据 * @param args */ @SuppressWarnings({ "rawtypes", "unchecked" }) public static void main(String[] args) { HttpServletResponse response; List exportData = new ArrayList<Map>(); Map row1 = new LinkedHashMap<String, String>(); row1.put("1", "11"); row1.put("2", "12"); row1.put("3", "13"); row1.put("4", "14"); exportData.add(row1); row1 = new LinkedHashMap<String, String>(); row1.put("1", "21"); row1.put("2", "22"); row1.put("3", "23"); row1.put("4", "24"); exportData.add(row1); LinkedHashMap map = new LinkedHashMap(); map.put("1", "第一列"); map.put("2", "第二列"); map.put("3", "第三列"); map.put("4", "第四列"); String path = "c:/"; String fileName = "文件导出"; // File file = CSVUtil.createCSVFile(response,exportData, map, path, fileName); // String fileName2 = file.getName(); // System.out.println("文件名称：" + fileName2); } }
到此就结束啦。。。
