html Table表格数据导出EXCEL通用工具（javaee）

        项目中多处需要将页面table表格导出excel，以前做法是server端写业务代码去查db，重新组织数据，生成excel，此种做法灵活性、适用性比较广，但是无法通用，鉴于此，开发一个将页面table数据直接转为excle的小工具，可以将简单的table转excel，实现所见即所得的效果，大致思路为将table读取转成json串传入后端，后端解析json串生成excel，以下为代码：

前端部分工具类excelExcel.js如下：

/**
    用法：ExcelExport.export(tableId, options);
    options : {
        url: 后台处理请求的url
        type：请求类型post & get, 默认post
        postKey：发起请求json数据key,默认jsonData
        getCellValue(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 org.apache.commons.lang.xwork.StringUtils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

/**
 * json数据导出excel工具类
 * @author bmf
 *
 */
@Slf4j
public 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;
    }

    @Override
    public 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 = Pattern.compile("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.apache.cxf.common.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 = java.net.URLDecoder.decode(data, "UTF-8");
        String filename = request.getParameter("fileName");
        filename = java.net.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: '运营数据统计'});