Java程序实现导出Excel的方法(支持IE低版本)

今天想整理一下自己前段时间遇到的一个导出的问题。

因为项目的需求，要做一部分导出功能。开始的时候用的公司的导出，但是很奇怪有部分模块导出的时候就是会报500错误，发现在删减一些字段后就恢复了正常，当时因为项目紧张，也就临时删减了一些，但也不是长久之计，之后自己在原本的基础上重新修改整理了一下，目前运行还算稳定，就此和大家分享一下。

导出需要三个部分，js，公共方法，后台方法。

js代码

function exportdata()
{
    //前台接收的参数
  rwmc = $("#txt_rwmc").val();
   rwlb = $("#com_rwlb").combobox("getvalues").join(",");
   
  //调用后台导出功能
  var service = new service("cx.rybjcxbndservice.exprotexcel");
  var str="<rwmc>" + rwmc + "</rwmc><rwlb>" + rwlb + "</rwlb>";
  var res = service.doservice(str);
  var odoc = loadxml(res);
  if (service.getcode() != "2000") {
    showmessage("查询失败："+service.getmessage());
    return;
  }
  var nodata = odoc.selectsinglenode("root/nodata").text;
  if (nodata == "nodata") {
    showmessage("无数据!");
    return;
  }
  // 获取导出信息
  var titlename = odoc.selectsinglenode("root/title_name").text;
  var filename = odoc.selectsinglenode("root/file_name").text;
  var outputinfo = odoc.selectsinglenode("root/outputinfo").text;
  var download_path = odoc.selectsinglenode("root/download_path").text;
  if (outputinfo != "") {
    showmessage(outputinfo);
    return;
  }
  if (confirm("导出成功!确认下载文件吗？\n文件名称为："+filename)) {
    var file = filename;
    var showfile = titlename + ".xls";
    showfile = decodeuricomponent(showfile);
    var idx = document.url.indexof("/adp");
    if (idx == -1) {
      alert("无法识别主机地址：" + document.url);
      return;
    }
    var host = document.url.substring(0, idx);
    var width = screen.width;
    var height = screen.height;
    debugger;
    // 打开下载页面
    var param = "toolbar=no,location=no,status=yes,resizable=no,scrollbars=yes,top=" + height + ",left=" + width + ",width=100,height=100";
    // ----------------------------------------------------------
    // 此代码块为解决ie6下导出excel失败问题，原因是ie6对window.open(url)支持度不好,
    // 当浏览器为ie6时改用window.location.href
    var isie=!!window.activexobject;
    var isie6=isie&&!window.xmlhttprequest;
    if (isie6) {
      window.location.href=host + "/adp/work/gzkp/common/js/download_new.jsp?file=" + file + "&showfile=" + showfile + "&download_path=" + download_path;
    } else {
      window.open(host + "/adp/work/gzkp/common/js/download_new.jsp?file=" + file + "&showfile=" + showfile + "&download_path=" + download_path, "_blank", param);
    }
  }
}

公共类

package ctais.business.gzkp.common;
import java.io.file;
import java.io.fileinputstream;
import java.text.simpledateformat;
import java.util.date;

import org.apache.poi2.hssf.usermodel.hssfcell;
import org.apache.poi2.hssf.usermodel.hssfcellstyle;
import org.apache.poi2.hssf.usermodel.hssffont;
import org.apache.poi2.hssf.usermodel.hssfrow;
import org.apache.poi2.hssf.usermodel.hssfsheet;
import org.apache.poi2.hssf.usermodel.hssfworkbook;

import ctais.business.dashboard.service.exportexcel;

import ctais.config.config;
import ctais.services.data.datawindow;
import ctais.services.xml.xmldataobject;
import ctais.services.xml.xmlparser;
import ctais.util.stringex;
import jxl.workbook;
import jxl.format.alignment;
import jxl.write.label;
import jxl.write.writablecellformat;
import jxl.write.writablefont;
import jxl.write.writablesheet;
import jxl.write.writableworkbook;


/**
 * <p>title: 生成excel文件</p>
 * <p>description: 转换string字符串为excel文档</p>
 * <p>copyright: copyright (c) 2004</p>
 * <p>company: dc</p>
 * @author fengzg
 * @version 1.0
 * 时间：2015-12-28
 */

public class createexcel {
    private final static string config_file_path = config.ctais_home;
    writableworkbook wwb = null;
    xmldataobject xdo = null;

    public createexcel(){

    }
    
