HuTool工具类使用之Excel文档的导入导出

最新推荐文章于 2024-08-14 15:13:38 发布

m0_50798715

最新推荐文章于 2024-08-14 15:13:38 发布

阅读量2.4k

点赞数

本文详细介绍了如何使用Hutool工具类进行Excel文档的导入导出，包括Hutool的基本介绍、导出Excel文件的步骤以及读取Excel文件的三种方法，旨在简化开发中的Excel操作。

摘要由CSDN通过智能技术生成

HuTool工具类使用之Excel文档的导入导出

前言

在日常的工作开发中，Excel的导入和导出是必不可少的，如果自己写相应的导入导出方法，会显得十分繁琐，本文采用Hutool工具类实现的Excel导入导出功能，可以大幅度减少今后开发中Excel的导入导出的相关操作。

提示：以下是本篇文章正文内容，下面案例可供参考

一、Hutool是什么？

Hutool是一个小而全的Java工具类库，通过静态方法封装，降低相关API的学习成本，提高工作效率，使Java拥有函数式语言般的优雅，让Java语言也可以“甜甜的”。

Hutool中的工具方法来自于每个用户的精雕细琢，它涵盖了Java开发底层代码中的方方面面，它既是大型项目开发中解决小问题的利器，也是小型项目中的效率担当；

Hutool是项目中“util”包友好的替代，它节省了开发人员对项目中公用类和公用工具方法的封装时间，使开发专注于业务，同时可以最大限度的避免封装不完善带来的bug。

二、导出Excel文件

1.引入相关依赖

Java针对MS Office的操作的库屈指可数，比较有名的就是Apache的POI库。这个库异常强大，但是使用起来也并不容易。Hutool针对POI封装一些常用工具，使Java操作Excel等文件变得异常简单。Hutool-poi是针对Apache POI的封装，因此需要用户自行引入POI库,Hutool默认不引入。

需要注意的是，hutool-4.x的poi-ooxml 版本需高于 3.17，hutool-5.x的poi-ooxml 版本需高于 4.1.2；本文使用的依赖信息如下图所示：

<span style="color:#000000"><code class="language-c"><span style="color:#669900"><</span><span style="color:#669900">!</span><span style="color:#669900">--</span> hutool工具类依赖<span style="color:#669900">--</span><span style="color:#669900">></span>
<span style="color:#669900"><</span>dependency<span style="color:#669900">></span>
<span style="color:#669900"><</span>groupId<span style="color:#669900">></span>cn<span style="color:#999999">.</span>hutool<span style="color:#669900"><</span><span style="color:#669900">/</span>groupId<span style="color:#669900">></span>
<span style="color:#669900"><</span>artifactId<span style="color:#669900">></span>hutool<span style="color:#669900">-</span>all<span style="color:#669900"><</span><span style="color:#669900">/</span>artifactId<span style="color:#669900">></span>
<span style="color:#669900"><</span>version<span style="color:#669900">></span><span style="color:#98c379">5.4</span><span style="color:#98c379">.3</span><span style="color:#669900"><</span><span style="color:#669900">/</span>version<span style="color:#669900">></span>
<span style="color:#669900"><</span><span style="color:#669900">/</span>dependency<span style="color:#669900">></span>

<span style="color:#669900"><</span><span style="color:#669900">!</span><span style="color:#669900">--</span>POI依赖，对office进行操作<span style="color:#669900">--</span><span style="color:#669900">></span>
<span style="color:#669900"><</span>dependency<span style="color:#669900">></span>
<span style="color:#669900"><</span>groupId<span style="color:#669900">></span>org<span style="color:#999999">.</span>apache<span style="color:#999999">.</span>poi<span style="color:#669900"><</span><span style="color:#669900">/</span>groupId<span style="color:#669900">></span>
<span style="color:#669900"><</span>artifactId<span style="color:#669900">></span>poi<span style="color:#669900">-</span>ooxml<span style="color:#669900"><</span><span style="color:#669900">/</span>artifactId<span style="color:#669900">></span>
<span style="color:#669900"><</span>version<span style="color:#669900">></span><span style="color:#98c379">4.1</span><span style="color:#98c379">.2</span><span style="color:#669900"><</span><span style="color:#669900">/</span>version<span style="color:#669900">></span>
<span style="color:#669900"><</span><span style="color:#669900">/</span>dependency<span style="color:#669900">></span>
</code></span>

