6、jeecg 筆記之 自定義excel 模板導出（一）

1、前言

jeecg 中已經自帶 excel 的導出導出功能，其所使用的是 easypoi，盡管所導出的 excel 能滿足大部分需求，

但總是有需要用到自定義 excel 導出模板，下文所用到的皆是 easypoi 提供的，為方便下次翻閱，故記之。

?

2、代碼部分

2.1、controller

@RequestMapping("/myExcel")public void myExcel(JeecgDemoExcelEntity jeecgDemoExcel, HttpServletRequest request, HttpServletResponse response)throws IOException {TemplateExportParams params = new TemplateExportParams(request.getServletContext().getRealPath("/") + "export/template/exportMyExcel.xls");params.setHeadingStartRow(3);params.setHeadingRows(2);Map<String, Object> map = new HashMap<String, Object>();map.put("date", "2014-12-25");map.put("money", 2000000.00);map.put("upperMoney", "貳佰萬");map.put("company", "執筆潛行科技有限公司");map.put("bureau", "財政局");map.put("person", "JueYue");map.put("phone", "1879740****");List<TemplateExcelExportEntity> list = new ArrayList<TemplateExcelExportEntity>();for (int i = 0; i < 4; i++) {TemplateExcelExportEntity entity = new TemplateExcelExportEntity();entity.setIndex(i + 1 + "");entity.setAccountType("開源項目");entity.setProjectName("EasyPoi " + i + "期");entity.setAmountApplied(i * 10000 + "");entity.setApprovedAmount((i + 1) * 10000 - 100 + "");List<BudgetAccountsEntity> budgetAccounts = Lists.newArrayList();for (int j = 0; j < 1; j++) {BudgetAccountsEntity accountsEntity = new BudgetAccountsEntity();accountsEntity.setCode("A001");accountsEntity.setName("設計");budgetAccounts.add(accountsEntity);accountsEntity = new BudgetAccountsEntity();accountsEntity.setCode("A002");accountsEntity.setName("開發");budgetAccounts.add(accountsEntity);}entity.setBudgetAccounts(budgetAccounts);PayeeEntity payeeEntity = new PayeeEntity();payeeEntity.setBankAccount("6222 0000 1234 1234");payeeEntity.setBankName("中國銀行");payeeEntity.setName("小明");entity.setPayee(payeeEntity);list.add(entity);}Workbook workbook = ExcelExportUtil.exportExcel(params, TemplateExcelExportEntity.class, list, map);File savefile = new File("D:/excel/");if (!savefile.exists()) {savefile.mkdirs();}FileOutputStream fos = new FileOutputStream("D:/excel/專項支出用款申請書.xls");workbook.write(fos);fos.close();}

2.2、entity 實體類

TemplateExcelExportEntity.class

public class TemplateExcelExportEntity implements Serializable { private static final long serialVersionUID = 1L;@Excel(name = "序號")private String index;@Excel(name = "資金性質")private String accountType;@ExcelCollection(name = "預算科目")private List<BudgetAccountsEntity> budgetAccounts;@Excel(name = "項目名稱")private String projectName;@ExcelEntity(name = "收款人")private PayeeEntity payee;@Excel(name = "申請金額")private String amountApplied;@Excel(name = "核定金額")private String approvedAmount;
　　 get set ...
}

BudgetAccountsEntity.class（預算）

public class BudgetAccountsEntity {@Excel(name = "編碼")private String code;@Excel(name = "名稱")private String name; get set ... }

PayeeEntity.class（收款人）

public class PayeeEntity {@Excel(name = "全稱")private String name;@Excel(name = "銀行賬號")private String bankAccount;@Excel(name = "開戶銀行")private String bankName;get set ...}

?

2.3、導出工具類?ExcelExportUtil.class

public final class ExcelExportUtil {private ExcelExportUtil() {}/*** @param entity* 表格標題屬性* @param pojoClass* Excel對象Class* @param dataSet* Excel對象數據List*/public static Workbook exportExcel(ExportParams entity, Class<?> pojoClass, Collection<?> dataSet) {Workbook workbook;if (ExcelType.HSSF.equals(entity.getType())) {workbook = new HSSFWorkbook();} else if (dataSet.size() < 1000) {workbook = new XSSFWorkbook();} else {workbook = new SXSSFWorkbook();}new ExcelExportServer().createSheet(workbook, entity, pojoClass, dataSet);return workbook;}/*** 根據Map創建對應的Excel* * @param entity* 表格標題屬性* @param pojoClass* Excel對象Class* @param dataSet* Excel對象數據List*/public static Workbook exportExcel(ExportParams entity, List<ExcelExportEntity> entityList, Collection<? extends Map<?, ?>> dataSet) {Workbook workbook;if (ExcelType.HSSF.equals(entity.getType())) {workbook = new HSSFWorkbook();} else if (dataSet.size() < 1000) {workbook = new XSSFWorkbook();} else {workbook = new SXSSFWorkbook();}new ExcelExportServer().createSheetForMap(workbook, entity, entityList, dataSet);return workbook;}/*** 一個excel 創建多個sheet* * @param list* 多個Map key title 對應表格Title key entity 對應表格對應實體 key data* Collection 數據* @return*/public static Workbook exportExcel(List<Map<String, Object>> list, String type) {Workbook workbook;if (ExcelType.HSSF.equals(type)) {workbook = new HSSFWorkbook();} else {workbook = new XSSFWorkbook();}for (Map<String, Object> map : list) {ExcelExportServer server = new ExcelExportServer();server.createSheet(workbook, (ExportParams) map.get("title"), (Class<?>) map.get("entity"), (Collection<?>) map.get("data"));}return workbook;}/*** 導出文件通過模板解析,不推薦這個了,推薦全部通過模板來執行處理* * @param params* 導出參數類* @param pojoClass* 對應實體* @param dataSet* 實體集合* @param map* 模板集合* @return*/public static Workbook exportExcel(TemplateExportParams params, Class<?> pojoClass, Collection<?> dataSet, Map<String, Object> map) {return new ExcelExportOfTemplateUtil().createExcleByTemplate(params, pojoClass, dataSet, map);}/*** 導出文件通過模板解析只有模板,沒有集合* * @param params* 導出參數類* @param map* 模板集合* @return*/public static Workbook exportExcel(TemplateExportParams params, Map<String, Object> map) {return new ExcelExportOfTemplateUtil().createExcleByTemplate(params, null, null, map);}}

?

2.4、excel 模板

需要用到的模板 exportMyExcel.xls

https://files.cnblogs.com/files/niceyoo/exportMyExcel.rar

?

?

2.5、調用地址

http://localhost:8080/項目名/XxxController/myExcel.do

?

2.6、效果圖

?

博客地址：http://www.cnblogs.com/niceyoo

?

posted @ 2018-12-17 13:13 niceyoo 閱讀(...) 評論(...) 編輯 收藏

總結

以上是生活随笔為你收集整理的6、jeecg 笔记之 自定义excel 模板导出（一）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。