实战通用导出excel工具适配多数据源任意表数据

标签: 自研小工具

以一个多数据源的demo项目为例介绍

DEMO项目源码地址: 传送门

1.项目搭建

1.1.Maven依赖

 <dependency>
   <groupId>com.xiaoqiang.tool.general.excel</groupId>
   <artifactId>general-export-tool</artifactId>
   <version>1.0.0</version>
</dependency>

1.2.配置信息

示例项目: 数据源 3 存储 gen_excel任务表与模板表,这里只贴了一下重要的配置,详见源码

server.port=8083

#数据源 >> 3 >>  配置
#连接地址
spring.datasource.test3.url=jdbc:mysql://localhost:3306/gen_excel_demo?useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false&serverTimezone=UTC&useSSL=false
#用户名
spring.datasource.test3.username=root
#密码
spring.datasource.test3.password=root


# redis config
redis.host=xxxx
redis.port=6699
redis.password=xxxx
redis.connectTimeout=10000


oss.accessId=xxx
oss.accessKey=xxxxx
oss.endpoint=oss-cn-hangzhou.aliyuncs.com
oss.bucket=gen-excel
oss.defaultDir=excel/

1.3.数据源配置

3.1业务数据源
注:业务数据源配置示例

@Configuration
@MapperScan(basePackages = {"com.example.mapper.db1", GenExcelMapperConstant.MAPPER_COMMON_PACKAGE}, sqlSessionTemplateRef = "test1SqlSessionTemplate")
public class DataSource1Config {

    /**
     * 数据源配置
     * 使用的连接池是 DruidDataSource
     */
    @Primary
    @Bean(name = "test1DataSource")
    @Qualifier("test1DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.test1")
    public DataSource testDataSource() {
        //这里指定使用类型 -- 阿里DruidDataSource 连接池
        return DataSourceBuilder.create().type(DruidDataSource.class).build();
    }


    /**
     * 创建 SqlSessionFactory 工厂
     */
    @Primary
    @Bean(name = "test1SqlSessionFactory")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("test1DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        //数据源
        bean.setDataSource(dataSource);

        //mapper文件 扫描地址
        Resource[] resources = GenExcelMapperLocationUtil.init()
                //当前数据源-业务xml地址
                .addMapperLocations("classpath:mybatis/mapper/db1/*.xml")
                //gen-excel 公共导出xml地址配置
                .addMapperLocations(GenExcelMapperConstant.XML_COMMON_LOCATIONS)
                .build();

        //mapper 地址
        bean.setMapperLocations(resources);
        return bean.getObject();
    }

