JXL导出Excel文件兼容性问题-CSDN博客

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<Map<String, Object>> 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<Map<String, Object>> 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<Map<String, Object>> 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