struts2导出excel

a68091555

浏览: 26426 次
性别:
来自: 深圳

最近访客更多访客>>

echo3311

huangqiyan

shun两年

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

完整页面

Excel C C++C#

public String ExcelExport() throws Exception{

    HttpServletResponse response = ServletActionContext.getResponse();
    // 定义request ,response.

    // 查询下载附件.
    // 设置下载头信息.begin
    response.setCharacterEncoding("UTF-8");
    response.setContentType("file");
    response.setHeader("Content-Disposition", "attachment; filename="+ new String("报表.xls".getBytes("GB2312"), "iso8859-1"));
    // 这个地方一定要进行编码的转换要不然中文字符会出现乱码.
    // 设置下载头信息.end,
    HttpServletRequest request = ServletActionContext.getRequest();

    OutputStream output = null;
    InputStream fis = null;
    try {
        output = response.getOutputStream();
        jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(output);
        jxl.write.WritableSheet ws = wwb.createSheet("各项产品月销售收入统计汇总表 ", 0);


        WritableFont wf = new WritableFont(WritableFont.ARIAL, 10,
                 WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
                 jxl.format.Colour.BLACK); // 定义格式字体下划线斜体粗体颜色

        //创建格式化对象实例
        WritableCellFormat totalx2Format = new WritableCellFormat(wf);

        //垂直居中
        totalx2Format.setVerticalAlignment(VerticalAlignment.CENTRE);

        //水平居中
        totalx2Format.setAlignment(Alignment.CENTRE);




        // 设置标题.ws.addCell(new jxl.write.Label(列, 行, 内容.));

        ws.addCell(new Label(0, 1, "单位名称",totalx2Format));
        ws.addCell(new Label(1, 1, "神州通(含邮政汇款)",totalx2Format));
        ws.addCell(new Label(7, 1, "充值卡",totalx2Format));
        ws.addCell(new Label(13, 1, "在线定卡",totalx2Format));
        ws.addCell(new Label(15, 1, "本月销售总金额(单位：元)",totalx2Format));

        ws.addCell(new Label(1, 2, "20元面值",totalx2Format));
        ws.addCell(new Label(3, 2, "25元面值",totalx2Format));
        ws.addCell(new Label(5, 2, "50元面值",totalx2Format));
        ws.addCell(new Label(7, 2, "30元面值",totalx2Format));
        ws.addCell(new Label(9, 2, "50元面值",totalx2Format));
        ws.addCell(new Label(11, 2, "100元面值",totalx2Format));
        ws.addCell(new Label(13, 2, "数量(张)",totalx2Format));
        ws.addCell(new Label(14, 2, "金额(元)",totalx2Format));


        ws.addCell(new Label(1, 3, "数量(张)",totalx2Format));
        ws.addCell(new Label(2, 3, "金额(元)",totalx2Format));
        ws.addCell(new Label(3, 3, "数量(张)",totalx2Format));
        ws.addCell(new Label(4, 3, "金额(元)",totalx2Format));
        ws.addCell(new Label(5, 3, "数量(张)",totalx2Format));
        ws.addCell(new Label(6, 3, "金额(元)",totalx2Format));
        ws.addCell(new Label(7, 3, "数量(张)",totalx2Format));
        ws.addCell(new Label(8, 3, "金额(元)",totalx2Format));
        ws.addCell(new Label(9, 3, "数量(张)",totalx2Format));
        ws.addCell(new Label(10, 3, "金额(元)",totalx2Format));
        ws.addCell(new Label(11, 3, "数量(张)",totalx2Format));
        ws.addCell(new Label(12, 3, "金额(元)",totalx2Format));

        ws.mergeCells(0, 1, 0, 3); //合并单元格，参数格式（开始列，开始行，结束列，结束行）
        ws.mergeCells(1, 1, 6, 1);
        ws.mergeCells(7, 1, 12, 1);
        ws.mergeCells(13, 1, 14, 1);

        ws.mergeCells(1, 2, 2, 2);
        ws.mergeCells(3, 2, 4, 2);
        ws.mergeCells(5, 2, 6, 2);
        ws.mergeCells(7, 2, 8, 2);
        ws.mergeCells(9, 2, 10, 2);
        ws.mergeCells(11, 2, 12, 2);

        ws.mergeCells(13, 2, 13, 3);
        ws.mergeCells(14, 2, 14, 3);

        ws.mergeCells(15, 1, 15, 3);

        ws.setColumnView(0, 15);
        ws.setColumnView(1, 10);

        ws.setColumnView(2, 10);
        ws.setColumnView(3, 10);
        ws.setColumnView(4, 10);
        ws.setColumnView(5, 10);
        ws.setColumnView(6, 10);
        ws.setColumnView(7, 10);
        ws.setColumnView(8, 10);
        ws.setColumnView(9, 10);
        ws.setColumnView(10, 10);
        ws.setColumnView(11, 10);
        ws.setColumnView(12, 10);
        ws.setColumnView(13, 10);
        ws.setColumnView(14, 10);
        ws.setColumnView(15, 15);


        beginDate = request.getParameter("beginDate");
        endDate = request.getParameter("endDate");
        orgname = request.getParameter("orgname");



        data = createQuery( beginDate, endDate, orgname);
        for (int i = 0; i < data.size(); i++) {
            DynaBean dynaBean = (DynaBean)data.get(i);
            String orgname = StringUtil.parseNull( dynaBean.get("orgname")) ;
            String c_amount20 = StringUtil.parseNull( dynaBean.get("c_amount20"));
            String c_paymoney20 = StringUtil.parseNumber( dynaBean.get("c_paymoney20"));
            String c_amount25 = StringUtil.parseNull( dynaBean.get("c_amount25"));
            String c_paymoney25 = StringUtil.parseNumber( dynaBean.get("c_paymoney25"));
            String c_amount50 = StringUtil.parseNull( dynaBean.get("c_amount50"));
            String c_paymoney50 = StringUtil.parseNumber( dynaBean.get("c_paymoney50"));
            String v_amount30 = StringUtil.parseNull( dynaBean.get("v_amount30"));
            String v_paymoney30 = StringUtil.parseNumber( dynaBean.get("v_paymoney30"));
            String v_amount50 = StringUtil.parseNull( dynaBean.get("v_amount50"));
            String v_paymoney50 = StringUtil.parseNumber( dynaBean.get("v_paymoney50"));
            String v_amount100 = StringUtil.parseNull( dynaBean.get("v_amount100"));
            String v_paymoney100 = StringUtil.parseNumber( dynaBean.get("v_paymoney100"));
            String c_amountonline = StringUtil.parseNull( dynaBean.get("c_amountonline"));
            String c_paymoneyonline = StringUtil.parseNumber( dynaBean.get("c_paymoneyonline"));

            String totle =
                StringUtil.parseNumber(Double.parseDouble(c_paymoney20)
                     + Double.parseDouble(c_paymoney25)
                     + Double.parseDouble(c_paymoney50)
                     + Double.parseDouble(v_paymoney30)
                     + Double.parseDouble(v_paymoney50)
                     + Double.parseDouble(v_paymoney100)
                     + Double.parseDouble(c_paymoneyonline));


            WritableCellFormat numberFormat = new WritableCellFormat();


            numberFormat.setAlignment(Alignment.RIGHT);

            ws.addCell(new Label(0, i + 4, orgname));
            ws.addCell(new Label(1, i + 4, c_amount20,numberFormat));
            ws.addCell(new Label(2, i + 4, c_paymoney20,numberFormat));
            ws.addCell(new Label(3, i + 4, c_amount25,numberFormat));
            ws.addCell(new Label(4, i + 4, c_paymoney25,numberFormat));
            ws.addCell(new Label(5, i + 4, c_amount50,numberFormat));
            ws.addCell(new Label(6, i + 4, c_paymoney50,numberFormat));
            ws.addCell(new Label(7, i + 4, v_amount30,numberFormat));
            ws.addCell(new Label(8, i + 4, v_paymoney30,numberFormat));
            ws.addCell(new Label(9, i + 4, v_amount50,numberFormat));
            ws.addCell(new Label(10, i + 4,v_paymoney50,numberFormat));
            ws.addCell(new Label(11, i + 4,v_amount100,numberFormat));
            ws.addCell(new Label(12, i + 4,v_paymoney100,numberFormat));
            ws.addCell(new Label(13, i + 4,c_amountonline,numberFormat));
            ws.addCell(new Label(14, i + 4,c_paymoneyonline,numberFormat));
            ws.addCell(new Label(15, i + 4,totle,numberFormat));
        }

        wwb.write();
        wwb.close();

    }

分享到：