    /**
     * 生成excel
     * @param sql 查询sql
     * @param czrydm 操作人员代码
     * @param titles 导出列标题
     * @param exltitle excel表头
     * @return
     * @throws exception
     */
    public string newtoexcel(string sql,string czrydm,string[] titles,string exltitle) throws exception
    {
      try {
        hssfworkbook wb = new hssfworkbook();
        hssfsheet sheet = wb.createsheet();
        exportexcel exportexcel = new exportexcel(wb, sheet);
        stringbuffer sffer = new stringbuffer();
        //int colnum = 30;
        
        datawindow dw = datawindow.dynamiccreate(sql.tostring());
        dw.setconnectionname(icomm.gzkpjndi);
        long dwret = dw.retrieve();
        if (dwret <= 0) {
          sffer.append("<nodata>nodata</nodata>");
          return sffer.tostring();
        } else {
          sffer.append("<nodata></nodata>");
        }
        
        int colnum = dw.getcolumncount();
        
        // 给工作表列定义列宽(实际应用自己更改列数)
        for (short i = 0; i <= colnum; i++) {
          sheet.setcolumnwidth(i, (short) 4000);
        }
        // 创建单元格样式
        hssfcellstyle cellheadstyle = wb.createcellstyle();
        // 指定单元格居中对齐
        cellheadstyle.setalignment(hssfcellstyle.align_center);
        // 指定单元格垂直居中对齐
        cellheadstyle.setverticalalignment(hssfcellstyle.vertical_center);
        // 指定当单元格内容显示不下时自动换行
        cellheadstyle.setwraptext(true);
        // 设置单元格字体
        hssffont headfont = wb.createfont();
        headfont.setboldweight(hssffont.boldweight_bold);
        headfont.setfontname("宋体");
        headfont.setfontheight((short) 200);
        cellheadstyle.setfont(headfont);
  
        // 创建报表头部
        date dt=new date();
        simpledateformat sdt=new simpledateformat("yyyymmddhhmmsss");
        string sfm = czrydm + "_" + sdt.format(dt);
        
        // 设置列头
        exportexcel.createnormalhead(exltitle, colnum-1);
        hssfrow row1 = sheet.createrow(1);
        
        for(int i = 0; i < titles.length; i ++) {
          hssfcell cell = row1.createcell((short)i);
          cell.setencoding(hssfcell.encoding_utf_16);
          cell.setcellstyle(cellheadstyle);
          cell.setcellvalue(titles[i]);
        } 
        object value = "";
        
        //设置表格样式
        hssfcellstyle cellstyle = wb.createcellstyle();
        // 指定单元格居中对齐
        cellstyle.setalignment(hssfcellstyle.align_center);
        // 指定单元格垂直居中对齐
        cellstyle.setverticalalignment(hssfcellstyle.vertical_center);
        // 指定当单元格内容显示不下时自动换行
        cellstyle.setwraptext(true);
        // 设置单元格字体
        hssffont font = wb.createfont();
        font.setboldweight(hssffont.ss_none);
        font.setfontname("宋体");
        font.setfontheight((short) 200);
        cellstyle.setfont(font);
        for(int i = 0 ; i < dw.getrowcount(); i++) {
          hssfrow row = sheet.createrow(i + 2);
          for(int j = 1; j <= dw.getcolumncount(); j++) {
            hssfcell cell = row.createcell((short)(j-1));
            cell.setencoding(hssfcell.encoding_utf_16);
            cell.setcellstyle(cellstyle);
            value = dw.getitemany(i, j-1);        
            if(value == null) {
              cell.setcellvalue("");
            } else {
              cell.setcellvalue(value.tostring());
            }
          }
        }
        //设置导出路径，此处需要注意如果是linux系统需要手动建路径，（此处的原因有人比较清楚的话还请指教）引用新建的文件路径
        string path = "/export/";
        file file = new file(path);
        if(!file.exists()) {
          file.mkdirs();
        }
  
        string filename = sfm+".xls";
  
        //string pth = path.trim() + file.separator + filename;
        string pth = path.trim() + filename;
        pth = pth.trim();
  
        string outputinfo = exportexcel.outputexcel(pth);
  
        sffer.append("<title_name>" + sfm + "</title_name>");
        sffer.append("<download_path>" + path + "</download_path>");
        sffer.append("<file_name>" + filename + "</file_name>");
        sffer.append("<outputinfo>" + outputinfo + "</outputinfo>");
  
        return sffer.tostring();
      } 
      catch (exception e) {
        e.printstacktrace();
        throw new exception(e.getmessage());
      }
    }

}

后台代码

/**
   * 导出功能
   * @param xdo 前台传参
   * @param czrydm 操作人员代码
   * @return 生成的xls信息
   * @throws exception 异常说明
   */
  public string exportexcel(xmldataobject args,string czrydm) throws exception 
  {
    //接收前台传递的查询参数
    string rwmc = stringex.snull(args.getitemvalue("rwmc"));   
    string rwlb = stringex.snull(args.getitemvalue("rwlb"));
    
    if(null != rwmc && !"".equals(rwmc))
    {
      sqlwhere.append(" and a.rwmc like '%"+rwmc+"%' ");
    }
    if(null != rwlb && !"".equals(rwlb))
    {
      sqlwhere.append(" and a.rwlb_dm = '"+rwlb+"' ");
    }
    
    stringbuilder sql = new stringbuilder();
    //拼接查询sql
    sql.append("select rwxh,rwmc from rwxx")
      .append(sqlwhere).append(" order by rwxh ) ").append(sqliswhere);
    //导出的列标题
    string[] titles = {"任务序号","任务名称"};

    //实例化公共类
    createexcel excel = new createexcel();
    return excel.newtoexcel(sql.tostring(), czrydm,titles,"exlcel表头");
  }

以上这篇java程序实现导出excel的方法(支持ie低版本)就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持。