POI总结-java读取写入excel文件（读取excel并导入数据库-读取数据库数据并写入excel）

本文链接：https://blog.youkuaiyun.com/programmer_trip/article/details/107293868

POI包结构
HSSF - 读写Microsoft Excel XLS格式 07office以前的版本
XSSF - 读写Microsoft Excel OOXML XLSX格式 07office以后的版本
HWPF- 读写Microsoft Word DOC格式针对的word文档
HSLF - 读写Micorsoft PowerPoint格式

创建一个maven项目
先导入POI相关的第三方jar包

<!-- 使用apache poi需要的依赖  -->
 <dependency>
       <groupId>org.apache.poi</groupId>
       <artifactId>poi</artifactId>
       <version>4.0.1</version>
 </dependency>
 <dependency>
       <groupId>org.apache.poi</groupId>
       <artifactId>poi-ooxml</artifactId>
       <version>4.0.1</version>
 </dependency>
 <dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-compress</artifactId>
    <version>1.19</version>
</dependency>
<!-- 使用druid连接池建立java程序与mysql数据库的连接需要的依赖  -->
<dependency>
     <groupId>mysql</groupId>
     <artifactId>mysql-connector-java</artifactId>
     <version>5.1.26</version>
</dependency>
<dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>druid</artifactId>
     <version>1.0.9</version>
</dependency>

如果不下载org.apache.commons这个依赖，运行的时候会抛出如下异常

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/commons/compress/utils/InputStreamStatistics
	at org.apache.poi.openxml4j.util.ZipArchiveThresholdInputStream.<init>(ZipArchiveThresholdInputStream.java:62)
	at org.apache.poi.openxml4j.util.ZipSecureFile.getInputStream(ZipSecureFile.java:147)
	at org.apache.poi.openxml4j.util.ZipSecureFile.getInputStream(ZipSecureFile.java:34)
	at org.apache.poi.openxml4j.util.ZipFileZipEntrySource.getInputStream(ZipFileZipEntrySource.java:66)
	at org.apache.poi.openxml4j.opc.ZipPackage.getPartsImpl(ZipPackage.java:258)
	at org.apache.poi.openxml4j.opc.OPCPackage.getParts(OPCPackage.java:721)
	at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:237)
	at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:166)
	at org.apache.poi.ooxml.POIXMLDocument.openPackage(POIXMLDocument.java:89)
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:340)
	at excelTest.ReadExcelTest.main(ReadExcelTest.java:15)
Caused by: java.lang.ClassNotFoundException: org.apache.commons.compress.utils.InputStreamStatistics
	at java.net.URLClassLoader.findClass(URLClassLoader.java:382)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
	at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:349)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
	... 11 more

Process finished with exit code 1

在resource目录下创建一个druid.properties配置文件，内容如下

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC
username=root
password=Root123*
maxActive=20
minIdle=3
initialSize=5
maxWait=60000

读取excel文件前，先准备一个有数据的excel表格
在这里插入图片描述
从excel文件读取数据的流程

1 创建工作簿
2 获取工作表
3 遍历工作表获得行对象
4 遍历行对象获取单元格对象
5 获得单元格的值

package excelTest;

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;

/**
 * 使用poi读取excel文件中的内容
 */
