EasyExcel 导出
在本指南中，我们将演示如何使用 EasyExcelUtils 和 EasyExcelResponseUtils 来支持 Excel 导出功能。EasyExcelUtils 主要用于将数据导出到本地文件，而 EasyExcelResponseUtils 则用于将导出的 Excel 表格文件直接下载到客户端。
1. 使用 EasyExcelUtils 导出数据到 Excel 文件
以下示例演示了如何使用 EasyExcelUtils 将查询结果导出到本地的 Excel 文件中。
依赖项
在开始之前，确保您的项目中已经引入了 api-table 和 EasyExcel 的依赖。以下是在 Maven 项目中添加依赖的方式：
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>4.0.3</version>
</dependency>
<dependency>
<groupId>com.litongjava</groupId>
<artifactId>api-table</artifactId>
<version>${api-table.version}</version>
</dependency>
示例代码：
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import com.litongjava.db.activerecord.Db;
import com.litongjava.db.activerecord.Row;
import com.litongjava.table.utils.EasyExcelUtils;
import com.litongjava.tio.utils.environment.EnvUtils;
public class ServiceExportDemo {
public static void main(String[] args) {
// 加载环境配置
EnvUtils.load();
// 配置数据库连接
new DbConfig().config();
// 查询数据
String sql = "select * from service where service_id < 1000";
List<Row> records = Db.find(sql);
// 导出数据到 Excel 文件
try (OutputStream outputStream = new FileOutputStream("service.xlsx")) {
EasyExcelUtils.write(outputStream, "service", records);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e1) {
e1.printStackTrace();
}
}
}
说明：
- 环境配置与数据库连接： 通过
EnvUtils.load()加载环境配置，并通过new DbConfig().config()配置数据库连接。 - 查询数据： 使用
Db.find(sql)执行 SQL 查询，获取结果集records。 - 导出数据： 使用
EasyExcelUtils.write()方法将数据导出到本地的 Excel 文件中。
2. 使用 EasyExcelResponseUtils 实现 Excel 文件的下载
以下示例展示了如何通过 EasyExcelResponseUtils 实现将 Excel 文件直接导出并提供客户端下载的功能。
示例代码：
import java.io.IOException;
import java.util.List;
import com.litongjava.db.activerecord.Db;
import com.litongjava.db.activerecord.Row;
import com.litongjava.table.utils.EasyExcelResponseUtils;
import com.litongjava.tio.boot.http.TioRequestContext;
import com.litongjava.tio.http.common.HttpRequest;
import com.litongjava.tio.http.common.HttpResponse;
import com.litongjava.annotation.RequestPath;
public class EexportController {
@RequestPath("/export-excel")
public HttpResponse exportExcel(String f, HttpRequest request) throws IOException {
// 生成文件名
String filename = "service_export_" + System.currentTimeMillis() + ".xlsx";
// 查询数据
String sql = "select * from service where service_id < 1000";
List<Row> records = Db.find(sql);
// 导出并提供下载
return EasyExcelResponseUtils.exportRecords(TioRequestContext.getResponse(), filename, "service", records);
}
}
说明：
- 生成文件名： 使用
System.currentTimeMillis()生成唯一的文件名，避免文件名冲突。 - 查询数据： 与
EasyExcelUtils类似，通过 SQL 查询获取数据记录。 - 导出并下载： 使用
EasyExcelResponseUtils.exportRecords()方法将数据导出为 Excel 文件，并将该文件作为 HTTP 响应提供下载。
通过以上两个示例，您可以轻松地在项目中集成 Excel 导出功能，无论是将数据保存到本地文件，还是直接提供客户端下载。
错误记录
java.lang.UnsatisfiedLinkError: /usr/java/jdk-17.0.12/lib/libfontmanager.so: libfreetype.so.6: cannot open shared object file
com.alibaba.excel.exception.ExcelGenerateException: java.lang.UnsatisfiedLinkError: /usr/java/jdk-17.0.12/lib/libfontmanager.so: libfreetype.so.6: cannot open shared object file: No such file or directory
at com.alibaba.excel.write.ExcelBuilderImpl.addContent(ExcelBuilderImpl.java:65)
at com.alibaba.excel.ExcelWriter.write(ExcelWriter.java:73)
at com.alibaba.excel.ExcelWriter.write(ExcelWriter.java:50)
at com.alibaba.excel.write.builder.ExcelWriterSheetBuilder.doWrite(ExcelWriterSheetBuilder.java:62)
at com.litongjava.table.utils.EasyExcelUtils.write(EasyExcelUtils.java:72)
at com.litongjava.table.utils.EasyExcelResponseUtils.exportRecords(EasyExcelResponseUtils.java:19)
at com.litongjava.tio.boot.admin.controller.ApiTableController.exportExcel(ApiTableController.java:183)
at com.litongjava.tio.boot.admin.controller.ApiTableControllerMethodAccess.invoke(Unknown Source)
at com.esotericsoftware.reflectasm.MethodAccess.invoke(MethodAccess.java:44)
at com.litongjava.tio.boot.http.handler.controller.DynamicRequestController.executeAction(DynamicRequestController.java:136)
at com.litongjava.tio.boot.http.handler.controller.DynamicRequestController.process(DynamicRequestController.java:53)
at com.litongjava.tio.boot.http.handler.internal.TioBootHttpRequestDispatcher.handler(TioBootHttpRequestDispatcher.java:360)
at com.litongjava.tio.http.server.HttpServerAioHandler.handler(HttpServerAioHandler.java:82)
at com.litongjava.tio.boot.server.TioBootServerHandler.handler(TioBootServerHandler.java:177)
at com.litongjava.tio.core.task.HandlePacketTask.handle(HandlePacketTask.java:72)
at com.litongjava.tio.core.task.DecodeTask.decode(DecodeTask.java:147)
at com.litongjava.tio.core.ReadCompletionHandler.completed(ReadCompletionHandler.java:83)
at com.litongjava.tio.core.ReadCompletionHandler.completed(ReadCompletionHandler.java:26)
at com.litongjava.enhance.channel.EnhanceAsynchronousServerChannel.doRead(EnhanceAsynchronousServerChannel.java:272)
at com.litongjava.enhance.channel.EnhanceAsynchronousChannelGroup.lambda$new$0(EnhanceAsynchronousChannelGroup.java:63)
at com.litongjava.enhance.channel.EnhanceAsynchronousChannelGroup$Worker.run(EnhanceAsynchronousChannelGroup.java:196)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:842)
Caused by: java.lang.UnsatisfiedLinkError: /usr/java/jdk-17.0.12/lib/libfontmanager.so: libfreetype.so.6: cannot open shared object file: No such file or directory
at java.base/jdk.internal.loader.NativeLibraries.load(Native Method)
at java.base/jdk.internal.loader.NativeLibraries$NativeLibraryImpl.open(NativeLibraries.java:388)
at java.base/jdk.internal.loader.NativeLibraries.loadLibrary(NativeLibraries.java:232)
at java.base/jdk.internal.loader.NativeLibraries.loadLibrary(NativeLibraries.java:174)
at java.base/jdk.internal.loader.NativeLibraries.findFromPaths(NativeLibraries.java:315)
at java.base/jdk.internal.loader.NativeLibraries.loadLibrary(NativeLibraries.java:285)
at java.base/java.lang.ClassLoader.loadLibrary(ClassLoader.java:2403)
at java.base/java.lang.Runtime.loadLibrary0(Runtime.java:818)
at java.base/java.lang.System.loadLibrary(System.java:1993)
at java.desktop/sun.font.FontManagerNativeLibrary$1.run(FontManagerNativeLibrary.java:58)
at java.base/java.security.AccessController.doPrivileged(AccessController.java:318)
at java.desktop/sun.font.FontManagerNativeLibrary.<clinit>(FontManagerNativeLibrary.java:33)
at java.desktop/sun.font.SunFontManager$1.run(SunFontManager.java:275)
at java.desktop/sun.font.SunFontManager$1.run(SunFontManager.java:273)
at java.base/java.security.AccessController.doPrivileged(AccessController.java:318)
at java.desktop/sun.font.SunFontManager.initStatic(SunFontManager.java:273)
at java.desktop/sun.font.SunFontManager.<clinit>(SunFontManager.java:268)
at java.base/java.lang.Class.forName0(Native Method)
at java.base/java.lang.Class.forName(Class.java:467)
at java.desktop/sun.font.FontManagerFactory$1.run(FontManagerFactory.java:83)
at java.base/java.security.AccessController.doPrivileged(AccessController.java:318)
at java.desktop/sun.font.FontManagerFactory.getInstance(FontManagerFactory.java:75)
at java.desktop/java.awt.Font.getFont2D(Font.java:526)
at java.desktop/java.awt.Font.canDisplayUpTo(Font.java:2282)
at java.desktop/java.awt.font.TextLayout.singleFont(TextLayout.java:469)
at java.desktop/java.awt.font.TextLayout.<init>(TextLayout.java:530)
at org.apache.poi.ss.util.SheetUtil.getDefaultCharWidthAsFloat(SheetUtil.java:352)
at org.apache.poi.xssf.streaming.AutoSizeColumnTracker.<init>(AutoSizeColumnTracker.java:117)
at org.apache.poi.xssf.streaming.SXSSFSheet.<init>(SXSSFSheet.java:106)
at org.apache.poi.xssf.streaming.SXSSFWorkbook.createAndRegisterSXSSFSheet(SXSSFWorkbook.java:694)
at org.apache.poi.xssf.streaming.SXSSFWorkbook.createSheet(SXSSFWorkbook.java:712)
at org.apache.poi.xssf.streaming.SXSSFWorkbook.createSheet(SXSSFWorkbook.java:104)
at com.alibaba.excel.util.WorkBookUtil.createSheet(WorkBookUtil.java:86)
at com.alibaba.excel.context.WriteContextImpl.createSheet(WriteContextImpl.java:223)
at com.alibaba.excel.context.WriteContextImpl.initSheet(WriteContextImpl.java:203)
at com.alibaba.excel.context.WriteContextImpl.currentSheet(WriteContextImpl.java:135)
at com.alibaba.excel.write.ExcelBuilderImpl.addContent(ExcelBuilderImpl.java:54)
... 23 more
RUN apt-get update \
&& apt-get install -y --no-install-recommends libfreetype6 fontconfig \
&& apt-get clean \
&& rm -rf /var/lib/apt/lists/*