6、jeecg 笔记之自定义excel 模板导出（一）

叁歲伎倆 2023-06-01 03:23 52阅读 0赞

# [6、jeecg 笔记之 自定义excel 模板导出（一）][6_jeecg _ _excel] #

## 1、前言 ##

jeecg 中已经自带 excel 的导出导出功能，其所使用的是 easypoi，尽管所导出的 excel 能满足大部分需求，

但总是有需要用到自定义 excel 导出模板，下文所用到的皆是 easypoi 提供的，为方便下次翻阅，故记之。

## 2、代码部分 ##

### 2.1、controller ###

![复制代码][copycode.gif]

@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();
        }

![复制代码][copycode.gif]

### 2.2、entity 实体类 ###

TemplateExcelExportEntity.class

![复制代码][copycode.gif]

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 ...
    
    }

![复制代码][copycode.gif]

BudgetAccountsEntity.class（预算）

![复制代码][copycode.gif]

public class BudgetAccountsEntity {
    
        @Excel(name = "编码")
        private String code;
    
        @Excel(name = "名称")
        private String name;

get set ...

}

![复制代码][copycode.gif]

PayeeEntity.class（收款人）

![复制代码][copycode.gif]

public class PayeeEntity {
    
        @Excel(name = "全称")
        private String name;
    
        @Excel(name = "银行账号")
        private String bankAccount;
    
        @Excel(name = "开户银行")
        private String bankName;
    
        get set ...
    
    }

![复制代码][copycode.gif]

### 2.3、导出工具类 ExcelExportUtil.class ###

![复制代码][copycode.gif]

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);
        }
    
    }

![复制代码][copycode.gif]

### 2.4、excel 模板 ###

需要用到的模板 exportMyExcel.xls

[https://files.cnblogs.com/files/niceyoo/exportMyExcel.rar][https_files.cnblogs.com_files_niceyoo_exportMyExcel.rar]

![1104426-20181217131540282-1473144831.png][]

### 2.5、调用地址 ###

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

### 2.6、效果图 ###

![1104426-20181217131028251-306298667.png][]

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

转载于:https://www.cnblogs.com/Jeely/p/11309356.html

[6_jeecg _ _excel]: https://www.cnblogs.com/niceyoo/p/10130923.html
[copycode.gif]: /images/20230601/0152f38b1e8a4c8a8ac4b208082c3f1a.png
[https_files.cnblogs.com_files_niceyoo_exportMyExcel.rar]: https://files.cnblogs.com/files/niceyoo/exportMyExcel.rar
[1104426-20181217131540282-1473144831.png]: /images/20230601/0d9375f9cdb64bcbbb25b97ef9a4e3ba.png
[1104426-20181217131028251-306298667.png]: /images/20230601/192506fbfda5465b8d0159cfa53b9ef5.png