Excel导入导出。

本文最后更新于:2 个月前

介绍一下Excel的导入导出,用到的开源项目MyExcel,这是个十分好用的轮子,开发者的代码功底十分了得,

说起这个’了得‘,有意思的是了得和了不得,是一个意思。代码功底十分了得,代码功底十分了不得,表达的是一个意思。中国的文字,确实有点难为老外。

这个项目的文档已经详细到一种让人感觉到啰嗦的地步,功能也十分健壮,涵盖了各种使用场景。话不多说。进入正文

依赖

版本号建议一致,项目中如果有之前的POI的依赖可能会导致版本冲突,解决依赖冲突的方法,请自行百度。

1
2
3
4
5
6
7
8
9
10
11
12
<!--excel导入导出-->
<dependency>
<groupId>com.github.liaochong</groupId>
<artifactId>myexcel</artifactId>
<version>3.11.4</version>
</dependency>
<!--使用模板导出时过程中如果报包含Ognl关键字的异常,导入此依赖-->
<dependency>
<groupId>ognl</groupId>
<artifactId>ognl</artifactId>
<version>3.2.21</version>
</dependency>

Excel导入

导入分为一般导入、sax导入,二者的区别在于sax导入更关注内存,内存使用更少,且sax方式导入功能增强,建议使用sax方式导入(可读取公式值)

一般导入(不支持csv文件导入,支持图片读取)

一般导入又分为两种方式,两种方式的区别:

  • 全部读取:速度快,相对一行一行读取要占内存一点
  • 一行一行读取:省内存,在高并发下不易引发OOM
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
URL htmlToExcelEampleURL = this.getClass().getResource("/templates/read_example.xlsx");
Path path = Paths.get(htmlToExcelEampleURL.toURI());

// 方式一:全部读取后处理
List<ArtCrowd> result = DefaultExcelReader.of(ArtCrowd.class)
.sheet(0) // 0代表第一个,如果为0,可省略该操作,也可sheet("名称")读取
.rowFilter(row -> row.getRowNum() > 0) // 如无需过滤,可省略该操作,0代表第一行
.beanFilter(ArtCrowd::isDance) // bean过滤
.startSheet(sheet->System.out.println(sheet.getName())) // 在开始读取sheet前执行指定操作
.read(path.toFile());// 可接收inputStream

// 方式二:读取一行处理一行,可自行决定终止条件
// readThen有两种重写接口,返回Boolean型接口允许在返回False情况下直接终止读取
DefaultExcelReader.of(ArtCrowd.class)
.sheet(0) // 0代表第一个,如果为0,可省略该操作,也可sheet("名称")读取
.rowFilter(row -> row.getRowNum() > 0) // 如无需过滤,可省略该操作,0代表第一行
.beanFilter(ArtCrowd::isDance) // bean过滤
.readThen(path.toFile() ,artCrowd -> {System.out.println(artCrowd.getName);});// 可接收inputStream

public class ArtCrowd {
// index代表列索引,从0开始
@ExcelColumn(index = 0)
private String name;

@ExcelColumn(index = 1)
private String age;

@ExcelColumn(index = 2,format="yyyy-MM-dd")
private Date birthday;
}

可不使用@ExcelColumn注解,如不使用,则使用字段默认顺序读取,意思是。excel中从左到右,对应bean中从上到下。

Excel默认导出

默认导出采用普通方式导出List< Bean >,适合小数据量场景,如数据量很大-10万+,建议使用DefaultStreamExcelBuilder,避免内存占用过多。

导出需使用到如下注解

  • @ExcelModel(includeAllField,excludeParent,workbookType,sheetName,useFieldNameAsTitle,defaultValue)(可选,用于全局设定, 一般情况下只需要使用sheetName)
  • @IgnoreColumn(可选,用于排出不需要导出的字段)
  • @ExcelColumn(title,order,format,groups,defaultValue,style)

默认导出默认计算宽度、斑马线背景色,若无需上述样式,请调用 noStyle() 方法

对应注解详情请见:注解

附件导出示例:

1
2
3
4
5
6
7
8
@GetMapping("/default/excel/example")
public void defaultBuild(HttpServletResponse response) throws Exception {
List<ArtCrowd> dataList = this.getDataList();
Workbook workbook = DefaultExcelBuilder.of(ArtCrowd.class)
.build(dataList);
AttachmentExportUtil.export(workbook, "艺术生信息", response);
}

