本文記錄一個真實需求：在 SSH（Struts2+Spring+Hibernate）框架下，使用 Apache POI 導出具有固定複雜格式的 Excel 文件——多 Sheet（按變電站分）、合併儲存格、多層標題、序號、自定義樣式、底部備註區——並實現前端參數傳遞、後端流式下載。包含踩坑總結與完整代碼。
業務表中「執行人/執行日期」與「核對人/核對日期」分散在不同行，需橫向合併。使用 SQL Server 游標（DECLARE CUR CURSOR FORWARD_ONLY）順序讀取兩行，賦值給變量 @NAME2~@NAME6，再在主查詢中拼接：
DECLARE @NAME2 NVARCHAR(300), @NAME3 NVARCHAR(300), @NAME5 NVARCHAR(300), @NAME6 NVARCHAR(300);
DECLARE CUR CURSOR FORWARD_ONLY FOR
SELECT TASKACTOR, ENDTIME FROM US_SYS.TV_WF_TASKLOG
WHERE PROCESSINSTANCE = (SELECT PROCESSINSTANCE_ FROM US_SYS.JBPM_VARIABLEINSTANCE WHERE STRINGVALUE_ = ? AND NAME_ = 'orderNo')
AND (TASKNAME LIKE '執行' OR TASKNAME LIKE '核對') AND ENDTIME IS NOT NULL;
OPEN CUR;
FETCH NEXT FROM CUR INTO @NAME2, @NAME3; -- 第一行：執行
FETCH NEXT FROM CUR INTO @NAME5, @NAME6; -- 第二行：核對
CLOSE CUR; DEALLOCATE CUR;
SELECT ...,
CAST(@NAME2 AS VARCHAR) + CAST(@NAME3 AS VARCHAR) AS ENDTIME1, -- 執行人+日期
CAST(@NAME5 AS VARCHAR) + CAST(@NAME6 AS VARCHAR) AS ENDTIME2 -- 核對人+日期
FROM ...
String guidInSql = "'" + guid.replaceAll(",", "','") + "'"; // 'guid1','guid2',...
List<String> subNames = getSubNames(guidInSql); // SELECT DISTINCT SUBSTATION ...
for (String sub : subNames) {
String[] guids = getGuidsBySubstation(sub, guidInSql);
DataTable dt = queryDetail(guids); // 含游標邏輯
buildSheet(workbook, sub, dt);
}
合併儲存格四步法：
// 1. 定義區域 (firstRow, lastRow, firstCol, lastCol)
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 12);
// 2. 註冊到 Sheet
sheet.addMergedRegion(region);
// 3. 獲取/創建左上角儲存格
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
// 4. 賦值 & 設置樣式
cell.setCellValue("台州電網繼電保護及安全自動裝置整定單核對表");
cell.setCellStyle(titleStyle);
樣式復用原則：創建 CellStyle、Font 對象一次，全局共享，避免超過 64k 樣式限制導致文件損壞。
AJAX 無法直接處理二進制流下載，採用動態 Form 提交：
// 前端 JS
function openPostWindow(url, params) {
var form = $('');
$.each(params, function(name, value) {
form.append($(''));
});
$('body').append(form);
form.submit();
form.remove();
}
// 調用
var params = { substationaa: '白雲變', voltageaa: '220kV', guid: 'g1,g2,g3' };
openPostWindow('checkManagement/cdzdExcel/importsExcel.do', params);
後端 Spring MVC 以 @RequestParam 接收：
@RequestMapping("importsExcel.do")
@ResponseBody
public String cdzdExcel(@RequestParam(value="substationaa", required=false) String substationaa,
@RequestParam(value="voltageaa", required=false) String voltageaa,
@RequestParam(value="guid", required=false) String guid) { ... }
HSSFWorkbook workbook = service.getExcel(guids, subNames, userModel);
HttpServletResponse response = (HttpServletResponse) ThreadLocalUtils.getObjectFromThreadLocal("response");
response.reset();
response.setCharacterEncoding("UTF-8");
response.setContentType("application/x-msdownload");
String fileName = year + "年" + voltage + substation + "變電站定值單三核對表.xls";
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
// 可選：保存服務器副本
FileOutputStream fileOut = new FileOutputStream("c:/" + fileName);
workbook.write(fileOut);
fileOut.close();
// 瀏覽器下載流
OutputStream out = response.getOutputStream();
workbook.write(out);
out.flush();
out.close();
@Controller
@RequestMapping("/cdzdExcel")
public class CdzdsuperImportExcelController {
@Resource(name="CdzdsuperImportExcelServiceImpl")
private CdzdsuperImportExcelServiceImpl service;
private JsonBinder json = JsonBinder.getJsonBinder();
@RequestMapping("importsExcel.do")
@ResponseBody
public String cdzdExcel(@RequestParam(value="substationaa",required=false) String substationaa,
@RequestParam(value="voltageaa",required=false) String voltageaa,
@RequestParam(value="guid",required=false) String guid) {
// 1. 解析 GUID 數組、構建 IN 子句
// 2. 查詢變電站列表 subNames
// 3. 生成 Workbook
HSSFWorkbook workbook = service.getExcel(guids, subNames, userModel);
// 4. 響應下載（見上文）
return json.toJson(new ValidateMessage(true, "success"));
}
}
@Service("CdzdsuperImportExcelServiceImpl")
public class CdzdsuperImportExcelServiceImpl {
@Resource GenericDao genericDao;
public HSSFWorkbook getExcel(String[] guids, String[] subNames, UserModel user) {
HSSFWorkbook wb = POIExcelUtil.createNewHSSFWorkbook();
for (int i=0; i DataTable
DataTable dt = queryDetail(guids, subNames[i]);
// 構建 Sheet
buildSheet(wb, subNames[i], dt);
}
return wb;
}
private void buildSheet(HSSFWorkbook wb, String sheetName, DataTable dt) {
HSSFSheet sheet = wb.createSheet(sheetName);
// 設置列寬、標題樣式、表頭樣式、數據樣式...
// 寫入標題行（合併 0-12 列）
// 寫入表頭（第 2 行序號，第 3 行列名）
// 循環寫入數據行
// 寫入底部合併區域（核對簽名、備註）
}
private DataTable queryDetail(String[] guids, String subName) {
// 構建含游標的 SQL，調用 genericDao.getDataIntoDataTable
// 返回單站 DataTable
}
public List getSubNames(String guidInSql) { ... }
}
CAST(GUID AS VARCHAR(500))。debugger 斷點 + Network 面板查看 Form Data，排查 400 錯誤（多為參數名不匹配）。完整代碼已開源於項目倉庫，歡迎交流指正。
