摘要：
1.在前言中，JEECG有自己的Excel导出功能，它使用easypoi。尽管导出的Excel可以满足大多数要求，但始终需要使用用户定义的Excel导出模板。以下所有内容均由easypoi提供。为了便于下次复习，请记住这一点。
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