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