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