超级简单POI多sheet导出Excel实战
本章节主要基于上一章节单sheet导出的基础上进行改造实现多sheet的导出,上一章节参考地址:https://www.cnblogs.com/sunny1009/p/11437005.html
1.数据准备
这里导出两个sheet为例进行讲解,第一个sheet导出学生基本信息,表结构和数据参考上一章节,第二个sheet导出区域基本信息,具体数据和脚本如下
CREATE TABLE `td_area` ( `id` mediumint(7) unsigned NOT NULL AUTO_INCREMENT, `level` tinyint(1) unsigned NOT NULL COMMENT '层级', `parent_code` bigint(14) unsigned NOT NULL DEFAULT '0' COMMENT '父级行政代码', `area_code` bigint(14) unsigned NOT NULL DEFAULT '0' COMMENT '行政代码', `zip_code` mediumint(6) unsigned zerofill NOT NULL DEFAULT '000000' COMMENT '邮政编码', `city_code` char(6) NOT NULL DEFAULT '' COMMENT '区号', `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称', `short_name` varchar(50) NOT NULL DEFAULT '' COMMENT '简称', `merger_name` varchar(50) NOT NULL DEFAULT '' COMMENT '组合名', `pinyin` varchar(30) NOT NULL DEFAULT '' COMMENT '拼音', `lng` decimal(10,6) NOT NULL DEFAULT '0.000000' COMMENT '经度', `lat` decimal(10,6) NOT NULL DEFAULT '0.000000' COMMENT '纬度', PRIMARY KEY (`id`), UNIQUE KEY `idx` (`area_code`) USING BTREE ) ENGINE=MyISAM AUTO_INCREMENT=778093 DEFAULT CHARSET=utf8 COMMENT='中国行政地区表';
INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('1', '0', '0', '110000000000', '000000', '', '北京市', '北京', '北京', 'BeiJing', '116.407526', '39.904030'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('7509', '0', '0', '120000000000', '000000', '', '天津市', '天津', '天津', 'TianJin', '117.200983', '39.084158'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('13412', '0', '0', '130000000000', '000000', '', '河北省', '河北', '河北', 'HeBei', '114.468664', '38.037057'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('69444', '0', '0', '140000000000', '000000', '', '山西省', '山西', '山西', 'ShanXi', '112.562398', '37.873531'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('100757', '0', '0', '150000000000', '000000', '', '内蒙古自治区', '内蒙古', '内蒙古', 'NeiMengGu', '111.765617', '40.817498'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('116752', '0', '0', '210000000000', '000000', '', '辽宁省', '辽宁', '辽宁', 'LiaoNing', '123.429440', '41.835441'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('134818', '0', '0', '220000000000', '000000', '', '吉林省', '吉林', '吉林', 'JiLin', '125.325990', '43.896536'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('147597', '0', '0', '230000000000', '000000', '', '黑龙江省', '黑龙江', '黑龙江', 'HeiLongJiang', '126.661669', '45.742347'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('163862', '0', '0', '310000000000', '000000', '', '上海市', '上海', '上海', 'ShangHai', '121.473701', '31.230416'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('170127', '0', '0', '320000000000', '000000', '', '江苏省', '江苏', '江苏', 'JiangSu', '118.763232', '32.061707'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('194122', '0', '0', '330000000000', '000000', '', '浙江省', '浙江', '浙江', 'ZheJiang', '120.152791', '30.267446'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('226257', '0', '0', '340000000000', '000000', '', '安徽省', '安徽', '安徽', 'AnHui', '117.284922', '31.861184'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('246320', '0', '0', '350000000000', '000000', '', '福建省', '福建', '福建', 'FuJian', '119.295144', '26.100779'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('264771', '0', '0', '360000000000', '000000', '', '江西省', '江西', '江西', 'JiangXi', '115.909228', '28.675696'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('288128', '0', '0', '370000000000', '000000', '', '山东省', '山东', '山东', 'ShanDong', '117.020359', '36.668530'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('368154', '0', '0', '410000000000', '000000', '', '河南省', '河南', '河南', 'HeNan', '113.753602', '34.765515'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('422993', '0', '0', '420000000000', '000000', '', '湖北省', '湖北', '湖北', 'HuBei', '114.341861', '30.546498'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('453480', '0', '0', '430000000000', '000000', '', '湖南省', '湖南', '湖南', 'HuNan', '112.983810', '28.112444'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('485044', '0', '0', '440000000000', '000000', '', '广东省', '广东', '广东', 'GuangDong', '113.266530', '23.132191'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('513496', '0', '0', '450000000000', '000000', '', '广西壮族自治区', '广西', '广西', 'GuangXi', '108.327546', '22.815478'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('531384', '0', '0', '460000000000', '000000', '', '海南省', '海南', '海南', 'HaiNan', '110.349228', '20.017377'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('534995', '0', '0', '500000000000', '000000', '', '重庆市', '重庆', '重庆', 'ChongQing', '106.551556', '29.563009'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('547273', '0', '0', '510000000000', '000000', '', '四川省', '四川', '四川', 'SiChuan', '104.075931', '30.651651'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('605719', '0', '0', '520000000000', '000000', '', '贵州省', '贵州', '贵州', 'GuiZhou', '106.707410', '26.598194'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('624851', '0', '0', '530000000000', '000000', '', '云南省', '云南', '云南', 'YunNan', '102.710002', '25.045806'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('640898', '0', '0', '540000000000', '000000', '', '西藏自治区', '西藏', '西藏', 'XiZang', '91.117212', '29.646922'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('647166', '0', '0', '610000000000', '000000', '', '陕西省', '陕西', '陕西', 'ShanXi', '108.954239', '34.265472'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('669143', '0', '0', '620000000000', '000000', '', '甘肃省', '甘肃', '甘肃', 'GanSu', '103.826308', '36.059421'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('688336', '0', '0', '630000000000', '000000', '', '青海省', '青海', '青海', 'QingHai', '101.780199', '36.620901'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('693528', '0', '0', '640000000000', '000000', '', '宁夏回族自治区', '宁夏', '宁夏', 'NingXia', '106.258754', '38.471317'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('696698', '0', '0', '650000000000', '000000', '', '新疆维吾尔自治区', '新疆', '新疆', 'XinJiang', '87.627704', '43.793026'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('713480', '0', '0', '7013135772653', '999077', '00852', '香港特别行政区', '香港', '香港', 'Hong Kong', '114.173355', '22.320048'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('713502', '0', '0', '7112407077174', '999078', '00853', '澳门特别行政区', '澳门', '澳门', 'Macau', '113.549090', '22.198951'); INSERT INTO `study`.`td_area` (`id`, `level`, `parent_code`, `area_code`, `zip_code`, `city_code`, `name`, `short_name`, `merger_name`, `pinyin`, `lng`, `lat`) VALUES ('713513', '0', '0', '7212684281636', '000000', '', '台湾省', '台湾', '台湾', 'Taiwan', '121.520076', '25.030724');
2.创建区域信息查询接口
package com.sunny.spring.boot.poi.service; import com.sunny.spring.boot.poi.pojo.TdArea; import java.util.List; /** * @ClassName: IAreaService * @Description: * @Author: sunt * @Date: 2019/9/2 15:49 * @Version 1.0 **/ public interface IAreaService { /** * 查询所有行政区域信息 * @return */ List<TdArea> queryAreaInfo(); }
3.实体Bean创建
package com.sunny.spring.boot.poi.pojo; import cn.afterturn.easypoi.excel.annotation.Excel; import lombok.Data; import lombok.EqualsAndHashCode; import lombok.experimental.Accessors; import java.io.Serializable; import java.math.BigDecimal; /** * <p> * 中国行政地区表 * </p> * * @author sunt * @since 2019-08-22 */ @Data @EqualsAndHashCode(callSuper = false) @Accessors(chain = true) public class TdArea implements Serializable { private static final long serialVersionUID = 1L; /** * 层级 */ @Excel(name = "层级", width = 20, orderNum = "1") private int level; /** * 父级行政代码 */ @Excel(name = "父级行政代码", width = 20, orderNum = "1") private Long parentCode; /** * 行政代码 */ @Excel(name = "行政代码", width = 20, orderNum = "1") private Long areaCode; /** * 邮政编码 */ @Excel(name = "邮政编码", width = 20, orderNum = "1") private Integer zipCode; /** * 区号 */ @Excel(name = "区号", width = 20, orderNum = "1") private String cityCode; /** * 名称 */ @Excel(name = "名称", width = 20, orderNum = "1") private String name; /** * 简称 */ @Excel(name = "简称", width = 20, orderNum = "1") private String shortName; /** * 组合名 */ @Excel(name = "组合名", width = 20, orderNum = "1") private String mergerName; /** * 拼音 */ @Excel(name = "拼音", width = 20, orderNum = "1") private String pinyin; /** * 经度 */ @Excel(name = "经度", width = 20, orderNum = "1") private BigDecimal lng; /** * 纬度 */ @Excel(name = "纬度", width = 20, orderNum = "1") private BigDecimal lat; }
4.导出Controller
具体代码都有详细注释
/** * 多个sheet的导出 * 第一个sheet展示学生基本信息 * 第二个sheet展示区域信息基本信息 * 步骤: * 构建一个List<Map<String, Object>> list 多个Map key title 对应表格Title key entity 对应表格对应实体 key data * 具体操作看代码 * @param response */ @RequestMapping("/exportMoreSheet") public void exportMoreSheet(HttpServletResponse response) { //1.分别构建学生与区域信息导出参数 ExportParams stuParam = new ExportParams(); ExportParams areaParam = new ExportParams(); stuParam.setSheetName("学生基本信息"); stuParam.setHeight((short) 8); stuParam.setStyle(ExcelExportMyStylerImpl.class); areaParam.setSheetName("区域基本信息"); areaParam.setHeight((short) 8); areaParam.setStyle(ExcelExportMyStylerImpl.class); //2.构建分别填充数据:data(数据)、title(标题)、entity(导出属性) Map<String, Object> exportStuMap = new HashMap<String, Object>(); Map<String, Object> exportAreaMap = new HashMap<String, Object>(); exportStuMap.put("data", studentService.queryAllStudent()); exportStuMap.put("title", stuParam); exportStuMap.put("entity", StudentInfoBean.class); exportAreaMap.put("data", areaService.queryAreaInfo()); exportAreaMap.put("title", areaParam); exportAreaMap.put("entity", TdArea.class); //3.组装参数 List<Map<String, Object>> sheetsList = new ArrayList<Map<String, Object>>(); sheetsList.add(exportStuMap); sheetsList.add(exportAreaMap); try { //4.调用导出接口 Workbook workbook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF); String fileName = "多sheet导出"+new SimpleDateFormat("yyyyMMdd").format(new Date()); fileName = URLEncoder.encode(fileName, "UTF8"); response.setContentType("application/vnd.ms-excel;chartset=utf-8"); response.setHeader("Content-Disposition", "attachment;filename="+fileName + ".xls"); ServletOutputStream out=response.getOutputStream(); workbook.write(out); out.flush(); out.close(); } catch (Exception e) { e.printStackTrace(); } }
5.测试
浏览器访问导出Controller:http://127.0.0.1:8080/export/exportMoreSheet
第二个sheet的值:
最新同步更新地址:https://www.sunnyblog.top/
感谢您花时间阅读此篇文章,如果您觉得这篇文章你学到了东西也是为了犒劳下博主的码字不易不妨打赏一下吧,让博主能喝上一杯咖啡,在此谢过了!
如果您觉得阅读本文对您有帮助,请点一下左下角“推荐”按钮,您的“推荐”将是我最大的写作动力!另外您也可以选择【关注我】,可以很方便找到我!
本文版权归作者和博客园共有,来源网址:https://www.cnblogs.com/sunny1009 欢迎各位转载,但是未经作者本人同意,转载文章之后必须在文章页面明显位置给出作者和原文连接,否则保留追究法律责任的权利!