附件加密导出示例:

1
2
3
4
5
6
7
@GetMapping("/default/excel/example")
public void defaultBuild(HttpServletResponse response) throws Exception {
List<ArtCrowd> dataList = this.getDataList();
Workbook workbook = DefaultExcelBuilder.of(ArtCrowd.class)
.build(dataList);
AttachmentExportUtil.encryptExport(workbook, "艺术生信息", response,"123456");
}

文件导出示例:

1
2
3
4
5
List<ArtCrowd> dataList = this.getDataList();
Workbook workbook = DefaultExcelBuilder.of(ArtCrowd.class)
.build(dataList);
FileExportUtil.export(workbook, new File("/User/demo.xlsx"));

文件加密导出示例:

1
2
3
4
5
List<ArtCrowd> dataList = this.getDataList();
Workbook workbook = DefaultExcelBuilder.of(ArtCrowd.class)
.build(dataList);
FileExportUtil.encryptExport(workbook, new File("/User/demo.xlsx"),"123456");

单纯数据获取:

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
private List<ArtCrowd> getDataList() {
List<ArtCrowd> dataList = new ArrayList<>(1000);
for (int i = 0; i < 1000; i++) {
ArtCrowd artCrowd = new ArtCrowd();
artCrowd.setName("李四");
artCrowd.setAge(18);
artCrowd.setGender("Woman");
artCrowd.setPaintingLevel("一级证书");
artCrowd.setDance(true);
artCrowd.setAssessmentTime(LocalDateTime.now());
artCrowd.setHobby("钓鱼");
dataList.add(artCrowd);
}
return dataList;
}

@ExcelModel(sheetName = "艺术生")
public class ArtCrowd {

@ExcelColumn(order = 0, title = "姓名")
private String name;

@ExcelColumn(order = 1, title = "年龄")
private Integer age;

@ExcelColumn(order = 2, title = "性别")
private String gender;

@ExcelColumn(order = 3,title = "绘画等级")
private String paintingLevel;

@ExcelColumn(order = 4, title = "是否会跳舞")
private boolean dance;

@ExcelColumn(order = 5, title = "考核时间", format = "yyyy-MM-dd HH:mm:ss")
private LocalDateTime assessmentTime;

@IgnoreColumn
private String hobby;
}

Excel模板构建

所有Excel构建器均不可单例化,务必注意!!!

模板构建,核心原理是根据模板引擎渲染出符合需求布局的Html Table元素,构建器迭代table元素中的单元格渲染出Excel单元格。

导出模板引擎选定

以下模板引擎默认均未被引入,使用者可根据自身需要选择在pom.xml中声明引入。

以下模板引擎版本为最低版本号。

使用时根据实际需要自行选择就行了,或者你会哪个引擎就用哪个引擎就Ok。

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
<dependency>
<groupId>com.ibeetl</groupId>
<artifactId>beetl</artifactId>
<version>2.7.23</version>
</dependency>

<dependency>
<groupId>org.freemarker</groupId>
<artifactId>freemarker</artifactId>
<version>2.3.23</version>
</dependency>

<dependency>
<groupId>org.codehaus.groovy</groupId>
<artifactId>groovy-templates</artifactId>
<version>2.4.13</version>
</dependency>

<dependency>
<groupId>org.thymeleaf</groupId>
<artifactId>thymeleaf</artifactId>
<version>2.1.6.RELEASE</version>
</dependency>

<dependency>
<groupId>org.apache.velocity</groupId>
<artifactId>velocity</artifactId>
<version>1.7</version>
</dependency>

<dependency>
<groupId>com.jfinal</groupId>
<artifactId>enjoy</artifactId>
<version>4.8</version>
</dependency>

Workbook生成

已存在Html文件时,使用这种方式,Html文件不局限于放在项目的classpath(如:resources)下,也无需模板引擎

1
2
3
4
5
6
7
8
// get html file
File htmlFile = new File("/Users/liaochong/Downloads/example.html");

// read the html file and use default excel style to create excel
Workbook workbook = HtmlToExcelFactory.readHtml(htmlFile).useDefaultStyle().build();

// this is a example,you can write the workbook to any valid outputstream
FileExportUtil.export(workbook, new File("/Users/liaochong/Downloads/excel.xlsx"));

