POI和EasyExcel-你还在为导入导出数据苦恼吗？

缺乏、安全感 2022-11-24 14:27 257阅读 0赞

### 文章目录 ###

*  写在前面
 *  POI
 *   *  导入依赖
     *  写入
     *  读取
     *  计算公式
 *  EasyExcel
 *   *  导入依赖
     *  写入
     *  读取
 *  实战

# 写在前面 #

--------------------

在开发中经常会涉及到excel的处理，比如导出用户信息为excel表格、打印报表、月销售情况、成绩单等等（导出数据），还有将excel的信息录入到网站数据库等（导入数据），我们不可能手动操作，所以介绍Apache的**POI**和阿里巴巴的**EasyExcel**。  
![在这里插入图片描述][20200805110704855.jpg_pic_center]

# POI #

--------------------

Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。  
官网[https://poi.apache.org/][https_poi.apache.org]  
**xls和xlsx**  
首先注意下xls(2003版)和xlsx(2007版)的区别，都是excel的格式，后缀名不同。关键xls只有65536行，而xlsx没有行数限制，后续都以xlsx为例，如果要用xls格式，操作都是类似的，导入对应依赖，修改后缀名和注意数据溢出(65536)。  
**对象说明**  
java是面向对象编程，那么excel的操作也是对应到具体对象的，即工作簿，工作表，行和列。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70]  
其中需要注意的是工作簿(Workbook)接口下实现类的区别，HSSF是03版的，XSSF是07版，SXSSF是07优化（提升速度）  
其余如word等导入依赖照葫芦画瓢  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 1]

![在这里插入图片描述][2020080609225284.png]

## 导入依赖 ##

[不会Maven参考这里][Maven]

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
            </dependency>
    
            
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.12</version>
            </dependency>

## 写入 ##

package com.wzl;
    
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.junit.Test;
    
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    
    public class Poi { 
        //当前项目路径
        static String PATH="C:\\Users\\Administrator\\IdeaProjects\\excel\\";
    
        @Test
        public void testWrite07() throws IOException { 
            //创建一个工作簿
            Workbook workbook=new XSSFWorkbook();
            //创建一个工作表
            Sheet sheet=workbook.createSheet();
            //创建一行
            Row row1=sheet.createRow(0);//第一行,下标从0开始
            //创建一列,行和列构成一个单元格cell
            Cell cell11=row1.createCell(0);//(1,1)
            //写入
            cell11.setCellValue("单元格(1,1)");
    
            Row row2=sheet.createRow(1);
            Cell cell25=row2.createCell(4);//(2,5)
            cell25.setCellValue("测试");
            //保存(IO流)
            FileOutputStream fileOutputStream=new FileOutputStream(PATH+"poi写入测试.xlsx");
            workbook.write(fileOutputStream);
            fileOutputStream.close();//关闭流
            System.out.println("finished");
        }
    }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 3]

## 读取 ##

@Test
        public void testRead07() throws IOException { 
            //获取文件流
            FileInputStream fileInputStream=new FileInputStream(PATH+"poi写入测试.xlsx");
            //读取工作簿
            Workbook workbook=new XSSFWorkbook(fileInputStream);
            fileInputStream.close();//关闭文件流
            //get第一个工作表
            Sheet sheet=workbook.getSheetAt(0);
            //get第一行
            Row row1=sheet.getRow(0);
            //get第一列
            Cell cell11=row1.getCell(0);
            //进行写入数据库等操作，这里直接输出
            System.out.println(cell11.getStringCellValue());
    
            //获取有多少行
            int rowCnt=sheet.getPhysicalNumberOfRows();
            int colCnt=0; //列数
            if(row1!=null)  //通过第一行获取有多少列
                colCnt=row1.getPhysicalNumberOfCells();
            //遍历表格
    // for(int i=0;i<rowCnt;i++)
    // for(int j=0;j<colCnt;j++)
        }

![在这里插入图片描述][20200806105017164.png]

## 计算公式 ##

excel还有很多公式，POI同样也可以处理。  
![在这里插入图片描述][20200806120208699.png]

@Test
        public void testFormula() throws IOException{ 
            FileInputStream fileInputStream=new FileInputStream(PATH+"poi写入测试.xlsx");
            Workbook workbook=new XSSFWorkbook(fileInputStream);
            fileInputStream.close();
            Sheet sheet=workbook.getSheetAt(0);
            Row row6=sheet.getRow(5);
            Cell cell62=row6.getCell(1);//(6,2)
            //输出单元格内容(计算公式)
            System.out.println(cell62.getCellFormula());
            //拿到计算公式 eval
            FormulaEvaluator formulaEvaluator=new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
            //计算
            CellValue evaluate=formulaEvaluator.evaluate(cell62);
            //输出计算结果
            String cellValue=evaluate.formatAsString();
            System.out.println(cellValue);
        }

