出復(fù)雜Excel：告別POI硬編碼的高效方案)
Freemarker導(dǎo)出復(fù)雜Excel這件事剛聽上去有點(diǎn)像用菜刀開罐頭——不是不能但總覺得應(yīng)該用POI。直到我手頭那個(gè)報(bào)表系統(tǒng)連續(xù)改了四五版表頭每次都要重新對(duì)著POI代碼調(diào)樣式、挪合并單元格、試列寬我才認(rèn)真把模板生成Excel這條路完整走了一遍。走完之后我的結(jié)論是如果你的報(bào)表特征是“表頭復(fù)雜、樣式固定、數(shù)據(jù)按模板填充”Freemarker模板導(dǎo)出Excel的性價(jià)比高到離譜。這篇文章不聊什么高深架構(gòu)就把我自己在項(xiàng)目中怎么選型、怎么做模板、怎么踩坑、怎么回頭的完整過(guò)程寫出來(lái)給那些正在被復(fù)雜Excel導(dǎo)出折磨的人一個(gè)能直接上手的方向。先把這個(gè)方案的邊界說(shuō)清楚Freemarker本身是個(gè)模板引擎它不生成xlsx二進(jìn)制文件而是生成一個(gè)“Excel能識(shí)別”的文本文件。目前最靠譜的玩法有兩種一種是XML Spreadsheet 2003格式另一種是HTML表格偽裝成xls。我的項(xiàng)目里最終選了前者后面我會(huì)解釋為什么。如果你做的是那種表頭三四層、帶合并單元格、帶公式、帶多個(gè)sheet的月度報(bào)表這篇文章應(yīng)該能幫你省掉至少一個(gè)星期的返工時(shí)間。1. 一個(gè)讓我重新考慮導(dǎo)出方案的真實(shí)場(chǎng)景1.1 最初用POI寫排班表導(dǎo)出的痛點(diǎn)我接手過(guò)一個(gè)排班表導(dǎo)出功能。表頭左邊是人員信息列中間是31天日期列最上面還要插一個(gè)“本月匯總”的大合并行每個(gè)人員一行按班組動(dòng)態(tài)分組。最初用POI寫的寫了兩千多行Java代碼。光是設(shè)置表頭樣式就寫了三個(gè)方法合并單元格的時(shí)候還要自己維護(hù)一個(gè)CellRangeAddress列表防止合并區(qū)域重疊。每次業(yè)務(wù)方提需求說(shuō)“表頭第三個(gè)層級(jí)加一列備注”“匯總行改一下底色”我就要重新定位代碼里那一段改完還得肉眼對(duì)著一堆setCellStyle排查有沒有漏掉。更惡心的是排班表里列寬會(huì)隨內(nèi)容變化POI里不同列要單獨(dú)setColumnWidth一旦數(shù)據(jù)量大了打開文件還容易出現(xiàn)“已修復(fù)部分內(nèi)容”的提示。后來(lái)我冷靜想了想這個(gè)功能本質(zhì)上是什么是“把固定的視覺模板和動(dòng)態(tài)數(shù)據(jù)拼在一起”。這不就是模板引擎最擅長(zhǎng)的事嗎我為什么非要用代碼去一行行畫格子1.2 Freemarker導(dǎo)出Excel適合解決什么問(wèn)題Freemarker導(dǎo)出Excel的真正適用場(chǎng)景一句話總結(jié)“Excel外觀幾乎不變變的只有里面填的數(shù)據(jù)和行數(shù)”。比如銷售報(bào)表、排班表、對(duì)賬單、固定資產(chǎn)臺(tái)賬這些表有共同特點(diǎn)表頭設(shè)計(jì)極其復(fù)雜但一旦定下來(lái)幾個(gè)月甚至幾年都不動(dòng)數(shù)據(jù)是按日/按月刷新的樣式要求高要跟企業(yè)現(xiàn)有表格保持一致偶爾還需要公式自動(dòng)算合計(jì)。這種場(chǎng)景下你用XWPF那套思路去硬編碼是災(zāi)難。你改的不是一處代碼而是所有涉及樣式、合并、邊框、字號(hào)的散點(diǎn)代碼。但如果你把Excel本身當(dāng)成模板用Freemarker往里面填數(shù)據(jù)業(yè)務(wù)方改樣式只需要改Excel模板文件Java代碼一行都不用動(dòng)。1.3 模板導(dǎo)出與POI的邊界劃分不要二極管。我這里不是說(shuō)POI沒用而是說(shuō)兩種方案各有邊界。POI/SXSSF適合需要?jiǎng)討B(tài)創(chuàng)建圖表、透視表、圖片嵌入、幾十萬(wàn)行數(shù)據(jù)流式導(dǎo)出、需要程序生成全新結(jié)構(gòu)的文件。Freemarker模板適合結(jié)構(gòu)相對(duì)穩(wěn)定、樣式要求高、多sheet、合并單元格多、依賴公式、數(shù)據(jù)量在幾萬(wàn)行以內(nèi)。我當(dāng)時(shí)那個(gè)排班表樣式固定、行數(shù)最多幾千完全在模板方案的安全區(qū)內(nèi)。選型選對(duì)了后面開發(fā)量是斷崖式下降。2. 為什么Excel能打開“Freemarker生成的文件”兩種模板格式的選型2.1 關(guān)鍵前提XML Spreadsheet 2003和HTML表格都能被Excel識(shí)別先說(shuō)原理不然你后面連錯(cuò)都不知道錯(cuò)在哪。Excel并不是只認(rèn)xls/xlsx兩種二進(jìn)制格式它內(nèi)置支持一種叫XML Spreadsheet 2003的純XML交換格式。這種格式的文件擴(kuò)展名可以直接寫成.xls文件開頭有一個(gè)特殊的處理指令?xml version1.0 encodingUTF-8? ?mso-application progidExcel.Sheet?這個(gè)?mso-application progidExcel.Sheet?就是關(guān)鍵標(biāo)識(shí)Windows和Excel看到它就知道這是Excel文檔雙擊就能直接打開。另外Excel也支持直接打開HTML格式的文件只要HTML里包含table結(jié)構(gòu)并且聲明了office的excel命名空間同樣可以偽裝成.xls。這就是很多老項(xiàng)目用Freemarker拼HTML表格導(dǎo)出Excel的原理。這兩種格式Freemarker都能生成但實(shí)戰(zhàn)中的體驗(yàn)差距很大下面分開講。2.2 XML Spreadsheet 2003的文件骨架與命名空間我最后選的就是這個(gè)格式。它的核心結(jié)構(gòu)是Workbook - Worksheet - Table - Row - Cell所有標(biāo)簽都有嚴(yán)格的命名空間urn:schemas-microsoft-com:office:spreadsheet縮寫通常寫作ss。一個(gè)最簡(jiǎn)單的骨架長(zhǎng)這樣?xml version1.0 encodingUTF-8? ?mso-application progidExcel.Sheet? Workbook xmlnsurn:schemas-microsoft-com:office:spreadsheet xmlns:ourn:schemas-microsoft-com:office:office xmlns:xurn:schemas-microsoft-com:office:excel xmlns:ssurn:schemas-microsoft-com:office:spreadsheet Styles Style ss:IDsHeader Alignment ss:HorizontalCenter ss:VerticalCenter/ Font ss:FontName微軟雅黑 ss:Size10 ss:Bold1/ Interior ss:Color#4472C4 ss:PatternSolid/ /Style /Styles Worksheet ss:NameSheet1 Table Column ss:Width90/ Row Cell ss:StyleIDsHeaderData ss:TypeString姓名/Data/Cell /Row /Table /Worksheet /Workbook注意兩點(diǎn)第一Data標(biāo)簽的ss:Type必須寫對(duì)字符串是String數(shù)字是Number日期是DateTime寫錯(cuò)了Excel打開會(huì)報(bào)錯(cuò)或者類型錯(cuò)亂第二Cell里的ss:StyleID引用的是Styles里定義的ss:ID樣式復(fù)用能力很強(qiáng)一個(gè)表頭樣式可以在幾十個(gè)單元格上共用。2.3 HTML Table偽裝xls的妥協(xié)方案再來(lái)說(shuō)說(shuō)另一種思路Freemarker直接輸出一個(gè)帶table的HTML頁(yè)面響應(yīng)頭設(shè)置成application/vnd.ms-excel后綴為.xls。Excel打開后確實(shí)能顯示表格但它對(duì)HTML表格的支持屬于“盡力而為”多sheet支持很別扭公式不會(huì)被執(zhí)行列寬、打印分頁(yè)、合并單元格的控制能力都遠(yuǎn)不如XML Spreadsheet。我在一個(gè)老項(xiàng)目里見過(guò)這種方案最典型的問(wèn)題是WPS打開會(huì)彈“文件格式與擴(kuò)展名不匹配”的警告用戶每次都以為文件壞了。而且一旦某個(gè)單元格數(shù)據(jù)里包含了類似td的字符串整個(gè)表格布局都可能被沖亂。所以我的選型結(jié)論很簡(jiǎn)單要長(zhǎng)期維護(hù)、公式多、樣式嚴(yán)的報(bào)表直接用XML Spreadsheet 2003只是臨時(shí)導(dǎo)個(gè)列表給用戶看HTML偽裝xls可以用但別指望它能扛住復(fù)雜需求。3. 動(dòng)手做一個(gè)真實(shí)可用的Freemarker Excel模板3.1 用Excel另存為XML拿到初始模板手寫整個(gè)XML骨架太容易出錯(cuò)正確做法是先讓Excel幫你生成一個(gè)“標(biāo)準(zhǔn)答案”。具體操作先用Excel/WPS把報(bào)表的靜態(tài)樣式全部做好包括表頭合并、邊框、底色、列寬、數(shù)字格式然后另存為“XML 電子表格 2003”格式得到一個(gè).xml文件。這個(gè)文件就是天然模板底稿。這時(shí)候不要急著改先做三件事用文本編輯器打開確認(rèn)編碼格式建議UTF-8。檢查?mso-application progidExcel.Sheet?是否存在沒有就手動(dòng)補(bǔ)上?？匆谎跾tyles里面的ss:ID后面改數(shù)據(jù)時(shí)大量引用這些ID。我通常會(huì)把文件名改成report.xml.ftl然后全局搜索需要?jiǎng)討B(tài)化的地方把靜態(tài)值替換成Freemarker表達(dá)式。3.2 把靜態(tài)單元格改造成Freemarker占位與循環(huán)假設(shè)報(bào)表主表是一個(gè)訂單明細(xì)行數(shù)不固定。模板里最核心的改動(dòng)是把一條靜態(tài)Row放進(jìn)#list循環(huán)里#list orderList as order Row Cell ss:StyleIDsTextData ss:TypeString${order.orderNo}/Data/Cell Cell ss:StyleIDsNumData ss:TypeNumber${order.amount}/Data/Cell Cell ss:StyleIDsDateData ss:TypeDateTime${order.orderTime}/Data/Cell /Row /#list這里有兩個(gè)細(xì)節(jié)會(huì)影響成敗第一#list標(biāo)簽和Row標(biāo)簽盡量寫在同一行或者標(biāo)簽之間不要有額外的空白文本節(jié)點(diǎn)。雖然XML里的空白文本不會(huì)導(dǎo)致Excel打不開但我在實(shí)際測(cè)試?yán)镉龅竭^(guò)個(gè)別sheet出現(xiàn)多余換行的情況字體小的時(shí)候根本看不出來(lái)打印時(shí)多出一頁(yè)空白。第二所有動(dòng)態(tài)插值都必須考慮轉(zhuǎn)義。XML里、、是結(jié)構(gòu)字符如果業(yè)務(wù)數(shù)據(jù)里出現(xiàn)“AB公司”這種字符串不轉(zhuǎn)義會(huì)直接破壞XML結(jié)構(gòu)Excel打開就報(bào)錯(cuò)。Freemarker里最穩(wěn)妥的做法是統(tǒng)一用?xmlCell ss:StyleIDsTextData ss:TypeString${order.companyName?xml}/Data/Cell如果單元格樣式ID也是動(dòng)態(tài)的屬性值也要轉(zhuǎn)義ss:StyleID${cell.styleId?xml}。這一步?jīng)]做好后期排查會(huì)讓你懷疑人生。3.3 Java端的三行核心調(diào)用與輸出流處理模板做好了Java端其實(shí)就三步創(chuàng)建Configuration、獲取Template、把數(shù)據(jù)模型和Writer交給process方法。Configuration cfg new Configuration(Configuration.VERSION_2_3_32); cfg.setDefaultEncoding(UTF-8); cfg.setClassForTemplateLoading(getClass(), /templates/excel); Template template cfg.getTemplate(report.xml.ftl); MapString, Object root new HashMap(); root.put(orderList, orderService.queryTodayOrders()); // 直接往響應(yīng)流寫不要先拼字符串再轉(zhuǎn) response.setContentType(application/vnd.ms-excel;charsetUTF-8); String fileName 訂單明細(xì)_ LocalDate.now() .xls; response.setHeader(Content-Disposition, attachment; filename URLEncoder.encode(fileName, UTF-8).replace(, %20)); Writer out new BufferedWriter(new OutputStreamWriter(response.getOutputStream(), UTF-8)); template.process(root, out); out.flush();為什么強(qiáng)調(diào)“直接往響應(yīng)流寫”因?yàn)镕reemarker的process本身是流式的模板渲染一行就寫一行。如果你改成StringWriter先把整份XML拼到內(nèi)存里再一次性輸出幾萬(wàn)行數(shù)據(jù)時(shí)內(nèi)存會(huì)成倍上漲后面第5章我會(huì)講我實(shí)測(cè)的GC教訓(xùn)。3.4 下載接口的響應(yīng)頭與文件名細(xì)節(jié)響應(yīng)頭是坑最多的地方。我一共踩過(guò)三個(gè)Content-Type設(shè)成text/html時(shí)文件能被打開但瀏覽器/ Office會(huì)把它當(dāng)網(wǎng)頁(yè)處理導(dǎo)致“文件格式與擴(kuò)展名不匹配”。中文文件名直接拼進(jìn)Content-Disposition會(huì)亂碼必須URLEncoder.encode且要把編碼后產(chǎn)生的替換成%20否則某些瀏覽器下載的文件名空格會(huì)變成加號(hào)。如果用application/octet-stream兜底部分WPS版本彈安全警告。標(biāo)準(zhǔn)做法就是我上面代碼里寫的application/vnd.ms-excel;charsetUTF-8 URLEncode后的文件名。這里的charsetUTF-8、XML聲明里的encodingUTF-8、模板文件保存的編碼、OutputStreamWriter指定的編碼四處必須一致任何一處不一致都可能在亂碼上翻車。4. 處理復(fù)雜報(bào)表的五個(gè)硬場(chǎng)景合并、分組、公式、多Sheet、圖片4.1 合并單元格Ref行號(hào)會(huì)隨動(dòng)態(tài)數(shù)據(jù)漂移XML Spreadsheet 2003的合并單元格長(zhǎng)這樣MergeCells Count1 MergeCell RefR1C1:R1C4/ /MergeCellsR1C1:R1C4表示從第1行第1列到第1行第4列合并。問(wèn)題在于你模板里寫死的Ref是固定行號(hào)一旦上面插入動(dòng)態(tài)行下面所有區(qū)域的行號(hào)全錯(cuò)位。處理思路分兩檔合并區(qū)域完全在固定表頭區(qū)域不隨數(shù)據(jù)變化那么直接寫死Ref沒問(wèn)題。合并區(qū)域跨動(dòng)態(tài)行范圍比如“每個(gè)分組自動(dòng)合并第一列”就不能寫死。我建議在數(shù)據(jù)模型里預(yù)先把每個(gè)分組的起始行和結(jié)束行算好模板里直接輸出#list groupList as g Row.../Row ... MergeCell RefR${g.startRow}C1:R${g.endRow}C1/ /#list初始行號(hào)怎么算一般規(guī)則是固定表頭占幾行從第表頭行數(shù)1行開始算數(shù)據(jù)每行Row占一行。我在項(xiàng)目里專門寫了一個(gè)小工具類專門根據(jù)“表頭占位行數(shù) 每組數(shù)據(jù)量”計(jì)算合并起點(diǎn)避免在模板里做復(fù)雜算術(shù)。4.2 分組行嵌套循環(huán)時(shí)行號(hào)錯(cuò)位怎么防再?gòu)?fù)雜一點(diǎn)分組嵌套循環(huán)每組有一個(gè)大標(biāo)題行下面若干明細(xì)行。這種場(chǎng)景最容易出的問(wèn)題不是語(yǔ)法錯(cuò)誤而是明明模板沒寫錯(cuò)合并行號(hào)卻忽前忽后。我的經(jīng)驗(yàn)是不要在模板里現(xiàn)算行號(hào)交給Java端做預(yù)處理。數(shù)據(jù)模型提前構(gòu)造成類似這樣ListGroupData groups; class GroupData { String groupName; ListRowData rows; int startRow; int endRow; }模板里循環(huán)輸出組標(biāo)題行和明細(xì)行然后單獨(dú)輸出MergeCell時(shí)直接引用group.startRow。這樣模板保持簡(jiǎn)單復(fù)雜的行號(hào)計(jì)算全部收進(jìn)Java代碼出問(wèn)題也好調(diào)試。4.3 公式的渲染方式與打開后不自動(dòng)計(jì)算問(wèn)題模板方式當(dāng)然支持公式在Data后面放Formula標(biāo)簽即可。比如訂單表里金額列、稅額列都有了要一列合計(jì)金額Cell ss:StyleIDsNum Data ss:TypeNumber0/Data FormulaRC[-2]RC[-1]/Formula /Cell這里RC[-2]和RC[-1]是相對(duì)引用代表當(dāng)前行往左數(shù)第2列、第1列。這個(gè)語(yǔ)法是從Excel本身的RC引用風(fēng)格來(lái)的比起拼A1、B1地址在動(dòng)態(tài)行列場(chǎng)景下不容易算錯(cuò)?？釉谀腅xcel打開文件時(shí)是否重新計(jì)算公式取決于文件的CalcMode設(shè)置和用戶本機(jī)Excel的計(jì)算選項(xiàng)。我遇到過(guò)文件打開后公式位置是空白的排查半天發(fā)現(xiàn)模板里少了Excel的Workbook配置節(jié)。你最好在Workbook根節(jié)點(diǎn)下保留這段x:ExcelWorkbook x:CalcModeAuto/x:CalcMode /x:ExcelWorkbook另外有一種更穩(wěn)妥的兜底公式依賴的原始數(shù)據(jù)在Java端都有你可以在導(dǎo)出時(shí)把公式結(jié)果直接算好填進(jìn)Data公式再放進(jìn)Formula。這樣即使Excel出于性能配置沒重新計(jì)算用戶看到的也有值F9手動(dòng)重算后又能驗(yàn)證公式本身是對(duì)的。4.4 動(dòng)態(tài)多Sheet模板的循環(huán)輸出多sheet的場(chǎng)景我用一個(gè)排班月報(bào)舉過(guò)例每個(gè)月一個(gè)sheet或者每個(gè)班組一個(gè)sheet。模板里不需要復(fù)制多個(gè)Worksheet直接用#list循環(huán)生成即可#list sheetList as sheet Worksheet ss:Name${sheet.name} Table ss:ExpandedRowCount${sheet.rowCount} ss:ExpandedColumnCount${sheet.colCount} ... /Table /Worksheet /#listss:ExpandedRowCount和ss:ExpandedColumnCount這兩個(gè)屬性容易被忽略。如果你不聲明Excel會(huì)按模板里的實(shí)際行數(shù)列數(shù)推斷動(dòng)態(tài)渲染后一旦數(shù)據(jù)行數(shù)超過(guò)模板里的靜態(tài)Row數(shù)量可能出現(xiàn)列寬丟失、全選區(qū)域變小的情況。我在做動(dòng)態(tài)多sheet時(shí)吃過(guò)這個(gè)虧最后是固定給Table補(bǔ)上這兩個(gè)屬性解決的。4.5 圖片和批注這個(gè)方案能做的邊界必須說(shuō)清楚Freemarker模板方案對(duì)圖片的支持非常弱。XML Spreadsheet 2003里嵌入圖片要寫OLEObject、VML對(duì)象結(jié)構(gòu)復(fù)雜且不同Excel版本兼容性不穩(wěn)定。我的建議是如果圖片只是“附件說(shuō)明”性質(zhì)單元格里放圖片URL最省事導(dǎo)出后用戶點(diǎn)擊鏈接打開。如果是產(chǎn)品圖片、簽名圖片必須嵌入單元格內(nèi)就別用模板方案硬剛了老老實(shí)實(shí)用POI的Picture API。批注同理XML Spreadsheet 2003里批注是用Comment 關(guān)聯(lián)作者配置實(shí)現(xiàn)的手工維護(hù)成本高。我記得有一次為了復(fù)現(xiàn)一條批注光VML路徑的坐標(biāo)就調(diào)了半小時(shí)。模板方案的核心價(jià)值在單元格數(shù)據(jù)和樣式不要拿它去跟POI拼“全能”。5. 我在項(xiàng)目中實(shí)際踩過(guò)的坑清單5.1 下載后提示“文件格式與擴(kuò)展名不匹配”這是模板導(dǎo)出最常見的翻車點(diǎn)我見過(guò)團(tuán)隊(duì)排查了整整一個(gè)下午最后發(fā)現(xiàn)是響應(yīng)頭寫錯(cuò)了。完整的排查鏈路應(yīng)該是這樣先確認(rèn)Content-Type是不是application/vnd.ms-excel不是就改。再確認(rèn)文件流里第一行是不是?xml version1.0 encodingUTF-8?第二行是不是?mso-application progidExcel.Sheet?progid丟了Excel就會(huì)認(rèn)為這只是一個(gè)普通XML文本。接著確認(rèn)根節(jié)點(diǎn)Workbook的命名空間xmlns:ssurn:schemas-microsoft-com:office:spreadsheet存在沒有這個(gè)命名空間里面所有ss:Type、ss:StyleID屬性都會(huì)被忽略。最后在本地寫個(gè)測(cè)試controller直接用瀏覽器訪問(wèn)把響應(yīng)體保存成.xls跟模板文件對(duì)比看是不是被框架層的攔截器改寫了響應(yīng)頭。那次我們卡在第3步用Freemarker拼模板時(shí)不小心把根節(jié)點(diǎn)的一行xmlns:ss漏掉了結(jié)果Excel打開后能顯示文件名但雙擊就報(bào)格式錯(cuò)誤。5.2 長(zhǎng)數(shù)字變成科學(xué)計(jì)數(shù)法日期變成一串?dāng)?shù)字訂單號(hào)、身份證號(hào)這類長(zhǎng)數(shù)字在Excel里動(dòng)不動(dòng)就變成1.23457E11。很多人第一反應(yīng)是改模板里單元格的數(shù)據(jù)類型但真正的原因可能是Excel的顯示格式。兩種解決方式我都用過(guò)第一種單元格數(shù)據(jù)類型保持Number但在Style里加數(shù)字格式NumberFormat ss:Format0/這樣Excel會(huì)按普通數(shù)字顯示不轉(zhuǎn)科學(xué)計(jì)數(shù)法。但超過(guò)15位時(shí)仍然有精度問(wèn)題。第二種更徹底既然這類編號(hào)本來(lái)就不參與計(jì)算不如直接按字符串輸出。Cell ss:StyleIDsTextData ss:TypeString${order.cardNo}/Data/Cell如果數(shù)據(jù)模型里是數(shù)字類型記得先toString或者模板里用${order.cardNo?string(0)}轉(zhuǎn)成無(wú)科學(xué)計(jì)數(shù)法的字符串再輸出。日期變成一串?dāng)?shù)字原因通常是把DateTime類型寫成了String。XML Spreadsheet 2003里日期要用DateTimeData ss:TypeDateTime2025-01-15T09:30:00.000/Data同時(shí)樣式里配套NumberFormat ss:Formatyyyy-mm-dd hh:mm:ss/能顯示成正常日期。5.3 用戶輸入的符號(hào)把XML結(jié)構(gòu)炸了這是一條線上事故教訓(xùn)。某個(gè)備注字段用戶填了“研發(fā)測(cè)試”導(dǎo)出時(shí)沒做轉(zhuǎn)義生成的文件里出現(xiàn)了裸XML解析直接失敗所有用戶同一時(shí)間導(dǎo)不出報(bào)表。排查鏈路也很清晰先下載文件用文本編輯器打開看到靠近報(bào)錯(cuò)位置的XML片段里出現(xiàn)了孤立的再定位到是哪個(gè)數(shù)據(jù)字段最后給所有動(dòng)態(tài)輸出統(tǒng)一加上?xml轉(zhuǎn)義。給所有動(dòng)態(tài)輸出統(tǒng)一加上?xml轉(zhuǎn)義。后來(lái)我在數(shù)據(jù)模型里加了一個(gè)escapeXml的統(tǒng)一出口所有進(jìn)入模板的字符串在Java端先轉(zhuǎn)義一遍模板里即使有人忘了寫?xml兜底邏輯也還在。雙保險(xiǎn)之后這個(gè)坑再?zèng)]出現(xiàn)。5.4 幾萬(wàn)行數(shù)據(jù)導(dǎo)出時(shí)內(nèi)存和GC的教訓(xùn)有一版我圖省事用了StringWriter先把整個(gè)XML拼出來(lái)再一次性轉(zhuǎn)成byte[]寫回響應(yīng)。導(dǎo)出一張6萬(wàn)行的對(duì)賬單時(shí)應(yīng)用程序的堆內(nèi)存直接從正常水平飆到接近上限連續(xù)幾次導(dǎo)出直接觸發(fā)頻繁Full GC。那次排查引向一個(gè)結(jié)論Freemarker的process方法是流式寫出的你不該把它拼成完整字符串。改成直接把OutputStreamWriter傳給process后內(nèi)存占用降了一個(gè)數(shù)量級(jí)。還有個(gè)容易被忽略的點(diǎn)模板里的#list內(nèi)盡量少調(diào)用復(fù)雜的自定義函數(shù)Freemarker每行數(shù)據(jù)都會(huì)重新解析表達(dá)式函數(shù)太重時(shí)單行渲染時(shí)間會(huì)飆升數(shù)據(jù)量一大性能就崩。提前把能算好的值全放進(jìn)數(shù)據(jù)模型模板保持“傻白甜”狀態(tài)性能最穩(wěn)。5.5 模板改了不生效Freemarker緩存問(wèn)題項(xiàng)目里同事改完模板刷新頁(yè)面發(fā)現(xiàn)還是舊樣式第一反應(yīng)是瀏覽器緩存實(shí)際上Freemarker自己會(huì)緩存模板。Debug階段建議關(guān)掉緩存或者把更新延遲設(shè)成0cfg.setTemplateUpdateDelayMilliseconds(0);生產(chǎn)環(huán)境再改回默認(rèn)緩存策略或者干脆只在發(fā)版時(shí)更新模板。這個(gè)機(jī)制平時(shí)是好事能少很多模板解析開銷但也正是因?yàn)樗疤奖恪比菀鬃屓嗽陂_發(fā)期誤以為模板沒保存成功。6. 我的個(gè)人建議什么時(shí)候繼續(xù)用模板什么時(shí)候回頭用POI6.1 模板目錄的權(quán)限與控制模板文件一定要放在項(xiàng)目資源目錄或者獨(dú)立配置目錄里不要散落在業(yè)務(wù)代碼中。我習(xí)慣建一個(gè)templates/excel目錄每個(gè)模板單獨(dú)一個(gè).ftl文件命名跟業(yè)務(wù)報(bào)表一一對(duì)應(yīng)。生產(chǎn)環(huán)境模板目錄只給運(yùn)維和核心開發(fā)寫權(quán)限防止業(yè)務(wù)方直接改了線上文件卻沒人知道。版本管理里模板和代碼一樣走審批別小看這一步模板出錯(cuò)造成的線上事故一點(diǎn)都不比代碼少。6.2 分工方式Excel模板由誰(shuí)維護(hù)這套方案最大的隱性收益是“樣式需求不再全壓在開發(fā)頭上”。業(yè)務(wù)方想要換個(gè)表頭底色、加一列、調(diào)列寬只要給到調(diào)好的Excel模板開發(fā)人員另存為XML后替換占位符即可。實(shí)際操作中我不建議完全交給業(yè)務(wù)方做畢竟另存為XML并改造占位符還是需要一點(diǎn)基本功的但至少他們可以先把樣式調(diào)到滿意開發(fā)只做數(shù)據(jù)接入。6.3 一套判斷標(biāo)準(zhǔn)最后給一張我在項(xiàng)目里用的簡(jiǎn)表直接對(duì)照著選需求特征推薦方案原因表頭復(fù)雜、樣式固定Freemarker XML Spreadsheet 2003樣式復(fù)用集中改樣式不動(dòng)代碼行數(shù)動(dòng)態(tài)、分組動(dòng)態(tài)Freemarker Java端預(yù)處理行號(hào)模板保持簡(jiǎn)單復(fù)雜計(jì)算收進(jìn)Java需要內(nèi)置公式自動(dòng)匯總Freemarker Formula CalcMode Auto天然支持相對(duì)引用多個(gè)Sheet固定模板Freemarker Worksheet循環(huán)一次渲染多表需要嵌入圖片、圖表POI模板方案對(duì)圖片支持太弱幾十萬(wàn)行大數(shù)據(jù)量POI SXSSF流式寫盤避免內(nèi)存暴漲需要生成全新未知結(jié)構(gòu)POI模板無(wú)法窮舉所有結(jié)構(gòu)文本導(dǎo)出的思路聽起來(lái)“不夠原生”但只要你看透它背后是Excel官方支持的交換格式就會(huì)明白這不是歪門邪道而是一條被低估的工程捷徑。我個(gè)人在實(shí)際項(xiàng)目里的體會(huì)是把復(fù)雜Excel導(dǎo)出的需求拆成“樣式”和“數(shù)據(jù)”兩半樣式的職責(zé)歸模板數(shù)據(jù)的職責(zé)歸Java兩者通過(guò)Freemarker匯合后續(xù)改動(dòng)成本能壓縮到非常小。如果你的項(xiàng)目正好卡在“表頭改一次我就得改一次代碼”的循環(huán)里不妨找個(gè)周末拿一個(gè)真實(shí)報(bào)表把這條鏈路完整跑一遍。 Freemarker模板導(dǎo)出復(fù)雜Excel：告別POI硬編碼的高效方案
Freemarker導(dǎo)出復(fù)雜Excel這件事剛聽上去有點(diǎn)像用菜刀開罐頭——不是不能但總覺得應(yīng)該用POI。直到我手頭那個(gè)報(bào)表系統(tǒng)連續(xù)改了四五版表頭每次都要重新對(duì)著POI代碼調(diào)樣式、挪合并單元格、試列寬我才認(rèn)真把模板生成Excel這條路完整走了一遍。走完之后我的結(jié)論是如果你的報(bào)表特征是“表頭復(fù)雜、樣式固定、數(shù)據(jù)按模板填充”Freemarker模板導(dǎo)出Excel的性價(jià)比高到離譜。這篇文章不聊什么高深架構(gòu)就把我自己在項(xiàng)目中怎么選型、怎么做模板、怎么踩坑、怎么回頭的完整過(guò)程寫出來(lái)給那些正在被復(fù)雜Excel導(dǎo)出折磨的人一個(gè)能直接上手的方向。先把這個(gè)方案的邊界說(shuō)清楚Freemarker本身是個(gè)模板引擎它不生成xlsx二進(jìn)制文件而是生成一個(gè)“Excel能識(shí)別”的文本文件。目前最靠譜的玩法有兩種一種是XML Spreadsheet 2003格式另一種是HTML表格偽裝成xls。我的項(xiàng)目里最終選了前者后面我會(huì)解釋為什么。如果你做的是那種表頭三四層、帶合并單元格、帶公式、帶多個(gè)sheet的月度報(bào)表這篇文章應(yīng)該能幫你省掉至少一個(gè)星期的返工時(shí)間。1. 一個(gè)讓我重新考慮導(dǎo)出方案的真實(shí)場(chǎng)景1.1 最初用POI寫排班表導(dǎo)出的痛點(diǎn)我接手過(guò)一個(gè)排班表導(dǎo)出功能。表頭左邊是人員信息列中間是31天日期列最上面還要插一個(gè)“本月匯總”的大合并行每個(gè)人員一行按班組動(dòng)態(tài)分組。最初用POI寫的寫了兩千多行Java代碼。光是設(shè)置表頭樣式就寫了三個(gè)方法合并單元格的時(shí)候還要自己維護(hù)一個(gè)CellRangeAddress列表防止合并區(qū)域重疊。每次業(yè)務(wù)方提需求說(shuō)“表頭第三個(gè)層級(jí)加一列備注”“匯總行改一下底色”我就要重新定位代碼里那一段改完還得肉眼對(duì)著一堆setCellStyle排查有沒有漏掉。更惡心的是排班表里列寬會(huì)隨內(nèi)容變化POI里不同列要單獨(dú)setColumnWidth一旦數(shù)據(jù)量大了打開文件還容易出現(xiàn)“已修復(fù)部分內(nèi)容”的提示。后來(lái)我冷靜想了想這個(gè)功能本質(zhì)上是什么是“把固定的視覺模板和動(dòng)態(tài)數(shù)據(jù)拼在一起”。這不就是模板引擎最擅長(zhǎng)的事嗎我為什么非要用代碼去一行行畫格子1.2 Freemarker導(dǎo)出Excel適合解決什么問(wèn)題Freemarker導(dǎo)出Excel的真正適用場(chǎng)景一句話總結(jié)“Excel外觀幾乎不變變的只有里面填的數(shù)據(jù)和行數(shù)”。比如銷售報(bào)表、排班表、對(duì)賬單、固定資產(chǎn)臺(tái)賬這些表有共同特點(diǎn)表頭設(shè)計(jì)極其復(fù)雜但一旦定下來(lái)幾個(gè)月甚至幾年都不動(dòng)數(shù)據(jù)是按日/按月刷新的樣式要求高要跟企業(yè)現(xiàn)有表格保持一致偶爾還需要公式自動(dòng)算合計(jì)。這種場(chǎng)景下你用XWPF那套思路去硬編碼是災(zāi)難。你改的不是一處代碼而是所有涉及樣式、合并、邊框、字號(hào)的散點(diǎn)代碼。但如果你把Excel本身當(dāng)成模板用Freemarker往里面填數(shù)據(jù)業(yè)務(wù)方改樣式只需要改Excel模板文件Java代碼一行都不用動(dòng)。1.3 模板導(dǎo)出與POI的邊界劃分不要二極管。我這里不是說(shuō)POI沒用而是說(shuō)兩種方案各有邊界。POI/SXSSF適合需要?jiǎng)討B(tài)創(chuàng)建圖表、透視表、圖片嵌入、幾十萬(wàn)行數(shù)據(jù)流式導(dǎo)出、需要程序生成全新結(jié)構(gòu)的文件。Freemarker模板適合結(jié)構(gòu)相對(duì)穩(wěn)定、樣式要求高、多sheet、合并單元格多、依賴公式、數(shù)據(jù)量在幾萬(wàn)行以內(nèi)。我當(dāng)時(shí)那個(gè)排班表樣式固定、行數(shù)最多幾千完全在模板方案的安全區(qū)內(nèi)。選型選對(duì)了后面開發(fā)量是斷崖式下降。2. 為什么Excel能打開“Freemarker生成的文件”兩種模板格式的選型2.1 關(guān)鍵前提XML Spreadsheet 2003和HTML表格都能被Excel識(shí)別先說(shuō)原理不然你后面連錯(cuò)都不知道錯(cuò)在哪。Excel并不是只認(rèn)xls/xlsx兩種二進(jìn)制格式它內(nèi)置支持一種叫XML Spreadsheet 2003的純XML交換格式。這種格式的文件擴(kuò)展名可以直接寫成.xls文件開頭有一個(gè)特殊的處理指令?xml version1.0 encodingUTF-8? ?mso-application progidExcel.Sheet?這個(gè)?mso-application progidExcel.Sheet?就是關(guān)鍵標(biāo)識(shí)Windows和Excel看到它就知道這是Excel文檔雙擊就能直接打開。另外Excel也支持直接打開HTML格式的文件只要HTML里包含table結(jié)構(gòu)并且聲明了office的excel命名空間同樣可以偽裝成.xls。這就是很多老項(xiàng)目用Freemarker拼HTML表格導(dǎo)出Excel的原理。這兩種格式Freemarker都能生成但實(shí)戰(zhàn)中的體驗(yàn)差距很大下面分開講。2.2 XML Spreadsheet 2003的文件骨架與命名空間我最后選的就是這個(gè)格式。它的核心結(jié)構(gòu)是Workbook - Worksheet - Table - Row - Cell所有標(biāo)簽都有嚴(yán)格的命名空間urn:schemas-microsoft-com:office:spreadsheet縮寫通常寫作ss。一個(gè)最簡(jiǎn)單的骨架長(zhǎng)這樣?xml version1.0 encodingUTF-8? ?mso-application progidExcel.Sheet? Workbook xmlnsurn:schemas-microsoft-com:office:spreadsheet xmlns:ourn:schemas-microsoft-com:office:office xmlns:xurn:schemas-microsoft-com:office:excel xmlns:ssurn:schemas-microsoft-com:office:spreadsheet Styles Style ss:IDsHeader Alignment ss:HorizontalCenter ss:VerticalCenter/ Font ss:FontName微軟雅黑 ss:Size10 ss:Bold1/ Interior ss:Color#4472C4 ss:PatternSolid/ /Style /Styles Worksheet ss:NameSheet1 Table Column ss:Width90/ Row Cell ss:StyleIDsHeaderData ss:TypeString姓名/Data/Cell /Row /Table /Worksheet /Workbook注意兩點(diǎn)第一Data標(biāo)簽的ss:Type必須寫對(duì)字符串是String數(shù)字是Number日期是DateTime寫錯(cuò)了Excel打開會(huì)報(bào)錯(cuò)或者類型錯(cuò)亂第二Cell里的ss:StyleID引用的是Styles里定義的ss:ID樣式復(fù)用能力很強(qiáng)一個(gè)表頭樣式可以在幾十個(gè)單元格上共用。2.3 HTML Table偽裝xls的妥協(xié)方案再來(lái)說(shuō)說(shuō)另一種思路Freemarker直接輸出一個(gè)帶table的HTML頁(yè)面響應(yīng)頭設(shè)置成application/vnd.ms-excel后綴為.xls。Excel打開后確實(shí)能顯示表格但它對(duì)HTML表格的支持屬于“盡力而為”多sheet支持很別扭公式不會(huì)被執(zhí)行列寬、打印分頁(yè)、合并單元格的控制能力都遠(yuǎn)不如XML Spreadsheet。我在一個(gè)老項(xiàng)目里見過(guò)這種方案最典型的問(wèn)題是WPS打開會(huì)彈“文件格式與擴(kuò)展名不匹配”的警告用戶每次都以為文件壞了。而且一旦某個(gè)單元格數(shù)據(jù)里包含了類似td的字符串整個(gè)表格布局都可能被沖亂。所以我的選型結(jié)論很簡(jiǎn)單要長(zhǎng)期維護(hù)、公式多、樣式嚴(yán)的報(bào)表直接用XML Spreadsheet 2003只是臨時(shí)導(dǎo)個(gè)列表給用戶看HTML偽裝xls可以用但別指望它能扛住復(fù)雜需求。3. 動(dòng)手做一個(gè)真實(shí)可用的Freemarker Excel模板3.1 用Excel另存為XML拿到初始模板手寫整個(gè)XML骨架太容易出錯(cuò)正確做法是先讓Excel幫你生成一個(gè)“標(biāo)準(zhǔn)答案”。具體操作先用Excel/WPS把報(bào)表的靜態(tài)樣式全部做好包括表頭合并、邊框、底色、列寬、數(shù)字格式然后另存為“XML 電子表格 2003”格式得到一個(gè).xml文件。這個(gè)文件就是天然模板底稿。這時(shí)候不要急著改先做三件事用文本編輯器打開確認(rèn)編碼格式建議UTF-8。檢查?mso-application progidExcel.Sheet?是否存在沒有就手動(dòng)補(bǔ)上?？匆谎跾tyles里面的ss:ID后面改數(shù)據(jù)時(shí)大量引用這些ID。我通常會(huì)把文件名改成report.xml.ftl然后全局搜索需要?jiǎng)討B(tài)化的地方把靜態(tài)值替換成Freemarker表達(dá)式。3.2 把靜態(tài)單元格改造成Freemarker占位與循環(huán)假設(shè)報(bào)表主表是一個(gè)訂單明細(xì)行數(shù)不固定。模板里最核心的改動(dòng)是把一條靜態(tài)Row放進(jìn)#list循環(huán)里#list orderList as order Row Cell ss:StyleIDsTextData ss:TypeString${order.orderNo}/Data/Cell Cell ss:StyleIDsNumData ss:TypeNumber${order.amount}/Data/Cell Cell ss:StyleIDsDateData ss:TypeDateTime${order.orderTime}/Data/Cell /Row /#list這里有兩個(gè)細(xì)節(jié)會(huì)影響成敗第一#list標(biāo)簽和Row標(biāo)簽盡量寫在同一行或者標(biāo)簽之間不要有額外的空白文本節(jié)點(diǎn)。雖然XML里的空白文本不會(huì)導(dǎo)致Excel打不開但我在實(shí)際測(cè)試?yán)镉龅竭^(guò)個(gè)別sheet出現(xiàn)多余換行的情況字體小的時(shí)候根本看不出來(lái)打印時(shí)多出一頁(yè)空白。第二所有動(dòng)態(tài)插值都必須考慮轉(zhuǎn)義。XML里、、是結(jié)構(gòu)字符如果業(yè)務(wù)數(shù)據(jù)里出現(xiàn)“AB公司”這種字符串不轉(zhuǎn)義會(huì)直接破壞XML結(jié)構(gòu)Excel打開就報(bào)錯(cuò)。Freemarker里最穩(wěn)妥的做法是統(tǒng)一用?xmlCell ss:StyleIDsTextData ss:TypeString${order.companyName?xml}/Data/Cell如果單元格樣式ID也是動(dòng)態(tài)的屬性值也要轉(zhuǎn)義ss:StyleID${cell.styleId?xml}。這一步?jīng)]做好后期排查會(huì)讓你懷疑人生。3.3 Java端的三行核心調(diào)用與輸出流處理模板做好了Java端其實(shí)就三步創(chuàng)建Configuration、獲取Template、把數(shù)據(jù)模型和Writer交給process方法。Configuration cfg new Configuration(Configuration.VERSION_2_3_32); cfg.setDefaultEncoding(UTF-8); cfg.setClassForTemplateLoading(getClass(), /templates/excel); Template template cfg.getTemplate(report.xml.ftl); MapString, Object root new HashMap(); root.put(orderList, orderService.queryTodayOrders()); // 直接往響應(yīng)流寫不要先拼字符串再轉(zhuǎn) response.setContentType(application/vnd.ms-excel;charsetUTF-8); String fileName 訂單明細(xì)_ LocalDate.now() .xls; response.setHeader(Content-Disposition, attachment; filename URLEncoder.encode(fileName, UTF-8).replace(, %20)); Writer out new BufferedWriter(new OutputStreamWriter(response.getOutputStream(), UTF-8)); template.process(root, out); out.flush();為什么強(qiáng)調(diào)“直接往響應(yīng)流寫”因?yàn)镕reemarker的process本身是流式的模板渲染一行就寫一行。如果你改成StringWriter先把整份XML拼到內(nèi)存里再一次性輸出幾萬(wàn)行數(shù)據(jù)時(shí)內(nèi)存會(huì)成倍上漲后面第5章我會(huì)講我實(shí)測(cè)的GC教訓(xùn)。3.4 下載接口的響應(yīng)頭與文件名細(xì)節(jié)響應(yīng)頭是坑最多的地方。我一共踩過(guò)三個(gè)Content-Type設(shè)成text/html時(shí)文件能被打開但瀏覽器/ Office會(huì)把它當(dāng)網(wǎng)頁(yè)處理導(dǎo)致“文件格式與擴(kuò)展名不匹配”。中文文件名直接拼進(jìn)Content-Disposition會(huì)亂碼必須URLEncoder.encode且要把編碼后產(chǎn)生的替換成%20否則某些瀏覽器下載的文件名空格會(huì)變成加號(hào)。如果用application/octet-stream兜底部分WPS版本彈安全警告。標(biāo)準(zhǔn)做法就是我上面代碼里寫的application/vnd.ms-excel;charsetUTF-8 URLEncode后的文件名。這里的charsetUTF-8、XML聲明里的encodingUTF-8、模板文件保存的編碼、OutputStreamWriter指定的編碼四處必須一致任何一處不一致都可能在亂碼上翻車。4. 處理復(fù)雜報(bào)表的五個(gè)硬場(chǎng)景合并、分組、公式、多Sheet、圖片4.1 合并單元格Ref行號(hào)會(huì)隨動(dòng)態(tài)數(shù)據(jù)漂移XML Spreadsheet 2003的合并單元格長(zhǎng)這樣MergeCells Count1 MergeCell RefR1C1:R1C4/ /MergeCellsR1C1:R1C4表示從第1行第1列到第1行第4列合并。問(wèn)題在于你模板里寫死的Ref是固定行號(hào)一旦上面插入動(dòng)態(tài)行下面所有區(qū)域的行號(hào)全錯(cuò)位。處理思路分兩檔合并區(qū)域完全在固定表頭區(qū)域不隨數(shù)據(jù)變化那么直接寫死Ref沒問(wèn)題。合并區(qū)域跨動(dòng)態(tài)行范圍比如“每個(gè)分組自動(dòng)合并第一列”就不能寫死。我建議在數(shù)據(jù)模型里預(yù)先把每個(gè)分組的起始行和結(jié)束行算好模板里直接輸出#list groupList as g Row.../Row ... MergeCell RefR${g.startRow}C1:R${g.endRow}C1/ /#list初始行號(hào)怎么算一般規(guī)則是固定表頭占幾行從第表頭行數(shù)1行開始算數(shù)據(jù)每行Row占一行。我在項(xiàng)目里專門寫了一個(gè)小工具類專門根據(jù)“表頭占位行數(shù) 每組數(shù)據(jù)量”計(jì)算合并起點(diǎn)避免在模板里做復(fù)雜算術(shù)。4.2 分組行嵌套循環(huán)時(shí)行號(hào)錯(cuò)位怎么防再?gòu)?fù)雜一點(diǎn)分組嵌套循環(huán)每組有一個(gè)大標(biāo)題行下面若干明細(xì)行。這種場(chǎng)景最容易出的問(wèn)題不是語(yǔ)法錯(cuò)誤而是明明模板沒寫錯(cuò)合并行號(hào)卻忽前忽后。我的經(jīng)驗(yàn)是不要在模板里現(xiàn)算行號(hào)交給Java端做預(yù)處理。數(shù)據(jù)模型提前構(gòu)造成類似這樣ListGroupData groups; class GroupData { String groupName; ListRowData rows; int startRow; int endRow; }模板里循環(huán)輸出組標(biāo)題行和明細(xì)行然后單獨(dú)輸出MergeCell時(shí)直接引用group.startRow。這樣模板保持簡(jiǎn)單復(fù)雜的行號(hào)計(jì)算全部收進(jìn)Java代碼出問(wèn)題也好調(diào)試。4.3 公式的渲染方式與打開后不自動(dòng)計(jì)算問(wèn)題模板方式當(dāng)然支持公式在Data后面放Formula標(biāo)簽即可。比如訂單表里金額列、稅額列都有了要一列合計(jì)金額Cell ss:StyleIDsNum Data ss:TypeNumber0/Data FormulaRC[-2]RC[-1]/Formula /Cell這里RC[-2]和RC[-1]是相對(duì)引用代表當(dāng)前行往左數(shù)第2列、第1列。這個(gè)語(yǔ)法是從Excel本身的RC引用風(fēng)格來(lái)的比起拼A1、B1地址在動(dòng)態(tài)行列場(chǎng)景下不容易算錯(cuò)?？釉谀腅xcel打開文件時(shí)是否重新計(jì)算公式取決于文件的CalcMode設(shè)置和用戶本機(jī)Excel的計(jì)算選項(xiàng)。我遇到過(guò)文件打開后公式位置是空白的排查半天發(fā)現(xiàn)模板里少了Excel的Workbook配置節(jié)。你最好在Workbook根節(jié)點(diǎn)下保留這段x:ExcelWorkbook x:CalcModeAuto/x:CalcMode /x:ExcelWorkbook另外有一種更穩(wěn)妥的兜底公式依賴的原始數(shù)據(jù)在Java端都有你可以在導(dǎo)出時(shí)把公式結(jié)果直接算好填進(jìn)Data公式再放進(jìn)Formula。這樣即使Excel出于性能配置沒重新計(jì)算用戶看到的也有值F9手動(dòng)重算后又能驗(yàn)證公式本身是對(duì)的。4.4 動(dòng)態(tài)多Sheet模板的循環(huán)輸出多sheet的場(chǎng)景我用一個(gè)排班月報(bào)舉過(guò)例每個(gè)月一個(gè)sheet或者每個(gè)班組一個(gè)sheet。模板里不需要復(fù)制多個(gè)Worksheet直接用#list循環(huán)生成即可#list sheetList as sheet Worksheet ss:Name${sheet.name} Table ss:ExpandedRowCount${sheet.rowCount} ss:ExpandedColumnCount${sheet.colCount} ... /Table /Worksheet /#listss:ExpandedRowCount和ss:ExpandedColumnCount這兩個(gè)屬性容易被忽略。如果你不聲明Excel會(huì)按模板里的實(shí)際行數(shù)列數(shù)推斷動(dòng)態(tài)渲染后一旦數(shù)據(jù)行數(shù)超過(guò)模板里的靜態(tài)Row數(shù)量可能出現(xiàn)列寬丟失、全選區(qū)域變小的情況。我在做動(dòng)態(tài)多sheet時(shí)吃過(guò)這個(gè)虧最后是固定給Table補(bǔ)上這兩個(gè)屬性解決的。4.5 圖片和批注這個(gè)方案能做的邊界必須說(shuō)清楚Freemarker模板方案對(duì)圖片的支持非常弱。XML Spreadsheet 2003里嵌入圖片要寫OLEObject、VML對(duì)象結(jié)構(gòu)復(fù)雜且不同Excel版本兼容性不穩(wěn)定。我的建議是如果圖片只是“附件說(shuō)明”性質(zhì)單元格里放圖片URL最省事導(dǎo)出后用戶點(diǎn)擊鏈接打開。如果是產(chǎn)品圖片、簽名圖片必須嵌入單元格內(nèi)就別用模板方案硬剛了老老實(shí)實(shí)用POI的Picture API。批注同理XML Spreadsheet 2003里批注是用Comment 關(guān)聯(lián)作者配置實(shí)現(xiàn)的手工維護(hù)成本高。我記得有一次為了復(fù)現(xiàn)一條批注光VML路徑的坐標(biāo)就調(diào)了半小時(shí)。模板方案的核心價(jià)值在單元格數(shù)據(jù)和樣式不要拿它去跟POI拼“全能”。5. 我在項(xiàng)目中實(shí)際踩過(guò)的坑清單5.1 下載后提示“文件格式與擴(kuò)展名不匹配”這是模板導(dǎo)出最常見的翻車點(diǎn)我見過(guò)團(tuán)隊(duì)排查了整整一個(gè)下午最后發(fā)現(xiàn)是響應(yīng)頭寫錯(cuò)了。完整的排查鏈路應(yīng)該是這樣先確認(rèn)Content-Type是不是application/vnd.ms-excel不是就改。再確認(rèn)文件流里第一行是不是?xml version1.0 encodingUTF-8?第二行是不是?mso-application progidExcel.Sheet?progid丟了Excel就會(huì)認(rèn)為這只是一個(gè)普通XML文本。接著確認(rèn)根節(jié)點(diǎn)Workbook的命名空間xmlns:ssurn:schemas-microsoft-com:office:spreadsheet存在沒有這個(gè)命名空間里面所有ss:Type、ss:StyleID屬性都會(huì)被忽略。最后在本地寫個(gè)測(cè)試controller直接用瀏覽器訪問(wèn)把響應(yīng)體保存成.xls跟模板文件對(duì)比看是不是被框架層的攔截器改寫了響應(yīng)頭。那次我們卡在第3步用Freemarker拼模板時(shí)不小心把根節(jié)點(diǎn)的一行xmlns:ss漏掉了結(jié)果Excel打開后能顯示文件名但雙擊就報(bào)格式錯(cuò)誤。5.2 長(zhǎng)數(shù)字變成科學(xué)計(jì)數(shù)法日期變成一串?dāng)?shù)字訂單號(hào)、身份證號(hào)這類長(zhǎng)數(shù)字在Excel里動(dòng)不動(dòng)就變成1.23457E11。很多人第一反應(yīng)是改模板里單元格的數(shù)據(jù)類型但真正的原因可能是Excel的顯示格式。兩種解決方式我都用過(guò)第一種單元格數(shù)據(jù)類型保持Number但在Style里加數(shù)字格式NumberFormat ss:Format0/這樣Excel會(huì)按普通數(shù)字顯示不轉(zhuǎn)科學(xué)計(jì)數(shù)法。但超過(guò)15位時(shí)仍然有精度問(wèn)題。第二種更徹底既然這類編號(hào)本來(lái)就不參與計(jì)算不如直接按字符串輸出。Cell ss:StyleIDsTextData ss:TypeString${order.cardNo}/Data/Cell如果數(shù)據(jù)模型里是數(shù)字類型記得先toString或者模板里用${order.cardNo?string(0)}轉(zhuǎn)成無(wú)科學(xué)計(jì)數(shù)法的字符串再輸出。日期變成一串?dāng)?shù)字原因通常是把DateTime類型寫成了String。XML Spreadsheet 2003里日期要用DateTimeData ss:TypeDateTime2025-01-15T09:30:00.000/Data同時(shí)樣式里配套NumberFormat ss:Formatyyyy-mm-dd hh:mm:ss/能顯示成正常日期。5.3 用戶輸入的符號(hào)把XML結(jié)構(gòu)炸了這是一條線上事故教訓(xùn)。某個(gè)備注字段用戶填了“研發(fā)測(cè)試”導(dǎo)出時(shí)沒做轉(zhuǎn)義生成的文件里出現(xiàn)了裸XML解析直接失敗所有用戶同一時(shí)間導(dǎo)不出報(bào)表。排查鏈路也很清晰先下載文件用文本編輯器打開看到靠近報(bào)錯(cuò)位置的XML片段里出現(xiàn)了孤立的再定位到是哪個(gè)數(shù)據(jù)字段最后給所有動(dòng)態(tài)輸出統(tǒng)一加上?xml轉(zhuǎn)義。給所有動(dòng)態(tài)輸出統(tǒng)一加上?xml轉(zhuǎn)義。后來(lái)我在數(shù)據(jù)模型里加了一個(gè)escapeXml的統(tǒng)一出口所有進(jìn)入模板的字符串在Java端先轉(zhuǎn)義一遍模板里即使有人忘了寫?xml兜底邏輯也還在。雙保險(xiǎn)之后這個(gè)坑再?zèng)]出現(xiàn)。5.4 幾萬(wàn)行數(shù)據(jù)導(dǎo)出時(shí)內(nèi)存和GC的教訓(xùn)有一版我圖省事用了StringWriter先把整個(gè)XML拼出來(lái)再一次性轉(zhuǎn)成byte[]寫回響應(yīng)。導(dǎo)出一張6萬(wàn)行的對(duì)賬單時(shí)應(yīng)用程序的堆內(nèi)存直接從正常水平飆到接近上限連續(xù)幾次導(dǎo)出直接觸發(fā)頻繁Full GC。那次排查引向一個(gè)結(jié)論Freemarker的process方法是流式寫出的你不該把它拼成完整字符串。改成直接把OutputStreamWriter傳給process后內(nèi)存占用降了一個(gè)數(shù)量級(jí)。還有個(gè)容易被忽略的點(diǎn)模板里的#list內(nèi)盡量少調(diào)用復(fù)雜的自定義函數(shù)Freemarker每行數(shù)據(jù)都會(huì)重新解析表達(dá)式函數(shù)太重時(shí)單行渲染時(shí)間會(huì)飆升數(shù)據(jù)量一大性能就崩。提前把能算好的值全放進(jìn)數(shù)據(jù)模型模板保持“傻白甜”狀態(tài)性能最穩(wěn)。5.5 模板改了不生效Freemarker緩存問(wèn)題項(xiàng)目里同事改完模板刷新頁(yè)面發(fā)現(xiàn)還是舊樣式第一反應(yīng)是瀏覽器緩存實(shí)際上Freemarker自己會(huì)緩存模板。Debug階段建議關(guān)掉緩存或者把更新延遲設(shè)成0cfg.setTemplateUpdateDelayMilliseconds(0);生產(chǎn)環(huán)境再改回默認(rèn)緩存策略或者干脆只在發(fā)版時(shí)更新模板。這個(gè)機(jī)制平時(shí)是好事能少很多模板解析開銷但也正是因?yàn)樗疤奖恪比菀鬃屓嗽陂_發(fā)期誤以為模板沒保存成功。6. 我的個(gè)人建議什么時(shí)候繼續(xù)用模板什么時(shí)候回頭用POI6.1 模板目錄的權(quán)限與控制模板文件一定要放在項(xiàng)目資源目錄或者獨(dú)立配置目錄里不要散落在業(yè)務(wù)代碼中。我習(xí)慣建一個(gè)templates/excel目錄每個(gè)模板單獨(dú)一個(gè).ftl文件命名跟業(yè)務(wù)報(bào)表一一對(duì)應(yīng)。生產(chǎn)環(huán)境模板目錄只給運(yùn)維和核心開發(fā)寫權(quán)限防止業(yè)務(wù)方直接改了線上文件卻沒人知道。版本管理里模板和代碼一樣走審批別小看這一步模板出錯(cuò)造成的線上事故一點(diǎn)都不比代碼少。6.2 分工方式Excel模板由誰(shuí)維護(hù)這套方案最大的隱性收益是“樣式需求不再全壓在開發(fā)頭上”。業(yè)務(wù)方想要換個(gè)表頭底色、加一列、調(diào)列寬只要給到調(diào)好的Excel模板開發(fā)人員另存為XML后替換占位符即可。實(shí)際操作中我不建議完全交給業(yè)務(wù)方做畢竟另存為XML并改造占位符還是需要一點(diǎn)基本功的但至少他們可以先把樣式調(diào)到滿意開發(fā)只做數(shù)據(jù)接入。6.3 一套判斷標(biāo)準(zhǔn)最后給一張我在項(xiàng)目里用的簡(jiǎn)表直接對(duì)照著選需求特征推薦方案原因表頭復(fù)雜、樣式固定Freemarker XML Spreadsheet 2003樣式復(fù)用集中改樣式不動(dòng)代碼行數(shù)動(dòng)態(tài)、分組動(dòng)態(tài)Freemarker Java端預(yù)處理行號(hào)模板保持簡(jiǎn)單復(fù)雜計(jì)算收進(jìn)Java需要內(nèi)置公式自動(dòng)匯總Freemarker Formula CalcMode Auto天然支持相對(duì)引用多個(gè)Sheet固定模板Freemarker Worksheet循環(huán)一次渲染多表需要嵌入圖片、圖表POI模板方案對(duì)圖片支持太弱幾十萬(wàn)行大數(shù)據(jù)量POI SXSSF流式寫盤避免內(nèi)存暴漲需要生成全新未知結(jié)構(gòu)POI模板無(wú)法窮舉所有結(jié)構(gòu)文本導(dǎo)出的思路聽起來(lái)“不夠原生”但只要你看透它背后是Excel官方支持的交換格式就會(huì)明白這不是歪門邪道而是一條被低估的工程捷徑。我個(gè)人在實(shí)際項(xiàng)目里的體會(huì)是把復(fù)雜Excel導(dǎo)出的需求拆成“樣式”和“數(shù)據(jù)”兩半樣式的職責(zé)歸模板數(shù)據(jù)的職責(zé)歸Java兩者通過(guò)Freemarker匯合后續(xù)改動(dòng)成本能壓縮到非常小。如果你的項(xiàng)目正好卡在“表頭改一次我就得改一次代碼”的循環(huán)里不妨找個(gè)周末拿一個(gè)真實(shí)報(bào)表把這條鏈路完整跑一遍。