feilong excel操作指南

Table of Contents

1. 依赖
2. 生成简单list循环的excel
3. 简单原理
4. 特殊用法
5. 垂直循环
6. 简单对象
7. 对象及循环融合式
8. 多sheet式

excel操作(导入/导出) 可谓是当今项目必备功能,大家可能都是基于网上攻略来自己生成excel文件,需要从头了解POI的 Workbook, Sheet, Row,Cell 等等各种类的API , 开发周期少则半天,多则2天不等

feilong的愿景就是,让JAVA开发更简便 ,feilong excel 只需要简单的配置,快则10分钟就可以完成一个excel 的生成

跟我来见证feilong excel 的精妙简洁之处吧

1. 依赖

首先,依赖feilong jar

<dependency>
    <groupId>com.github.ifeilong</groupId>
    <artifactId>feilong</artifactId>
    <version>3.0.10</version>
</dependency>

其次,在使用feilong excel之前, 需要依赖下面jar 包

Warning	注意是依赖 poi 4 系列版本, 和 poi3 系列不兼容

<!-- poi HSSF is our port of the Microsoft Excel 97(-2007) file format (BIFF8) to pure Java. -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>

<!-- poi-ooxml XSSF is our port of the Microsoft Excel XML (2007+) file format (OOXML) to pure Java -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
    <exclusions>
        <exclusion>
            <groupId>com.github.virtuald</groupId>
            <artifactId>curvesapi</artifactId>
        </exclusion>
    </exclusions>
</dependency>

<!-- poi-ooxml-schemas是ooxml-schemas的精简版 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>ooxml-schemas</artifactId>
    <version>1.4</version>
</dependency>

其中 poi 是目前业界最流行的 excel 解析jar , commons-digester3 和`ognl` 是 feilong excel 用来配置化解析excel文件所需要的jar

准备工作做好了之后, 下面开始上"才艺"

2. 生成简单list循环的excel

2.1. 背景

BOSS : 小王, 帮我导出今年每个月的销售数据, 今天下班前要
小王 : 好的,老板

假设数据库有以下list 数据,需要生成excel 文件

private List<SalesData> buildList(){
    List<SalesData> list = newArrayList();

    for (int i = 1; i <= 12; ++i){
        list.add(new SalesData(2020, i, toBigDecimal(i * 533033.88)));
    }
    return list;
}

SalesData 对象数据情况

public class SalesData{

    /** 年份 *. */
    private int        year;

    /** 月份 *. */
    private int        month;

    /** 总金额. */
    private BigDecimal total;

    //省略 setter getter
}

2.2. 第1步准备 excel 文件模板

export-template-sales.xlsx

Figure 1. 简单excel模板

你可以点击下载模板

2.3. 第2步准备对应的excel xml

<?xml version="1.0" encoding="UTF-8"?>
<sheets xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="exceltemplate-1.0.xsd">
	<!-- 销售数据导出 -->
	<sheet name="salesDataExport">

		<block start="A4" end="C4" dataName="salesDataList" loop="true">
			<cell position="A4" dataName="year" />
			<cell position="B4" dataName="month" />
			<cell position="C4" dataName="total" />
		</block>
	</sheet>

</sheets>

可以看出上面excel 模板是从 A4 开始循环, 所以此处定义的block start 从 A4 开始, C4 结束,然后循环类是 com.feilong.excel.销售数据.SalesData , 然后定义每个cell 和 SalesData 属性的对应关系即可

简单说明下此处 xml 参数含义:

说明项	含义
name="salesDataExport"	给sheet命名
loop="true"	是否循环,默认false, true表示循环
start="A4" end="C4"	单元格开始单元格是 A4 ,结束是C4,用来划分一个区间
position="A4" dataName="year"	第一列的第四个单元格取 SalesData 的year 属性值

2.4. 第3步调用

String excelTemplateLocation = "classpath:excel/销售数据/export-template-sales.xlsx";
String configurations = "excel/销售数据/feilong-sheets-salesData.xml";

String sheetName = "salesDataExport";

String dataName = "salesDataList";
Map<String, Object> beans = toMap(dataName, (Object) list);

String outputFileName = Slf4jUtil.format(
        SystemUtils.USER_HOME + "/feilong/excel/{}{}.{}",
        sheetNames,
        nowTimestamp(),
        ".xls");

ExcelWriteUtil.write(excelTemplateLocation, configurations, sheetNames, beans, outputFileName);

结果文件:

Figure 2. 数据报表

结果日志情况