public class ReadExcelTest {
    public static void main(String[] args) {
        XSSFWorkbook workbook = null;
        try {
            //创建工作簿
             workbook = new XSSFWorkbook("D:/test.xlsx");
            //获取工作表
            XSSFSheet sheet = workbook.getSheetAt(0);
            //遍历工作表
            for (Row cells : sheet) {
                //遍历行，获取单元格
                for (Cell cell : cells) {
                    //获取单元格的值,判断是数字还是字符串，然后分别输出
                    if(cell.getCellType() == CellType.NUMERIC){
                        System.out.print(cell.getNumericCellValue() + '\t');
                    }else if(cell.getCellType() == CellType.STRING){
                        System.out.print(cell.getStringCellValue()+ '\t');
                    }
                }
                //每打印一行就换一行
                System.out.println();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                //关闭资源
                if (workbook != null) {
                    workbook.close();

                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

结果输出

姓名	年龄	性别	兴趣爱好	
张三	24.0male	唱歌	
里斯	29.0female	吃饭	
网五	34.0male	跳舞	
赵六	39.0male	喝酒

------------>到这里excel文件内容的读取就完成了

向excel文件写入数据流程

1 创建一个excel文件
2 创建工作表
3 创建行
4 创建单元格赋值
5 通过输出流将对象写入磁盘

package excelTest;

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;

/**
 * 使用poi将数据写入excel文件中
 */
public class WriteExcelTest {
    public static void main(String[] args) {

        XSSFWorkbook xssfWorkbook = null;
        FileOutputStream out = null;
        try {
            //创建工作簿
            xssfWorkbook = new XSSFWorkbook();
            //创建表
            XSSFSheet xssfSheet = xssfWorkbook.createSheet("first_sheet");
            //创建行，这里创建四行
            XSSFRow row1 = xssfSheet.createRow(0);
            XSSFRow row2 = xssfSheet.createRow(1);
            XSSFRow row3 = xssfSheet.createRow(2);
            XSSFRow row4 = xssfSheet.createRow(3);
            //为第一行创建单元格并赋值
            row1.createCell(0).setCellValue("姓名");
            row1.createCell(1).setCellValue("年龄");
            row1.createCell(2).setCellValue("性别");
            //为第二行创建单元格并赋值
            row2.createCell(0).setCellValue("张三");
            row2.createCell(1).setCellValue("18");
            row2.createCell(2).setCellValue("男");
            //为第三行创建单元格并赋值
            row3.createCell(0).setCellValue("李四");
            row3.createCell(1).setCellValue("17");
            row3.createCell(2).setCellValue("男");
            //为第四行创建单元格并赋值
            row4.createCell(0).setCellValue("黄蓉");
            row4.createCell(1).setCellValue("20");
            row4.createCell(2).setCellValue("女");
            //创建一个输出流
            out = new FileOutputStream("D:/output.xlsx");
            //将创建的工作簿内容写入磁盘
            xssfWorkbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                //关闭资源,需做非空判断
                if (out != null) {
                    out.close();
                }
                if (xssfWorkbook != null) {
                    xssfWorkbook.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        System.out.println("输出成功！");
    }
}

java数据写入excel文件中
在这里插入图片描述
java利用POI将本地的excel表格内的数据读取并导入数据库中，以及将数据库中的表数据读取出来并写入本地excel文件中
下面是代码
包的结构

下面是具体的代码
程序入口

package cn.excel.exercute;

import cn.excel.domain.Product;
import cn.excel.server.ProductServer;
import cn.excel.server.impl.ProductServerImpl;
import org.apache.poi.ss.usermodel.*;
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.ArrayList;
import java.util.List;
import java.util.Scanner;

public class ProgramTest {
    public static void main(String[] args)  {
        Scanner sc = new Scanner(System.in);
        System.out.println("请您输入操作数：1 读取本地excel文件，并将数据导入到数据库中 2 读取数据库中的表数据并写入本地的excel文件");
        //根据不同的数据做不同的操作
        int num = sc.nextInt();
        ProductServer productServer = new ProductServerImpl();
        if(num == 1){
            //读取excel文件中的内容
            System.out.println("请您输入需要读取的excel文件的路径");
            String path = sc.next();
            //将封装好的对象信息导入到数据库中
            List<Product> read = read(path);
            //将List<Product>解析并写入数据库中
            productServer.parse(read);
        }else if(num == 2){
            //查找数据库中student表的信息，并将数据封装成一个productList集合
            List<Product> productList = productServer.findAll();
            //将productList集合中的数据写入磁盘
            System.out.println("请您输入需要写入的文件路径：");
            //通过控制台输入路径
            String path = sc.next();
            //将封装好的对象进行解析，并写入本地excel文件
            write(productList,path);
            //程序运行完毕后提示运行成功
            System.out.println("写入成功");
        }else {
            System.out.println("请您重新输入");
        }
    }

    /**
     * 读取excel文件后，将一行数据封装成一个Product对象，多行返回一个List对象即可
     * param path 输出的文件路径
     */
    public static List<Product> read(String path){
        XSSFWorkbook xssfWorkbook = null;
        List<Product> productList = new ArrayList<>();
        try {
        	//创建工作簿
            xssfWorkbook = new XSSFWorkbook("D:/student.xlsx");
            //创建工作表
            XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
            //获取最后一行是第几行
            int lastRowNum = sheet.getLastRowNum();
            //由于第一行是字段名称，不做读取，后面建表的时候生成字段，因此这里从第二行开始读取，注意第二行的下标是1
            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集合中
                            list.add(value);
                        }
                    }
                    //将读取出的每个行的数据封装成一个Product对象
                    Product product = new Product(Integer.parseInt(list.get(0)), list.get(1), Integer.parseInt(list.get(2)), list.get(3), list.get(4));
                    //将每个Product对象添加到productList集合中，相当于一个productList集合装的就是一个sheet表的数据
                    productList.add(product);
                }
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    return productList;
    }

    public static void write(List<Product> productList,String path){
        System.out.println(productList);
        //创建一个工作簿
        Workbook xssfWorkbook = new XSSFWorkbook();
        //创建一个表
        Sheet student = xssfWorkbook.createSheet("student");
        //创建第一行
        Row row1 = student.createRow(0);
        //为第一行的每个单元格赋值
        row1.createCell(0).setCellValue("编号");
        row1.createCell(1).setCellValue("姓名");
        row1.createCell(2).setCellValue("年龄");
        row1.createCell(3).setCellValue("性别");
        row1.createCell(4).setCellValue("爱好");
        FileOutputStream out = null;
        try {
            //创建一个输出流
            out = new FileOutputStream(path);
            //遍历productList集合，并为每行的每一个单元格赋值
            for (int i = 0; i < productList.size(); i++) {
                Row row = student.createRow(i + 1);
                row.createCell(0).setCellValue(productList.get(i).getId());
                row.createCell(1).setCellValue(productList.get(i).getName());
                row.createCell(2).setCellValue(productList.get(i).getAge());
                row.createCell(3).setCellValue(productList.get(i).getSex());
                row.createCell(4).setCellValue(productList.get(i).getHabit());
            }
            xssfWorkbook.write(out);
            out.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            try{
                if(out != null){
                    out.close();
                }
                if(xssfWorkbook != null){
                    xssfWorkbook.close();
                }
            }catch(Exception e){
                e.printStackTrace();
            }
        }
    }
}

Product实体类

package cn.excel.domain;

import java.util.Objects;

/**
 * 通过表格中的属性映射成一个java类
 */
public class Product {
    private int id;
    private String name;
    private int age;
    private String sex;
    private String habit;

    public Product() {}

    public Product(int id, String name, int age, String sex, String habit) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.sex = sex;
        this.habit = habit;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getHabit() {
        return habit;
    }

    public void setHabit(String habit) {
        this.habit = habit;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Product product = (Product) o;
        return id == product.id &&
                age == product.age &&
                Objects.equals(name, product.name) &&
                Objects.equals(sex, product.sex) &&
                Objects.equals(habit, product.habit);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, name, age, sex, habit);
    }

    @Override
    public String toString() {
        return "Product{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", sex='" + sex + '\'' +
                ", habit='" + habit + '\'' +
                '}';
    }
}

JDBC连接的工具类

package cn.excel.util;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import jdk.internal.util.xml.impl.Input;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * @author XXX
 * 数据库工具类 内部维护一个通过druid实现的连接池
 */
public class DBUtil_druid {
    private static DataSource pool;

    static {
        InputStream is = DBUtil_druid.class.getClassLoader().getResourceAsStream("druid.properties");
        Properties ps = new Properties();
        try {
            ps.load(is);
            pool = DruidDataSourceFactory.createDataSource(ps);
            is.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 返回一个连接池实例
     * @return 返回一个连接池实例
     */
    public static DataSource getDataSource(){
        return pool;
    }

    /**
     * 从连接池中返回一个Connection连接对象
     * @return 返回一个Connection对象
     */
    public static Connection getConnection(){
        try {
            return pool.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 关闭结果集,sql执行载体对象,归还连接给连接池
     * @param res 结果集
     * @param stat sql执行载体
     * @param conn 连接对象
     */
    public static void closeConnection(ResultSet res, Statement stat,Connection conn) throws SQLException {
        try {
            if(res != null){
                res.close();
            }
            if(stat != null){
                stat.close();
            }
            if(conn != null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        conn.commit();
        conn.rollback();
        conn.setAutoCommit(true);
        conn.close();
    }
}

ProductServer接口

package cn.excel.server;

import cn.excel.domain.Product;

import java.util.List;

public interface ProductServer {

    /**
     * 对传入的List<Product>中的每个Product调用parse方法
     * @param read
     */
    void parse(List<Product> read);

    /**
     * 查询student表中的所有数据
     * @return
     */
    List<Product> findAll();
}

ProductServer实现类

package cn.excel.server.impl;

import cn.excel.dao.ProductDao;
import cn.excel.dao.impl.ProductDaoImpl;
import cn.excel.domain.Product;
import cn.excel.server.ProductServer;

import java.util.List;

public class ProductServerImpl implements ProductServer {
    private ProductDao productDao = new ProductDaoImpl();

    /**
     * 遍历List<Product>，对里面的每一个produce对象调用parse方法
     * @param read
     */
    @Override
    public void parse(List<Product> read) {
        for (Product product : read) {
            productDao.parse(product);
        }
    }

    /**
     * 查找student表中的所有数据，并返回成一个List对象
     * @return
     */
    @Override
    public List<Product> findAll() {
        return productDao.findAll();
    }
}

ProductDao 接口

package cn.excel.dao;

import cn.excel.domain.Product;
import java.util.List;

public interface ProductDao {

     /**
      * 解析每一个 Product对象
      * @param product
      */
     void parse(Product product) ;

     /**
      * 查询student表中所有字段信息
      * @return
      */
     List<Product> findAll();
}

ProductDao 实现类

package cn.excel.dao.impl;

import cn.excel.dao.ProductDao;
import cn.excel.domain.Product;
import cn.excel.util.DBUtil_druid;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductDaoImpl implements ProductDao {
    Connection connection = null;

    public ProductDaoImpl() {}

    @Override
    public void parse(Product product) {
        PreparedStatement ps = null;
        //如果把获取连接对象的代码作为成员变量，可能会导致有一些代码还需要使用这个连接，时连接已经被关闭的情况，运行代码的过程中遇到了这个坑，所以在这里做个记录，把获取连接对象的代码
        connection = DBUtil_druid.getConnection();
        try {
            String sql = "insert into student values(?,?,?,?,?)";
            ps = connection.prepareStatement(sql);
            ps.setInt(1,product.getId());
            ps.setString(2,product.getName());
            ps.setInt(3,product.getAge());
            ps.setString(4,product.getSex());
            ps.setString(5,product.getHabit());
            ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public List<Product> findAll() {
        String sql = "select id,name,age,sex,habit from student";
        PreparedStatement ps = null;
        ResultSet rs = null;
        Product product = null;
        connection = DBUtil_druid.getConnection();
        List<Product> productList = new ArrayList<>();
        try {
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();

            while(rs.next()){
                int id = rs.getInt(1);
                String name = rs.getString(2);
                int age = rs.getInt(3);
                String sex = rs.getString(4);
                String habit = rs.getString(5);
                productList.add(new Product(id,name,age,sex,habit));
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
        return productList;
    }
}

读取文件前的数据准备,D:/student.xlsx
在这里插入图片描述
当运行操作数为1时，执行结果

查询数据库的student表显示的数据，说明已经成功将本地excel文件中的数据导入到mysql数据库中

将数据库student表中数据读取出来，并写入本地excel文件
操作数为2

查看本地的D:/output.xlsx文件的内容，说明已经成功将数据库表数据写入本地excel文件i中
在这里插入图片描述
加下来改变表格的样式
代码如下

public static void write(List<Product> productList,String path){
        System.out.println(productList);
        //创建一个工作簿
        Workbook xssfWorkbook = new XSSFWorkbook();
        //创建一个表
        Sheet student = xssfWorkbook.createSheet("student");
        //修改的开始标志------------------------------
        //获取工作簿的样式
        CellStyle cellStyle = xssfWorkbook.createCellStyle();
        //设置背景色为红色
        cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        //设置背景色的填充样式为实心
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //实例化一个工作簿的字体对象
        Font font = xssfWorkbook.createFont();
        //设置字体为黑体
        font.setFontName("黑体");
        //设置字体颜色为蓝色
        font.setColor(IndexedColors.BLUE.getIndex());
        //将实例化的字体建立与实例化的样式的联系
        cellStyle.setFont(font);

        //创建第一行
        Row row1 = student.createRow(0);
        //创建单元格，并为每个单元格赋值和设置样式
        Cell cell0 = row1.createCell(0);
        Cell cell1 = row1.createCell(1);
        Cell cell2 = row1.createCell(2);
        Cell cell3 = row1.createCell(3);
        Cell cell4 = row1.createCell(4);
        cell0.setCellValue("编号");
        cell0.setCellStyle(cellStyle);
        cell1.setCellValue("姓名");
        cell1.setCellStyle(cellStyle);
        cell2.setCellValue("年龄");
        cell2.setCellStyle(cellStyle);
        cell3.setCellValue("性别");
        cell3.setCellStyle(cellStyle);
        cell4.setCellValue("爱好");
        cell4.setCellStyle(cellStyle);
        //修改的结束标志------------------------------
        FileOutputStream out = null;
        try {
            //创建一个输出流
            out = new FileOutputStream(path);
            //遍历productList集合，并为每行的每一个单元格赋值
            for (int i = 0; i < productList.size(); i++) {
                Row row = student.createRow(i + 1);
                row.createCell(0).setCellValue(productList.get(i).getId());
                row.createCell(1).setCellValue(productList.get(i).getName());
                row.createCell(2).setCellValue(productList.get(i).getAge());
                row.createCell(3).setCellValue(productList.get(i).getSex());
                row.createCell(4).setCellValue(productList.get(i).getHabit());
            }
            xssfWorkbook.write(out);
            out.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            try{
                if(out != null){
                    out.close();
                }
                if(xssfWorkbook != null){
                    xssfWorkbook.close();
                }
            }catch(Exception e){
                e.printStackTrace();
            }
        }
    }