欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

超级简单POI多sheet导出Excel实战

程序员文章站 2022-10-15 16:13:20
超级简单POI多sheet导出Excel实战,与现有系统无缝对接 ......

本章节主要基于上一章节单sheet导出的基础上进行改造实现多sheet的导出,上一章节参考地址:

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', '', '**自治区', '*', '*', '*', '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', '香港特别行政区', '香港', '香港', '*', '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', '', '*省', '*', '*', '*', '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

超级简单POI多sheet导出Excel实战

 

第二个sheet的值:

超级简单POI多sheet导出Excel实战