使用内置的Freemarker等模板引擎Excel构建器,模板文件应当存放在classpath下,具体请参照项目中的example

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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
/**
* use non-default-style excel builder
* 模板文件放置在resources下
*
* @param response response
*/
@GetMapping("/freemarker/example")
public void build(HttpServletResponse response) {
Map<String, Object> dataMap = this.getDataMap();
try (ExcelBuilder excelBuilder = new FreemarkerExcelBuilder()) {
Workbook workbook = excelBuilder
// fileTemplate(dirPath,fileName)
.classpathTemplate("/templates/freemarkerToExcelExample.ftl")
.build(dataMap);
AttachmentExportUtil.export(workbook, "freemarker_excel", response);
}
}

/**
* use default-style excel builder
* 模板文件放置在resources下
*
* @param response response
*/
@GetMapping("/freemarker/defaultStyle/example")
public void buildWithDefaultStyle(HttpServletResponse response) {
Map<String, Object> dataMap = this.getDataMap();
try (ExcelBuilder excelBuilder = new FreemarkerExcelBuilder()){
Workbook workbook = excelBuilder
// fileTemplate(dirPath,fileName)
.classpathTemplate("/templates/freemarkerToExcelExample.ftl")
.useDefaultStyle()
.build(dataMap);
AttachmentExportUtil.export(workbook, "freemarker_excel", response);
}
}

private Map<String, Object> getDataMap() {
Map<String, Object> dataMap = new HashMap<>();
dataMap.put("sheetName", "freemarker_excel_example");

List<String> titles = new ArrayList<>();
titles.add("Category");
titles.add("Product Name");
titles.add("Count");
dataMap.put("titles", titles);

List<Product> data = new ArrayList<>();
for (int i = 0; i < 10; i++) {
Product product = new Product();
if (i % 2 == 0) {
product.setCategory("蔬菜");
product.setName("小白菜");
product.setCount(100);
} else {
product.setCategory("电子产品");
product.setName("ipad");
product.setCount(999);
}
data.add(product);
}
dataMap.put("data", data);
return dataMap;
}

模板示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<table>
<caption>${sheetName}</caption>
<thead>
<tr style="background-color: #6495ED">
<th colspan="3" style="text-align: center;vertical-align: middle;font-weight: bold;font-size: 14px;">产品介绍</th>
</tr>
<tr>
<#list titles as title>
<th>${title}</th>
</#list>
</tr>
</thead>
<tbody>
<#list data as item>
<tr>
<td>${item.category}</td>
<td>${item.name}</td>
<td>${item.count}</td>
<td url>百度地址</td>
</tr>
</#list>
</tbody>
</table>

模板中的样式请看这里样式支持

csv文件导出

一般方式导出

1
2
3
4
try(Csv csv = CsvBuilder.of(People.class).build(getDataList())){
AttachmentExportUtil.export(csv.getFilePath(), "test.csv", response);
// csv.write(Paths.get("/User/append.csv"));
}

Map导出

Map类型为Map<String,Object>

1
2
3
4
try(Csv csv = CsvBuilder.of(Map.class).build(getMapList())){
AttachmentExportUtil.export(csv.getFilePath(), "test.csv", response);
// csv.write(Paths.get("/User/append.csv"));
}

追加方式导出

1
2
3
4
5
6
7
8
9
try(CsvBuilder<CsvPeople> csvBuilder = CsvBuilder.of(People.class)){
for (int i = 0; i < 10; i++) {
csvBuilder.append(data(1000));
}
Csv csv = csvBuilder.build();
AttachmentExportUtil.export(csv.getFilePath(), "test.csv", response);
// csv.write(Paths.get("/User/append.csv"));
}

二次文件追加

1
2
3
4
5
6
7
8
9
try(CsvBuilder<CsvPeople> csvBuilder = CsvBuilder.of(People.class).noTitles()){
for (int i = 0; i < 10; i++) {
csvBuilder.append(data(1000));
}
Csv csv = csvBuilder.build();
// 该种方式会在原append.csv文件继续追加数据,而不是覆盖
csv.write(Paths.get("/User/append.csv"),true);
}

Bean定义