![在这里插入图片描述][20200806121643994.png]

# EasyExcel #

--------------------

EasyExcel是阿里巴巴开源的excel处理框架，因为没有一次全部加载进内存，是从磁盘上一行行解析，所以节省内存，同时操作十分简便，**一行代码**，可以根据实体类自动生成表。  
官网[https://github.com/alibaba/easyexcel][https_github.com_alibaba_easyexcel]  
文档[https://www.yuque.com/easyexcel/doc/easyexcel][https_www.yuque.com_easyexcel_doc_easyexcel]

## 导入依赖 ##

<dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>2.1.7</version>
            </dependency>

EasyExcel有用到POI，，删掉前面的依赖，注意重复冲突。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 4]

## 写入 ##

**格式类**

package com.wzl.easy;
    
    import com.alibaba.excel.annotation.ExcelIgnore;
    import com.alibaba.excel.annotation.ExcelProperty;
    import lombok.Data;
    
    import java.util.Date;
    
    @Data
    public class DemoData { 
        @ExcelProperty("自定义标题")
        private String string;
    
        @ExcelProperty("时间")
        private Date date;
        private Double doubleData;
    
        @ExcelIgnore    //忽略
        private int ignore;
    
        public void setString(String string) { 
            this.string = string;
        }
    
        public void setDate(Date date) { 
            this.date = date;
        }
    
        public void setDoubleData(Double doubleData) { 
            this.doubleData = doubleData;
        }
    }

**写测试**

package com.wzl.easy;
    
    import com.alibaba.excel.EasyExcel;
    import com.wzl.easy.DemoData;
    import org.junit.Test;
    
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.List;
    
    public class Easy { 
        static String PATH="C:\\Users\\Administrator\\IdeaProjects\\excel\\";
    
        //生成DemoData类随机数据
        private List<DemoData> data() { 
            List<DemoData> list = new ArrayList<DemoData>();
            for (int i = 0; i < 15; i++) { 
                DemoData data = new DemoData();
                data.setString("字符串" + i);
                data.setDate(new Date());
                data.setDoubleData(0.56);
                list.add(data);
            }
            return list;
        }
    
        @Test
        public void simpleWrite() { 
            String fileName = PATH+"EasyExcelWrite.xlsx";
            // 文件名 格式类 表名 数据
            EasyExcel.write(fileName, DemoData.class).sheet("工作表名").doWrite(data());
            System.out.println("finished");
        }
    }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 5]

## 读取 ##

Demo把持久层逻辑都写好了，这里直接演示输出(偷懒 )  
**持久层**

package com.wzl.easy;
    
    import java.util.List;
    
    /** * 假设这个是你的DAO存储。当然还要这个类让spring管理，当然你不用需要存储，也不需要这个类。 **/
    public class DemoDAO { 
        public void save(List<DemoData> list) { 
            // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
        }
    }

**监听器**

package com.wzl.easy;
    
    import com.alibaba.excel.context.AnalysisContext;
    import com.alibaba.excel.event.AnalysisEventListener;
    import com.alibaba.fastjson.JSON;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    import java.util.ArrayList;
    import java.util.List;
    public class DemoDataListener extends AnalysisEventListener<DemoData> { 
        private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);
    
        private static final int BATCH_COUNT = 5;
        List<DemoData> list = new ArrayList<DemoData>();
        private DemoDAO demoDAO;
        public DemoDataListener() { 
            // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
            demoDAO = new DemoDAO();
        }
    
        public DemoDataListener(DemoDAO demoDAO) { 
            this.demoDAO = demoDAO;
        }
        @Override
        public void invoke(DemoData data, AnalysisContext context) { 
            System.out.println(JSON.toJSONString(data));
            //LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
            list.add(data);
            // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
            if (list.size() >= BATCH_COUNT) { 
                saveData(); //持久化逻辑
                // 存储完成清理 list
                list.clear();
            }
        }
        @Override
        public void doAfterAllAnalysed(AnalysisContext context) { 
            // 这里也要保存数据，确保最后遗留的数据也存储到数据库
            saveData();
            LOGGER.info("所有数据解析完成！");
        }
        private void saveData() { 
            LOGGER.info("{}条数据，开始存储数据库！", list.size());
            demoDAO.save(list);
            LOGGER.info("存储数据库成功！");
        }
    }

测试

@Test
        public void simpleRead() { 
            String fileName = PATH+"EasyExcelWrite.xlsx";
            EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
            System.out.println("finished");
        }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 6]  
更多其他操作可以查看[文档][Link 1]，调用API即可，十分详细。

# 实战 #

--------------------

数据来源是爬虫（因为我没有数据库和数据 ），[Jsoup爬虫可参考我另一篇博客][Jsoup]，用数据库也是一样的，怎么拿到数据都行，重点演示下POI。  
以CSDN搜索java为例，爬取博客相关信息后存入excel。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 7]

