你的位置:首页 > Java教程

[Java教程]Java程序实现导出Excel,支持IE低版本


  来博客园两年多了,最近才开通了微博,因为懒所以也一直没有写东西,今天想整理一下自己前段时间遇到的一个导出的问题。 

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

 

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

  js代码

 1 function exportData() 2 { 3     //前台接收的参数 4   rwmc = $("#txt_rwmc").val(); 5   rwlb = $("#com_rwlb").combobox("getValues").join(","); 6    7   //调用后台导出功能 8   var service = new Service("cx.RybjcxBndService.exprotExcel"); 9   var str="<RWMC>" + rwmc + "</RWMC><RWLB>" + rwlb + "</RWLB>";10   var res = service.doService(str);11   var oDoc = load12   if (service.getCode() != "2000") {13     showMessage("查询失败:"+service.getMessage());14     return;15   }16   var nodata = oDoc.selectSingleNode("ROOT/NODATA").text;17   if (nodata == "nodata") {18     showMessage("无数据!");19     return;20   }21   // 获取导出信息22   var titleName = oDoc.selectSingleNode("ROOT/TITLE_NAME").text;23   var fileName = oDoc.selectSingleNode("ROOT/FILE_NAME").text;24   var outPutInfo = oDoc.selectSingleNode("ROOT/OUTPUTINFO").text;25   var download_path = oDoc.selectSingleNode("ROOT/DOWNLOAD_PATH").text;26   if (outPutInfo != "") {27     showMessage(outPutInfo);28     return;29   }30   if (confirm("导出成功!确认下载文件吗?\n文件名称为:"+fileName)) {31     var file = fileName;32     var showfile = titleName + ".xls";33     showfile = decodeURIComponent(showfile);34     var idx = document.URL.indexOf("/adp");35     if (idx == -1) {36       alert("无法识别主机地址:" + document.URL);37       return;38     }39     var host = document.URL.substring(0, idx);40     var width = screen.width;41     var height = screen.height;42     debugger;43     // 打开下载页面44     var param = "toolbar=no,location=no,status=yes,resizable=no,scrollbars=yes,top=" + height + ",left=" + width + ",width=100,height=100";45     // ----------------------------------------------------------46     // 此代码块为解决ie6下导出excel失败问题,原因是ie6对window.open(url)支持度不好,47     // 当浏览器为ie6时改用window.location.href48     var isIE=!!window.ActiveXObject;49     var isIE6=isIE&&!window.50     if (isIE6) {51       window.location.href=host + "/adp/work/gzkp/common/js/download_new.jsp?file=" + file + "&showfile=" + showfile + "&download_path=" + download_path;52     } else {53       window.open(host + "/adp/work/gzkp/common/js/download_new.jsp?file=" + file + "&showfile=" + showfile + "&download_path=" + download_path, "_blank", param);54     }55   }56 }

 

公共类

 1 package ctais.business.gzkp.common; 2 import java.io.File; 3 import java.io.FileInputStream; 4 import java.text.SimpleDateFormat; 5 import java.util.Date; 6  7 import org.apache.poi2.hssf.usermodel.HSSFCell; 8 import org.apache.poi2.hssf.usermodel.HSSFCellStyle; 9 import org.apache.poi2.hssf.usermodel.HSSFFont; 10 import org.apache.poi2.hssf.usermodel.HSSFRow; 11 import org.apache.poi2.hssf.usermodel.HSSFSheet; 12 import org.apache.poi2.hssf.usermodel.HSSFWorkbook; 13  14 import ctais.business.dashboard.service.ExportExcel; 15  16 import ctais.config.Config; 17 import ctais.services.data.DataWindow; 18 import ctais.services. 19 import ctais.services. 20 import ctais.util.StringEx; 21 import jxl.Workbook; 22 import jxl.format.Alignment; 23 import jxl.write.Label; 24 import jxl.write.WritableCellFormat; 25 import jxl.write.WritableFont; 26 import jxl.write.WritableSheet; 27 import jxl.write.WritableWorkbook; 28  29  30 /** 31  * <p>Title: 生成EXCEL文件</p> 32  * <p>Description: 转换String字符串为EXCEL文档</p> 33  * <p>Copyright: Copyright (c) 2004</p> 34  * <p>Company: DC</p> 35  * @author FENGZG 36  * @version 1.0 37  * 时间:2015-12-28 38 */ 39  40 public class CreateExcel { 41     private final static String CONFIG_FILE_PATH = Config.CTAIS_HOME; 42     WritableWorkbook wwb = null; 43     null; 44  45     public CreateExcel(){ 46  47     } 48      49     /** 50      * 生成EXCEL 51      * @param sql 查询SQL 52      * @param czryDm 操作人员代码 53      * @param titles 导出列标题 54      * @param exlTitle excel表头 55      * @return 56      * @throws Exception 57     */ 58     public String newToExcel(String sql,String czryDm,String[] titles,String exlTitle) throws Exception 59     { 60       try { 61         HSSFWorkbook wb = new HSSFWorkbook(); 62         HSSFSheet sheet = wb.createSheet(); 63         ExportExcel exportExcel = new ExportExcel(wb, sheet); 64         StringBuffer sffer = new StringBuffer(); 65         //int colNum = 30; 66          67         DataWindow dw = DataWindow.dynamicCreate(sql.toString()); 68         dw.setConnectionName(Icomm.GZKPJNDI); 69         long dwRet = dw.retrieve(); 70         if (dwRet <= 0) { 71           sffer.append("<NODATA>nodata</NODATA>"); 72           return sffer.toString(); 73         } else { 74           sffer.append("<NODATA></NODATA>"); 75         } 76          77         int colNum = dw.getColumnCount(); 78          79         // 给工作表列定义列宽(实际应用自己更改列数) 80         for (short i = 0; i <= colNum; i++) { 81           sheet.setColumnWidth(i, (short) 4000); 82         } 83         // 创建单元格样式 84         HSSFCellStyle cellHeadStyle = wb.createCellStyle(); 85         // 指定单元格居中对齐 86         cellHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); 87         // 指定单元格垂直居中对齐 88         cellHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 89         // 指定当单元格内容显示不下时自动换行 90         cellHeadStyle.setWrapText(true); 91         // 设置单元格字体 92         HSSFFont headFont = wb.createFont(); 93         headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 94         headFont.setFontName("宋体"); 95         headFont.setFontHeight((short) 200); 96         cellHeadStyle.setFont(headFont); 97    98         // 创建报表头部 99         Date dt=new Date();100         SimpleDateFormat sdt=new SimpleDateFormat("yyyyMMddhhmmssS");101         String sfm = czryDm + "_" + sdt.format(dt);102         103         // 设置列头104         exportExcel.createNormalHead(exlTitle, colNum-1);105         HSSFRow row1 = sheet.createRow(1);106         107         for(int i = 0; i < titles.length; i ++) {108           HSSFCell cell = row1.createCell((short)i);109           cell.setEncoding(HSSFCell.ENCODING_UTF_16);110           cell.setCellStyle(cellHeadStyle);111           cell.setCellValue(titles[i]);112         } 113         Object value = "";114         115         //设置表格样式116         HSSFCellStyle cellStyle = wb.createCellStyle();117         // 指定单元格居中对齐118         cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);119         // 指定单元格垂直居中对齐120         cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);121         // 指定当单元格内容显示不下时自动换行122         cellStyle.setWrapText(true);123         // 设置单元格字体124         HSSFFont font = wb.createFont();125         font.setBoldweight(HSSFFont.SS_NONE);126         font.setFontName("宋体");127         font.setFontHeight((short) 200);128         cellStyle.setFont(font);129         for(int i = 0 ; i < dw.getRowCount(); i++) {130           HSSFRow row = sheet.createRow(i + 2);131           for(int j = 1; j <= dw.getColumnCount(); j++) {132             HSSFCell cell = row.createCell((short)(j-1));133             cell.setEncoding(HSSFCell.ENCODING_UTF_16);134             cell.setCellStyle(cellStyle);135             value = dw.getItemAny(i, j-1);        136             if(value == null) {137               cell.setCellValue("");138             } else {139               cell.setCellValue(value.toString());140             }141           }142         }143         //设置导出路径,此处需要注意如果是Linux系统需要手动建路径,(此处的原因有人比较清楚的话还请指教)引用新建的文件路径144         String path = "/export/";145         File file = new File(path);146         if(!file.exists()) {147           file.mkdirs();148         }149   150         String fileName = sfm+".xls";151   152         //String pth = path.trim() + File.separator + fileName;153         String pth = path.trim() + fileName;154         pth = pth.trim();155   156         String outPutInfo = exportExcel.outputExcel(pth);157   158         sffer.append("<TITLE_NAME>" + sfm + "</TITLE_NAME>");159         sffer.append("<DOWNLOAD_PATH>" + path + "</DOWNLOAD_PATH>");160         sffer.append("<FILE_NAME>" + fileName + "</FILE_NAME>");161         sffer.append("<OUTPUTINFO>" + outPutInfo + "</OUTPUTINFO>");162   163         return sffer.toString();164       } 165       catch (Exception e) {166         e.printStackTrace();167         throw new Exception(e.getMessage());168       }169     }170 171 }

 

 

后台代码

 1 /** 2    * 导出功能 3    * @param xdo 前台传参 4    * @param czryDm 操作人员代码 5    * @return 生成的XLS信息 6    * @throws Exception 异常说明 7   */ 8   public String exportExcel(throws Exception  9   {10     //接收前台传递的查询参数11     String rwmc = StringEx.sNull(args.getItemValue("RWMC"));   12     String rwlb = StringEx.sNull(args.getItemValue("RWLB"));13    14     if(null != rwmc && !"".equals(rwmc))15     {16       sqlWhere.append(" AND A.RWMC LIKE '%"+rwmc+"%' ");17     }18     if(null != rwlb && !"".equals(rwlb))19     {20       sqlWhere.append(" AND A.RWLB_DM = '"+rwlb+"' ");21     }22    23     StringBuilder sql = new StringBuilder();24     //拼接查询SQL25     sql.append("SELECT RWXH,RWMC FROM RWXX")26       .append(sqlWhere).append(" ORDER BY RWXH ) ").append(sqlisWhere);27     //导出的列标题28     String[] titles = {"任务序号","任务名称"};29 30     //实例化公共类31     CreateExcel excel = new CreateExcel();32     return excel.newToExcel(sql.toString(), czryDm,titles,"Exlcel表头");33   }