项目中多处需要将页面table表格导出excel，以前做法是server端写业务代码去查db，重新组织数据，生成excel，此种做法灵活性、适用性比较广，但是无法通用，鉴于此，开发一个将页面table数据直接转为excle的小工具，可以将简单的table转excel，实现所见即所得的效果，大致思路为将table读取转成json串传入后端，后端解析json串生成excel，以下为代码：
前端部分工具类excelExcel.js如下：
/**用法：ExcelExport.export(tableId, options);options : {url: 后台处理请求的urltype：请求类型post & get, 默认postpostKey：发起请求json数据key,默认jsonDatagetCellValue(cell): 可重载的获取cell内数据的方法extendCellObject(cellObject, cell)生成cell对象后，执行该方法ajax(json)：发起请求方法，可重载}*/(function() {var ExcelExport = {extend: function(destination, source) {for (var property in source) {destination[property] = source[property];}return destination;},getTable: function(tid) {return document.getElementById(tid);},option: function (options) {var parent = this;var staticOption = {url: '/dataExport',type: 'post',postKey: 'jsonData',fileName: 'excelFile',getCellValue: function (cell) {return cell.textContent ? cell.textContent : cell.innerHTML ? cell.innerHTML : '';},extendCellObject: function (obj, cell) {return obj;},ajax: function (data) {var postData = {};postData[this.postKey] = encodeURIComponent(data);postData['dataType'] = 'json';postData['fileType'] = 'xls';postData['fileName'] = encodeURIComponent(this.fileName);parent.submitData(postData, this.url, this.type);}};return this.extend(staticOption, options || {});},newDom: function (tag, type, name, value) {var dom = document.createElement(tag);dom.type = type;dom.value = value;dom.name = name;return dom;},submitData: function (data, url, method) {var form = document.createElement("form");for (var d in data) {form.appendChild(this.newDom('input', 'hidden', d, data[d]));}form.action = url;form.method = method;form.style.display = 'none';document.body.appendChild(form);form.submit();},toJson: function (array) {var json = '[';for (var i = 0; i < array.length; i++) {if (i !== 0) {json += ',';}json += '[';for (var j = 0; j < array[i].length; j++) {if (j !== 0) {json += ',';}var obj = array[i][j];var attIndex = 0;json += '{';for (var att in obj) {if (attIndex !== 0) {json += ',';}attIndex ++;json += '"' + att + '":"' + obj[att] +'"';}json += '}';}json += ']';}json += ']';return json;},exp: function(id, optionObj) {var dataTable = this.getTable(id);var options = this.option(optionObj);var rowArry = new Array(dataTable.rows.length);for (var i = 0; i < dataTable.rows.length; i++) {var row = dataTable.rows[i];var colArray = new Array(row.cells.length);for (var j = 0; j < row.cells.length; j++) {var cell = row.cells[j];var obj = {};obj.value = options.getCellValue(cell);if (cell.colSpan && cell.colSpan > 1) {obj.colspan = cell.colSpan;}if (cell.rowSpan && cell.rowSpan > 1) {obj.rowspan = cell.rowSpan;}if (cell.style.backgroundColor) {obj.backgroundcolor = cell.style.backgroundColor;}if (cell.style.color) {obj.color = cell.style.color;}if (cell.style.width) {obj.width = cell.style.width;}colArray[j] = options.extendCellObject(obj, cell);}rowArry[i] = colArray;}options.ajax(this.toJson(rowArry));}};if ( typeof define === "function" && define.amd ) {define(function() {return ExcelExport;});} else {window.ExcelExport = ExcelExport;}})();
后端部分导出工具接口DataExporter.java如下：
import java.io.OutputStream;/*** 数据导出接口* @author bmf** @param <T> 数据类型*/public interface DataExporter<T> {/*** 导出* @param data 原始数据* @param output 输出流*/void export(T data, OutputStream output);}
后端部分json导出excel工具类JSONToExcelExporter.java如下：
import java.io.OutputStream;import java.util.HashMap;import java.util.HashSet;import java.util.Map;import java.util.Set;import java.util.regex.Matcher;import java.util.regex.Pattern;import jxl.CellView;import jxl.Workbook;import jxl.format.Alignment;import jxl.format.Border;import jxl.format.BorderLineStyle;import jxl.format.Colour;import jxl.format.RGB;import jxl.format.VerticalAlignment;import jxl.write.Label;import jxl.write.WritableCellFormat;import jxl.write.WritableFont;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;import jxl.write.WriteException;import jxl.write.biff.RowsExceededException;import lombok.extern.slf4j.Slf4j;import mons.lang.xwork.StringUtils;import com.alibaba.fastjson.JSONArray;import com.alibaba.fastjson.JSONObject;/*** json数据导出excel工具类* @author bmf**/@Slf4jpublic class JSONToExcelExporter implements DataExporter<JSONArray> {/*** html rgb 颜色字符串对应jxl近似颜色缓存*/private static ThreadLocal<Map<String, Colour>> COLOR_CACHE_MAP = new ThreadLocal<Map<String, Colour>>();/*** 被合并的单元格副本*/private static ThreadLocal<Set<Integer>> MERGED_CELLS = new ThreadLocal<Set<Integer>>();/*** 实例*/private static JSONToExcelExporter instance = null;private JSONToExcelExporter() {}/*** getInstance* @return JSONToExcelExporter*/public static JSONToExcelExporter newInstance() {if (instance == null) {instance = new JSONToExcelExporter();}return instance;}@Overridepublic void export(JSONArray data, OutputStream output) {writeExcel(data, output);}private void writeExcel(JSONArray jsonArray, OutputStream os) {WritableWorkbook book = null;try {book = Workbook.createWorkbook(os);WritableSheet sheet = book.createSheet("Sheet1", 0);CellView cellView = new CellView();// 设置自动大小// cellView.setAutosize(true);cellView.setSize(3500);for (int r = 0; r < jsonArray.size(); r++) {JSONArray colArr = JSONObject.parseArray(jsonArray.get(r).toString());int increment = 0;sheet.setRowView(r, 500, false);for (int l = 0; l < colArr.size(); l++) {int newCol = l + increment;while (MERGED_CELLS.get() != null && MERGED_CELLS.get().contains(uniqueKey(r, newCol))) {newCol = l + ++increment;}JSONObject object = JSONObject.parseObject(colArr.get(l).toString());Label label = new Label(newCol, r, object.getString("value"), buildFormat(object));sheet.addCell(label);mergeCells(sheet, object, newCol, r);sheet.setColumnView(newCol, cellView);}}book.write();book.close();} catch (Exception e) {log.error("http data export to excel error", e);} finally {try {if (os != null) {os.close();}} catch (Exception e) {e.printStackTrace();}}}/*** 合并单元格* * @param sheet* @param object* @param l* @param r* @throws RowsExceededException* @throws WriteException*/private void mergeCells(WritableSheet sheet, JSONObject object, int l, int r) throws RowsExceededException,WriteException {String rowspan = object.getString("rowspan");String colspan = object.getString("colspan");int ls = l;int rs = r;if (!StringUtils.isEmpty(rowspan) && Integer.valueOf(rowspan) > 1) {rs += Integer.valueOf(rowspan) - 1;}if (!StringUtils.isEmpty(colspan) && Integer.valueOf(colspan) > 1) {ls += Integer.valueOf(colspan) - 1;}if ((rs > r || ls > l) && rs >= r && ls >= l) {sheet.mergeCells(l, r, ls, rs);if (MERGED_CELLS.get() == null) {MERGED_CELLS.set(new HashSet<Integer>());}int temp = ls;while (ls > l || rs > r) {MERGED_CELLS.get().add(uniqueKey(rs, ls));if (ls > l) {ls--;} else {ls = temp;rs--;}}}}/*** 创建单元格样式* * @param object jsonObject* @return WritableCellFormat* @throws WriteException*/private WritableCellFormat buildFormat(JSONObject object) throws WriteException {WritableCellFormat wc = new WritableCellFormat();wc.setAlignment(Alignment.CENTRE);wc.setVerticalAlignment(VerticalAlignment.CENTRE);wc.setBorder(Border.ALL, BorderLineStyle.THIN);if (!StringUtils.isEmpty(object.getString("backgroundcolor"))) {Colour color = findSimilarColor(object.getString("backgroundcolor"));if (color != null) {wc.setBackground(color);}}WritableFont wf2 = new WritableFont(WritableFont.ARIAL);wf2.setPointSize(12);if (!StringUtils.isEmpty(object.getString("color"))) {Colour color = findSimilarColor(object.getString("color"));wf2.setColour(color);wc.setFont(wf2);}return wc;}/*** 通过html rgb颜色获取excel对应的近似颜色* * @param domColorString like rgb(233, 45, 66)* @return Color*/private Colour findSimilarColor(String domColorString) {if (StringUtils.isEmpty(domColorString)) {return null;}Pattern pattern = pile("rgb(.*)");Matcher matcher = pattern.matcher(domColorString);if (!matcher.matches()) {return null;}String[] rgbArr = domColorString.replace("rgb(", "").replace(")", "").replace(" ", "").split(",");if (rgbArr.length != 3) {return null;}if (COLOR_CACHE_MAP.get() == null) {COLOR_CACHE_MAP.set(new HashMap<String, Colour>());}if (COLOR_CACHE_MAP.get().containsKey(domColorString)) {return COLOR_CACHE_MAP.get().get(domColorString);}Colour[] colours = Colour.getAllColours();// 色差最小的索引int index = 0;// 最小色差值int mixFix = 255;// 存放色差系数int[] coloursLength = new int[colours.length];for (int i = 0; i < colours.length; i++) {RGB rgb = colours[i].getDefaultRGB();coloursLength[i] =Math.abs(rgb.getRed() - Integer.parseInt(rgbArr[0]))+ Math.abs(rgb.getGreen() - Integer.parseInt(rgbArr[1]))+ Math.abs(rgb.getBlue() - Integer.parseInt(rgbArr[2]));if (coloursLength[i] < mixFix) {mixFix = coloursLength[i];index = i;}}COLOR_CACHE_MAP.get().put(domColorString, colours[index]);return colours[index];}/*** 根据行列坐标算出可作为主键的值* * @param r 行坐标* @param l 列坐标* @return 用以区别坐标的唯一值*/private Integer uniqueKey(int r, int l) {return (r << 1) * 7 + (l << 2) * 13;}}
后端接口servlet代码如下：
import java.io.IOException;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.mon.util.StringUtils;import com.alibaba.fastjson.JSONObject;/*** 数据导出servlet* * @author bmf* */public class DataExportServlet extends HttpServlet {private static final long serialVersionUID = 1L;/*** @see HttpServlet#HttpServlet()*/public DataExportServlet() {super();}/*** @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)*/protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {exportData(request, response, "get");}/*** @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)*/protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException,IOException {exportData(request, response, "post");}private void exportData(HttpServletRequest request, HttpServletResponse response, String type) throws IOException {request.setCharacterEncoding("UTF-8");String data = request.getParameter("jsonData");if (StringUtils.isEmpty(data)) {return;}data = .URLDecoder.decode(data, "UTF-8");String filename = request.getParameter("fileName");filename = .URLDecoder.decode(filename, "UTF-8");String dataType = request.getParameter("dataType");String fileType = request.getParameter("fileType");response.setHeader("Content-Disposition", "inline;filename=" + filename + "." + fileType);response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");response.setCharacterEncoding("UTF-8");if (dataType.equals("json") && fileType.equals("xls")) {JSONToExcelExporter.newInstance().export(JSONObject.parseArray(data), response.getOutputStream());}}}
wb.xml增加配置：
<servlet><servlet-name>dataExportServlet</servlet-name><servlet-class>xxx.xxx.xxx.DataExportServlet</servlet-class></servlet><servlet-mapping><servlet-name><span style="font-family: Arial, Helvetica, sans-serif;">dataExportServlet</span><span style="font-family: Arial, Helvetica, sans-serif;"></servlet-name></span><url-pattern>/dataExport</url-pattern></servlet-mapping>
需要导出的时用法：（amd）
var excelExport = require('tool/excelExport');excelExport.exp('table', {fileName: '运营数据统计'});
