一、分析
????????原生的poi導(dǎo)出匿垄，最麻煩的就是導(dǎo)出excel樣式的設(shè)置移宅，十分麻煩。于是有一種想法椿疗，就是以導(dǎo)入的思路做導(dǎo)出漏峰，就像我們知道的那樣，導(dǎo)入excel進(jìn)內(nèi)存也必然對(duì)象化了届榄，那么我們只要替換對(duì)應(yīng)的業(yè)務(wù)數(shù)據(jù)不就可以了嗎浅乔？
? ? ? ? 正如所想的那樣，可以實(shí)現(xiàn)的痒蓬，只是有人做到了童擎，也就是有現(xiàn)成的工具類可以使用，說(shuō)道本文的主角攻晒，那就是jxls-poi.看名字也知道必然依賴poi，沒(méi)錯(cuò)必須依賴poi班挖，只是poi的很多版本存在兼容的問(wèn)題鲁捏，略微有點(diǎn)坑。
? ? ? ? jxls是一個(gè)簡(jiǎn)單的萧芙、輕量級(jí)的excel導(dǎo)出庫(kù),使用特定的標(biāo)記在excel模板文件中來(lái)定義輸出格式和布局给梅，底層應(yīng)該是利用反射做的映射，這里本文不針對(duì)jxls的具體實(shí)現(xiàn)原理做探究双揪，本文重點(diǎn)在于記錄一下jxls的使用动羽。(有空再做研究，有空的話或者需要的話)渔期。
? ? ? ? jxls-poi的使用相對(duì)來(lái)說(shuō)运吓，是比較簡(jiǎn)單的渴邦，但是其功能是比較強(qiáng)大的。最關(guān)鍵的是對(duì)jxls的標(biāo)記語(yǔ)言的熟悉與使用拘哨，當(dāng)然不熟悉也沒(méi)關(guān)系谋梭，因?yàn)槲乙膊皇呛苁煜さ摹１疚挠涗浀氖褂冒咐腔緦?duì)導(dǎo)出excel來(lái)說(shuō)是通用的倦青，多sheet頁(yè)瓮床，當(dāng)然還沒(méi)用多行遍歷集合數(shù)據(jù)。重在在使用中進(jìn)行摸索产镐，不采坑不進(jìn)步~
二隘庄、導(dǎo)出模板
批注的使用
//指定模板的范圍
jx:area(lastCell="L56")?
//items對(duì)導(dǎo)出數(shù)據(jù)進(jìn)行遍歷，var遍歷出的每一項(xiàng)數(shù)據(jù)癣亚，lastCell指定模板的范圍峭沦，multisheet多sheet的對(duì)應(yīng)sheet名稱集合
jx:each(items="aiFormulaResultData", var="data", lastCell="L56" multisheet="aiFormulaResultDataSheetNames")
三、業(yè)務(wù)代碼
@RequestMapping("/downloadExcel.action")
public void downloadExcel(HttpServletRequest request, HttpServletResponse response)throws IOException {
????List?data =dataService.getData();
????List dataSheetNames =new ArrayList<>(data?.size());
????//sheetName
????String templateSheetName1 = ConstVar.SHEET_DATA1;
????String templateSheetName2 = ConstVar.SHEET_DATA2;
????for (int i =0; i < data.size(); i++) {
????????dataSheetNames.add((String)data.get(i).get("code"));
????}
????Map bindDataMap =new HashMap<>();
????bindDataMap.put("aiChemicalDataSheetNames", aiChemicalDataSheetNames);
????bindDataMap.put("aiChemicalDataList", chemicalData);
????//獲取獲取模板? 國(guó)際化
????String templateFileName = RequestContextUtils.getLocale(request).equals(Locale.SIMPLIFIED_CHINESE) ? ConstVar.AI_EXCEL_CHINESE_NAME : ConstVar.AI_EXCEL_ENGLISH_NAME;
????File tempTargetFile =new File(CommonUtil.getGuid() +".xlsx");
????InputStream inputStreamClassPath =null;
????InputStream inputStream =null;
try {
????inputStreamClassPath =new ClassPathResource("downloadTemplateExcel/" + templateFileName).getInputStream();
????//讀取模板-寫(xiě)入數(shù)據(jù)-刪除模板sheet
????JxlsExportTemplateExcelUtils.exportExcelAndDeleteTemplateSheet(inputStreamClassPath, tempTargetFile, bindDataMap, templateSheetName1, templateSheetName2);
????inputStream =new FileInputStream(tempTargetFile);
????//設(shè)置response頭信息
????response.reset();
????String title = CommonUtil.encodingFileName(I18nUtils.getMessage("chemical.pdfTitleName"));
????response.setContentType("application/octet-stream");
????response.setHeader("Content-Length", String.valueOf(inputStream.available()));
????response.setHeader("Content-Disposition","attachment;filename=" + title + CommonUtil.getCurrentDate() +".xlsx");
????OutputStream out = response.getOutputStream();
????//創(chuàng)建緩沖區(qū)
????FileCopyUtils.copy(new BufferedInputStream(inputStream),new BufferedOutputStream(out));
}catch (Exception e) {
????logger.error("下載EXCEL出現(xiàn)異常 " + CommonUtil.getExceptionDetail(e));
}finally {
????if (inputStream !=null) {
????????inputStream.close();
????}
????if (inputStreamClassPath !=null) {
????????inputStreamClassPath.close();
????}
????if (tempTargetFile !=null && tempTargetFile.exists()) {
????????tempTargetFile.delete();
????}
}
}
四逃糟、工具類
JxlsExportTemplateExcelUtils
//對(duì)外提供
public static void exportExcelAndDeleteTemplateSheet(InputStream templateFileInputStreamn, File tempTargetFile, Map bindDataMap, String... templateSheetNames)throws FileNotFoundException, IOException {
exportExcel(templateFileInputStreamn, tempTargetFile, bindDataMap, templateSheetNames);
}
//多種重載
private static void exportExcel(InputStream is, File out, Map model, String... sheetNames)throws IOException {
? ? OutputStream os =new FileOutputStream(out);
Context context = PoiTransformer.createInitialContext();
if (model !=null) {
for (Map.Entry entry : model.entrySet()) {
context.putVar(entry.getKey(), entry.getValue());
}
}
JxlsHelper jxlsHelper = JxlsHelper.getInstance();
//必須要這個(gè)吼鱼，否者表格函數(shù)統(tǒng)計(jì)會(huì)錯(cuò)亂
? ?jxlsHelper.getInstance().setUseFastFormulaProcessor(false).processTemplate(context, jxlsHelper.createTransformer(is, os));
//刪除對(duì)應(yīng)的模板sheet頁(yè)
? ? deleteSheet(out, sheetNames);
}
//刪除對(duì)應(yīng)的模板sheet頁(yè)
public static void deleteSheet(File file, String... sheetNames) {
try {
FileInputStream fis =new FileInputStream(file);
XSSFWorkbook wb =new XSSFWorkbook(fis);
fileWrite(file, wb);
//刪除Sheet
? ? ? ? for (String sheetName : sheetNames) {
wb.removeSheetAt(wb.getSheetIndex(sheetName));
}
fileWrite(file, wb);
fis.close();
}catch (Exception e) {
//e.printStackTrace();
? ? }
}
五、maven依賴
<!--項(xiàng)目中已使用poi绰咽，此處排除-->
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-poi</artifactId>
<version>1.1.0</version>
<exclusions>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
</exclusion>
</exclusions>
</dependency>
<!--POI-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
六菇肃、總結(jié)
整體來(lái)說(shuō)，是相對(duì)簡(jiǎn)單的取募，思路與想法很重要琐谤，只要你能想到的，一般就已經(jīng)有人做到了玩敏，而且做得還不錯(cuò)斗忌。但是需要在實(shí)踐中發(fā)現(xiàn)問(wèn)題，解決問(wèn)題旺聚。