1
2
3
4
5
6
7
8
9
10
11
public class People {

@ExcelColumn(order = 0, title = "姓名")
private String name;

@ExcelColumn(order = 1, title = "年龄")
private Integer age;

@ExcelColumn(order = 2, title = "性别")
private String gender;
}

与excel导出注解相比,支持属性减少,width、style属性将不被支持,注解请移步: 注解

多sheet导入

多sheet导入,指的是SaxExcelReader多sheet导入,不包含DefaultExcelReader。

按sheet索引多sheet导入

1
SaxExcelReader.of(People.class).sheets(0,1).read(excelFile);

按sheet名称多sheet导入

1
SaxExcelReader.of(People.class).sheets("sheet名称1","sheet名称2").read(excelFile);

多sheet导出

DefaultExcelBuilder、DefaultStreamExcelBuilder

1
2
3
Workbook workbook1=DefaultExcelBuilder.of(A.class).build(dataList);
// of(class,workbook)
Workbook workbook2=DefaultExcelBuilder.of(B.class , workbook1).build(dataList);

模板导出

模板导出情况下,每一个table就是一个sheet。

如需多table导出在同一sheet中,使用sheetStrategy(SheetStrategy.ONE_SHEET)

自定义样式

本模块仅针对DefaultExcelBuilder、DefaultStreamExcelBuilder,模板构建天然支持。

标题(title)样式自定义

1
2
@ExcelColumn(style={"title->color:red","cell->color:green"})
Integer age;

内容行样式自定义

1
2
@ExcelColumn(style="cell->color:green")
Integer age;

方法调用设定样式

1
2
3
DefaultExcelBuilder.of(ArtCrowd.class)
.style("title->color:red","background-color:green;")
.build(dataList);

使用 -> 分隔符:

  • title标明该样式针对标题;
  • cell标明该样式针对内容行;
  • odd标明该样式针对奇数内容行;
  • even标明该样式针对偶数内容行;

具体样式支持,请移步样式支持

单元格内换行

DefaultExcelBuilder/DefaultSteamExcelBuilder

默认上述构建器会自动换行,@ExcelModel(wrapText=true)

模板

如需在模板的单元格内换行,请使用
或p标签。

图片导出

DefaultExcelBuilder、DefaultStreamExcelBuilder图片导出

图片导出属性定义:

1
2
@ExcelColumn(fileType = FileType.IMAGE)
File image;

属性类型必须是File,后续会增加其他类型支持。

模板图片导出

td单元格内含图片即可

1
2
3
<td>
<img src="图片绝对路径"/>
</td>

图片导入

图片导入仅DefaultExcelReader支持,属性类型为InputStream,实际赋值类型为ByteArrayInputStream。

1
2
3
4
class People {
@ExcelColumn(index=0)
InputStream picture;
}

单元格设置

默认情况下,程序会自动判别单元格内容类型,目前自动识别类型有以下三种:

  • String
  • Double
  • Boolean

如果String的内容全为数字时,导出时有可能会被使用科学计数法展示。解决这种情况强制使用String的方式构建即可。

设置为字符串类型数据可做如下操作:

在td上增加属性string设置该单元格类型为字符串

1
2
3
4
5
<table>
<tr>
<td string>19</td>
</tr>
</table>

设置为数值类型数据可做如下操作:

1
2
3
4
5
<table>
<tr>
<td double>19</td>
</tr>
</table>

设置为布尔类型数据可做如下操作:

1
2
3
4
5
<table>
<tr>
<td boolean>true</td>
</tr>
</table>

添加水印

不支持.xls文件添加水印,支持XSSF、SXSSF模式下添加水印,如数据量过大,可能会造成内存溢出,请注意⚠️

1
2
3
4
5
6
7
import com.github.liaochong.myexcel.utils.WatermarkUtil;

List<ArtCrowd> dataList = this.getDataList();
Workbook workbook = DefaultExcelBuilder.of(ArtCrowd.class).build(dataList);
// 使用水印工具添加水印
WatermarkUtil.addWatermark(workbook,"艺术生水印");
AttachmentExportUtil.export(workbook, "艺术生信息", response);

模板相关

在html中设置excel的sheet名称

设置sheet name:在表格里添加sheet名称

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Document</title>
</head>
<body>
<table>
<caption>sheet名称</caption>
<thead>
<tr>
<th></th>
<th></th>
</tr>
</thead>
<tbody>
<tr>
<td></td>
<td></td>
</tr>
</tbody>
</table>
</body>
</html>

