利用模板方法模式实现导出CSV文件功能
测试结果：
数据库表截图：
导出cvs文件：
页面，导出按钮，导出js
<%@ page contentType="text/html;charset=UTF-8" language="java" %> <html> <body> <h2>Hello World!</h2> <%-- 引入jQuery --%> <script src="js/jquery-1.10.2.min.js"></script> <script type="text/javascript"> $(function () { $('#exportCsv').bind("click", function(){ var formElement=$("<form>"); // 定义一个form表单 formElement.attr("style","display:none"); formElement.attr("target",""); formElement.attr("method","post"); var contextPath = "${pageContext.request.contextPath}"; formElement.attr("action", contextPath + "/item/export"); /*var filterRules = $("#purchaseDetail_datagrid").datagrid("options").filterRules; //把过滤条件传入 var inputElement=$("<input>"); inputElement.attr("type","hidden"); inputElement.attr("name","filterRules"); inputElement.attr("value",JSON.stringify(filterRules)); formElement.append(inputElement);*/ $("body").append(formElement); // 将表单放置在web中 /*inputElement=$("<input>"); inputElement.attr("type","hidden"); inputElement.attr("name","deptId"); inputElement.attr("value",deptId); formElement.append(inputElement);*/ formElement.submit();//表单提交 return false; }); }) </script> </body> <a id="exportCsv" href="javascript:void(0)">导出CSV</a> </html>
页面点击“导出cvs”按钮后，会在页面body中追加一个表单
controller
@RequestMapping(value = "/export")
@ResponseBody
public void export(@RequestParam Map<String, Object> params, Model model, HttpServletRequest request, HttpServletResponse resp) {
itemService.export(params, request, resp);
}
service
package com.alphajuns.ssm.service.impl; import com.alphajuns.ssm.mybatis.ItemMapper; import com.alphajuns.ssm.pojo.Item; import com.alphajuns.ssm.service.ItemService; import com.alphajuns.ssm.util.AbstractCsvHelper; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Service; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.*; @Service public class ItemServiceImpl extends AbstractCsvHelper implements ItemService { @Autowired private ItemMapper itemMapper; @Override public void export(Map<String, Object> params, HttpServletRequest request, HttpServletResponse resp) { exportCvs(params, request, resp); } @Override protected Map<String, String> getCsvHeader() { Map<String, String> header = new LinkedHashMap<String, String>(); header.put("id", "类目ID"); header.put("parent_id", "父类目ID"); header.put("title", "分类名称"); header.put("status", "状态"); header.put("sort_order", "排列序号"); header.put("is_parent", "是否为父类目"); header.put("created", "创建时间"); header.put("updated", "更新时间"); return header; } @Override protected String getFilename() { Date date = new Date(); DateFormat df=new SimpleDateFormat("yyyy-MM-dd"); String time=df.format(date); return "分类详情_" + time + ".csv"; } @Override protected List<Map<String, ?>> getCsvRecords(Map<String, Object> params) { List<Map<String, ?>> itemContentCategoryMapList = itemMapper.queryItemContentCategory(params); for(Map itemContentCategoryMap : itemContentCategoryMapList){ // 状态 String status = String.valueOf(itemContentCategoryMap.get("status")); String statusVal = ""; if ("1".equals(status)) { statusVal = "正常"; } else if("2".equals(status)) { statusVal = "删除"; } itemContentCategoryMap.put("status", statusVal); // 状态 String isParent = String.valueOf(itemContentCategoryMap.get("is_parent")) ; String isParentVal = ""; if ("1".equals(isParent)) { isParentVal = "true"; } else if("2".equals(isParent)) { isParentVal = "false"; } itemContentCategoryMap.put("is_parent", statusVal); } return itemContentCategoryMapList; } @Override protected void prepareParams(Map<String, Object> params) { } }
其中需要cvs的jar包，maven坐标
<dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-csv</artifactId> <version>1.4</version> </dependency>
模板方法模式中，导出步骤位于抽象类的一个方法中，该方法中调用了抽象类中的抽象方法，该抽象方法的实现类位于子类中，这样根据导出可以根据需要，自定义表头，导出的数据
导出cvs模板类AbstractCsvHelper
package com.alphajuns.ssm.util; import java.io.IOException; import java.io.PrintWriter; import java.net.URLEncoder; import java.util.ArrayList; import java.util.List; import java.util.Map; import java.util.Map.Entry; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.commons.csv.CSVFormat; import org.apache.commons.csv.CSVPrinter; import org.apache.log4j.Logger; /** * 导出csv的帮助类 * @author warrenxia * */ public abstract class AbstractCsvHelper { /** * logger */ private static Logger logger = Logger.getLogger(AbstractCsvHelper.class); /** * 导出csv * @param params * @param request * @param resp */ public void exportCvs(Map<String, Object> params, HttpServletRequest request, HttpServletResponse resp) { resp.setContentType("application/octet-stream"); resp.setCharacterEncoding("GBK"); int rows = 500; CSVFormat csvFormat = CSVFormat.DEFAULT; CSVPrinter csvPrinter = null; PrintWriter printWriter = null; try { String agent = (String)request.getHeader("USER-AGENT"); if(agent != null && agent.toLowerCase().indexOf("firefox") > 0) { resp.setHeader("Content-Disposition","attachment; filename=" + new String(getFilename().getBytes("GB2312"),"ISO-8859-1")); } else { resp.setHeader("Content-Disposition","attachment; filename=" + URLEncoder.encode(getFilename(), "UTF-8")); } printWriter = resp.getWriter(); csvPrinter = new CSVPrinter(printWriter, csvFormat); Map<String, String> header = getCsvHeader(); insertHeader(header, csvPrinter); prepareParams(params); params.put("rows", rows); params.put("page", 1); //查询导出的数据 List<Map<String,?>> mapList = getCsvRecords(params); int totalCount = 0; if(mapList!=null&&!mapList.isEmpty()){ if (mapList.get(0).containsKey("TOTALCOUNT")) { Number nTotalCount = (Number) mapList.get(0).get("TOTALCOUNT"); totalCount = nTotalCount.intValue(); } else { totalCount = mapList.size(); } } // 设置最大导出数量 String reportMaxRow = "20000"; int intReportMaxRow = 5000; if(reportMaxRow!=null && !reportMaxRow.trim().equals("")){ intReportMaxRow = Integer.parseInt(reportMaxRow.trim()); } if(totalCount>intReportMaxRow){ String errMsg = "不能导出大于"+intReportMaxRow+"条的数据，请缩小选择范围！"; List<String> record = new ArrayList<String>(); record.add(errMsg); csvPrinter.printRecord(record); throw new ReportBusinessException(errMsg); } insertRecords(mapList, header, csvPrinter); int totalPage = (int) Math.ceil((double)totalCount / rows); for (int i = 1; i < totalPage; i++) { params.put("page", i + 1); mapList = getCsvRecords(params); insertRecords(mapList, header, csvPrinter); } } catch (IOException e) { e.printStackTrace(); } catch(ReportBusinessException e){ logger.error("报表导出异常",e); throw e; } catch (Exception e) { e.printStackTrace(); } finally { try { csvPrinter.flush(); printWriter.flush(); csvPrinter.close(); printWriter.close(); } catch (Exception e) { e.printStackTrace(); } } } protected abstract Map<String, String> getCsvHeader(); protected abstract String getFilename(); protected abstract List<Map<String,?>> getCsvRecords(Map<String, Object> params); protected abstract void prepareParams(Map<String, Object> params); private void insertHeader(Map<String, String> header, CSVPrinter csvPrinter) throws IOException { csvPrinter.printRecord(header.values()); } private void insertRecords(List<Map<String,?>> mapList, Map<String, String> header, CSVPrinter csvPrinter) throws IOException { for (Map<String, ?> map : mapList) { List<String> record = new ArrayList<String>(); for (Entry<String, String> entry : header.entrySet()) { Object object = map.get(entry.getKey()); if (object == null) { record.add(""); } else { record.add(String.valueOf(object)); } } csvPrinter.printRecord(record); } } }
报表导出异常类
package com.alphajuns.ssm.util; public class ReportBusinessException extends RuntimeException{ /** * serialVersionUID */ private static final long serialVersionUID = 1L; /** * */ private String errorCode; /** * */ private String message; /** * BusinessException * @param message message */ public ReportBusinessException(String message) { this.message = message; } /** * BusinessException * @param errorCode errorCode * @param message message */ public ReportBusinessException(String errorCode, String message) { this.message = message; this.errorCode = errorCode; } /** * BusinessException * @param message message * @param cause cause */ public ReportBusinessException(String message, Throwable cause) { super(cause); this.message = message; } /** * BusinessException * @param errorCode errorCode, * @param message message * @param cause cause */ public ReportBusinessException(String errorCode, String message, Throwable cause) { super(errorCode, cause); this.errorCode = errorCode; this.message = message; } public String getErrorCode() { return this.errorCode; } public void setErrorCode(String errorCode) { this.errorCode = errorCode; } @Override public String getMessage() { return this.message; } public void setMessage(String message) { this.message = message; } }
mapper接口
List<Map<String,?>> queryItemContentCategory(Map<String, Object> params);
mapper.xml
<select id="queryItemContentCategory" resultType="map"> select * from tb_content_category </select>
