jxls实现基于excel模板的报表
此文章是基于 搭建Jquery+SpringMVC+Spring+Hibernate+MySQL平台
一. jar包介绍
1. commons-collections-3.2.jar
2. commons-digester-2.1.jar
3. commons-jexl-2.1.1.jar
4. jxls-core-1.0.jar
5. poi-3.8-20120326.jar
6. poi-ooxml-3.8-20120326.jar
二. 相关文件介绍
1. TestController.java
package com.ims.web.controller; import java.io.BufferedInputStream; import java.io.FileInputStream; import java.io.InputStream; import java.io.OutputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import net.sf.jxls.transformer.XLSTransformer; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.servlet.ModelAndView; @Controller @RequestMapping("test") public class TestController extends BaseController{ @RequestMapping("view") public ModelAndView test(){ ModelAndView view = new ModelAndView("test.jsp"); return view; } @RequestMapping("export!excel") public void export(){ String templateFileName = "runRecord.xls"; String webrootPath = System.getProperty("webapp.root"); String templateFilePath = webrootPath + "view\\iot\\"; String destFileName= "设备运行记录卡.xls"; Map<String, Object> beans = new HashMap<String, Object>(); // 设备信息 Map<String, Object> product = new HashMap<String, Object>(); product.put("model", "XG2016"); product.put("version", "V1.0"); beans.put("product", product); // 年、月、日 beans.put("year", "2016"); beans.put("month", "08"); beans.put("day", "08"); // 运行记录 Map<String, Object> record = new HashMap<String, Object>(); record.put("inputVoltage", "200"); record.put("inputVoltageStatus", 0); record.put("inputCurrent", "50"); record.put("inputCurrentStatus", 0); beans.put("record", record); // 异常处理 List<Map<String, Object>> exceptions = new ArrayList<Map<String, Object>>(); Map<String, Object> e1 = new HashMap<String, Object>(); e1.put("detail", "输入电压过大"); e1.put("handle", "降低输入电压"); e1.put("handleMode", 1); exceptions.add(e1); Map<String, Object> e2 = new HashMap<String, Object>(); e2.put("detail", "输入电流过大"); e2.put("handle", "降低输入电流"); e2.put("handleMode", 1); exceptions.add(e2); beans.put("exceptions", exceptions); InputStream in = null; OutputStream out = null; try { XLSTransformer transformer = new XLSTransformer(); in = new BufferedInputStream(new FileInputStream(templateFilePath+templateFileName)); Workbook workbook=transformer.transformXLS(in, beans); HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(0); int startMergeLine = 7; // 开始合并的行数 // 合并异常处理清单单元格 if(exceptions.size()>0){ sheet.addMergedRegion(new CellRangeAddress(startMergeLine, startMergeLine+exceptions.size()-1, 0, 0)); } //将内容写入输出流并把缓存的内容全部发出去 // out = new FileOutputStream(templateFilePath+destFileName); request.setCharacterEncoding("UTF-8"); response.setContentType("application/x-download;charset=GBK"); response.setHeader("Content-Disposition", "attachment;filename="+new String(destFileName.getBytes("GBK"),"ISO8859_1")); out=response.getOutputStream(); workbook.write(out); out.flush(); } catch (Exception e) { e.printStackTrace(); } finally{ try{ if(out!=null) out.close(); if(in!=null) in.close(); }catch(Exception e){ e.printStackTrace(); } } } }
2. excelModelExport.jsp
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>测试</title> <%@ include file="/common/basePath.jsp"%> </head> <body> ~~~~~~~~~~~~~~~~~~~~~~excel模板导出~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ <br><br> <button type="button" onclick="exportExl();">导出</button> <script type="text/javascript" src="content/js/jquery/jquery-1.8.1.min.js"></script> <script type="text/javascript" src="content/js/core/utils.js"></script> <script type="text/javascript" src="content/js/core/common.js"></script> <script type="text/javascript"> function exportExl(){ var exporter = com.exporter(); exporter.params.action = rootPath+"/test/export!excel.do"; exporter.download('xls'); } </script> </body> </html>
3. utils.js
/** * 模块名：共通脚本 * 程序名: 通用工具函数 **/ var utils = {}; /** * 格式化字符串 * 用法: .formatString("{0}-{1}","a","b"); */ utils.formatString = function () { for (var i = 1; i < arguments.length; i++) { var exp = new RegExp('\\{' + (i - 1) + '\\}', 'gm'); arguments[0] = arguments[0].replace(exp, arguments[i]); } return arguments[0]; };
4. common.js
/// <reference path="utils.js" /> /** * 模块名：共通脚本 * 程序名: 通用方法common.js **/ var com = {}; com.exporter = function (opt) { var self = this; var defaultOptions = { action: "", dataAction: "", dataParams: {}, titles: [[]], fileType: 'xls', compressType: 'none' }; this.paging = function (page,rows) { self.params.dataParams.page = page; self.params.dataParams.rows = rows; return self; }; this.compress = function () { self.params.compressType = 'zip'; return self; }; this.title = function (filed,title) { self.params.titles[filed] = title; return self; }; this.download = function (suffix) { self.params.fileType = suffix || "xls"; self.params.dataParams = JSON.stringify(self.params.dataParams); self.params.titles = JSON.stringify(self.params.titles); //创建iframe var downloadHelper = $('<iframe style="display:none;" id="downloadHelper"></iframe>').appendTo('body')[0]; var doc = downloadHelper.contentWindow.document; if (doc) { doc.open(); doc.write('')//微软为doc.clear(); doc.writeln(utils.formatString("<html><body><form id='downloadForm' name='downloadForm' method='post' action='{0}'>", self.params.action)); for (var key in self.params) doc.writeln(utils.formatString("<input type='hidden' name='{0}' value='{1}'>", key, self.params[key])); doc.writeln('<\/form><\/body><\/html>'); doc.close(); var form = doc.forms[0]; if (form) { form.submit(); } } }; initFromGrid = function (grid) { var options = grid.$element().datagrid('options'); if (grid.treegrid) options.url = options.url || grid.treegrid('options').url; var titles = [[]], length = Math.max(options.frozenColumns.length, options.columns.length); for (var i = 0; i < length; i++) titles[i] = (options.frozenColumns[i] || []).concat(options.columns[i] || []) self.params = $.extend(true, {}, defaultOptions, { dataAction: options.url, dataParams: options.queryParams, titles: titles }); }; if (opt && opt.$element) initFromGrid(opt); else self.params = $.extend(true, {}, defaultOptions, opt); return self; };
5. excel模板可参考： jxls实现动态图表
三. 运行测试
访问：http://localhost:8080/ims/test/excelModelExport.do，点击 导出 按钮，就有"设备运行记录卡.xls"导出到浏览器