17:58:12 DEBUG (FormulaEvaluatorUtil.java:76) [reCalculate()] reCalculate workbook use time: [17毫秒]
17:58:12 INFO  (WorkbookUtil.java:104) [write()] write workbook to outputStream use time: [138毫秒]
17:58:12 DEBUG (ExcelWriteUtil.java:265) [write()] write excel [SUCCESS],params info:[    {
        "excelTemplateLocationInputStream class": "java.io.BufferedInputStream",
        "xmlSheetConfigurations": "excel/销售数据/feilong-sheets-salesData.xml",
        "sheetNames": ["salesDataExport"],
        "outputFileOutputStream class": "java.io.FileOutputStream",
        "data info": {"salesDataList size": 12},
        "useTime": "2秒96毫秒"
    }]

3. 简单原理

feilong excel 会基于你设的excel模板文件来生产poi Workbook, 并在循环生成每行数据的时候,会动态copy 第一行的样式和高度,甚至于你设定的表格条件格式颜色规则等来填充数据, 也会保留你既定的筛选以及冻结功能

因此你只需要准备数据,excel 模板,以及数据和excel 对应关系(xml) 即可, 就可以轻松生成excel文件

Figure 3. 示意图

就是这么简单高效

4. 特殊用法

4.1. 公式

BOSS: 小王,做的不错, 只不过我想在excel 最后一列新增 税这一列, 简单公式是 总金额*0.2
小王: 好的,老板

对于这种需求,我们不需要修改JAVA 代码, 只需要修改下 excel模板和 xml 即可

excel 模板新增一列

Figure 4. 公式

然后 xml 进行修改, 支持 = 公式表达式, 如 "=C4*0.2"

<?xml version="1.0" encoding="UTF-8"?>
<sheets xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="exceltemplate-1.0.xsd">
	<!-- 销售数据导出 -->
	<sheet name="salesDataExport">

		<block start="A4" end="D4" dataName="salesDataList" loop="true">
			...
			<cell position="D4" dataName="=C4*0.2" />
		</block>
	</sheet>

</sheets>

结果如下:

Figure 5. 结果

4.2. 函数 (如VLOOKUP)

feilong excel 还支持配置如 VLOOKUP 类的函数

假设销售数据模板还有第二个 sheet ,是2019年的销售数据

现在需要在 2020年数据中, vlookup 2019年的销售数据

第一个sheet修改如下:

此时你只需要修改下 xml 即可

<?xml version="1.0" encoding="UTF-8"?>
<sheets xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="exceltemplate-1.0.xsd">
	<!-- 销售数据导出 -->
	<sheet name="salesDataExport">

		<block start="A4" end="E4" dataName="salesDataList" loop="true">
			....
			<cell position="E4" dataName="=VLOOKUP(B4,'2019'!B3:D15,2,FALSE)" />
		</block>
	</sheet>

</sheets>

结果如下:

你还可以增加 2020年和2019年的增长额和同比增长数据

Excel模板如下:

XML修改 :

<?xml version="1.0" encoding="UTF-8"?>
<sheets xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="exceltemplate-1.0.xsd">
	<!-- 销售数据导出 -->
	<sheet name="salesDataExport">

		<block start="A4" end="G4" dataName="salesDataList" loop="true">
			...
			<cell position="F4" dataName="=C4-E4" />
			<cell position="G4" dataName="=F4/E4" />
		</block>
	</sheet>

</sheets>

结果如下:

4.3. 级联属性

feilong excel 还支持配置如级联属性

比如

<cell position="H4" dataName="auditMember.name" />

假设销售数据需要显示每个月审计人员的姓名

JAVA 示例代码新增

public class AuditMember{

    private String name;

	//setter getter

}


public class SalesData{
	....
    private AuditMember auditMember;

	//setter getter

然后需要修改下 xml 即可

<?xml version="1.0" encoding="UTF-8"?>
<sheets xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="exceltemplate-1.0.xsd">
	<!-- 销售数据导出 -->
	<sheet name="salesDataExport">

