在企業級應用中，數據導出 Excel 是高頻需求。早期專案使用現有框架導出時，發現部分模塊導出大量字段會拋出 500 錯誤，刪減字段後恢復正常。為徹底解決此問題並支援 IE6 等低版本瀏覽器，重新設計了導出方案，現分享給大家。
導出功能包含三層：前端 JS 交互、公共導出工具類、後端業務控制器。
負責收集查詢條件、調用後端服務、解析返回的 XML 結果、處理下載邏輯（含 IE6 兼容）。
function exportData() {
// 1. 獲取查詢參數
var rwmc = $("#txt_rwmc").val();
var rwlb = $("#com_rwlb").combobox("getValues").join(",");
// 2. 調用後端導出服務
var service = new Service("cx.RybjcxBndService.exprotExcel");
var str = "<RWMC>" + rwmc + "</RWMC><RWLB>" + rwlb + "</RWLB>";
var res = service.doService(str);
var oDoc = loadXml(res);
// 3. 錯誤處理
if (service.getCode() != "2000") {
showMessage("查詢失敗：" + service.getMessage());
return;
}
var nodata = oDoc.selectSingleNode("ROOT/NODATA").text;
if (nodata == "nodata") {
showMessage("無數據！");
return;
}
// 4. 解析導出信息
var titleName = oDoc.selectSingleNode("ROOT/TITLE_NAME").text;
var fileName = oDoc.selectSingleNode("ROOT/FILE_NAME").text;
var outPutInfo = oDoc.selectSingleNode("ROOT/OUTPUTINFO").text;
var downloadPath = oDoc.selectSingleNode("ROOT/DOWNLOAD_PATH").text;
if (outPutInfo != "") {
showMessage(outPutInfo);
return;
}
// 5. 確認下載
if (confirm("導出成功!確認下載文件嗎？\n文件名稱為：" + 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);
// IE6 兼容處理
var isIE = !!window.ActiveXObject;
var isIE6 = isIE && !window.XMLHttpRequest;
var downloadUrl = host + "/adp/work/gzkp/common/js/download_new.jsp?file=" + fileName
+ "&showfile=" + showfile + "&download_path=" + downloadPath;
if (isIE6) {
window.location.href = downloadUrl;
} else {
window.open(downloadUrl, "_blank",
"toolbar=no,location=no,status=yes,resizable=no,scrollbars=yes,width=100,height=100");
}
}
}
基於 Apache POI (HSSF) 實現，支援動態 SQL 查詢、自動列寬、樣式定義、文件輸出。
package ctais.business.gzkp.common;
import java.io.File;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.poi.hssf.usermodel.*;
import ctais.business.dashboard.service.ExportExcel;
import ctais.config.Config;
import ctais.services.data.DataWindow;
import ctais.services.xml.XMLDataObject;
public class CreateExcel {
private final static String CONFIG_FILE_PATH = Config.CTAIS_HOME;
/**
* 生成 Excel 文件
* @param sql 查詢 SQL
* @param czryDm 操作人員代碼
* @param titles 列標題數組
* @param exlTitle 表頭標題
* @return XML 格式的結果信息
*/
public String newToExcel(String sql, String czryDm, String[] titles, String exlTitle) throws Exception {
StringBuffer sffer = new StringBuffer();
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
ExportExcel exportExcel = new ExportExcel(wb, sheet);
try {
DataWindow dw = DataWindow.dynamicCreate(sql);
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 headStyle = wb.createCellStyle();
headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
headStyle.setWrapText(true);
HSSFFont headFont = wb.createFont();
headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headFont.setFontName("宋體");
headFont.setFontHeight((short) 200);
headStyle.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(headStyle);
cell.setCellValue(titles[i]);
}
// 數據行樣式
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);
Object value = dw.getItemAny(i, j - 1);
cell.setCellValue(value == null ? "" : value.toString());
}
}
// 輸出文件
String path = "/export/";
File dir = new File(path);
if (!dir.exists()) dir.mkdirs();
String fileName = sfm + ".xls";
String fullPath = path + fileName;
String outPutInfo = exportExcel.outputExcel(fullPath);
sffer.append("<TITLE_NAME>").append(sfm).append("</TITLE_NAME>");
sffer.append("<DOWNLOAD_PATH>").append(path).append("</DOWNLOAD_PATH>");
sffer.append("<FILE_NAME>").append(fileName).append("</FILE_NAME>");
sffer.append("<OUTPUTINFO>").append(outPutInfo).append("</OUTPUTINFO>");
return sffer.toString();
} catch (Exception e) {
e.printStackTrace();
throw new Exception(e.getMessage());
}
}
}
接收前端參數，構建動態 SQL，調用工具類生成 Excel。
/**
* 導出功能入口
* @param args 前端傳遞的 XML 參數對象
* @param czryDm 操作人員代碼
* @return 生成的 Excel 信息 XML
*/
public String exportExcel(XMLDataObject args, String czryDm) throws Exception {
String rwmc = StringEx.sNull(args.getItemValue("RWMC"));
String rwlb = StringEx.sNull(args.getItemValue("RWLB"));
StringBuilder sqlWhere = new StringBuilder();
if (rwmc != null && !"".equals(rwmc)) {
sqlWhere.append(" AND A.RWMC LIKE '%").append(rwmc).append("%' ");
}
if (rwlb != null && !"".equals(rwlb)) {
sqlWhere.append(" AND A.RWLB_DM = '").append(rwlb).append("' ");
}
StringBuilder sql = new StringBuilder();
sql.append("SELECT RWXH, RWMC FROM RWXX")
.append(sqlWhere)
.append(" ORDER BY RWXH");
String[] titles = {"任務序號", "任務名稱"};
CreateExcel excel = new CreateExcel();
return excel.newToExcel(sql.toString(), czryDm, titles, "任務信息導出表");
}
window.location.href 直接跳轉下載，避免 window.open 被攔截或失效。
