JXL导出Excel文件兼容性问题-白红宇

JXL导出Excel文件兼容性问题

阅读量：6114 次

发布时间：2019-06-21

本文共 8747 字，大约阅读时间需要 29 分钟。

1、Java封装导出类：

package com.boonya.excel;import javax.servlet.http. HttpServletRequest;import javax.servlet.http. HttpServletResponse;.............................. ................import org.apache.commons.codec. binary.Base64;public class CExportTableManager {       private String getDates(Date time) {        Calendar cal = Calendar.getInstance();        cal.setTime(time);        long tm;        tm = cal.getTimeInMillis();        Date time1 = new Date(tm);        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");        String tms = sdf.format(time1);        return tms;    }      public void exportExcel(HttpServletRequest request,            HttpServletResponse response, String title, String[] header,            String[] name, List
     
      > list, String time,            String user) {//list是数据对象拆分的map对象集合        try {            // 得到输出流            OutputStream os = response.getOutputStream();            // 清空输出            response.reset();            // 设置文件标题            setTitle(request, response, title, time);            // 定义输出类型            response.setContentType(" application/msexcel");            // 建立excel文件            WritableWorkbook wbook = Workbook.createWorkbook(os);            // sheet名称            WritableSheet wsheet = wbook.createSheet(title, 0);            // 设置表格样式            setTableStyle(wsheet, title, header, user);            // 设置表头样式            WritableCellFormat wcfFCHeader = new WritableCellFormat();            wcfFCHeader.setBackground( Colour.YELLOW);            // 生成主体内容            // 设置表头            for (int i = 0; i < header.length; i++) {                wsheet.addCell(new Label(i + 2, 3, header[i], wcfFCHeader));            }            // 设置表格内容            // 行            for (int i = 0; i < list.size(); i++) {                // 列                for (int j = 0; j < header.length; j++) {                                        Object obj= list.get(i).get(name[j]);                    String s=null;                    //map中保存对象为空时不能调用toString方法                    if(obj!=null){                        s=obj.toString();                    }                    wsheet.addCell(new Label(j + 2, i + 4, s));                }            }            // 写入文件            wbook.write();            // 主体内容生成结束            wbook.close();            // 关闭            os.close();        } catch (Exception ex) {            ex.printStackTrace();        }    }    // 设置表格样式    private void setTableStyle(WritableSheet wsheet, String title,            String[] header, String user) throws WriteException {        // 设置excel标题        WritableFont wfont = new WritableFont(WritableFont. ARIAL, 14,                WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,                Colour.BLACK);        WritableCellFormat wcfFC = new WritableCellFormat(wfont);        wcfFC.setBackground(Colour. AQUA);        wsheet.addCell(new Label(3, 0, title, wcfFC)); // 合并单元格        int size = title.length() % 6 == 0 ? title.length() / 6 : (title                .length() / 6 + 1);        wsheet.mergeCells(3, 0, 3 + size, 0);        // 添加操作人及设置表格导出时间        wsheet.addCell(new Label(header.length + 2, 1, "操作人：" + user));        wsheet.addCell(new Label(header.length + 2, 2, "导出时间:"                + getDates(new Date())));    }    // 设置文件标题    private void setTitle(HttpServletRequest request,            HttpServletResponse response, String title, String time)            throws UnsupportedEncodingException {        String fileName = "";        if (time == "" || time == null)            fileName = title;        else            fileName = title + "(" + time + ")";        String agent = request.getHeader("USER-AGENT" ).toLowerCase();        // 定义输出类型        response.setContentType(" application/vnd.ms-excel");                // 文件名有中文及空格的处理        if (agent != null && (agent.indexOf("firefox") >=0)) {            // firefox            String enableFileName = "=?UTF-8?B?"                    + (new String(Base64.encodeBase64( fileName                            .getBytes("UTF-8")))) + "?=";            // response.setHeader("Content- Disposition", "attachment; filename="            // + enableFileName);            response.setHeader("Content- disposition", "attachment; filename="                    + enableFileName + ".xls");// 设定输出文件        } else  {            // IE            String enableFileName = new String(fileName.getBytes("GBK" ),                    "ISO-8859-1");            response.setHeader("Content- Disposition", "attachment; filename="                    + enableFileName + ".xls");        }    }    // 统计    public void exportTotalExcel( HttpServletRequest request,            HttpServletResponse response, String title, String[] header,            String[] name, List
      
       
        > list, String cars,            String time, String user) {        try {            String[] carArr = cars.split(",");            OutputStream os = response.getOutputStream();            response.reset();            setTitle(request, response, title, time);            response.setContentType(" application/msexcel");            WritableWorkbook wbook = Workbook.createWorkbook(os);            WritableSheet wsheet = wbook.createSheet(title, 0);            // 设置表格样式            setTableStyle(wsheet, title, header, user);            // 设置表头样式            WritableCellFormat wcfFCHeader = new WritableCellFormat();            wcfFCHeader.setBackground( Colour.YELLOW);            // 设置表头            for (int i = 0; i < header.length; i++) {                wsheet.addCell(new Label(i + 2, 3, header[i], wcfFCHeader));            }            // 总计样式设置            WritableCellFormat wcfFCCount = new WritableCellFormat();            wcfFCCount.setBackground( Colour.RED);            // 表格内容            int m = 0;            for (int t = 0; t < carArr.length; t++) {                for (int i = 0; i < list.size(); i++) {                    if (carArr[t].equals(list.get(i). get(name[1]))) {                        for (int j = 0; j < header.length; j++) {                            // label(列空两列、行)                            if (list.get(i).get(name[0]) == "总计")                                 wsheet.addCell(new Label(j + 2, 4 + m, list                                         .get(i).get(name[j]).toString( ),                                         wcfFCCount));                            else                                 wsheet.addCell(new Label(j + 2, 4 + m, list                                         .get(i).get(name[j]).toString( )));                        }                        m++;                    }                }            }            wbook.write();            wbook.close();            os.close();        } catch (Exception ex) {            ex.printStackTrace();        }    }    // 每辆车生成一张sheet    public void exportSheetExcel( HttpServletRequest request,            HttpServletResponse response, String title, String[] header,            String[] name, String time, String cars,            List
        
         
          > list, String user) { try { String[] carArrs = cars.split(","); String[] carArr = new String[carArrs.length]; for (int i = 0; i < carArrs.length; i++) { carArr[i] = carArrs[i].split("-")[0]; } OutputStream os = response.getOutputStream(); response.reset(); setTitle(request, response, title, time); response.setContentType(" application/msexcel "); WritableWorkbook wbook = Workbook.createWorkbook(os); // 每个车辆终端编号一张sheet for (int t = 0; t < carArr.length; t++) { String tmptitle = carArr[t] + "-" + title; WritableSheet wsheet = wbook.createSheet(tmptitle, t); setTableStyle(wsheet, tmptitle, header, user); WritableCellFormat wcfFCHeader = new WritableCellFormat(); wcfFCHeader.setBackground( Colour.YELLOW); // 设置表头 for (int i = 0; i < header.length; i++) { wsheet.addCell(new Label(i + 2, 3, header[i], wcfFCHeader)); } // 总计样式设置 WritableCellFormat wcfFCCount = new WritableCellFormat(); wcfFCCount.setBackground( Colour.RED); // 添加内容 int m = 0; for (int i = 0; i < list.size(); i++) { if (carArr[t].equals(list.get(i). get(name[1]).toString())) { for (int j = 0; j < header.length; j++) { if (list.get(i).get(name[0]). toString() == "总计") wsheet.addCell(new Label(j + 2, 4 + m, list .get(i).get(name[j]).toString( ), wcfFCCount)); else wsheet.addCell(new Label(j + 2, 4 + m, list .get(i).get(name[j]).toString( ))); } m++; } } } wbook.write(); wbook.close(); os.close(); } catch (Exception ex) { ex.printStackTrace(); } }}

2、

在servlet中设置header和name属性对应数组等属

性等

ExportTableManager ex=new ExportTableManager();            ex.exportExcel(request, response, title, header,name, list,time,username);

注意：setTitle方法的agent 判断部分是处理浏览器问题不兼容的{

在chrome和opera下不能正常导出}。

转载于:https://my.oschina.net/boonya/blog/104549

你可能感兴趣的文章

Apache 强制全站https请求(配置ssl证书)

查看>>

laravel源码分析之--Application 实例化

前段开发环境部署（1）--nvm（node版本管理器）

查看>>

Javascript数组的“字符串”索引 & for…in 和 for…of的区别

查看>>

mysql函数集

查看>>

android效果集 - 收藏集 - 掘金

查看>>

cartcontrol.vue购物车操作按钮

查看>>

java的线程、锁相关

查看>>

国产PHP框架——ThinkPHP各功能开发系列一环境搭建

查看>>

微信开源mars源码分析2—上层samples分析（续）

查看>>

六大设计原则 -- 5.迪米特法则

查看>>

LC 267 Palindrome Permutation II

查看>>

PHP －－－ Session

查看>>

在多主机Docker网络中运行XtraDB Cluster

查看>>