POI
简介：
POI是Apache软件基金会用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程序对Microsoft Office格式档案读和写的功能。POI为“Poor Obfuscation Implementation”的首字母缩写，意为“可怜的模糊实现”。
所以POI的主要功能是可以用Java操作Microsoft Office的相关文件，但是一般我们都是用来操作Excel相关文件。
优劣势：
Jxl：消耗小，图片和图形支持有限
Poi：功能更加完善
POI包结构
HSSF：读写Microsoft Excel XLS
XSSF：读写Microsoft Excel XLSX
HWPF：读写Microsoft Word DOC
HSLF：读写 Microsoft PowerPoint
本次课主要使用XSSF包下的类
注意：
操作Excel文件区分版本：
版本（包含）以前的扩展名为.xls需要用HSSFWorkbook类操作
版本（包含）以后的扩展名为.xlsx需要用XSSFWorkbook类操作
POI入门案例
XSSFWorkbook：工作簿XSSFSheet：工作表Row：行Cell：单元格从Excel文件中读取数据
获取工作簿获取工作表遍历工作表，获取行对象便利行对象，过去单元格对象获的单元格的值导入poi相关的依赖
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version></dependency>
编写Java代码
package com.aaa.read;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellType;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.IOException;/*** @author : 尚腾飞* @version : 1.0* @createTime : /9/26 19:38* @description :从Excel文件中读取数据*/public class Demo1 {public static void main(String[] args) throws IOException {//获取工作簿XSSFWorkbook workbook = new XSSFWorkbook("E:\\mind\\poi\\student.xlsx");//获取工作表XSSFSheet sheet = workbook.getSheetAt(0);//获取行for (Row row : sheet) {//获取单元格for (Cell cell : row) {//设置单元格的数据类型cell.setCellType(CellType.STRING);System.out.println(cell.getStringCellValue());}}}}
普通for循环
package com.aaa.read;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellType;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.IOException;/*** @author : 尚腾飞* @version : 1.0* @createTime : /9/26 19:38* @description :*/public class Demo1 {public static void main(String[] args) throws IOException {//获取工作簿XSSFWorkbook workbook = new XSSFWorkbook("E:\\mind\\poi\\student.xlsx");//获取工作表XSSFSheet sheet = workbook.getSheetAt(0);//获取行/*for (Row row : sheet) {//获取单元格for (Cell cell : row) {//设置单元格的数据类型cell.setCellType(CellType.STRING);System.out.println(cell.getStringCellValue());}}*/for (int i = 0; i <= sheet.getLastRowNum(); i++) {XSSFRow row = sheet.getRow(i);if (row!=null){for (int j = 0; j < row.getLastCellNum(); j++) {//获取单元格XSSFCell cell = row.getCell(j);if (cell!=null){//设置单元格的数据类型cell.setCellType(CellType.STRING);System.out.println(cell);}}}}//释放资源workbook.close();}}
测试结果：
测试的时候一定把要操作的表关闭，否则两个进程都要读这个文件就会报下面的错
Exception in thread “main” java.io.FileNotFoundException: E:\mind\poi\student.xlsx (另一个程序正在使用此文件，进程无法访问。)
正常情况：
向Excel文件中写入数据
创建一个Excel表格创建工作表创建行创建单元格赋值通过输出流将对象下载到磁盘编写Java代码
package com.aaa.write;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.FileOutputStream;import java.io.IOException;/*** @author : 尚腾飞* @version : 1.0* @createTime : /9/26 20:13* @description :* 1. 创建一个Excel表格* 2. 创建工作表* 3. 创建行* 4. 创建单元格赋值* 5. 通过输出流将对象下载到磁盘*/public class Demo2 {public static void main(String[] args) throws IOException {//1. 创建一个Excel表格XSSFWorkbook workbook = new XSSFWorkbook();//2. 创建工作表XSSFSheet sheet = workbook.createSheet("工作表一");//3. 创建行XSSFRow row1 = sheet.createRow(0);//4. 创建单元格赋值XSSFCell cell1 = row1.createCell(0);cell1.setCellValue("陈娇花");XSSFCell cell2 = row1.createCell(1);cell2.setCellValue("赵铁牛 ");XSSFRow row2 = sheet.createRow(1);XSSFCell cell21 = row2.createCell(0);cell21.setCellValue("陈娇花");XSSFCell cell22 = row2.createCell(1);cell22.setCellValue("赵铁牛");//5. 通过输出流将对象下载到磁盘FileOutputStream fileOutputStream = new FileOutputStream("E:\\mind\\poi\\aaa.xlsx");workbook.write(fileOutputStream);//刷新输出流fileOutputStream.flush();//6. 释放资源fileOutputStream.close();workbook.close();}}
执行结果：
将Excel表格中的数据插入数据库
导入数据库连接相关的jar包
<!--阿里巴巴数据源--><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.2.11</version></dependency><!--MySQL连接--><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.28</version></dependency><!--mybatis--><dependency><groupId>org.mybatis</groupId><artifactId>mybatis</artifactId><version>3.5.10</version></dependency>
配置MybatisConfig.xml文件
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configurationPUBLIC "-////DTD Config 3.0//EN""/dtd/mybatis-3-config.dtd"><configuration><environments default="development"><environment id="development"><transactionManager type="JDBC"/><dataSource type="POOLED"><property name="driver" value="com.mysql.cj.jdbc.Driver"/><property name="url" value="jdbc:mysql://localhost:3306/poidemo?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true"/><property name="username" value="root"/><property name="password" value="1234"/></dataSource></environment></environments><mappers><package name="com.aaa.dao"/></mappers></configuration>
编写实体类
package com.aaa.domain;/*** @author : 尚腾飞* @version : 1.0* @createTime : /9/26 20:56* @description :*/public class Product {Integer id;String name;Double price;Integer pstock;//省略get,set方法,全参构造器,无参构造器,toString方法//大家写的时候记得写上}
操作类
获取工作簿
过去工作表
获取最后一行的行号
排除第一行
将每一行封装到一个list中
设置单元格类型
将每一行的内容封装为一个实体类
将每一个实体类加入到productList中
关闭资源
package com.aaa.web;import com.aaa.domain.Product;import com.aaa.service.ProductService;import com.aaa.service.impl.ProductServiceImpl;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellType;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.IOException;import java.util.ArrayList;import java.util.List;import java.util.Scanner;/*** @author : 尚腾飞* @version : 1.0* @createTime : /9/26 20:48* @description : 将Excel表中的数据添加到数据库*/public class InsertDB {public static void main(String[] args) throws IOException {ProductService productService = new ProductServiceImpl();//Scanner输入文件地址Scanner scanner = new Scanner(System.in);System.out.println("请输入文件地址（不包含空格）");String path = scanner.nextLine();//调用getObjList方法，得到Product的list集合List<Product> objList = getObjList(path);System.out.println(objList);//插入数据库Integer save = productService.save(objList);System.out.println("成功插入"+save+"条数据");}/*** 传入文件地址，返回list集合* @param path* @return* @throws IOException*/public static List<Product> getObjList(String path) throws IOException {List<Product> productList = new ArrayList<>();//获取工作簿XSSFWorkbook workbook = new XSSFWorkbook(path);//过去工作表XSSFSheet sheet = workbook.getSheetAt(0);//获取最后一行的行号int lastRowNum = sheet.getLastRowNum();//排除第一行for (int i = 1; i <= lastRowNum; i++) {//获取每一行XSSFRow row = sheet.getRow(i);if (row!=null){//将每一行封装到一个list中List<String> list = new ArrayList<>();for (Cell cell : row) {if (cell!=null){//设置单元格类型cell.setCellType(CellType.STRING);String value = cell.getStringCellValue();list.add(value);}}//将每一行的内容封装为一个实体类Product product = new Product(Integer.parseInt(list.get(0)),list.get(1),Double.parseDouble(list.get(2)),Integer.parseInt(list.get(3)));//将每一个实体类加入到productList中productList.add(product);}}//关闭资源workbook.close();return productList;}}
service层接口
package com.aaa.service;import com.aaa.domain.Product;import java.util.List;/*** @author : 尚腾飞* @version : 1.0* @createTime : /9/26 21:26* @description :*/public interface ProductService {Integer save(List<Product> list);}
service层实现类
package com.aaa.service.impl;import com.aaa.dao.ProductDao;import com.aaa.domain.Product;import com.aaa.service.ProductService;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import java.io.IOException;import java.io.InputStream;import java.util.List;/*** @author : 尚腾飞* @version : 1.0* @createTime : /9/26 21:27* @description :*/public class ProductServiceImpl implements ProductService {//mybatis配置文件的路径String resource = "MybatisConfig.xml";InputStream inputStream;{try {inputStream = Resources.getResourceAsStream(resource);} catch (IOException e) {throw new RuntimeException(e);}}//得到SqlSessionFactorySqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);/*** 添加方法* @param product* @return*/@Overridepublic Integer save(List<Product> product) {//开启SqlSessionSqlSession sqlSession = sqlSessionFactory.openSession();//获取代理对象ProductDao productDao1 = sqlSession.getMapper(ProductDao.class);int i = 0;for (Product product1 : product) {//插入数据Integer save = productDao1.save(product1);if (save!=null){i = i+save;}mit();}sqlSession.close();return i;}}
dao接口
package com.aaa.dao;import com.aaa.domain.Product;import java.util.List;/*** @author : 尚腾飞* @version : 1.0* @createTime : /9/26 21:28* @description :*/public interface ProductDao {Integer save(Product product);}
mapper文件
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapperPUBLIC "-////DTD Mapper 3.0//EN""/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.aaa.dao.ProductDao"><insert id="save">insert into product (id,name,price,pstock) values(#{id},#{name},#{price},#{pstock})</insert></mapper>
运行InsertDB.class
将数据库中的数据添加到Excel表
编写操作类
新建工作簿
新建工作表
第一行,表头
将每条list中的商品写入工作表中，从第二行开始，第一行,表头
获取文件输出流
工作簿写入
刷新输出流
关闭资源
package com.aaa.web;import com.aaa.domain.Product;import com.aaa.service.ProductService;import com.aaa.service.impl.ProductServiceImpl;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.FileOutputStream;import java.io.IOException;import java.util.List;import java.util.Scanner;/*** @author : 尚腾飞* @version : 1.0* @createTime : /9/26 23:07* @description : 将数据库中的数据添加到Excel表*/public class InsertExcel {public static void main(String[] args) throws IOException {ProductService productService = new ProductServiceImpl();//查询所有List<Product> productList = productService.getAll();System.out.println(productList);//输入路径Scanner scanner = new Scanner(System.in);System.out.println("请输入文件的输出路径");String path = scanner.nextLine();//调用getWorkbook方法InsertExcel.getWorkbook(productList, path);//如果执行成功打印System.out.println("操作成功");}/*** 将数据库中的数据，写入Excel文件中* @param productList* @param path* @throws IOException*/public static void getWorkbook(List<Product> productList,String path) throws IOException {//新建工作簿XSSFWorkbook workbook = new XSSFWorkbook();//新建工作表XSSFSheet sheet = workbook.createSheet("商品表");//第一行,表头XSSFRow row = sheet.createRow(0);XSSFCell cell = row.createCell(0);cell.setCellValue("商品编号");XSSFCell cell1 = row.createCell(1);cell1.setCellValue("商品名称");XSSFCell cell2 = row.createCell(2);cell2.setCellValue("商品价格（单位：元/斤）");XSSFCell cell3 = row.createCell(3);cell3.setCellValue("商品库存（单位：吨）");//将每条list中的商品写入工作表中，从第二行开始，第一行,表头for (int i = 1; i <= productList.size(); i++) {//获取product（注意是i-1）Product product = productList.get(i-1);//插入数据XSSFRow row1 = sheet.createRow(i);XSSFCell cell4 = row1.createCell(0);cell4.setCellValue(product.getId());XSSFCell cell5 = row1.createCell(1);cell5.setCellValue(product.getName());XSSFCell cell6 = row1.createCell(2);cell6.setCellValue(product.getPrice());XSSFCell cell7 = row1.createCell(3);cell7.setCellValue(product.getPstock());}//获取文件输出流FileOutputStream fos = new FileOutputStream(path);//工作簿写入workbook.write(fos);//刷新输出流fos.flush();//关闭资源fos.close();workbook.close();}}
service接口
package com.aaa.service;import com.aaa.domain.Product;import java.util.List;/*** @author : 尚腾飞* @version : 1.0* @createTime : /9/26 21:26* @description :*/public interface ProductService {/*** 插入数据* @param list* @return*/Integer save(List<Product> list);/*** 查询所有* @return*/List<Product> getAll();}
service实现类
package com.aaa.service.impl;import com.aaa.dao.ProductDao;import com.aaa.domain.Product;import com.aaa.service.ProductService;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import java.io.IOException;import java.io.InputStream;import java.util.List;/*** @author : 尚腾飞* @version : 1.0* @createTime : /9/26 21:27* @description :*/public class ProductServiceImpl implements ProductService {//mybatis配置文件的路径String resource = "MybatisConfig.xml";InputStream inputStream;{try {inputStream = Resources.getResourceAsStream(resource);} catch (IOException e) {throw new RuntimeException(e);}}//得到SqlSessionFactorySqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);/*** 添加方法* @param* @return*/@Overridepublic Integer save(List<Product> productList) {//开启SqlSessionSqlSession sqlSession = sqlSessionFactory.openSession();//获取代理对象ProductDao productDao = sqlSession.getMapper(ProductDao.class);int i = 0;for (Product product : productList) {//插入数据Integer save = productDao.save(product);if (save!=null){i = i+save;}mit();}sqlSession.close();return i;}/*** 查询所有* @return List<Product>*/@Overridepublic List<Product> getAll() {SqlSession sqlSession = sqlSessionFactory.openSession();//获取代理对象ProductDao productDao = sqlSession.getMapper(ProductDao.class);//关闭资源List<Product> productList = productDao.getAll();sqlSession.close();return productList;}}
dao接口
package com.aaa.dao;import com.aaa.domain.Product;import java.util.List;/*** @author : 尚腾飞* @version : 1.0* @createTime : /9/26 21:28* @description :*/public interface ProductDao {Integer save(Product product);List<Product> getAll();}
mapper文件
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapperPUBLIC "-////DTD Mapper 3.0//EN""/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.aaa.dao.ProductDao"><insert id="save">insert into product (id,name,price,pstock) values(#{id},#{name},#{price},#{pstock})</insert><select id="getAll" resultType="com.aaa.domain.Product">select *from product;</select></mapper>
运行InsertExcel.class
设置样式
在Demo2中基础上
package com.aaa.write;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.FillPatternType;import org.apache.poi.ss.usermodel.IndexedColors;import org.apache.poi.xssf.usermodel.*;import java.io.FileOutputStream;import java.io.IOException;/*** @author : 尚腾飞* @version : 1.0* @createTime : /9/26 20:13* @description :* 1. 创建一个Excel表格* 2. 创建工作表* 3. 创建行* 4. 创建单元格赋值* 5. 通过输出流将对象下载到磁盘*/public class Demo2 {public static void main(String[] args) throws IOException {//1. 创建一个Excel表格XSSFWorkbook workbook = new XSSFWorkbook();//2. 创建工作表XSSFSheet sheet = workbook.createSheet("工作表一");//单元格样式XSSFCellStyle cellStyle = workbook.createCellStyle();cellStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//字体样式XSSFFont font = workbook.createFont();font.setFontName("宋体");font.setColor(IndexedColors.PINK.getIndex());cellStyle.setFont(font);//3. 创建行XSSFRow row1 = sheet.createRow(0);//4. 创建单元格赋值XSSFCell cell1 = row1.createCell(0);cell1.setCellStyle(cellStyle);cell1.setCellValue("陈娇花");XSSFCell cell2 = row1.createCell(1);cell2.setCellStyle(cellStyle);cell2.setCellValue("赵铁牛 ");XSSFRow row2 = sheet.createRow(1);XSSFCell cell21 = row2.createCell(0);cell21.setCellValue("陈娇花");XSSFCell cell22 = row2.createCell(1);cell22.setCellValue("赵铁牛");//5. 通过输出流将对象下载到磁盘FileOutputStream fileOutputStream = new FileOutputStream("E:\\mind\\poi\\aaa.xlsx");workbook.write(fileOutputStream);//刷新输出流fileOutputStream.flush();//6. 释放资源fileOutputStream.close();workbook.close();}}
测试结果：
全文参考的下面教程：视频特点人美声甜，年轻貌美
Java教程使用POI读取excel文档
/video/BV1bJ411G7Aw?p=1&vd_source=0eeffffe21421dad257058d19ad2f1d2
EasyPoi
But
apache的缺点
但是apache存在一些缺点
对于复杂的Excel模板样式需要编写大量的代码实现大数据量的读取/导成效率低下，甚至可能内存溢出
为了解决上述poi的缺点，国内有很多开源项目对poi进行了封装，大大减少代码量，使其能够更简单的被我们使用并提高开发效率，例如EasyPoi,Excel4J,HuTools等优秀的开源项目。我们这次以EasyPoi为例
easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板语言(熟悉的表达式语法),完成以前复杂的写法。
使用EasyPoi
1. 导入jar
SpringBoot<!-- /artifact/cn.afterturn/easypoi-spring-boot-starter --><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-spring-boot-starter</artifactId><version>4.4.0</version></dependency>
普通maven项目
<!-- /artifact/cn.afterturn/easypoi-base --><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>4.4.0</version></dependency>
2. 编写实体类
package com.aaa.domain;import cn.afterturn.easypoi.excel.annotation.Excel;import lombok.Data;import java.util.Date;/*** @author : 尚腾飞(838449693@)* @version : 1.0* @createTime : /10/8 20:01* @description :*/@Datapublic class Userinfo {@Excel(name = "id")private Integer id;@Excel(name = "用户名")private String username;@Excel(name = "密码")private String password;@Excel(name = "手机号")private String phone;@Excel(name = "部门编号")private Integer deptid;@Excel(name = "头像")private String avatar;@Excel(name = "创建时间" ,format = "yyyy-MM-dd HH:mm:ss")private Date createtime;}
3. 编写service，controller
package com.aaa.web;import cn.afterturn.easypoi.excel.ExcelExportUtil;import cn.afterturn.easypoi.excel.entity.ExportParams;import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;import com.aaa.domain.Userinfo;import com.aaa.service.impl.UserinfoServiceImpl;import org.apache.poi.ss.usermodel.Workbook;import java.io.File;import java.io.FileOutputStream;import java.util.List;/*** @author : 尚腾飞(838449693@)* @version : 1.0* @createTime : /10/8 20:45* @description :*/public class EasyPoiController {public static void main(String[] args) throws Exception {UserinfoServiceImpl userinfoService = new UserinfoServiceImpl();List<Userinfo> userinfoList = userinfoService.findAll();System.out.println(userinfoList);ExportParams exportParams = new ExportParams("员工表", "员工表", ExcelType.XSSF);Workbook workbook = ExcelExportUtil.exportExcel(exportParams, Userinfo.class, userinfoList);FileOutputStream fos = new FileOutputStream(new File("E:\\mind\\poi\\userinfo.xlsx"));workbook.write(fos);//刷新输出流fos.flush();//关闭资源fos.close();workbook.close();}}
POI：从Excel文件中读取数据 向Excel文件中写入数据 将Excel表格中的数据插入数据库 将数据库中的数据添加到Excel表