@Test
        public void jsoupPoiDemo()throws IOException{ 
            //创建一个工作簿,使用07优化
            Workbook workbook=new SXSSFWorkbook();
            //创建一个工作表
            Sheet sheet=workbook.createSheet();
    
            //创建第一行（表头）
            Row row1=sheet.createRow(0);
            row1.createCell(0).setCellValue("标题");
            row1.createCell(1).setCellValue("浏览量");
            row1.createCell(2).setCellValue("作者");
            row1.createCell(3).setCellValue("时间");
            row1.createCell(4).setCellValue("地址");
    
            int rowCnt=1;//记录行数
            //获取数据
            String url="https://so.csdn.net/so/search/s.do?q=java&p=";
            for(int page=1;page<=20;page++){     //遍历20页
                //解析网页
                Document document=Jsoup.parse(new URL(url+Integer.toString(page)),60000);//60s超时
                Elements elements=document.getElementsByClass("search-list J_search");
                for (Element el:elements) {      //遍历该页每组数据
                    //数据筛选
                    String title=el.getElementsByTag("a").eq(0).text();//标题
                    String countStr=el.getElementsByClass("mr16").html();//浏览量
                    if(countStr==null||"".equals(countStr))continue; //搜索结果含少量course,我们只要blog
                    int count=Integer.parseInt(countStr);
                    String writer=el.getElementsByTag("a").eq(1).text();//作者
                    String time=el.getElementsByClass("date").html();//时间
                    String hrefAll=el.getElementsByTag("a").eq(0).attr("href");//博客地址
                    String href=hrefAll.substring(0, hrefAll.indexOf("?"));//截取?之前，避免过长
    
                    //写入数据
                    Row row=sheet.createRow(rowCnt++);  //另起一行
                    row.createCell(0).setCellValue(title);
                    row.createCell(1).setCellValue(count);
                    row.createCell(2).setCellValue(writer);
                    row.createCell(3).setCellValue(time);
                    row.createCell(4).setCellValue(href);
                }
            }
            //保存(IO流)
            FileOutputStream fileOutputStream=new FileOutputStream(PATH+"CSDN博客数据分析.xlsx");
            workbook.write(fileOutputStream);
            fileOutputStream.close();//关闭流
            
            System.out.println("finished");
        }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 8]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 9]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 10]

> **原创不易，请勿转载**（本不富裕的访问量雪上加霜 ）  
> 博主首页：[https://blog.csdn.net/qq\_45034708][https_blog.csdn.net_qq_45034708]  
> *如果文章对你有帮助，记得关注点赞收藏❤*

[20200805110704855.jpg_pic_center]: /images/20221124/6e6a31db03244e159db2a858a637e486.png
[https_poi.apache.org]: https://poi.apache.org/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70]: /images/20221124/e452de43e056405e8a3d169868ae8bc7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 1]: /images/20221124/05cea6930ac742ac83f4291f81f446c7.png
[2020080609225284.png]: /images/20221124/4c0d8ed2c6a244ab98db628e4057b542.png
[Maven]: https://blog.csdn.net/qq_45034708/article/details/106172089
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 2]: /images/20221124/87a6f7e45dff4bb18f7467f616c42f4d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 3]: /images/20221124/b1a27ae00ab54fd0b3752242eaeda40e.png
[20200806105017164.png]: /images/20221124/af0234b8868f48709179e9eddb2d9284.png
[20200806120208699.png]: /images/20221124/655114149f904ae4b24e1f4ecec85bda.png
[20200806121643994.png]: /images/20221124/6f0cf29ba811441e9d133d1501cab9b7.png
[https_github.com_alibaba_easyexcel]: https://github.com/alibaba/easyexcel
[https_www.yuque.com_easyexcel_doc_easyexcel]: https://www.yuque.com/easyexcel/doc/easyexcel
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 4]: /images/20221124/f47142d912554042a3bf16039d88238c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 5]: /images/20221124/fb2bcd202f7042a6a7f8a887037615b6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 6]: /images/20221124/7ecd1cf6921b4bdcb41f3651751d2d8f.png
[Link 1]: https://www.yuque.com/easyexcel/doc/quickstart
[Jsoup]: https://blog.csdn.net/qq_45034708/article/details/107728401
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 7]: /images/20221124/ae66e8caee414cee976e13ed555fea7b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 8]: /images/20221124/99d74518581b4cff9bc0feb7a79e2140.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 9]: /images/20221124/abbcf7adb6b14a40954c1f11d759f6c4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 10]: /images/20221124/2142671b41174261961396a25579d98e.png
[https_blog.csdn.net_qq_45034708]: https://blog.csdn.net/qq_45034708