样式支持

宽度(widht)

CSSValueExample
width数值,如15pxstyle=”width: 15px”

高度(height)

CSSValueExample
height数值,如15px,特别注意⚠️:如模板使用,该样式只能放置在 tr 上style=”height: 15px”

背景色(background)

CSSValueExample
background-color十六进制:#FFFFFF、RGB:rgb(0,255,255)、常用背景色名称:green,推荐使用十六进制style=”background-color:#ABFF00”

边框(border)

CSSValueExample
border-stylethin、dashed、medium、dotted、thick、double、hair、medium_dashed、dash_dotstyle=”border-style:thin”
border-top-stylethin、dashed、medium、dotted、thick、double、hair、medium_dashed、dash_dotstyle=”border-top-style:thin”
border-right-stylethin、dashed、medium、dotted、thick、double、hair、medium_dashed、dash_dotstyle=”border-right-style:thin”
border-bottom-stylethin、dashed、medium、dotted、thick、double、hair、medium_dashed、dash_dotstyle=”border-bottom-style:thin”
border-left-stylethin、dashed、medium、dotted、thick、double、hair、medium_dashed、dash_dotstyle=”border-left-style:thin”

字体(font)

CSSValueExample
colorgreenstyle=”color:green;”
font-size数值:14px(只会截取数值,非数值部分会被删除)style=”font-size:14px”
font-familyTimes New Roman(不支持备选字体)style=”font-family:Times New Roman”
font-styleitalicstyle=”font-style:italic”
font-weightboldstyle=”font-weight:bold”
text-decorationtext-decorationstyle=”text-decoration: underline”

对齐方式(align)

CSSValueExample
text-aligngeneral,left,center,right,fill,justify,center_selection,distributedstyle=”text-align:center”
vertical-aligntop,center,bottom,justify,distributedstyle=”vertical-align:center”

隐藏(visibility)

CSSValueExample
visibilityhidden:隐藏,目前仅设置在tr上生效< tr style=”visibility: hidden” >

自动换行(word-break)

CSSValueExample
word-breakbreak-all:自动换行< tr style=”word-break: break-all” >、< td style=”word-break: break-all” >

注解

注解说明

  • 注解

    @ExcelModel

  • 位置

    Class

  • 方法

    • includeAllField:是否导出该类的所有字段,默认为true
    • excludeParent:是否排除父类字段,默认为false
    • workbookType:设置工作簿类型,默认WorkbookType.SXLSX,不建议修改
    • sheetName:设置导出的工作簿sheet名称
    • useFieldNameAsTitle:设置是否直接使用字段名称作为导出标题,默认false
    • defaultValue:设置全局导出时,字段为Null时的默认值,如字段也包含defaultValue,则进行覆盖
    • wrapText:是否开启自动换行,默认开启
  • dateFormat:设置全局LocalDate格式,默认为yyyy-MM-dd
  • dateTimeFormat:设置全局Date\LocalDateTime格式,默认为yyyy-MM-dd HH:mm:ss
  • ignoreStaticFields:是否忽略静态字段导出,默认忽略
  • titleSeparator:标题分离器,用于多级标题,默认为“->”
  • |设置导出Excel的公共属性 @ExcelColumn|Field|
  • title:导出时使用,该字段对应导出列标题
  • order:导出时使用,该字段对应的导出展示顺序,初始为0,建议按列顺序设置,如字段顺序就是列顺序,可不设置
  • format:导入导出时使用,当字段类型为时间类型、金钱类型时生效,用于设置该时间类型字段格式化,如yyyy-MM-dd HH:mm:ss
  • groups:导出时使用,当前字段所属分组,构建时根据传入的分组选择导出的字段
  • index:导入时使用,用于标志该字段对应的Excel列,从0开始,不允许重复
  • defaultValue:导出时使用,设置导出时字段为Null时的默认值
  • width:导出时使用,用于自定义单元格宽度,配合AutoWidthStrategy.CUSTOM_WIDTH使用
  • style:自定义样式
  • 设置导入、导出字段属性 @IgnoreColumn,排除该字段的导出

项目地址

myexcel

伴我同行


本博客所有文章除特别声明外,均采用 CC BY-SA 4.0 协议 ,转载请注明出处!