Java怎么实现导出Excel

以你之姓@ 2021-11-11 03:54 262阅读 0赞

使用的工具类：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