jquery][struts][poi]异步导出excel

主要功能点，提供异步查询数据导出excel功能。
不希望将excel保存在服务器中，所以在服务器的内存中生产excel stream后返回给客户端，
ajax请求接收到stream后无法处理，所以需要使用submit，
常规submit会刷新当前页面
无刷新submit我想过采用有两种：1、target="iframe name" 2、_blank
通过iframe的submit，需要设置submit的callback方法，将页面中给客户看的exporting提示取消掉，更麻烦一些，所以用了_blank，打开一个空的窗口，excel返回后窗口会自动关闭，虽然不算异步查询，但不影响主页的使用，所以soso。
网上说内存导出excel通常会有数据量的问题，这个问题我还没开始考虑。
代码的移植性还没设计。


html
一共有两个页面，report.html 和 excel.html, js框架这里主要用了jquery

excel.html中定义了以下内容：
<form id="excel" style="display:none;" target="_blank" action="../metrics/excel.action">
<input name="forecast_date" />
<input name="wid" />
<input name="product_sort" />
<input name="product_sort_level" />
<input name="dimon" />
<input name="target_selected" />
</form>
form里面的target="_blank"，提供了不刷新当前页面的submit效果，action定义了submit的请求路径

report.html中主要编写了export按钮的click事件，先通过ajax请求获取到excel.html，将dom元素append到当前html中，然后使用jquery的submit方法，因为excel.html中form的target是_blank，所以不会刷新report.html这个页面，而是打开一个新窗口。jdForm.setFormValues和jdForm.getFormValues只是填充和获取form里面的内容，在export excel中没有参考价值
$("#export").click(function(e) {
        var obj = jdForm.getFormValues("#form_query");
        if (!jdForm.validate(obj))
                return false;
        $.ajax({
                url : "excel.html",
                success : function(html) {
                        $("form#excel").remove();
                        $("body").append(html);
                        jdForm.setFormValues(obj, $("form#excel"));
                        $("form#excel").submit();
                }
        });
        return false;
});


java使用struts2和poi完成excel导出，poi将数据在内存中做成excel stream，然后交付struts2返回给页面。所以普通的ajax get/post不能直接调用这个action，因为返回的是stream给ajax的callback函数，这样在回调函数中只能获取到一堆乱码。这里需要使用submit方式，浏览器才会将服务端返回的stream作为excel提供给用户下载或打开，应该和http request协议有关，但没有仔细研究过协议，吃了哑巴亏。

struts的配置：
<action name="excel" method="excel" class="com.scm.forecasting.action.metrics.ReportAction">
        <result name="success" type="stream">
                <param name="contentType">application/vnd.ms-excel</param>
                <param name="contentDisposition">attachment;filename="metrics.xls"</param>
                <param name="inputName">excelFile</param>
        </result>
        <result name="error" type="flexVelocity">
                <param name="location">/WEB-INF/vm/conf/common_error.vm</param>
        </result>
</action>

Action中的主要代码：
public class ReportAction extends ForecastBaseAction {
        private static final long serialVersionUID = 12163451254776L;
        private final static Log log = LogFactory.getLog(ReportAction.class);
        private ReportService reportService;
        private List<Report> rowsData;
        private List<String> across;
        private String e;
        private InputStream excelFile;
        public String excel() throws Exception {
                try {
                        this.readData();
                        excelFile = ExcelUtil.genExcel(rowsData);
                        return SUCCESS;
                } catch (Exception e) {
                        this.e = StringUtil.generateStackTrace(e);
                        return ERROR;
                }
        }
        private List<Report> readData() throws Exception {
                String map = "Metrics.metric";
                String targetSelected = this.request.getParameter("target_selected");
                HashMap<String, Object> param = new HashMap<String, Object>();
                param.put("forecast_date", this.request.getParameter("forecast_date"));
                param.put("wid", this.request.getParameter("wid"));
                param.put("product_sort", this.request.getParameter("product_sort"));
                param.put("product_sort_level", this.request.getParameter("product_sort_level"));
                param.put("dimon", this.request.getParameter("dimon"));
                // 用户输入的target_selected中含有多个指标，以;作为分隔符
                this.across = new ArrayList<String>();
                for (String s : targetSelected.split(";")) {
                        if (!s.isEmpty()) {
                                this.across.add(s);
                                param.put(s, s);
                        }
                }
                this.rowsData = reportService.query(map, param);
                return this.rowsData;
        }

        public List<Report> getRowsData() {
                return rowsData;
        }

        public void setReportService(ReportService reportService) {
                this.reportService = reportService;
        }

        public List<String> getAcross() {
                return this.across;
        }

        public String getE() {
                return this.e;
        }

        public InputStream getExcelFile() {
                return excelFile;
        }

}



ExcelUtil工具类的主要代码，借助了poi，poi那部分代码是抄网上的，自己只是做了反射填充
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class ExcelUtil {
        public static ByteArrayInputStream genExcel(List<Report> rows)
                        throws Exception {
                HSSFWorkbook workbook = new HSSFWorkbook();
                HSSFSheet sheet = workbook.createSheet("sheet1");
                {
                        String[] header = { "预测日期", "比较方式", "MAPE", "Override MAPE", "CR",
                                        "Override CR" };
                        String[] data = { "date", "dimon", "mape", "omape", "cr", "ocr" };
                        // 创建表头
                        int ri = 0, ci = 0;
                        HSSFRow row = sheet.createRow(ri++);
                        HSSFCell cell = null;
                        for (ci = 0; ci < header.length; ci++) {
                                cell = row.createCell(ci);
                                cell.setCellValue(header[ci]);
                        }
                        // 创建数据
                        for (ri = 0; ri < rows.size(); ri++) {
                                row = sheet.createRow(ri + 1);
                                for (ci = 0; ci < data.length; ci++) {
                                        cell = row.createCell(ci);
                                    cell.setCellValue(rows.get(ri).getField(data[ci]).toString());
                                }
                        }
                }
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                baos.flush();
                workbook.write(baos);
                byte[] ba = baos.toByteArray();
                ByteArrayInputStream bais = new ByteArrayInputStream(ba);
                return bais;
        }
}

domain模型的主要代码
public class Report {
        private Date date;
        private String dimon;
        private float mape;
        private float omape;
        private float cr;
        private float ocr;
        //提供反射获取Report中属性值的方法getField
        public Object getField(String name) {
                try {
                        Field f = this.getClass().getDeclaredField(name);
                        Object o = f.get(this);
                        if (f.getType().equals(Date.class)) {
                                o = DateUtil.sf.format(o);
                        }
                        return o;
                } catch (Exception e) {
                        return null;
                }
        }
        //get/set......
}