    /**
     * 事务管理
     */
    @Primary
    @Bean(name = "test1TransactionManager")
    public DataSourceTransactionManager testTransactionManager(@Qualifier("test1DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     * MyBatis提供的持久层访问模板化的工具
     * 线程安全,可通过构造参数或依赖注入SqlSessionFactory实例
     */
    @Primary
    @Bean(name = "test1SqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

需要将配置引入
image.png

3.2 gen-excel数据源
注:任务表与模板表所在数据源

@Configuration
@MapperScan(basePackages = {"com.example.mapper.db3", GenExcelMapperConstant.MAPPER_ROOT_PACKAGE}, sqlSessionTemplateRef = "test3SqlSessionTemplate")
public class DataSource3Config {

    /**
     * 数据源配置
     * 使用的连接池是 DruidDataSource
     */
    @Bean(name = "test3DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.test3")
    public DataSource test3DataSource() {
        return DataSourceBuilder.create().type(DruidDataSource.class).build();
    }


    /**
     * 创建 SqlSessionFactory 工厂
     */
    @Bean(name = "test3SqlSessionFactory")
    public SqlSessionFactory test3SqlSessionFactory(@Qualifier("test3DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        //数据源
        bean.setDataSource(dataSource);

        //mapper文件 扫描地址
        Resource[] resource = GenExcelMapperLocationUtil.init()
                //项目业务xml 地址
                //.addMapperLocations("classpath:mybatis/mapper/db3/*.xml")
                //gen-excel xml地址配置
                .addMapperLocations(GenExcelMapperConstant.XML_ROOT_LOCATIONS)
                .build();

        //mapper 地址
        bean.setMapperLocations(resource);
        return bean.getObject();
    }


    /**
     * 事务管理
     */
    @Bean(name = "test3TransactionManager")
    public DataSourceTransactionManager test3TransactionManager(@Qualifier("test3DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     * MyBatis提供的持久层访问模板化的工具
     * 线程安全,可通过构造参数或依赖注入SqlSessionFactory实例。
     */
    @Bean(name = "test3SqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test3SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

image.png

1.4.Redis配置

@Data
@Configuration
@ConfigurationProperties(prefix = "redis")
public class RedisProperty {

    private String host;

    private int port;

    private int database = 0;

    private String password;

    private Integer connectTimeout;

}


@Configuration
public class SpringRedisConfig {

    @Autowired
    private RedisProperty redisProperty;

    @Bean
    public RedisCache redisCache() {
        RedisCache redisCache = new RedisCache();
        redisCache.setHost("redis://" + redisProperty.getHost() + ":" + redisProperty.getPort());
        redisCache.setPassword(redisProperty.getPassword());
        redisCache.setDatabase(redisProperty.getDatabase());
        redisCache.setConnectTimeout(redisProperty.getConnectTimeout());
        return redisCache;
    }
}   

1.5.公共导出-数据源与oss关系配置

注:多数据源项目适配,需要将使用哪一个数据源做导出配置好

@Configuration
public class GenExcelConfig {

    
    @Autowired
    @Qualifier("test1SqlSessionFactory")
    private SqlSessionFactory test1SqlSessionFactory;
    @Autowired
    @Qualifier("test2SqlSessionFactory")
    private SqlSessionFactory test2SqlSessionFactory;

    @Autowired
    @Qualifier("test3SqlSessionFactory")
    private SqlSessionFactory test3SqlSessionFactory;

    @Autowired
    private OssProperty ossProperty;
    
    /**
     * 数据源与SqlSessionFactory关系配置
     * 将数据源添加到配置
     */
    @Bean
    public GenExcelDataSourceConfig genExcelSqlSessionFactory() {
        return GenExcelDataSourceConfig.initDefault(16)
                //数据库编码 对应 export_template_info表 >  db_code
                .addSqlSessionFactory("DB1001", test1SqlSessionFactory)
                .addSqlSessionFactory("DB1002", test2SqlSessionFactory)
                .addSqlSessionFactory("DB1003", test3SqlSessionFactory);
    }
    
    /**
     * oss 配置
     */
    @Bean
    public GenExcelOssProperty genExcelOssProperty() {
        GenExcelOssProperty genExcelOssProperty = new GenExcelOssProperty();
        genExcelOssProperty.setAccessId(ossProperty.getAccessId());
        genExcelOssProperty.setAccessKey(ossProperty.getAccessKey());
        genExcelOssProperty.setBucket(ossProperty.getBucket());
        genExcelOssProperty.setDefaultDir(ossProperty.getDefaultDir());
        genExcelOssProperty.setEndpoint(ossProperty.getEndpoint());
        return genExcelOssProperty;
    }
    
}

6.启动类
注意:@ComponentScan(“com.tool.general.excel”)

/**
 * 启动类
 *
 * @author mengqiang
 * @date 2019-03-25
 */
@ComponentScans({@ComponentScan("com.tool.general.excel")})
@SpringBootApplication
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

2.项目实战

2.1初始化测试数据

注:一下测试表,与通用导出组件无关
学生测试表

CREATE TABLE `student_info` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `student_id` varchar(32) NOT NULL COMMENT '学生ID',
  `class_id` varchar(32) NOT NULL COMMENT '班级ID',
  `name` varchar(32) NOT NULL DEFAULT '' COMMENT '名称',
  `sex` tinyint(4) NOT NULL DEFAULT '0' COMMENT '性别 0-未知, 1-男,2-女 ',
  `age` tinyint(4) NOT NULL DEFAULT '0' COMMENT '年龄',
  `school_date` int(10) NOT NULL COMMENT '入学时间 yyyyMMdd',
  `progress_rate` decimal(10,2) NOT NULL COMMENT '成绩进步率 示例 2.01',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uniq_student_id` (`student_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表';

-- 初始化一批测试数据
INSERT INTO `student_info`
(`id`, `student_id`, `class_id`, `name`, `sex`, `age`, `school_date`, `progress_rate`) 
VALUES 
(1, 'S3001', 'C1001', '小明', 1, 10, 20190930, 0.90),
(2, 'S3002', 'C1001', '小华', 1, 11, 20190929, 0.80),
(3, 'S3003', 'C1001', '小东', 1, 10, 20190930, 0.93),
(4, 'S3004', 'C1001', '小红', 2, 11, 20190929, 0.93),
(5, 'S3005', 'C1001', '小白', 1, 9, 20191001, 0.80),
(6, 'S3006', 'C1001', '小小', 2, 10, 20191001, 0.97),
(7, 'S3007', 'C1001', '小米', 2, 10, 20190929, 0.93),
(8, 'S3008', 'C1001', '小溪', 1, 11, 20191001, 0.80),
(9, 'S3009', 'C1001', '小北', 1, 10, 20190930, 0.95),
(10, 'S3010', 'C1001', '小夏', 2, 11, 20190930, 0.8),
(11, 'S3011', 'C1002', '大明', 1, 10, 20191001, 0.9),
(12, 'S3012', 'C1002', '大华', 1, 10, 20190930, 0.9);

老师测试表

CREATE TABLE `teacher_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `teacher_id` varchar(32) NOT NULL COMMENT '班主任ID',
  `name` varchar(32) NOT NULL DEFAULT '' COMMENT '名称',
  `job_title` varchar(255) NOT NULL DEFAULT '' COMMENT '职称',
  `teacher_type` tinyint(4) NOT NULL DEFAULT '1' COMMENT '教师类型 1-代课老师 2-班主任',
  `phone` varchar(20) NOT NULL DEFAULT '' COMMENT '联系电话',
  `email` varchar(100) NOT NULL DEFAULT '' COMMENT '邮箱地址',
  `address` varchar(200) NOT NULL DEFAULT '' COMMENT '家庭住址',
  `progression_rate` decimal(10,4) NOT NULL COMMENT '升学率',
  `profession_date` int(10) NOT NULL COMMENT '入教时间 格式:yyyyMMdd',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uniq_teacher_id` (`teacher_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='老师表';

-- 初始化测试数据
INSERT INTO `teacher_info`(`id`, `teacher_id`, `name`, `job_title`, `teacher_type`, `phone`, `email`, `address`, `progression_rate`, `profession_date`) 
VALUES
(1, 'T2001', '张三', '普通班主任', 1, '13235727262', '[email protected]', '浙江省杭州市西湖区', 0.9870, 20170809),
(2,'T2002', '李四', '优秀班主任', 1, '15264447382', '[email protected]', '浙江省杭州市拱墅区', 0.8890, 20160809),
(3,'T2003', '王五', '最佳班主任', 1, '18776549832', '[email protected]', '浙江省杭州市滨江区', 0.9980, 20150809);

班级表

CREATE TABLE `class_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `class_id` varchar(32) NOT NULL COMMENT '班级ID',
  `head_teacher_id` varchar(32) NOT NULL COMMENT '班主任ID',
  `name` varchar(32) NOT NULL COMMENT '名称',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uniq_class_id` (`class_id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='班级表';

-- 初始化测试数据
INSERT INTO `class_info`(`id`, `class_id`, `head_teacher_id`, `name`) 
VALUES 
 (1, 'C1001', 'T2001', '一班'),
 (2, 'C1002', 'T2002', '二班'),
 (3, 'C1003', 'T2003', '三班');

2.2创建模板

初始化模板数据,模板表配置介绍 传送门

INSERT INTO `export_template_info`(`id`, `template_id`, `platform_id`, `db_code`, `template_child_id`, `template_title`, `template_status`, `template_type`, `table_columns`, `table_names`, `export_file_name`, `export_fields_exp`, `query_condition`, `order_by_exp`, `page_size`, `download_url_expire`, `template_file_url`, `template_file_local_path`, `template_refresh_flag`, `create_time`, `update_time`) VALUES (1, '10001', 'T1001', 'DB1001', '0', '学生信息', 1, 1, 'student_id|name|sex|age|school_date', 'student_info', '学生信息列表', 'student_id?title=学生ID|name?title=学生名称|sex?title=性别&replace=0_未知,1_男,2_女|age?title=年龄|school_date?title=入学时间&dateFormat=yyyyMMdd_yyyy-MM-dd', '<if test=\"classId != null and classId!=\'\' \">\n AND class_id = #{classId,jdbcType=VARCHAR}\n</if>\n<if test=\"studentName != null and studentName != \'\' \">\n AND name LIKE CONCAT(#{studentName,jdbcType=VARCHAR},\'%\')\n</if>', '', 100, 3600, '', '', 1, '2019-11-23 11:06:39', '2019-12-15 17:23:37');
INSERT INTO `export_template_info`(`id`, `template_id`, `platform_id`, `db_code`, `template_child_id`, `template_title`, `template_status`, `template_type`, `table_columns`, `table_names`, `export_file_name`, `export_fields_exp`, `query_condition`, `order_by_exp`, `page_size`, `download_url_expire`, `template_file_url`, `template_file_local_path`, `template_refresh_flag`, `create_time`, `update_time`) VALUES (2, '10002', 'T1001', 'DB1001', '0', '学生与班级信息', 1, 1, 'a.student_id|a.`name` student_name|a.sex|a.age|a.school_date|b.`name` class_name|c.`name` teacher_name', '  student_info a INNER JOIN class_info b ON a.class_id = b.class_id INNER JOIN teacher_info c ON b.head_teacher_id = c.teacher_id ', '学生与班级信息列表', 'student_id?title=学生ID|student_name?title=学生名称|sex?title=性别&replace=0_未知,1_男,2_女|age?title=年龄|school_date?title=入学时间&dateFormat=yyyyMMdd_yyyy-MM-dd|teacher_name?title=班主任名称|class_name?title=班级名称', '<if test=\"classId != null\">\n AND a.class_id = #{classId,jdbcType=VARCHAR}\n</if>\n<if test=\"studentName != null and studentName != \'\' \">\n AND a.name LIKE CONCAT(#{studentName,jdbcType=VARCHAR},\'%\')\n</if>\n<if test=\"teacherName != null and teacherName != \'\' \">\n AND c.name LIKE CONCAT(#{teacherName,jdbcType=VARCHAR},\'%\')\n</if>', '', 150, 3600, '', '', 1, '2019-11-23 11:06:39', '2019-12-15 16:17:50');
INSERT INTO `export_template_info`(`id`, `template_id`, `platform_id`, `db_code`, `template_child_id`, `template_title`, `template_status`, `template_type`, `table_columns`, `table_names`, `export_file_name`, `export_fields_exp`, `query_condition`, `order_by_exp`, `page_size`, `download_url_expire`, `template_file_url`, `template_file_local_path`, `template_refresh_flag`, `create_time`, `update_time`) VALUES (3, '10003', 'T1001', 'DB1001', '0', '学生与班级信息模板导出', 1, 2, 'a.student_id|a.`name` student_name|a.sex|a.age|a.school_date|b.`name` class_name|c.`name` teacher_name', '  student_info a INNER JOIN class_info b ON a.class_id = b.class_id INNER JOIN teacher_info c ON b.head_teacher_id = c.teacher_id ', '学生与班级模板导出列表', 'student_id|student_name|sex?replace=0_未知,1_男,2_女|age|school_date?title=入学时间&dateFormat=yyyyMMdd_yyyy-MM-dd|teacher_name|class_name', '<if test=\"classId != null\">\n AND a.class_id = #{classId,jdbcType=VARCHAR}\n</if>\n<if test=\"studentName != null and studentName != \'\' \">\n AND a.name LIKE CONCAT(#{studentName,jdbcType=VARCHAR},\'%\')\n</if>\n<if test=\"teacherName != null and teacherName != \'\' \">\n AND c.name LIKE CONCAT(#{teacherName,jdbcType=VARCHAR},\'%\')\n</if>', '', 150, 3600, 'https://gen-excel.oss-cn-hangzhou.aliyuncs.com/gen-excel/template/学生与班级信息列表模板01.xlsx', '', 1, '2019-11-23 11:06:39', '2019-12-25 21:10:05');
INSERT INTO `export_template_info`(`id`, `template_id`, `platform_id`, `db_code`, `template_child_id`, `template_title`, `template_status`, `template_type`, `table_columns`, `table_names`, `export_file_name`, `export_fields_exp`, `query_condition`, `order_by_exp`, `page_size`, `download_url_expire`, `template_file_url`, `template_file_local_path`, `template_refresh_flag`, `create_time`, `update_time`) VALUES (4, '10004', 'T1001', 'DB1001', '0', '教职工个人信息', 1, 3, 'teacher_id|name|profession_date|job_title|phone,email|address|progression_rate', 'teacher_info', '教职工个人信息表', 'teacher_id|name|profession_date?dateFormat=yyyyMMdd_yyyy-MM-dd|job_title|phone|email|address|progression_rate?numFormat=multiply_100,format_2', '<if test=\"teacherId != null and teacherId!=\'\' \">\n AND teacher_id = #{teacherId,jdbcType=VARCHAR}\n</if>', '', 100, 3600, 'https://gen-excel.oss-cn-hangzhou.aliyuncs.com/gen-excel/template/教职工个人信息表导出模板.xlsx', '', 1, '2019-11-23 11:06:39', '2019-12-25 21:10:13');
INSERT INTO `export_template_info`(`id`, `template_id`, `platform_id`, `db_code`, `template_child_id`, `template_title`, `template_status`, `template_type`, `table_columns`, `table_names`, `export_file_name`, `export_fields_exp`, `query_condition`, `order_by_exp`, `page_size`, `download_url_expire`, `template_file_url`, `template_file_local_path`, `template_refresh_flag`, `create_time`, `update_time`) VALUES (5, '10005', 'T1001', 'DB1001', '10006', '班级全员信息-老师', 1, 4, 'b.name class_name|a.name teacher_name|a.progression_rate|a.phone|a.email', 'teacher_info  a INNER JOIN class_info b on b.head_teacher_id =a.teacher_id\n', '班级全员信息', 'class_name|teacher_name|progression_rate?numFormat=multiply_100,format_2|phone|email', 'class_id = #{classId,jdbcType=VARCHAR}', '', 100, 3600, 'https://gen-excel.oss-cn-hangzhou.aliyuncs.com/gen-excel/template/班级全员信息模板.xlsx', '', 1, '2019-11-23 11:06:39', '2019-12-25 21:10:15');
INSERT INTO `export_template_info`(`id`, `template_id`, `platform_id`, `db_code`, `template_child_id`, `template_title`, `template_status`, `template_type`, `table_columns`, `table_names`, `export_file_name`, `export_fields_exp`, `query_condition`, `order_by_exp`, `page_size`, `download_url_expire`, `template_file_url`, `template_file_local_path`, `template_refresh_flag`, `create_time`, `update_time`) VALUES (6, '10006', 'T1001', 'DB1001', '0', '班级全员信息-学生', 1, 4, 'student_id|name student_name|sex|age|school_date', 'student_info', '班级全员信息', 'student_id|student_name|sex?replace=0_未知,1_男,2_女|age|school_date?dateFormat=yyyyMMdd_yyyy-MM-dd', 'class_id = #{classId,jdbcType=VARCHAR}', '', 100, 3600, '', '', 1, '2019-11-23 11:06:39', '2019-12-15 17:28:44');

2.3导出测试

2.3.1公共导出触发接口
http://127.0.0.1:8083/gen-excel/export?templateId=10005&platformId=T1001&paramContent={“classId”:“C1002”}

image.png

2.3.2根据任务ID-获取下载任务结果接口
http://127.0.0.1:8083/gen-excel/getTask?taskId=201912252117451834643986103
image.png

2.3.1普通列表导出

示例图->学生列表
image.png
导出模板配置

INSERT INTO `export_template_info`(`id`, `template_id`, `platform_id`, `db_code`, `template_child_id`, `template_title`, `template_status`, `template_type`, `table_columns`, `table_names`, `export_file_name`, `export_fields_exp`, `query_condition`, `order_by_exp`, `page_size`, `download_url_expire`, `template_file_url`, `template_file_local_path`, `template_refresh_flag`, `create_time`, `update_time`) 
VALUES 
(1, '10001', 'T1001', 'DB1001', '0', '学生信息', 1, 1, 'student_id|name|sex|age|school_date', 'student_info', '学生信息列表', 'student_id?title=学生ID|name?title=学生名称|sex?title=性别&replace=0_未知,1_男,2_女|age?title=年龄|school_date?title=入学时间&dateFormat=yyyyMMdd_yyyy-MM-dd', '<if test=\"classId != null and classId!=\'\' \">\n AND class_id = #{classId,jdbcType=VARCHAR}\n</if>\n<if test=\"studentName != null and studentName != \'\' \">\n AND name LIKE CONCAT(#{studentName,jdbcType=VARCHAR},\'%\')\n</if>', '', 100, 3600, '', '', 1, '2019-11-23 11:06:39', '2019-12-15 17:23:37');

请求示例

http://127.0.0.1:8083/gen-excel/export?templateId=10001&platformId=T1001&paramContent={"classId":"C1001"}

2.3.2表关联列表导出


示例图->学生列表
image.png

导出模板配置

INSERT INTO `export_template_info`
(`id`, `template_id`, `platform_id`, `db_code`, `template_child_id`, `template_title`, `template_status`, `template_type`, `table_columns`, `table_names`, `export_file_name`, `export_fields_exp`, `query_condition`, `order_by_exp`, `page_size`, `download_url_expire`, `template_file_url`, `template_file_local_path`, `template_refresh_flag`, `create_time`, `update_time`) 
VALUES 
(2, '10002', 'T1001', 'DB1001', '0', '学生与班级信息', 1, 1, 'a.student_id|a.`name` student_name|a.sex|a.age|a.school_date|b.`name` class_name|c.`name` teacher_name', '  student_info a INNER JOIN class_info b ON a.class_id = b.class_id INNER JOIN teacher_info c ON b.head_teacher_id = c.teacher_id ', '学生与班级信息列表', 'student_id?title=学生ID|student_name?title=学生名称|sex?title=性别&replace=0_未知,1_男,2_女|age?title=年龄|school_date?title=入学时间&dateFormat=yyyyMMdd_yyyy-MM-dd|teacher_name?title=班主任名称|class_name?title=班级名称', '<if test=\"classId != null\">\n AND a.class_id = #{classId,jdbcType=VARCHAR}\n</if>\n<if test=\"studentName != null and studentName != \'\' \">\n AND a.name LIKE CONCAT(#{studentName,jdbcType=VARCHAR},\'%\')\n</if>\n<if test=\"teacherName != null and teacherName != \'\' \">\n AND c.name LIKE CONCAT(#{teacherName,jdbcType=VARCHAR},\'%\')\n</if>', '', 150, 3600, '', '', 1, '2019-11-23 11:06:39', '2019-12-15 16:17:50');

请求示例

http://127.0.0.1:8083/gen-excel/export?templateId=10002&platformId=T1001&paramContent={"classId":"C1001"}

2.3.3根据Excel模板列表导出

excel模板图示
image.png
excel模板下载地址

示例图->学生列表
image.png

导出模板配置

INSERT INTO `export_template_info`
(`id`, `template_id`, `platform_id`, `db_code`, `template_child_id`, `template_title`, `template_status`, `template_type`, `table_columns`, `table_names`, `export_file_name`, `export_fields_exp`, `query_condition`, `order_by_exp`, `page_size`, `download_url_expire`, `template_file_url`, `template_file_local_path`, `template_refresh_flag`, `create_time`, `update_time`)
VALUES 
(3, '10003', 'T1001', 'DB1001', '0', '学生与班级信息模板导出', 1, 2, 'a.student_id|a.`name` student_name|a.sex|a.age|a.school_date|b.`name` class_name|c.`name` teacher_name', '  student_info a INNER JOIN class_info b ON a.class_id = b.class_id INNER JOIN teacher_info c ON b.head_teacher_id = c.teacher_id ', '学生与班级模板导出列表', 'student_id|student_name|sex?replace=0_未知,1_男,2_女|age|school_date?title=入学时间&dateFormat=yyyyMMdd_yyyy-MM-dd|teacher_name|class_name', '<if test=\"classId != null\">\n AND a.class_id = #{classId,jdbcType=VARCHAR}\n</if>\n<if test=\"studentName != null and studentName != \'\' \">\n AND a.name LIKE CONCAT(#{studentName,jdbcType=VARCHAR},\'%\')\n</if>\n<if test=\"teacherName != null and teacherName != \'\' \">\n AND c.name LIKE CONCAT(#{teacherName,jdbcType=VARCHAR},\'%\')\n</if>', '', 150, 3600, 'https://gen-excel.oss-cn-hangzhou.aliyuncs.com/gen-excel/template/学生与班级信息列表模板01.xlsx', '', 1, '2019-11-23 11:06:39', '2019-12-25 21:10:05');

请求示例

http://127.0.0.1:8083/gen-excel/export?templateId=10003&platformId=T1001&paramContent={}

2.3.4根据Excel模板导出单个对象

excel模板图示
image.png
excel模板下载地址

示例图-> 教师个人信息
image.png

模板内容

INSERT INTO `export_template_info`
(`id`, `template_id`, `platform_id`, `db_code`, `template_child_id`, `template_title`, `template_status`, `template_type`, `table_columns`, `table_names`, `export_file_name`, `export_fields_exp`, `query_condition`, `order_by_exp`, `page_size`, `download_url_expire`, `template_file_url`, `template_file_local_path`, `template_refresh_flag`, `create_time`, `update_time`) 
VALUES 
(4, '10004', 'T1001', 'DB1001', '0', '教职工个人信息', 1, 3, 'teacher_id|name|profession_date|job_title|phone,email|address|progression_rate', 'teacher_info', '教职工个人信息表', 'teacher_id|name|profession_date?dateFormat=yyyyMMdd_yyyy-MM-dd|job_title|phone|email|address|progression_rate?numFormat=multiply_100,format_2', '<if test=\"teacherId != null and teacherId!=\'\' \">\n AND teacher_id = #{teacherId,jdbcType=VARCHAR}\n</if>', '', 100, 3600, 'https://gen-excel.oss-cn-hangzhou.aliyuncs.com/gen-excel/template/教职工个人信息表导出模板.xlsx', '', 1, '2019-11-23 11:06:39', '2019-12-25 21:10:13');

请求示例

http://127.0.0.1:8083/gen-excel/export?templateId=10004&platformId=T1001&paramContent={"teacherId":"T2001"}


2.3.5根据Excel模板导出主单+子单

excel模板图示
image.png
excel模板下载地址

示例图->班级信息
image.png
模板内容

-- 主单模板
INSERT INTO `export_template_info`
(`id`, `template_id`, `platform_id`, `db_code`, `template_child_id`, `template_title`, `template_status`, `template_type`, `table_columns`, `table_names`, `export_file_name`, `export_fields_exp`, `query_condition`, `order_by_exp`, `page_size`, `download_url_expire`, `template_file_url`, `template_file_local_path`, `template_refresh_flag`, `create_time`, `update_time`) 
VALUES
(5, '10005', 'T1001', 'DB1001', '10006', '班级全员信息-老师', 1, 4, 'b.name class_name|a.name teacher_name|a.progression_rate|a.phone|a.email', 'teacher_info  a INNER JOIN class_info b on b.head_teacher_id =a.teacher_id\n', '班级全员信息', 'class_name|teacher_name|progression_rate?numFormat=multiply_100,format_2|phone|email', 'class_id = #{classId,jdbcType=VARCHAR}', '', 100, 3600, 'https://gen-excel.oss-cn-hangzhou.aliyuncs.com/gen-excel/template/班级全员信息模板.xlsx', '', 1, '2019-11-23 11:06:39', '2019-12-25 21:10:15');

-- 子单模板
INSERT INTO `export_template_info`
(`id`, `template_id`, `platform_id`, `db_code`, `template_child_id`, `template_title`, `template_status`, `template_type`, `table_columns`, `table_names`, `export_file_name`, `export_fields_exp`, `query_condition`, `order_by_exp`, `page_size`, `download_url_expire`, `template_file_url`, `template_file_local_path`, `template_refresh_flag`, `create_time`, `update_time`) 
VALUES
(6, '10006', 'T1001', 'DB1001', '0', '班级全员信息-学生', 1, 4, 'student_id|name student_name|sex|age|school_date', 'student_info', '班级全员信息', 'student_id|student_name|sex?replace=0_未知,1_男,2_女|age|school_date?dateFormat=yyyyMMdd_yyyy-MM-dd', 'class_id = #{classId,jdbcType=VARCHAR}', '', 100, 3600, '', '', 1, '2019-11-23 11:06:39', '2019-12-15 17:28:44');

请求示例

http://127.0.0.1:8083/gen-excel/export?templateId=10005&platformId=T1001&paramContent={"classId":"C1001"}
原文链接:加载失败,请重新获取