Easypoi（二）表格工具类使用

我就是我 2022-09-05 01:55 563阅读 0赞

### 文章目录 ###

*   *  一、种类
     *  二、导入
     *   *  1、调用OSS(带有图片)
         *  2、调用本地(带有图片)
         *  3、调用流（带图片）
         *  4、调用流（不带图片，带校验器）
         *  5、调用OSS(不带有图片)
         *  6、调用本地（不带图片）
         *  7、调用流（不带图片，带校验器）
     *  三、导出
     *   *  1、导出到本地
         *  2、导出到OSS
         *  3、导出为流

> 本文首发于CSDN，作者：lomtom
> 
> 原文链接：**[https://blog.csdn.net/qq\_41929184/article/details/119738265][https_blog.csdn.net_qq_41929184_article_details_119738265]**
> 
> 个人网站：**[https://lomtom.top][https_lomtom.top]**，个人公众号：**[博思奥园][Link 1]**，同步更新。
> 
> 你的支持就是我最大的动力。

表格导入工具类系列：

1.  [Easypoi（一）表格导入工具类封装][Easypoi]
2.  [Easypoi（二）表格工具类使用][https_blog.csdn.net_qq_41929184_article_details_119738265]

注： [工具类及测试github源码][github]

## 一、种类 ##

工具类导入分为六种，分别为：

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>importExcelByLocalUrlWithImg</td> 
   <td>本地文件、带图片</td> 
  </tr> 
  <tr> 
   <td>importExcelByLocalUrl</td> 
   <td>本地文件、不带图片</td> 
  </tr> 
  <tr> 
   <td>importExcelByOssUrlWithImg</td> 
   <td>OSS文件、带图片</td> 
  </tr> 
  <tr> 
   <td>importExcelByOssUrl</td> 
   <td>OSS文件、不带图片</td> 
  </tr> 
  <tr> 
   <td>importExcelByStreamWithImg</td> 
   <td>流、带图片</td> 
  </tr> 
  <tr> 
   <td>importExcelByStream</td> 
   <td>流、不带图片</td> 
  </tr> 
 </tbody> 
</table>

注意：若带图片，则需要指定OSS的保存的路径。

String url1 = "https://demo-excel.oss-cn-hangzhou.aliyuncs.com/测试.xlsx";
        String url2 = "D:\\测试.xlsx";
        String url3 = "D:\\会员档案信息.xls";

`OssProperties`为OSS连接参数类，`TalentImportVerifyHandler`为自定义校验类。（参考代码在github上有）

@Autowired
    OssProperties ossProperties;
    
    @Autowired
    TalentImportVerifyHandler talentImportVerifyHandler;

## 二、导入 ##

### 1、调用OSS(带有图片) ###

/** * 调用OSS(带有图片) * @throws Exception Exception */
        @Test
        public void importExcelByOssUrlWithImg() throws Exception { 
            ImportParams params = new ImportParams();
            ExcelImportResult<CommodityTemplate> result= ExcelImportUtils.importExcelByOssUrlWithImg(url1,
                    CommodityTemplate.class,ossProperties,"changxing/test/",params);
            if (result.isVerifyFail()){ 
                for (CommodityTemplate entity : result.getFailList()) { 
                    String msg = "第" + entity.getRowNum() + "行的错误是：" + entity.getErrorMsg();
                    System.out.println(msg);
                }
            }
            result.getList().forEach(System.out::println);
        }

### 2、调用本地(带有图片) ###

/** * 调用本地(带有图片) * @throws Exception Exception */
        @Test
        public void importExcelByLocalUrlWithImg() throws Exception { 
            ImportParams params = new ImportParams();
            ExcelImportResult<CommodityTemplate> result= ExcelImportUtils.importExcelByLocalUrlWithImg(url2,
                    CommodityTemplate.class,ossProperties,"changxing/test/",params);
            if (result.isVerifyFail()){ 
                for (CommodityTemplate entity : result.getFailList()) { 
                    String msg = "第" + entity.getRowNum() + "行的错误是：" + entity.getErrorMsg();
                    System.out.println(msg);
                }
            }
            result.getList().forEach(System.out::println);
        }

### 3、调用流（带图片） ###

/** * 调用流（带图片） * @throws Exception Exception */
        @Test
        public void importExcelByStreamWithImg() throws Exception { 
            FileInputStream fis = new FileInputStream(url2);
            ImportParams params = new ImportParams();
            ExcelImportResult<CommodityTemplate> result= ExcelImportUtils.importExcelByStreamWithImg(fis,CommodityTemplate.class,ossProperties,"changxing/test/",params);
            if (result.isVerifyFail()){ 
                for (CommodityTemplate entity : result.getFailList()) { 
                    String msg = "第" + entity.getRowNum() + "行的错误是：" + entity.getErrorMsg();
                    System.out.println(msg);
                }
            }
            result.getList().forEach(System.out::println);
        }

### 4、调用流（不带图片，带校验器） ###

/** * 调用流 * @throws Exception Exception */
        @Test
        public void importExcelByStream() throws Exception { 
            FileInputStream fis = new FileInputStream(url3);
            ImportParams params = new ImportParams();
            ExcelImportResult<MemberRecord> result= ExcelImportUtils.importExcelByStream(fis,MemberRecord.class,params);
            if (result.isVerifyFail()){ 
                for (MemberRecord entity : result.getFailList()) { 
                    String msg = "第" + entity.getRowNum() + "行的错误是：" + entity.getErrorMsg();
                    System.out.println(msg);
                }
            }
            result.getList().forEach(System.out::println);
        }

### 5、调用OSS(不带有图片) ###

/** * 调用OSS(不带有图片) * @throws Exception Exception */
        @Test
        public void importExcelByOssUrl() throws Exception { 
            ImportParams params = new ImportParams();
            ExcelImportResult<CommodityTemplate> result= ExcelImportUtils.importExcelByOssUrl(url1,
                    CommodityTemplate.class,ossProperties,params);
            if (result.isVerifyFail()){ 
                for (CommodityTemplate entity : result.getFailList()) { 
                    String msg = "第" + entity.getRowNum() + "行的错误是：" + entity.getErrorMsg();
                    System.out.println(msg);
                }
            }
            result.getList().forEach(System.out::println);
        }

### 6、调用本地（不带图片） ###

/** * 调用本地（不带图片） * @throws Exception Exception */
        @Test
        public void importExcelByLocalUrl() throws Exception { 
            ImportParams params = new ImportParams();
            ExcelImportResult<MemberRecord> result= ExcelImportUtils.importExcelByLocalUrl(url3,MemberRecord.class,params);
            if (result.isVerifyFail()){ 
                for (MemberRecord entity : result.getFailList()) { 
                    String msg = "第" + entity.getRowNum() + "行的错误是：" + entity.getErrorMsg();
                    System.out.println(msg);
                }
            }
            result.getList().forEach(System.out::println);
        }

### 7、调用流（不带图片，带校验器） ###

自定义校验器：实现`IExcelVerifyHandler`，编译自己的校验逻辑。

@Component
    public class TalentImportVerifyHandler implements IExcelVerifyHandler<MemberRecord> { 
    
        List<String> test1 = new ArrayList<>();
    
    
    
        @Override
        public ExcelVerifyHandlerResult verifyHandler(MemberRecord entity) { 
            String asparagusCode = entity.getAsparagusCode();
            StringJoiner msg = new StringJoiner(",");
            if (checkIsDuplicates(asparagusCode)){ 
                msg.add("MIS编码重复,请核实");
            }
            if (entity.getName() ==null){ 
                msg.add("名字为空,请核实");
            }
            if (StringUtils.isEmpty(asparagusCode)) { 
                msg.add("MIS编码为空,请核实");
            }
            if (msg.length() != 0){ 
                return new ExcelVerifyHandlerResult(false, msg.toString());
            }
            test1.add(asparagusCode);
            return new ExcelVerifyHandlerResult(true);
        }
    
        boolean checkIsDuplicates(String asparagusCode){ 
            return test1.contains(asparagusCode);
        }
    }

测试

/** * 调用流（不带图片，带校验器） * @throws Exception Exception */
        @Test
        public void importExcelByStream() throws Exception { 
            FileInputStream fis = new FileInputStream(url3);
            ImportParams params = new ImportParams();
            //自定义校验器
            params.setVerifyHandler(talentImportVerifyHandler);
            ExcelImportResult<MemberRecord> result= ExcelImportUtils.importExcelByStream(fis,MemberRecord.class,params);
            if (result.isVerifyFail()){ 
                for (MemberRecord entity : result.getFailList()) { 
                    String msg = "第" + entity.getRowNum() + "行的错误是：" + entity.getErrorMsg();
                    System.out.println(msg);
                }
            }
            result.getList().forEach(System.out::println);
        }

结果：  
![bcf91ca66f4b4aeca5079d0bc734ed13.png][]

![20487aed4d5a4d539f4b1f194891d3e3.png][]

**注意**：

1.  实现`IExcelDataModel`与 `IExcelModel`接口，将不能使用链式编程，即不能在实体类上加`@Accessors(chain = true)`。
2.  若项目中还是用了其他excel工具类，请将公有的依赖排除，例如easyexcel
    
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>2.3.0</version>
            <exclusions>
                <exclusion>
                    <groupId>com.google.guava</groupId>
                    <artifactId>guava</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.0-beta2</version>
            <exclusions>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml-schemas</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
3.  若使用了`easyexcel`，在引入easypoi并且排除共有依赖导致easyexcel的部分不可用，请升级easypoi的版本。

<dependency>
         <groupId>cn.afterturn</groupId>
         <artifactId>easypoi-base</artifactId>
         <version>4.1.3</version>
     </dependency>

## 三、导出 ##

### 1、导出到本地 ###

需要指定文件保存的路径与文件名

List<CommodityTemplate> data = new ArrayList<>();
    { 
        data.add(new CommodityTemplate("1","小飞扬","https://demo-excel.oss-cn-hangzhou.aliyuncs.com/changxing/test/img/imggggpic16141250914.JPG",new Date(),"https://demo-excel.oss-cn-hangzhou.aliyuncs.com/changxing/test/img/imggggpic34024334651.JPG"));
        data.add(new CommodityTemplate("2","小道科","https://demo-excel.oss-cn-hangzhou.aliyuncs.com/changxing/test/img/imggggpic3728058748.JPG",new Date(),"https://demo-excel.oss-cn-hangzhou.aliyuncs.com/changxing/test/img/imggggpic22109010312.JPG"));
        data.add(new CommodityTemplate("3","小浩林,","https://demo-excel.oss-cn-hangzhou.aliyuncs.com/changxing/test/img/imggggpic3728058748.JPG",new Date(),"https://demo-excel.oss-cn-hangzhou.aliyuncs.com/changxing/test/img/imggggpic49446558335.JPG"));
    }
    
    @Test
    public void exportLocal() throws Exception { 
        ExportParams params1 = new ExportParams();
        params1.setHeight((short) 28);
        List<CommodityTemplate> list = data;
        String url = ExcelExportUtils.exportExcelByLocal("D:\\", "ceshi.xls", CommodityTemplate.class, ossProperties, list, params1);
        System.out.println(url);
    }

### 2、导出到OSS ###

需要指定文件保存的路径（不包括域名）与文件名

List<CommodityTemplate> data = new ArrayList<>();
    { 
        data.add(new CommodityTemplate("1","小飞扬","https://demo-excel.oss-cn-hangzhou.aliyuncs.com/changxing/test/img/imggggpic16141250914.JPG",new Date(),"https://demo-excel.oss-cn-hangzhou.aliyuncs.com/changxing/test/img/imggggpic34024334651.JPG"));
        data.add(new CommodityTemplate("2","小道科","https://demo-excel.oss-cn-hangzhou.aliyuncs.com/changxing/test/img/imggggpic3728058748.JPG",new Date(),"https://demo-excel.oss-cn-hangzhou.aliyuncs.com/changxing/test/img/imggggpic22109010312.JPG"));
        data.add(new CommodityTemplate("3","小浩林,","https://demo-excel.oss-cn-hangzhou.aliyuncs.com/changxing/test/img/imggggpic3728058748.JPG",new Date(),"https://demo-excel.oss-cn-hangzhou.aliyuncs.com/changxing/test/img/imggggpic49446558335.JPG"));
    }
    
    @Test
    public void exportOSS() throws Exception { 
        ExportParams params1 = new ExportParams();
        params1.setHeight((short) 28);
        List<CommodityTemplate> list = data;
        String url = ExcelExportUtils.exportExcelByOss("changxing/test/", "ceshi.xls", CommodityTemplate.class, ossProperties, list, params1);
        System.out.println(url);
    }

### 3、导出为流 ###

@Test
    public void exportStream() throws Exception { 
        ExportParams params1 = new ExportParams();
        params1.setHeight((short) 28);
        List<CommodityTemplate> list = data;
        String filePath = "D:\\";
        String fileName = "ceshi.xls";
        byte[] bytes = ExcelExportUtils.exportExcelToOutPutStream(CommodityTemplate.class, list, params1);
        File file =  new File(filePath + "\\" + fileName);
        FileOutputStream fos = new FileOutputStream(file);;
        fos.write(bytes);
        fos.close();
    }

![94c5095d8b2041d9b9b65f3a34a5f064.png_pic_center][]

[https_blog.csdn.net_qq_41929184_article_details_119738265]: https://blog.csdn.net/qq_41929184/article/details/119738265
[https_lomtom.top]: https://lomtom.top
[Link 1]: https://mp.weixin.qq.com/s/HmMw8sVKEMcsdXL1im0-Lw
[Easypoi]: https://blog.csdn.net/qq_41929184/article/details/119734707
[github]: https://github.com/zero028/demo_excel
[bcf91ca66f4b4aeca5079d0bc734ed13.png]: /images/20220829/70607ef78e5c42ebab0fba6162d9cbdb.png
[20487aed4d5a4d539f4b1f194891d3e3.png]: /images/20220829/a761afcabb7c4019ace1a3ef25ac8154.png
[94c5095d8b2041d9b9b65f3a34a5f064.png_pic_center]: /images/20220829/bbd1f033d28448eb93b48df42efa7dae.png