		<block start="A4" end="H4" dataName="salesDataList" loop="true">
			...
			<cell position="H4" dataName="auditMember.name" />
		</block>
	</sheet>

</sheets>

构造测试数据

protected List<SalesData> buildList(){
	List<SalesData> list = newArrayList();

	for (int i = 1; i <= buildMonth(); ++i){
		AuditMember auditMember = new AuditMember(i % 2 == 0 ? "张飞" : "关羽");
		list.add(new SalesData(2020, i, toBigDecimal(i * 533033.88), auditMember));
	}
	return list;
}

结果如下:

5. 垂直循环

水平循环很方便可以实现, 那么能否实现垂直循环,这种效果:

安排 ,依然3步曲 : 准备excel模板-→ 准备xml --→套数据执行

5.1. 第1步 excel 模板

模板参见上图即可

5.2. 第2步 excel xml

<?xml version="1.0" encoding="UTF-8"?>
<sheets xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="exceltemplate-1.0.xsd">
	
	<!--**************** 销售报表******************** -->
	<sheet name="exportDailySalesReport">
		<!--****日期********* -->
		<block start="A1" end="B1" loop="true" dataName="dateList">
			<childblock start="B1" dataName="childList">
				<cell position="B1" dataName="value" />
			</childblock>
		</block>

		<!--****订单********* -->
		<block start="A2" end="B2" loop="true" dataName="soCountList">
			<childblock start="B2" dataName="childList">
				<cell position="B2" dataName="value" />
			</childblock>
		</block>

		<!--****销售********* -->
		<block start="A3" end="B3" loop="true" dataName="soSellPriceList">
			<childblock start="B3" dataName="childList">
				<cell position="B3" dataName="value" />
			</childblock>
		</block>


		<!--****退货金额********* -->
		<block start="A4" end="B4" loop="true" dataName="soReturnPriceList">
			<childblock start="B4" dataName="childList">
				<cell position="B4" dataName="value" />
			</childblock>
		</block>

		<!--****非COD下单********* -->
		<block start="A5" end="B5" loop="true" dataName="soNotCodPriceList">
			<childblock start="B5" dataName="childList">
				<cell position="B5" dataName="value" />
			</childblock>
		</block>

		<!--****COD下单********* -->
		<block start="A6" end="B6" loop="true" dataName="soCodPriceList">
			<childblock start="B6" dataName="childList">
				<cell position="B6" dataName="value" />
			</childblock>
		</block>

		<!--****退货率********* -->
		<block start="A7" end="B7" loop="true" dataName="returnRateList">
			<childblock start="B7" dataName="childList">
				<cell position="B7" dataName="value" />
			</childblock>
		</block>

		<!--****会员注册********* -->
		<block start="A8" end="B8" loop="true" dataName="memberCountList">
			<childblock start="B8" dataName="childList">
				<cell position="B8" dataName="value" />
			</childblock>
		</block>

		<!--****日期-会员累计总数********* -->
		<block start="A9" end="B9" loop="true" dataName="memberAllCountList">
			<childblock start="B9" dataName="childList">
				<cell position="B9" dataName="value" />
			</childblock>
		</block>
	</sheet>

</sheets>

5.3. 第3步数据执行

6. 简单对象

7. 对象及循环融合式

8. 多sheet式

--未完待续

🌀 feilong 即时交流

QQ 群 `243306798`

🐼 About

如果您对本项目有任何建议和批评,可以使用下面的联系方式：

iteye博客:http://feitianbenyue.iteye.com/

core

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

feilong excel操作指南

feilong excel操作指南

1. 依赖

2. 生成简单list循环的excel

2.1. 背景

2.2. 第1步准备 excel 文件模板

2.3. 第2步准备对应的excel xml

2.4. 第3步调用

3. 简单原理

4. 特殊用法

4.1. 公式

4.2. 函数 (如VLOOKUP)

4.3. 级联属性

5. 垂直循环

5.1. 第1步 excel 模板

5.2. 第2步 excel xml

5.3. 第3步数据执行

6. 简单对象

7. 对象及循环融合式

8. 多sheet式

🌀 feilong 即时交流

🐼 About

Clone this wiki locally

feilong excel操作指南

feilong excel操作指南

1. 依赖

2. 生成简单list循环的excel

2.1. 背景

2.2. 第1步 准备 excel 文件模板

2.3. 第2步 准备对应的excel xml

2.4. 第3步 调用

3. 简单原理

4. 特殊用法

4.1. 公式

4.2. 函数 (如VLOOKUP)

4.3. 级联属性

5. 垂直循环

5.1. 第1步 excel 模板

5.2. 第2步 excel xml

5.3. 第3步 数据执行

6. 简单对象

7. 对象及循环融合式

8. 多sheet式

🌀 feilong 即时交流

🐼 About

Clone this wiki locally

2.2. 第1步准备 excel 文件模板

2.3. 第2步准备对应的excel xml

2.4. 第3步调用

5.3. 第3步数据执行