Java怎么实现导出Excel

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

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值