Dhatim FastExcel 读写 Excel 文件
- 一、说明
- 1、主要特点
- 2、应用场景
- 二、使用方法
- 1、引入依赖
- 2、Sheet 数据
- 3、读取 Excel
- 4、写入 Excel
一、说明
Github 地址：Dhatim FastExcel
Dhatim FastExcel是一个高性能、轻量级的Java库，专门用于读取和写入Excel文件（包括.xlsx和.xls格式）。以下是对Dhatim FastExcel的详细介绍：
1、主要特点
- 高速读写：FastExcel采用直接操作字节流的方式，避免了内存中创建大量对象，从而大大提高了读写速度。据官方宣称，其性能可以达到Apache POI的20倍。
- 轻量级：FastExcel不依赖任何其他库，如Apache POI或OpenCSV，这使得它的体积更小，更容易集成到项目中。
- 易于使用：其API设计简洁直观，无论是读取还是写入，都可以通过几行代码轻松实现。
- 兼容性：FastExcel支持所有版本的Excel文件，包括2003版的.xls和2007及以后版本的.xlsx。
- 多线程支持：支持多线程读写，可以在处理大型文件时充分利用多核处理器的性能。
- 内存友好：对内存占用极低，即使处理大文件也不会造成内存压力。
- 灵活配置：可以根据需要自定义行列读写策略，适应不同需求。
2、应用场景
- 数据导入导出：在Web应用中，允许用户批量上传或下载Excel数据。
- 数据分析：对大量Excel数据进行预处理或转换。
- 自动化报告生成：基于模板快速生成大量个性化的Excel报表。
二、使用方法
1、引入依赖
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.wxhntmy</groupId>
<artifactId>dhatim-fastexcel</artifactId>
<version>1.0.0</version>
<packaging>jar</packaging>
<name>dhatim-fastexcel</name>
<url>http://maven.apache.org</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>11</maven.compiler.source>
<maven.compiler.target>11</maven.compiler.target>
<fastexcel.version>0.18.4</fastexcel.version>
<commons-io.version>2.16.1</commons-io.version>
<commons-lang3.version>3.16.0</commons-lang3.version>
<log4j.version>2.24.0</log4j.version>
<slf4j.version>2.0.16</slf4j.version>
<fastjson2.version>2.0.32</fastjson2.version>
<junit-jupiter.version>5.10.2</junit-jupiter.version>
<junit-platform.version>1.10.2</junit-platform.version>
</properties>
<dependencies>
<dependency>
<groupId>org.dhatim</groupId>
<artifactId>fastexcel</artifactId>
<version>${fastexcel.version}</version>
</dependency>
<dependency>
<groupId>org.dhatim</groupId>
<artifactId>fastexcel-reader</artifactId>
<version>${fastexcel.version}</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>${commons-io.version}</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>${commons-lang3.version}</version>
</dependency>
<dependency>
<groupId>com.alibaba.fastjson2</groupId>
<artifactId>fastjson2</artifactId>
<version>${fastjson2.version}</version>
</dependency>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-core</artifactId>
<version>${log4j.version}</version>
</dependency>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-api</artifactId>
<version>${log4j.version}</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-reload4j</artifactId>
<version>${slf4j.version}</version>
</dependency>
<!--junit5-->
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter</artifactId>
<version>${junit-jupiter.version}</version>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-api</artifactId>
<version>${junit-jupiter.version}</version>
</dependency>
<dependency>
<groupId>org.junit.platform</groupId>
<artifactId>junit-platform-runner</artifactId>
<version>${junit-platform.version}</version>
</dependency>
<dependency>
<groupId>org.junit.platform</groupId>
<artifactId>junit-platform-launcher</artifactId>
<version>${junit-platform.version}</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.junit.platform</groupId>
<artifactId>junit-platform-console-standalone</artifactId>
<version>${junit-platform.version}</version>
<scope>test</scope>
</dependency>
</dependencies>
</project>
2、Sheet 数据
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* 表格数据实体类
*/
public class SheetData {
/**
* Sheet 名称
*/
private String sheetName;
/**
* 表头
*/
private List<String> header = new ArrayList<>();
/**
* List<Map<String, String>> 的表格数据
*/
private List<Map<String, String>> dataMapList = new ArrayList<>();
public List<Map<String, String>> getDataMapList() {
return dataMapList;
}
public void setDataMapList(List<Map<String, String>> dataMapList) {
this.dataMapList = dataMapList;
}
public List<String> getHeader() {
return header;
}
public void setHeader(List<String> header) {
this.header = header;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
}
3、读取 Excel
import org.dhatim.fastexcel.reader.ReadableWorkbook;
import org.dhatim.fastexcel.reader.Row;
import org.dhatim.fastexcel.reader.Sheet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Stream;
public class FastExcelRead {
private static final Logger logger = LoggerFactory.getLogger(FastExcelRead.class);
/**
* 读取Excel文件第 1 个Sheet
*
* @param file Excel文件
* @return sheet数据
*/
public static SheetData read(File file) {
return read(file, 0);
}
/**
* 读取Excel文件第 index 个Sheet
*
* @param file Excel文件
* @param sheetName Sheet名称
* @return sheet数据
*/
public static SheetData read(File file, String sheetName) {
SheetData sheetData = new SheetData();
try (InputStream is = new FileInputStream(file); ReadableWorkbook wb = new ReadableWorkbook(is)) {
Optional<Sheet> sheetOptional = wb.findSheet(sheetName);
if (sheetOptional.isEmpty()) {
throw new RuntimeException("读取Sheet数据失败！");
}
Sheet sheet = sheetOptional.get();
sheetData.setSheetName(sheet.getName());
List<Row> rows = sheet.read();
Row header = rows.get(0);
List<String> headerList = new ArrayList<>();
for (int i = 0; i < header.getCellCount(); i++) {
headerList.add(header.getCellText(i));
}
sheetData.setHeader(headerList);
List<Map<String, String>> dataMapList = getDataMapList(rows, headerList);
sheetData.setDataMapList(dataMapList);
} catch (Exception e) {
logger.error("Error: ", e);
}
return sheetData;
}
/**
* 读取Excel文件第 index 个Sheet
*
* @param file Excel文件
* @param index Sheet下标
* @return sheet数据
*/
public static SheetData read(File file, int index) {
SheetData sheetData = new SheetData();
try (InputStream is = new FileInputStream(file); ReadableWorkbook wb = new ReadableWorkbook(is)) {
Optional<Sheet> sheetOptional = wb.getSheet(index);
if (sheetOptional.isEmpty()) {
throw new RuntimeException("读取Sheet数据失败！");
}
Sheet sheet = sheetOptional.get();
sheetData.setSheetName(sheet.getName());
List<Row> rows = sheet.read();
Row header = rows.get(0);
List<String> headerList = new ArrayList<>();
for (int i = 0; i < header.getCellCount(); i++) {
headerList.add(header.getCellText(i));
}
sheetData.setHeader(headerList);
List<Map<String, String>> dataMapList = getDataMapList(rows, headerList);
sheetData.setDataMapList(dataMapList);
} catch (Exception e) {
logger.error("Error: ", e);
}
return sheetData;
}
/**
* 使用流读取Excel文件第 index 个Sheet（逐行读取）
*
* @param file Excel文件
* @param index Sheet下标
* @return sheet数据
*/
public static SheetData readByStream(File file, int index) {
SheetData sheetData = new SheetData();
try (InputStream is = new FileInputStream(file); ReadableWorkbook wb = new ReadableWorkbook(is)) {
Optional<Sheet> sheetOptional = wb.getSheet(index);
if (sheetOptional.isEmpty()) {
throw new RuntimeException("读取Sheet数据失败！");
}
Sheet sheet = sheetOptional.get();
sheetData.setSheetName(sheet.getName());
try (Stream<Row> rows = sheet.openStream()) {
AtomicInteger cnt = new AtomicInteger();
List<String> headerList = new ArrayList<>();
List<Map<String, String>> dataMapList = new ArrayList<>();
rows.forEach(r -> {
//表头
if (cnt.get() == 0) {
for (int i = 0; i < r.getCellCount(); i++) {
headerList.add(r.getCellText(i));
}
sheetData.setHeader(headerList);
}
//数据行
else {
Map<String, String> rowMap = new HashMap<>();
for (int j = 0; j < sheetData.getHeader().size(); j++) {
if (j > r.getCellCount()) {
rowMap.put(sheetData.getHeader().get(j), "");
} else {
rowMap.put(sheetData.getHeader().get(j), r.getCellText(j));
}
}
dataMapList.add(rowMap);
}
cnt.getAndIncrement();
});
sheetData.setDataMapList(dataMapList);
}
} catch (Exception e) {
logger.error("Error: ", e);
}
return sheetData;
}
/**
* 数据转换
*
* @param rows 读取到的 List<Row>
* @param headerList 读取到的表头
* @return 转换后的数据 List<Map<String, String>>
*/
private static List<Map<String, String>> getDataMapList(List<Row> rows, List<String> headerList) {
List<Map<String, String>> dataMapList = new ArrayList<>();
for (int i = 1; i < rows.size(); i++) {
Row row = rows.get(i);
Map<String, String> rowMap = new HashMap<>();
for (int j = 0; j < headerList.size(); j++) {
if (j > row.getCellCount()) {
rowMap.put(headerList.get(j), "");
} else {
rowMap.put(headerList.get(j), row.getCellText(j));
}
}
dataMapList.add(rowMap);
}
return dataMapList;
}
}
4、写入 Excel
import org.apache.commons.lang3.StringUtils;
import org.dhatim.fastexcel.Color;
import org.dhatim.fastexcel.Workbook;
import org.dhatim.fastexcel.Worksheet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.CompletableFuture;
public class FastExcelWrite {
private static final Logger logger = LoggerFactory.getLogger(FastExcelWrite.class);
/**
* 写入Excel文件
*
* @param file Excel文件
* @param sheetData Sheet数据
*/
public static void write(File file, SheetData sheetData) {
try (OutputStream os = new FileOutputStream(file); Workbook wb = new Workbook(os, "MyApplication", "1.0")) {
logger.info(">>>>>写入Excel文件：{}", file.getAbsolutePath());
// 设置全局默认字体
wb.setGlobalDefaultFont("宋体", 12);
logger.info(">>>>>设置全局默认字体为宋体，字号12");
if (StringUtils.isEmpty(sheetData.getSheetName())){
sheetData.setSheetName("Sheet1");
}
Worksheet ws = wb.newWorksheet(sheetData.getSheetName());
List<String> header = sheetData.getHeader();
List<Map<String, String>> dataMapList = sheetData.getDataMapList();
// 冻结第1行
ws.freezePane(0, 1);
logger.info(">>>>>冻结表头....");
// 命名单元格区域（单元格区域的名称只能包含字母、数字和下划线）
ws.range(0, 0, 0, header.size()).setName("header");
// 在单元格区域设置样式
ws.range(0, 0, 0, header.size()).style().horizontalAlignment("center").italic().set();
// 设置字体颜色和加粗显示
ws.range(0, 0, 0, header.size()).style().bold().fontColor(Color.RED).fillColor(Color.GREEN).set();
logger.info(">>>>>设置表头样式....");
//开启筛选
ws.setAutoFilter(0, 0, header.size() - 1);
logger.info(">>>>>开启筛选....");
setWorksheetData(ws, header, dataMapList);
} catch (Exception e) {
logger.error("Error: ", e);
}
}
/**
* 给工作表写入数据
*
* @param ws Worksheet
* @param header 表头
* @param dataMapList 行数据
*/
private static void setWorksheetData(Worksheet ws, List<String> header, List<Map<String, String>> dataMapList) {
logger.info(">>>>>写入表头....");
// 第一行为表头
for (int j = 0; j < header.size(); j++) {
ws.value(0, j, header.get(j));
}
logger.info(">>>>>写入表头完成....");
logger.info(">>>>>写入数据行....");
//第二行开始为数据
for (int i = 1; i < dataMapList.size(); i++) {
for (int j = 0; j < header.size(); j++) {
ws.value(i, j, dataMapList.get(i).get(header.get(j)));
}
}
logger.info(">>>>>写入数据行完成....");
}
/**
* 同一个Excel文件写入多个Sheet
* 每个工作表由不同的线程生成
*
* @param file Excel文件
* @param sheetDataList Sheet数据
*/
public static void writeMultipleSheet(File file, List<SheetData> sheetDataList) {
try (OutputStream os = new FileOutputStream(file); Workbook wb = new Workbook(os, "MyApplication", "1.0")) {
logger.info(">>>>>写入Excel文件：{}", file.getAbsolutePath());
// 设置全局默认字体
wb.setGlobalDefaultFont("宋体", 12);
logger.info(">>>>>设置全局默认字体为宋体，字号12");
List<CompletableFuture<Void>> futureList = new ArrayList<>(sheetDataList.size());
int sheetIndex = 1;
for (SheetData sheetData : sheetDataList) {
if (StringUtils.isEmpty(sheetData.getSheetName())){
sheetData.setSheetName("Sheet" + sheetIndex);
}
sheetIndex++;
Worksheet ws = wb.newWorksheet(sheetData.getSheetName());
CompletableFuture<Void> cf = CompletableFuture.runAsync(() -> {
List<String> header = sheetData.getHeader();
List<Map<String, String>> dataMapList = sheetData.getDataMapList();
// 冻结第1行
ws.freezePane(0, 1);
logger.info(">>>>>冻结表头....");
// 命名单元格区域（单元格区域的名称只能包含字母、数字和下划线）
ws.range(0, 0, 0, header.size()).setName("header");
// 在单元格区域设置样式
ws.range(0, 0, 0, header.size()).style().horizontalAlignment("center").italic().set();
// 设置字体颜色和加粗显示
ws.range(0, 0, 0, header.size()).style().bold().fontColor(Color.RED).fillColor(Color.GREEN).set();
logger.info(">>>>>设置表头样式....");
//开启筛选
ws.setAutoFilter(0, 0, header.size() - 1);
logger.info(">>>>>开启筛选....");
setWorksheetData(ws, header, dataMapList);
});
futureList.add(cf);
}
// 等待所有线程完成
CompletableFuture.allOf(futureList.toArray(new CompletableFuture[0])).get();
} catch (Exception e) {
logger.error("Error: ", e);
}
}
}
