sql2java-excel(一):基于apache poi实现数据库表的导出及支持spring web

sql2java是我几年年开始写的一个sql2java是一个轻量级数据库(SQL)访问代码(java)生成器。这几年一直在根据工作需要维护升级，最近的项目中需要对数据库的记录提供导出excel的功能。
就开始学习apache的POI，参照网上的示例实现了单张表的导出。并进一步将它封装成一个通用库成为sql2java下的子项目sql2java-excel.以方便在其他项目中技术复用。
本文开始介绍sql2java-excel的使用.

引入依赖

maven下引入sql2java-excel项目依赖

		<dependency>
			<groupId>com.gitee.comgroupId>
			<artifactId>sql2java-excelartifactId>
			<version>3.10.0version>
		dependency>
1
2
3
4
5

excelGenerator

特性

基于apach/poi实现数据记录导出为excel,为Spring Web服务提供切面(aspect)支持,简化excel数据导出实现.

• 数据记录列表支持数组,java.util.Set,java.util.List,以及以及所有实现java.lang.Iterable接口的可迭代容器类型
• 数据记录列表元素类型支持java.util.Map,JavaBean(gu.sql2java.BeseBean,fastjson的JSONObject属于Map)
• 支持导出为文件,及导出到javal.io.OutputStream
• 支持导出到HTTP请求javax.servlet.http.HttpServletResponse
• 支持(aspect)切面导出,基于Spring AOP 技术简化服务端导出Excel实现
• 支持成员子成员导出.如 props.name.first代表成员props的子成员name的子成员first,成员及子成员字段都支持Map,Java Bean
• 支持字段名驼峰命名(camel-case)格式与蛇形命名(snake-case)格式的自动转换,当以create_time读取字段而只存在createTime字段时会自动返回createTime字段值
• 支持注解方式配置导出列输出顺序,导出列过滤(白名单/黑名单/隐藏名单)
• 支持注解方式配置导出表的默认单元格格式配置:字体,颜色,填充色,对齐方式
• 支持注解方式配置导出表的标题单元格配置:标题名,字体,颜色,填充色,对齐方式
• 支持注解方式配置导出表的首行单元格配置:字体,颜色,填充色,对齐方式
• 支持注解方式配置导出列的配置:输出顺序,字段名,单元格宽度,颜色,填充色,对齐方式,数据格式(DataFormat),数据转换
• 支持简单数据类型转换表达式,参见注解类 ExcelColumn的readConverterExp()方法
• 支持列数据自定义类型转接口,参见ExcelHandlerAdapter
• 不支持图像数据导出

JDK requirement

JDK 要求 1.8

快速入门

定义一个Java Bean

	@Data
	public static class TestUserBean {
		private int id;
		private String name;
		private Date birthday;
		private String phone;
		private String idnum;
		public TestUserBean(int id, String name, Date birthday, String phone, String idnum) {
			super();
			this.id = id;
			this.name = name;
			this.birthday = birthday;
			this.phone = phone;
			this.idnum = idnum;
		}
	}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

Excel导出调用示例

public class ExcelExportTest {

