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