使用的工具类:org.jeecgframework.poi.excel.ExcelExportUtil
源码如下:
public class ExcelExportUtil {
private ExcelExportUtil() {
}
/**
* @param entity
* 表格标题属性
* @param pojoClass
* Excel对象Class
* @param dataSet
* Excel对象数据List
*/
public static Workbook exportBigExcel(ExportParams entity, Class<?> pojoClass,
Collection<?> dataSet) {
ExcelBatchExportServer batachServer = ExcelBatchExportServer
.getExcelBatchExportServer(entity, pojoClass);
return batachServer.appendData(dataSet);
}
public static void closeExportBigExcel() {
ExcelBatchExportServer batachServer = ExcelBatchExportServer.getExcelBatchExportServer(null,
null);
batachServer.closeExportBigExcel();
}
/**
* @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() < 10000) {
workbook = new XSSFWorkbook();
} else {
workbook = new SXSSFWorkbook();
}
new ExcelExportServer().createSheet(workbook, entity, pojoClass, dataSet);
return workbook;
}
/**
* 根据Map创建对应的Excel
* @param entity
* 表格标题属性
* @param entityList
* Map对象列表
* @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() < 10000) {
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, ExcelType 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
*/
@Deprecated
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);
}
/**
* 导出文件通过模板解析只有模板,没有集合
* 每个sheet对应一个map,导出到处,key是sheet的NUM
* @param params
* 导出参数类
* @param map
* 模板集合
* @return
*/
public static Workbook exportExcel(Map<Integer, Map<String, Object>> map,
TemplateExportParams params) {
return new ExcelExportOfTemplateUtil().createExcleByTemplate(params, map);
}
}
下面使用public static Workbook exportExcel(ExportParams entity, Class<?> pojoClass, Collection<?> dataSet)
方法来实现excel的导出功能。其方法参数如下:
- @param entity
-
表格标题属性 一般为ExcelBaseParams及其子类,如ExportParams类,它定义了Excel 的导出参数,如表格名称,表的大小等
- @param pojoClass
-
Excel对象Class 简单说就是excel有什么列名
- @param dataSet
-
Excel对象数据List List的元素对应着Excel的一行,也就是一个pojoClass实例。
PeopleExcel.class:
class PeopleExcel{
private Integer id;
private String name;
private Integer age;
}
Controller:输入地址直接打印出Excel:
@RequestMapping("/peopleExcelList")
@ResponseBody
public void queryList(HttpServletResponse response) throws Exception {
Map<String, Object> params = new HashMap<>();
List<PeopleExcel> peopleExcelList = peopleExcelService.exportTo(params);
response.setHeader("content-Type", SysConstants.ExportQueue.EXPORT_EXCEL_CONTENT_TYPE);
String fileName = "Excel导出例子.xls";
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), PeopleExcel.class, peopleExcelList );
workbook.write(response.getOutputStream());
}
Service:调用mapper方法取出PeopleExcel列表
Service接口:
List<PeopleExcel> exportTo(Map<String, Object> params);
Service接口:实现类:
@Override
public List<PeopleExcel> exportTo(Map<String, Object> params) {
params.put("pageBegin", 0);
params.put("pageSize", 30000);
List<PeopleExcel> list = peopleExcelMapper.selectList(params);
return list ;
}
Mapper:取出数据库查询的值
Mapper接口
List<PeopleExcel> selectList(Map<String, Object> param);
Mapper.xml
<select id="selectList" parameterType="Object" resultType="PeopleExcel">
select p.* from peopel p
ORDER BY p.id DESC
limit #{pageBegin},#{pageSize}
</select>
启动服务器,如tomcat,输入地址:http://localhost:8080/peopleExcelList
即可打印出excel