湖北省建设招投标监督机构网站,六安做网站的公司,桂林论坛天涯社区,网站建设 网址导航Dhatim FastExcel 读写 Excel 文件 一、说明1、主要特点2、应用场景 二、使用方法1、引入依赖2、Sheet 数据3、读取 Excel4、写入 Excel 一、说明
Github 地址#xff1a;Dhatim FastExcel
Dhatim FastExcel是一个高性能、轻量级的Java库#xff0c;专门用于读取和写入Exce… Dhatim FastExcel 读写 Excel 文件 一、说明1、主要特点2、应用场景 二、使用方法1、引入依赖2、Sheet 数据3、读取 Excel4、写入 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 xmlnshttp://maven.apache.org/POM/4.0.0 xmlns:xsihttp://www.w3.org/2001/XMLSchema-instancexsi:schemaLocationhttp://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsdmodelVersion4.0.0/modelVersiongroupIdcom.wxhntmy/groupIdartifactIddhatim-fastexcel/artifactIdversion1.0.0/versionpackagingjar/packagingnamedhatim-fastexcel/nameurlhttp://maven.apache.org/urlpropertiesproject.build.sourceEncodingUTF-8/project.build.sourceEncodingmaven.compiler.source11/maven.compiler.sourcemaven.compiler.target11/maven.compiler.targetfastexcel.version0.18.4/fastexcel.versioncommons-io.version2.16.1/commons-io.versioncommons-lang3.version3.16.0/commons-lang3.versionlog4j.version2.24.0/log4j.versionslf4j.version2.0.16/slf4j.versionfastjson2.version2.0.32/fastjson2.versionjunit-jupiter.version5.10.2/junit-jupiter.versionjunit-platform.version1.10.2/junit-platform.version/propertiesdependenciesdependencygroupIdorg.dhatim/groupIdartifactIdfastexcel/artifactIdversion${fastexcel.version}/version/dependencydependencygroupIdorg.dhatim/groupIdartifactIdfastexcel-reader/artifactIdversion${fastexcel.version}/version/dependencydependencygroupIdcommons-io/groupIdartifactIdcommons-io/artifactIdversion${commons-io.version}/version/dependencydependencygroupIdorg.apache.commons/groupIdartifactIdcommons-lang3/artifactIdversion${commons-lang3.version}/version/dependencydependencygroupIdcom.alibaba.fastjson2/groupIdartifactIdfastjson2/artifactIdversion${fastjson2.version}/version/dependencydependencygroupIdorg.apache.logging.log4j/groupIdartifactIdlog4j-core/artifactIdversion${log4j.version}/version/dependencydependencygroupIdorg.apache.logging.log4j/groupIdartifactIdlog4j-api/artifactIdversion${log4j.version}/version/dependencydependencygroupIdorg.slf4j/groupIdartifactIdslf4j-reload4j/artifactIdversion${slf4j.version}/version/dependency!--junit5--dependencygroupIdorg.junit.jupiter/groupIdartifactIdjunit-jupiter/artifactIdversion${junit-jupiter.version}/version/dependencydependencygroupIdorg.junit.jupiter/groupIdartifactIdjunit-jupiter-api/artifactIdversion${junit-jupiter.version}/version/dependencydependencygroupIdorg.junit.platform/groupIdartifactIdjunit-platform-runner/artifactIdversion${junit-platform.version}/version/dependencydependencygroupIdorg.junit.platform/groupIdartifactIdjunit-platform-launcher/artifactIdversion${junit-platform.version}/versionscopetest/scope/dependencydependencygroupIdorg.junit.platform/groupIdartifactIdjunit-platform-console-standalone/artifactIdversion${junit-platform.version}/versionscopetest/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 ListString header new ArrayList();/*** ListMapString, String 的表格数据*/private ListMapString, String dataMapList new ArrayList();public ListMapString, String getDataMapList() {return dataMapList;}public void setDataMapList(ListMapString, String dataMapList) {this.dataMapList dataMapList;}public ListString getHeader() {return header;}public void setHeader(ListString 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)) {OptionalSheet sheetOptional wb.findSheet(sheetName);if (sheetOptional.isEmpty()) {throw new RuntimeException(读取Sheet数据失败);}Sheet sheet sheetOptional.get();sheetData.setSheetName(sheet.getName());ListRow rows sheet.read();Row header rows.get(0);ListString headerList new ArrayList();for (int i 0; i header.getCellCount(); i) {headerList.add(header.getCellText(i));}sheetData.setHeader(headerList);ListMapString, 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)) {OptionalSheet sheetOptional wb.getSheet(index);if (sheetOptional.isEmpty()) {throw new RuntimeException(读取Sheet数据失败);}Sheet sheet sheetOptional.get();sheetData.setSheetName(sheet.getName());ListRow rows sheet.read();Row header rows.get(0);ListString headerList new ArrayList();for (int i 0; i header.getCellCount(); i) {headerList.add(header.getCellText(i));}sheetData.setHeader(headerList);ListMapString, 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)) {OptionalSheet sheetOptional wb.getSheet(index);if (sheetOptional.isEmpty()) {throw new RuntimeException(读取Sheet数据失败);}Sheet sheet sheetOptional.get();sheetData.setSheetName(sheet.getName());try (StreamRow rows sheet.openStream()) {AtomicInteger cnt new AtomicInteger();ListString headerList new ArrayList();ListMapString, 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 {MapString, 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 读取到的 ListRow* param headerList 读取到的表头* return 转换后的数据 ListMapString, String*/private static ListMapString, String getDataMapList(ListRow rows, ListString headerList) {ListMapString, String dataMapList new ArrayList();for (int i 1; i rows.size(); i) {Row row rows.get(i);MapString, 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());ListString header sheetData.getHeader();ListMapString, 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, ListString header, ListMapString, 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, ListSheetData 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);ListCompletableFutureVoid 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());CompletableFutureVoid cf CompletableFuture.runAsync(() - {ListString header sheetData.getHeader();ListMapString, 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);}}
}