2.Excel导出功能的实现

Hutool将Excel写出封装为ExcelWriter，原理为包装了Workbook对象，每次调用merge（合并单元格）或者write（写出数据）方法后只是将数据写入到Workbook，并不写出文件，只有调用flush或者close方法后才会真正写出文件。由于机制原因，在写出结束后需要关闭ExcelWriter对象，调用close方法即可关闭，此时才会释放Workbook对象资源，否则带有数据的Workbook一直会常驻内存。代码如下所示：

<span style="color:#000000"><code class="language-c">@<span style="color:#61aeee">RequestMapping</span><span style="color:#999999">(</span>EXCEL_DOWNLOAD<span style="color:#999999">)</span>
public <span style="color:#c678dd">void</span> <span style="color:#61aeee">excelExport</span><span style="color:#999999">(</span>HttpServletResponse httpServletResponse<span style="color:#999999">)</span> throws IOException <span style="color:#999999">{</span>
UserDTO userDTO <span style="color:#669900">=</span> new <span style="color:#61aeee">UserDTO</span><span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
List<span style="color:#669900"><</span>UserDTO<span style="color:#669900">></span> userDTOS <span style="color:#669900">=</span> userService<span style="color:#999999">.</span><span style="color:#61aeee">selectUserDOBatch</span><span style="color:#999999">(</span>userDTO<span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#5c6370">//通过hutool工具创建的excel的writer，默认为xls格式</span>
ExcelWriter writer <span style="color:#669900">=</span> ExcelUtil<span style="color:#999999">.</span><span style="color:#61aeee">getWriter</span><span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#5c6370">//设置要导出到的sheet</span>
writer<span style="color:#999999">.</span><span style="color:#61aeee">setSheet</span><span style="color:#999999">(</span><span style="color:#669900">"表2"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
writer<span style="color:#999999">.</span><span style="color:#61aeee">setSheet</span><span style="color:#999999">(</span><span style="color:#669900">"表3"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#5c6370">//自定义excel标题和列名</span>
writer<span style="color:#999999">.</span><span style="color:#61aeee">addHeaderAlias</span><span style="color:#999999">(</span><span style="color:#669900">"id"</span><span style="color:#999999">,</span><span style="color:#669900">"用户ID"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
writer<span style="color:#999999">.</span><span style="color:#61aeee">addHeaderAlias</span><span style="color:#999999">(</span><span style="color:#669900">"userName"</span><span style="color:#999999">,</span><span style="color:#669900">"用户名"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
writer<span style="color:#999999">.</span><span style="color:#61aeee">addHeaderAlias</span><span style="color:#999999">(</span><span style="color:#669900">"loginPassword"</span><span style="color:#999999">,</span><span style="color:#669900">"密码"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
writer<span style="color:#999999">.</span><span style="color:#61aeee">addHeaderAlias</span><span style="color:#999999">(</span><span style="color:#669900">"email"</span><span style="color:#999999">,</span><span style="color:#669900">"邮箱"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
writer<span style="color:#999999">.</span><span style="color:#61aeee">addHeaderAlias</span><span style="color:#999999">(</span><span style="color:#669900">"createDate"</span><span style="color:#999999">,</span><span style="color:#669900">"数据创建日期"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#5c6370">//合并单元格后的标题行，使用默认标题样式</span>
writer<span style="color:#999999">.</span><span style="color:#61aeee">merge</span><span style="color:#999999">(</span><span style="color:#98c379">4</span><span style="color:#999999">,</span><span style="color:#669900">"用户基本信息表"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
writer<span style="color:#999999">.</span><span style="color:#61aeee">renameSheet</span><span style="color:#999999">(</span><span style="color:#98c379">0</span><span style="color:#999999">,</span><span style="color:#669900">"用户登录信息"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#5c6370">//一次性写出内容，使用默认样式，强制输出标题</span>
writer<span style="color:#999999">.</span><span style="color:#61aeee">write</span><span style="color:#999999">(</span>userDTOS<span style="color:#999999">,</span>true<span style="color:#999999">)</span><span style="color:#999999">;</span>

httpServletResponse<span style="color:#999999">.</span><span style="color:#61aeee">setContentType</span><span style="color:#999999">(</span><span style="color:#669900">"application/vnd.ms-excel;charset=utf-8"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#5c6370">//name是下载对话框的名称，不支持中文，想用中文名称需要进行utf8编码</span>
String excelName <span style="color:#669900">=</span> <span style="color:#669900">"用户基本信息表"</span><span style="color:#999999">;</span>
<span style="color:#5c6370">//excelName = new String(excelName.getBytes(),"utf-8");</span>
excelName <span style="color:#669900">=</span> URLEncoder<span style="color:#999999">.</span><span style="color:#61aeee">encode</span><span style="color:#999999">(</span>excelName<span style="color:#999999">,</span> <span style="color:#669900">"utf-8"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
httpServletResponse<span style="color:#999999">.</span><span style="color:#61aeee">setHeader</span><span style="color:#999999">(</span><span style="color:#669900">"Content-Disposition"</span><span style="color:#999999">,</span> <span style="color:#669900">"attachment;filename="</span> <span style="color:#669900">+</span> excelName <span style="color:#669900">+</span><span style="color:#669900">".xls"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>

<span style="color:#5c6370">//将excel文件信息写入输出流，返回给调用者</span>
ServletOutputStream excelOut <span style="color:#669900">=</span> null<span style="color:#999999">;</span>
try <span style="color:#999999">{</span>
excelOut <span style="color:#669900">=</span> httpServletResponse<span style="color:#999999">.</span><span style="color:#61aeee">getOutputStream</span><span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
writer<span style="color:#999999">.</span><span style="color:#61aeee">flush</span><span style="color:#999999">(</span>excelOut<span style="color:#999999">,</span>true<span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#999999">}</span> catch <span style="color:#999999">(</span>IOException e<span style="color:#999999">)</span> <span style="color:#999999">{</span>
e<span style="color:#999999">.</span><span style="color:#61aeee">printStackTrace</span><span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#999999">}</span>finally <span style="color:#999999">{</span>
writer<span style="color:#999999">.</span><span style="color:#61aeee">close</span><span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#999999">}</span>
IoUtil<span style="color:#999999">.</span><span style="color:#61aeee">close</span><span style="color:#999999">(</span>excelOut<span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#999999">}</span>
</code></span>

以上导出的Excel格式为.xls，如果想要导出格式为.xlsx的excel文件，只需修改上图所示的相应位置代码即可，修改代码为：

<span style="color:#000000"><code class="language-c"><span style="color:#5c6370">//设置返回excel的格式为xlsx</span>
httpServletResponse<span style="color:#999999">.</span><span style="color:#61aeee">setContentType</span><span style="color:#999999">(</span><span style="color:#669900">"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
httpServletResponse<span style="color:#999999">.</span><span style="color:#61aeee">setHeader</span><span style="color:#999999">(</span><span style="color:#669900">"Content-Disposition"</span><span style="color:#999999">,</span><span style="color:#669900">"attachment;filename="</span><span style="color:#669900">+</span> URLEncoder<span style="color:#999999">.</span><span style="color:#61aeee">encode</span><span style="color:#999999">(</span><span style="color:#669900">"用户信息表"</span><span style="color:#999999">,</span><span style="color:#669900">"utf-8"</span><span style="color:#999999">)</span> <span style="color:#669900">+</span> <span style="color:#669900">".xlsx"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
</code></span>

生成的excel文件如下如所示：

在这里插入图片描述

二、导出Excel文件

1.Excel读取-ExcelReader

Excel文件的导入分为三种情况：

1.读取Excel中所有行和列，都用列表表示

<span style="color:#000000"><code class="language-c">ExcelReader reader <span style="color:#669900">=</span> ExcelUtil<span style="color:#999999">.</span><span style="color:#61aeee">getReader</span><span style="color:#999999">(</span><span style="color:#669900">"d:/aaa.xlsx"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
List<span style="color:#669900"><</span>List<span style="color:#669900"><</span>Object<span style="color:#669900">>></span> readAll <span style="color:#669900">=</span> reader<span style="color:#999999">.</span><span style="color:#61aeee">read</span><span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
</code></span>

2.读取为Map列表，默认第一行为标题行，Map中的key为标题，value为标题对应的单元格值。

<span style="color:#000000"><code class="language-c">ExcelReader reader <span style="color:#669900">=</span> ExcelUtil<span style="color:#999999">.</span><span style="color:#61aeee">getReader</span><span style="color:#999999">(</span><span style="color:#669900">"d:/aaa.xlsx"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
List<span style="color:#669900"><</span>Map<span style="color:#669900"><</span>String<span style="color:#999999">,</span>Object<span style="color:#669900">>></span> readAll <span style="color:#669900">=</span> reader<span style="color:#999999">.</span><span style="color:#61aeee">readAll</span><span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
</code></span>

3.读取为Bean列表，Bean中的字段名为标题，字段值为标题对应的单元格值。

<span style="color:#000000"><code class="language-c">ExcelReader reader <span style="color:#669900">=</span> ExcelUtil<span style="color:#999999">.</span><span style="color:#61aeee">getReader</span><span style="color:#999999">(</span><span style="color:#669900">"d:/aaa.xlsx"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
List<span style="color:#669900"><</span>Person<span style="color:#669900">></span> all <span style="color:#669900">=</span> reader<span style="color:#999999">.</span><span style="color:#61aeee">readAll</span><span style="color:#999999">(</span>Person<span style="color:#999999">.</span>class<span style="color:#999999">)</span><span style="color:#999999">;</span>
</code></span>

本文Excel导入读取示例采用的是第三种方法实现，相关代码如下所示：

<span style="color:#000000"><code class="language-c"> @<span style="color:#61aeee">RequestMapping</span><span style="color:#999999">(</span>READ_EXCEL<span style="color:#999999">)</span>
    public <span style="color:#c678dd">void</span> <span style="color:#61aeee">readExcel</span><span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#999999">{</span>
        ExcelReader reader <span style="color:#669900">=</span> ExcelUtil<span style="color:#999999">.</span><span style="color:#61aeee">getReader</span><span style="color:#999999">(</span><span style="color:#669900">"H:\\user.xlsx"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
        List<span style="color:#669900"><</span>UserDTO<span style="color:#669900">></span> userDTOS <span style="color:#669900">=</span> reader<span style="color:#999999">.</span><span style="color:#61aeee">readAll</span><span style="color:#999999">(</span>UserDTO<span style="color:#999999">.</span>class<span style="color:#999999">)</span><span style="color:#999999">;</span>
        <span style="color:#5c6370">//日志输出读取到的信息</span>
        log<span style="color:#999999">.</span><span style="color:#61aeee">info</span><span style="color:#999999">(</span>userDTOS<span style="color:#999999">.</span><span style="color:#61aeee">toString</span><span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
    <span style="color:#999999">}</span>
</code></span>

其中UserDTO类的代码如下所示：

HuTool工具类使用之Excel文档的导入导出

前言

提示：以下是本篇文章正文内容，下面案例可供参考

一、Hutool是什么？

二、导出Excel文件

1.引入相关依赖

需要注意的是，hutool-4.x的poi-ooxml 版本需高于 3.17，hutool-5.x的poi-ooxml 版本需高于 4.1.2；本文使用的依赖信息如下图所示：

<span style="color:#000000"><code class="language-c"><span style="color:#669900"><</span><span style="color:#669900">!</span><span style="color:#669900">--</span> hutool工具类依赖<span style="color:#669900">--</span><span style="color:#669900">></span>
<span style="color:#669900"><</span>dependency<span style="color:#669900">></span>
<span style="color:#669900"><</span>groupId<span style="color:#669900">></span>cn<span style="color:#999999">.</span>hutool<span style="color:#669900"><</span><span style="color:#669900">/</span>groupId<span style="color:#669900">></span>
<span style="color:#669900"><</span>artifactId<span style="color:#669900">></span>hutool<span style="color:#669900">-</span>all<span style="color:#669900"><</span><span style="color:#669900">/</span>artifactId<span style="color:#669900">></span>
<span style="color:#669900"><</span>version<span style="color:#669900">></span><span style="color:#98c379">5.4</span><span style="color:#98c379">.3</span><span style="color:#669900"><</span><span style="color:#669900">/</span>version<span style="color:#669900">></span>
<span style="color:#669900"><</span><span style="color:#669900">/</span>dependency<span style="color:#669900">></span>

<span style="color:#669900"><</span><span style="color:#669900">!</span><span style="color:#669900">--</span>POI依赖，对office进行操作<span style="color:#669900">--</span><span style="color:#669900">></span>
<span style="color:#669900"><</span>dependency<span style="color:#669900">></span>
<span style="color:#669900"><</span>groupId<span style="color:#669900">></span>org<span style="color:#999999">.</span>apache<span style="color:#999999">.</span>poi<span style="color:#669900"><</span><span style="color:#669900">/</span>groupId<span style="color:#669900">></span>
<span style="color:#669900"><</span>artifactId<span style="color:#669900">></span>poi<span style="color:#669900">-</span>ooxml<span style="color:#669900"><</span><span style="color:#669900">/</span>artifactId<span style="color:#669900">></span>
<span style="color:#669900"><</span>version<span style="color:#669900">></span><span style="color:#98c379">4.1</span><span style="color:#98c379">.2</span><span style="color:#669900"><</span><span style="color:#669900">/</span>version<span style="color:#669900">></span>
<span style="color:#669900"><</span><span style="color:#669900">/</span>dependency<span style="color:#669900">></span>
</code></span>

2.Excel导出功能的实现

<span style="color:#000000"><code class="language-c">@<span style="color:#61aeee">RequestMapping</span><span style="color:#999999">(</span>EXCEL_DOWNLOAD<span style="color:#999999">)</span>
public <span style="color:#c678dd">void</span> <span style="color:#61aeee">excelExport</span><span style="color:#999999">(</span>HttpServletResponse httpServletResponse<span style="color:#999999">)</span> throws IOException <span style="color:#999999">{</span>
UserDTO userDTO <span style="color:#669900">=</span> new <span style="color:#61aeee">UserDTO</span><span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
List<span style="color:#669900"><</span>UserDTO<span style="color:#669900">></span> userDTOS <span style="color:#669900">=</span> userService<span style="color:#999999">.</span><span style="color:#61aeee">selectUserDOBatch</span><span style="color:#999999">(</span>userDTO<span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#5c6370">//通过hutool工具创建的excel的writer，默认为xls格式</span>
ExcelWriter writer <span style="color:#669900">=</span> ExcelUtil<span style="color:#999999">.</span><span style="color:#61aeee">getWriter</span><span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#5c6370">//设置要导出到的sheet</span>
writer<span style="color:#999999">.</span><span style="color:#61aeee">setSheet</span><span style="color:#999999">(</span><span style="color:#669900">"表2"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
writer<span style="color:#999999">.</span><span style="color:#61aeee">setSheet</span><span style="color:#999999">(</span><span style="color:#669900">"表3"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#5c6370">//自定义excel标题和列名</span>
writer<span style="color:#999999">.</span><span style="color:#61aeee">addHeaderAlias</span><span style="color:#999999">(</span><span style="color:#669900">"id"</span><span style="color:#999999">,</span><span style="color:#669900">"用户ID"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
writer<span style="color:#999999">.</span><span style="color:#61aeee">addHeaderAlias</span><span style="color:#999999">(</span><span style="color:#669900">"userName"</span><span style="color:#999999">,</span><span style="color:#669900">"用户名"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
writer<span style="color:#999999">.</span><span style="color:#61aeee">addHeaderAlias</span><span style="color:#999999">(</span><span style="color:#669900">"loginPassword"</span><span style="color:#999999">,</span><span style="color:#669900">"密码"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
writer<span style="color:#999999">.</span><span style="color:#61aeee">addHeaderAlias</span><span style="color:#999999">(</span><span style="color:#669900">"email"</span><span style="color:#999999">,</span><span style="color:#669900">"邮箱"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
writer<span style="color:#999999">.</span><span style="color:#61aeee">addHeaderAlias</span><span style="color:#999999">(</span><span style="color:#669900">"createDate"</span><span style="color:#999999">,</span><span style="color:#669900">"数据创建日期"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#5c6370">//合并单元格后的标题行，使用默认标题样式</span>
writer<span style="color:#999999">.</span><span style="color:#61aeee">merge</span><span style="color:#999999">(</span><span style="color:#98c379">4</span><span style="color:#999999">,</span><span style="color:#669900">"用户基本信息表"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
writer<span style="color:#999999">.</span><span style="color:#61aeee">renameSheet</span><span style="color:#999999">(</span><span style="color:#98c379">0</span><span style="color:#999999">,</span><span style="color:#669900">"用户登录信息"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#5c6370">//一次性写出内容，使用默认样式，强制输出标题</span>
writer<span style="color:#999999">.</span><span style="color:#61aeee">write</span><span style="color:#999999">(</span>userDTOS<span style="color:#999999">,</span>true<span style="color:#999999">)</span><span style="color:#999999">;</span>

httpServletResponse<span style="color:#999999">.</span><span style="color:#61aeee">setContentType</span><span style="color:#999999">(</span><span style="color:#669900">"application/vnd.ms-excel;charset=utf-8"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#5c6370">//name是下载对话框的名称，不支持中文，想用中文名称需要进行utf8编码</span>
String excelName <span style="color:#669900">=</span> <span style="color:#669900">"用户基本信息表"</span><span style="color:#999999">;</span>
<span style="color:#5c6370">//excelName = new String(excelName.getBytes(),"utf-8");</span>
excelName <span style="color:#669900">=</span> URLEncoder<span style="color:#999999">.</span><span style="color:#61aeee">encode</span><span style="color:#999999">(</span>excelName<span style="color:#999999">,</span> <span style="color:#669900">"utf-8"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
httpServletResponse<span style="color:#999999">.</span><span style="color:#61aeee">setHeader</span><span style="color:#999999">(</span><span style="color:#669900">"Content-Disposition"</span><span style="color:#999999">,</span> <span style="color:#669900">"attachment;filename="</span> <span style="color:#669900">+</span> excelName <span style="color:#669900">+</span><span style="color:#669900">".xls"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>

<span style="color:#5c6370">//将excel文件信息写入输出流，返回给调用者</span>
ServletOutputStream excelOut <span style="color:#669900">=</span> null<span style="color:#999999">;</span>
try <span style="color:#999999">{</span>
excelOut <span style="color:#669900">=</span> httpServletResponse<span style="color:#999999">.</span><span style="color:#61aeee">getOutputStream</span><span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
writer<span style="color:#999999">.</span><span style="color:#61aeee">flush</span><span style="color:#999999">(</span>excelOut<span style="color:#999999">,</span>true<span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#999999">}</span> catch <span style="color:#999999">(</span>IOException e<span style="color:#999999">)</span> <span style="color:#999999">{</span>
e<span style="color:#999999">.</span><span style="color:#61aeee">printStackTrace</span><span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#999999">}</span>finally <span style="color:#999999">{</span>
writer<span style="color:#999999">.</span><span style="color:#61aeee">close</span><span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#999999">}</span>
IoUtil<span style="color:#999999">.</span><span style="color:#61aeee">close</span><span style="color:#999999">(</span>excelOut<span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#999999">}</span>
</code></span>

以上导出的Excel格式为.xls，如果想要导出格式为.xlsx的excel文件，只需修改上图所示的相应位置代码即可，修改代码为：http://www.17tui.cc/thread-15917646-1-1.html

<span style="color:#000000"><code class="language-c"><span style="color:#5c6370">//设置返回excel的格式为xlsx</span>
httpServletResponse<span style="color:#999999">.</span><span style="color:#61aeee">setContentType</span><span style="color:#999999">(</span><span style="color:#669900">"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
httpServletResponse<span style="color:#999999">.</span><span style="color:#61aeee">setHeader</span><span style="color:#999999">(</span><span style="color:#669900">"Content-Disposition"</span><span style="color:#999999">,</span><span style="color:#669900">"attachment;filename="</span><span style="color:#669900">+</span> URLEncoder<span style="color:#999999">.</span><span style="color:#61aeee">encode</span><span style="color:#999999">(</span><span style="color:#669900">"用户信息表"</span><span style="color:#999999">,</span><span style="color:#669900">"utf-8"</span><span style="color:#999999">)</span> <span style="color:#669900">+</span> <span style="color:#669900">".xlsx"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
</code></span>

生成的excel文件如下如所示：

在这里插入图片描述

二、导出Excel文件

1.Excel读取-ExcelReader

Excel文件的导入分为三种情况：http://www.17tui.cc/thread-15917631-1-1.html

1.读取Excel中所有行和列，都用列表表示

<span style="color:#000000"><code class="language-c">ExcelReader reader <span style="color:#669900">=</span> ExcelUtil<span style="color:#999999">.</span><span style="color:#61aeee">getReader</span><span style="color:#999999">(</span><span style="color:#669900">"d:/aaa.xlsx"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
List<span style="color:#669900"><</span>List<span style="color:#669900"><</span>Object<span style="color:#669900">>></span> readAll <span style="color:#669900">=</span> reader<span style="color:#999999">.</span><span style="color:#61aeee">read</span><span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
</code></span>

2.读取为Map列表，默认第一行为标题行，Map中的key为标题，value为标题对应的单元格值。http://www.17tui.cc/thread-15917610-1-1.html

<span style="color:#000000"><code class="language-c">ExcelReader reader <span style="color:#669900">=</span> ExcelUtil<span style="color:#999999">.</span><span style="color:#61aeee">getReader</span><span style="color:#999999">(</span><span style="color:#669900">"d:/aaa.xlsx"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
List<span style="color:#669900"><</span>Map<span style="color:#669900"><</span>String<span style="color:#999999">,</span>Object<span style="color:#669900">>></span> readAll <span style="color:#669900">=</span> reader<span style="color:#999999">.</span><span style="color:#61aeee">readAll</span><span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
</code></span>

3.读取为Bean列表，Bean中的字段名为标题，字段值为标题对应的单元格值。

<span style="color:#000000"><code class="language-c">ExcelReader reader <span style="color:#669900">=</span> ExcelUtil<span style="color:#999999">.</span><span style="color:#61aeee">getReader</span><span style="color:#999999">(</span><span style="color:#669900">"d:/aaa.xlsx"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
List<span style="color:#669900"><</span>Person<span style="color:#669900">></span> all <span style="color:#669900">=</span> reader<span style="color:#999999">.</span><span style="color:#61aeee">readAll</span><span style="color:#999999">(</span>Person<span style="color:#999999">.</span>class<span style="color:#999999">)</span><span style="color:#999999">;</span>
</code></span>

本文Excel导入读取示例采用的是第三种方法实现，相关代码如下所示：http://www.17tui.cc/thread-15917566-1-1.html

<span style="color:#000000"><code class="language-c"> @<span style="color:#61aeee">RequestMapping</span><span style="color:#999999">(</span>READ_EXCEL<span style="color:#999999">)</span>
    public <span style="color:#c678dd">void</span> <span style="color:#61aeee">readExcel</span><span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#999999">{</span>
        ExcelReader reader <span style="color:#669900">=</span> ExcelUtil<span style="color:#999999">.</span><span style="color:#61aeee">getReader</span><span style="color:#999999">(</span><span style="color:#669900">"H:\\user.xlsx"</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
        List<span style="color:#669900"><</span>UserDTO<span style="color:#669900">></span> userDTOS <span style="color:#669900">=</span> reader<span style="color:#999999">.</span><span style="color:#61aeee">readAll</span><span style="color:#999999">(</span>UserDTO<span style="color:#999999">.</span>class<span style="color:#999999">)</span><span style="color:#999999">;</span>
        <span style="color:#5c6370">//日志输出读取到的信息</span>
        log<span style="color:#999999">.</span><span style="color:#61aeee">info</span><span style="color:#999999">(</span>userDTOS<span style="color:#999999">.</span><span style="color:#61aeee">toString</span><span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
    <span style="color:#999999">}</span>
</code></span>

其中UserDTO类的代码如下所示：

<span style="color:#000000"><code class="language-c">@Data
public class UserDTO <span style="color:#999999">{</span>
    @<span style="color:#61aeee">NotNull</span><span style="color:#999999">(</span>message <span style="color:#669900">=</span> <span style="color:#669900">"用户id不能为空"</span><span style="color:#999999">)</span>
    private Integer id<span style="color:#999999">;</span>

    @<span style="color:#61aeee">NotNull</span><span style="color:#999999">(</span>message <span style="color:#669900">=</span> <span style="color:#669900">"用户名不能为空"</span><span style="color:#999999">)</span>
    @<span style="color:#61aeee">Size</span><span style="color:#999999">(</span>min <span style="color:#669900">=</span> <span style="color:#98c379">4</span><span style="color:#999999">,</span> max <span style="color:#669900">=</span> <span style="color:#98c379">16</span><span style="color:#999999">,</span> message <span style="color:#669900">=</span> <span style="color:#669900">"用户名长度错误"</span><span style="color:#999999">)</span>
    private String userName<span style="color:#999999">;</span>

    @<span style="color:#61aeee">NotNull</span><span style="color:#999999">(</span>message <span style="color:#669900">=</span> <span style="color:#669900">"密码不能为空"</span><span style="color:#999999">)</span>
    @<span style="color:#61aeee">Size</span><span style="color:#999999">(</span>min <span style="color:#669900">=</span> <span style="color:#98c379">4</span><span style="color:#999999">,</span> max <span style="color:#669900">=</span> <span style="color:#98c379">16</span><span style="color:#999999">,</span> message <span style="color:#669900">=</span> <span style="color:#669900">"密码长度错误"</span><span style="color:#999999">)</span>
    private String loginPassword<span style="color:#999999">;</span>

    @<span style="color:#61aeee">NotNull</span><span style="color:#999999">(</span>message <span style="color:#669900">=</span> <span style="color:#669900">"邮箱不能为空"</span><span style="color:#999999">)</span>
    @<span style="color:#61aeee">Email</span><span style="color:#999999">(</span>message <span style="color:#669900">=</span> <span style="color:#669900">"邮箱格式错误"</span><span style="color:#999999">)</span>
    private String email<span style="color:#999999">;</span>


    @<span style="color:#61aeee">NotNull</span><span style="color:#999999">(</span>message <span style="color:#669900">=</span> <span style="color:#669900">"日期不能为空"</span><span style="color:#999999">)</span>
    @<span style="color:#61aeee">JsonFormat</span><span style="color:#999999">(</span>pattern <span style="color:#669900">=</span> <span style="color:#669900">"yyyy-MM-dd HH:mm:ss"</span><span style="color:#999999">,</span> timezone <span style="color:#669900">=</span> <span style="color:#669900">"GMT+8"</span><span style="color:#999999">)</span>
    @<span style="color:#61aeee">JSONField</span><span style="color:#999999">(</span>format <span style="color:#669900">=</span> <span style="color:#669900">"yyyy-MM-dd HH:mm:ss"</span><span style="color:#999999">)</span>
    private Date createDate<span style="color:#999999">;</span>
<span style="color:#999999">}</span></code></span>