	/**
	 * 普通JavaBean记录导出到excel文件测试
	 */
	@Test  
	public void test1JavaBeanExport() {
		try(FileOutputStream outputStream = new FileOutputStream(new File("export.xlsx"))) {
			SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
            /** 待输出的数据记录 */
			List<TestUserBean> beans = Lists.newArrayList(
					new TestUserBean(1, "tom", fmt.parse("1976-01-01"), "13327963101", "157713197601012534"),
					new TestUserBean(15, "smith", fmt.parse("1950-03-25"), "13707968801", "201513195003257774"),
					new TestUserBean(29, "brown", fmt.parse("2003-05-13"), "13327385801", "102813200305137118"),
					new TestUserBean(3, "张小泉", fmt.parse("1999-11-28"), "13327968801", "450113199911285320"),
					new TestUserBean(7, "阿明", fmt.parse("1980-10-01"), "13366668487", "310113198010047890")
					);
            /** 创建 ExcelGenerator 实例 */
			ExcelGenerator<TestUserBean> generator = new ExcelGenerator<TestUserBean>(beans){};
            /** Excel Sheet 配置 */
			SheetConfig sheetConfig = generator.getSheetConfig();
            /** Excel Sheet 标题 */
			sheetConfig.setTitle("人员花名册");
            /** 标题居单元格填充色 */
			sheetConfig.setTitleFillColor(IndexedColors.LIGHT_YELLOW);
            /** 标题居中对齐 */
			sheetConfig.setTitleHorizontalAlign(HorizontalAlignment.CENTER);
            /** 输出到文件流 */
			generator.generate(outputStream);
		} catch (IOException | ParseException e) {
			fail(e.getMessage());
		};		
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

输出的Excel

使用@ExcelSheet,@ExcelColumn注解

上面的输出Excel输出的列名是JavaBean中的英文字段名,而且导出字段的顺序不符合我们的要求,我们可以通过为每个字段定义@ExcelColumn注解来指定导出的列表名，用@ExcelSheet来配置Exce导出全全局配置，重新定义TestUserBean如下：

	@Data
	@ExcelSheet(title="人员花名册",titleFillColor="LIGHT_YELLOW",titleHorizontalAlign="CENTER")
	public static class TestUserBean {
		@ExcelColumn(name="用户ID",sort=1)
		private int id;
		@ExcelColumn(name="用户名",sort=2)
		private String name;
		@ExcelColumn(name="出生日期",sort=3,dateFormat="yyyy-MM-dd")
		private Date birthday;
		@ExcelColumn(name="联系电话",sort=4,integralFormat="0")
		private String phone;
		@ExcelColumn(name="身份证号码",sort=5,integralFormat="0")
		private String idnum;
		public TestUserBean(int id, String name, Date birthday, String phone, String idnum) {
			super();
			this.id = id;
			this.name = name;
			this.birthday = birthday;
			this.phone = phone;
			this.idnum = idnum;
		}
	}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

则Excel导出调用代码可以简化如下示例

public class ExcelExportTest {

	/**
	 * 普通JavaBean (带{@link ExcelSheet},{@link ExcelColumn} 注解)记录导出excel测试
	 */
	@Test  
	public void test1JavaBeanExport() {
		try(FileOutputStream outputStream = new FileOutputStream(new File("export.xlsx"))) {
			SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
            /** 待输出的数据记录 */
			List<TestUserBean> beans = Lists.newArrayList(
					new TestUserBean(1, "tom", fmt.parse("1976-01-01"), "13327963101", "157713197601012534"),
					new TestUserBean(15, "smith", fmt.parse("1950-03-25"), "13707968801", "201513195003257774"),
					new TestUserBean(29, "brown", fmt.parse("2003-05-13"), "13327385801", "102813200305137118"),
					new TestUserBean(3, "张小泉", fmt.parse("1999-11-28"), "13327968801", "450113199911285320"),
					new TestUserBean(7, "阿明", fmt.parse("1980-10-01"), "13366668487", "310113198010047890")
					);
            /** 创建 ExcelGenerator 实例 */
			ExcelGenerator<TestUserBean> generator = new ExcelGenerator<TestUserBean>(beans){};
			generator.generate(outputStream);
		} catch (IOException | ParseException e) {
			fail(e.getMessage());
		};		
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

导出的Excel

Map记录导出示例

ExcelGenerator支持导出数据记录的元素类型为Map.示例如下：

	/**
	 * Map记录导出excel测试
	 */
	@SuppressWarnings("rawtypes")
	@Test  
	public void test3MapExport() {
		try(FileOutputStream outputStream = new FileOutputStream(new File("export.xlsx"))) {
			SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
			/** 待输出的数据记录 */
			ArrayList<Map> beans = Lists.newArrayList(
					ImmutableMap.of("id", 1, "name", "tom", "birthday", fmt.parse("1976-01-01"), "phone", "13327963101", "idnum", "157713197601012534"),
					ImmutableMap.of("id", 15, "name", "smith", "birthday", fmt.parse("1950-03-25"), "phone", "13707968801", "idnum", "201513195003257774"),
					ImmutableMap.of("id", 29, "name", "brown", "birthday", fmt.parse("2003-05-13"), "phone", "13327385801", "idnum", "102813200305137118"),
					ImmutableMap.of("id", 3, "name", "张小泉", "birthday", fmt.parse("1999-11-28"), "phone", "13327968801", "idnum", "450113199911285320"),
					ImmutableMap.of("id", 7, "name", "阿明", "birthday", fmt.parse("1980-10-01"), "phone", "13366668487", "idnum", "310113198010047890")
					);
			
			/** 创建 ExcelGenerator 实例 */
			ExcelGenerator<Map> generator = new ExcelGenerator<Map>(beans){};
            /** 也可以使用为Map类型定义的便利化封装类MapExcelGenerator */
            //MapExcelGenerator generator = new MapExcelGenerator(beans);
			/** Excel Sheet 配置 */
			SheetConfig sheetConfig = generator.getSheetConfig();
			/** Excel Sheet 标题 */
			sheetConfig.setTitle("人员花名册");
			/** 标题居单元格填充色 */
			sheetConfig.setTitleFillColor(IndexedColors.LIGHT_YELLOW);
			/** 标题居中对齐 */
			sheetConfig.setTitleHorizontalAlign(HorizontalAlignment.CENTER);
            /** 定义导出字段名及数据格式 */
			sheetConfig.configOf("id").getColumnConfig().setName("用户ID");
			sheetConfig.configOf("name").getColumnConfig().setName("用户名");
			sheetConfig.configOf("birthday").getColumnConfig().setName("出生日期").setDateFormat("yyyy-MM-dd");
			sheetConfig.configOf("phone").getColumnConfig().setName("联系电话");
			sheetConfig.configOf("idnum").getColumnConfig().setName("身份证号码");
			/** 输出到文件流 */
			generator.generate(outputStream);
		} catch (IOException | ParseException e) {
			fail(e.getMessage());
		};		
	}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

导出的Excel

子成员导出

ExcelGenerator支持子成员导出,子成员的引用名格式为自顶向下以.分割的各级成员名

如下我们在TestUserBean中增加一个类型为com.alibaba.fastjson.JSONObject()的props字段用于保存用户的扩展信息,

在@ExcelSheet注解中增加includeColumns配置导出的字段,其中props.salary和props.car为props字段的子成员,同时定义将props定义为隐藏字段(hideColumns)

	@Data
	@ExcelSheet(title="人员花名册",titleFillColor="LIGHT_YELLOW",
						titleHorizontalAlign="CENTER",
						hideColumns="props",
						includeColumns={"id","name","birthday","phone","idnum","props.salary","props.car"})
	public static class TestUserBean {
		@ExcelColumn(name="用户ID",sort=1)
		private int id;
		@ExcelColumn(name="用户名",sort=2)
		private String name;
		@ExcelColumn(name="出生日期",sort=3,dateFormat="yyyy-MM-dd")
		private Date birthday;
		@ExcelColumn(name="联系电话",sort=4,integralFormat="0")
		private String phone;
		@ExcelColumn(name="身份证号码",sort=5,integralFormat="0")
		private String idnum;
		private JSONObject props;
		public TestUserBean(int id, String name, Date birthday, String phone, String idnum) {
			super();
			this.id = id;
			this.name = name;
			this.birthday = birthday;
			this.phone = phone;
			this.idnum = idnum;
		}
		public TestUserBean(int id, String name, Date birthday, String phone, String idnum, JSONObject props) {
			super();
			this.id = id;
			this.name = name;
			this.birthday = birthday;
			this.phone = phone;
			this.idnum = idnum;
			this.props = props;
		}
	}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

修改test2JavaBeanExport测试代码如下，在测试数据中为每个用户定义props字段

	/**
	 * 普通JavaBean (带{@link ExcelSheet},{@link ExcelColumn} 注解)记录导出excel测试
	 */
	@Test  
	public void test2JavaBeanExport() {
		try(FileOutputStream outputStream = new FileOutputStream(new File("export.xlsx"))) {
			SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
			/** 待输出的数据记录 */
			List<TestUserBean> beans = Lists.newArrayList(
					new TestUserBean(1, "tom", fmt.parse("1976-01-01"), "13327963101", "157713197601012534",new JSONObject().fluentPut("salary", 5000).fluentPut("car", "京AA4382")),
					new TestUserBean(15, "smith", fmt.parse("1950-03-25"), "13707968801", "201513195003257774",new JSONObject().fluentPut("salary", 60000)),
					new TestUserBean(29, "brown", fmt.parse("2003-05-13"), "13327385801", "102813200305137118",new JSONObject().fluentPut("salary", 3200)),
					new TestUserBean(3, "张小泉", fmt.parse("1999-11-28"), "13327968801", "450113199911285320",new JSONObject().fluentPut("salary", 200)),
					new TestUserBean(7, "阿明", fmt.parse("1980-10-01"), "13366668487", "310113198010047890",new JSONObject().fluentPut("salary", 350000).fluentPut("car", "粤AS9988"))
					);
			/** 创建 ExcelGenerator 实例 */
			ExcelGenerator<TestUserBean> generator = new ExcelGenerator<TestUserBean>(beans){};
			/** Excel Sheet 配置 */
			SheetConfig sheetConfig = generator.getSheetConfig();
            /** 定义扩展字段的列名 */
			sheetConfig.configOf("props.salary").getColumnConfig().setName("工资");
			sheetConfig.configOf("props.car").getColumnConfig().setName("车牌");
			generator.generate(outputStream);
		} catch (IOException | ParseException e) {
			fail(e.getMessage());
		};		
	}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

输出的Excel

完整示例代码参见：
https://gitee.com/l0km/sql2java/blob/master/sql2java-excel/src/test/java/gu/sql2java/excel/ExcelExportTest.java

关于Spring支持参见下一篇博客：
《sql2java-excel(二):基于apache poi实现数据库表的导出的spring web支持》