Excel导入导出。

本文最后更新于:1 年前

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

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

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

依赖

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

<!--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
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() 方法

对应注解详情请见:注解

附件导出示例:

@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);
}

附件加密导出示例:

@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");
}

文件导出示例:

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

文件加密导出示例:

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

单纯数据获取:

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。

<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)下,也无需模板引擎

// 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

/**
* 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;
}

模板示例

<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文件导出

一般方式导出

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>

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

追加方式导出

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"));
}

二次文件追加

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定义

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导入

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

按sheet名称多sheet导入

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

多sheet导出

DefaultExcelBuilder、DefaultStreamExcelBuilder

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)样式自定义

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

内容行样式自定义

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

方法调用设定样式

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

使用 -> 分隔符:

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

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

单元格内换行

DefaultExcelBuilder/DefaultSteamExcelBuilder

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

模板

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

图片导出

DefaultExcelBuilder、DefaultStreamExcelBuilder图片导出

图片导出属性定义:

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

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

模板图片导出

td单元格内含图片即可

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

图片导入

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

class People {
   @ExcelColumn(index=0)
   InputStream picture;
}

单元格设置

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

  • String
  • Double
  • Boolean

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

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

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

<table>
    <tr>
        <td string>19</td>
    </tr>
</table>

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

<table>
    <tr>
        <td double>19</td>
    </tr>
</table>

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

<table>
    <tr>
        <td boolean>true</td>
    </tr>
</table>

添加水印

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

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:在表格里添加<caption>sheet名称

<!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

伴我同行


Excel导入导出。
https://wangijun.com/2021/08/16/java-02/
作者
无良芳
发布于
2021年8月16日
许可协议