javaPOI编程（EXCEL读写）

javaPOI编程（EXCEL读写）

准备工作：

注意：需要导入的核心依赖:

  <!--POI -->
<!--xls(03版本)-->
 <!-- 
https://mvnrepository.com/artifact/org.apache.poi/poi 
-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.0.0</version>
        </dependency>
<!--xlsx(07版本)-->
<!-- 
https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.0.0</version>
        </dependency>

其他工具依赖：

<!--    日期格式化工具-->
        <!-- https://mvnrepository.com/artifact/joda-time/joda-time -->
        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.10.10</version>
        </dependency>
<!--        单元测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13</version>
        </dependency>
<!--        连接数据库-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.25</version>
        </dependency>

没有maven的小伙伴可以去上面的URL下载对应的jar包然后手动导入

一.读取Excel文件

        /*
         * HSSFWorkbook 对应03版本的Excel文件 后缀为xls
         * XSSFWorkbook 对应07版本的Excel文件 后缀为xlsx
         * SXSSFWorkbook 对应07版本的Excel文件 后缀为xlsx,相比较XSSFWorkbook读取速度更快
         */

注意：读取时03版本的工作表对象（workbook）应该只作用于03版本的文件

1.步骤：

1.根据文件输入流创建工作簿对象

2.通过工作簿对象创建工作表对象

3.通过工作表创建行对象并读取标题头

4.判断并读取表中的数据元素

5.关闭流

小试牛刀:

package POI;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.junit.Test;

import java.io.FileInputStream;
public class ReadTest {
    String path = "E:\\Java\\代码\\poi-study\\src\\excel\\";
    @Test
    public void readTest() throws Exception{
        //1.获取文件输入流
        FileInputStream in = new FileInputStream(path+"03统计表.xls");
        //2.得到对应的工作簿
        Workbook workbook = new HSSFWorkbook(in);
        //3.得到对应的表
        Sheet sheet = workbook.getSheetAt(0);
        //4.得到行
        Row row = sheet.getRow(0);
        //5.得到列行中的
        Cell cell = row.getCell(1);
        System.out.println(cell.getStringCellValue());

        in.close();
    }
}

2.读取实例

下面以读取未知Excel文件为例：

注意：新版本的POI将Excel中的日期类型也改为字符串类型！！！！所以日期不再包含于numeric中

package POI;


import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.joda.time.DateTime;
import org.junit.Test;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Date;

public class ReadTest02 {
    String path = "E:\\Java\\代码\\poi-study\\src\\excel\\";
    @Test
    public void readTest02(){
        FileInputStream fileInputStream = null;
        try{
            fileInputStream = new FileInputStream(path+"emp.xls");
            /*
             * HSSFWorkbook 对应03版本的Excel文件 后缀为xls
             * XSSFWorkbook 对应07版本的Excel文件 后缀为xlsx
             * SXSSFWorkbook 对应07版本的Excel文件 后缀为xlsx,相比较XSSFWorkbook读取速度更快
             */

            //通过文件输入流创建工作簿对象
            //使用泛型使代码更加通用，使用07版本的Excel时仅需要改变指向
            Workbook workbook = new HSSFWorkbook(fileInputStream);
            //通过工作簿创建单元表
            Sheet sheet = workbook.getSheet("emp工作表");
            //得到行标题,Excel可以操作的，这里都可以操作
            Row title = sheet.getRow(0);//第一列即为行标题

        /*
        注意：
        java中行和列从0开始
        Excel中从0开始
         */

            int columnCount = title.getPhysicalNumberOfCells();
            for (int i = 0; i <columnCount ; i++) {
                System.out.print(title.getCell(i)+"|");
            }
            System.out.println();//换行
            //通过getPhysicalNumberOfRows()方法得到该文件中行的数量
            int rowCount  = sheet.getPhysicalNumberOfRows();
            //遍历所有行
            for (int rowNum = 1; rowNum <rowCount ; rowNum++) {

                Row rowDate = sheet.getRow(rowNum);
                String cellValue = "";
                //遍历行中的所有元素
                for (Cell cell : rowDate) {

                    if (cell!=null){
                        //得到单元格的数据类型
                        CellType cellType = cell.getCellType();
                        //匹配单元格的数据类型
                        switch(cellType){
                            case STRING://字符串
                                System.out.print("[String]");
                                cellValue = cell.getStringCellValue();
                                break;
                            case BOOLEAN://布尔值
                                System.out.print("[boolean]");
                                cellValue =String.valueOf(cell.getBooleanCellValue());
                                break;
                            case BLANK://空的
                                System.out.print("[blank]");
                                break;
                            case NUMERIC://数字
                                System.out.print("[numeric]");
                                //如果是日期类型，则将其格式化为字符串类型
                                if(HSSFDateUtil.isCellDateFormatted(cell)){
                                    System.out.print("[date]");
                                    Date date = cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString("yyyy/MM/dd HH:mm:ss");
                                }else {
                                    System.out.print("[number]");
                                    //为了防止字符串过长，将其转化为字符串输出
                                    //cell.setCellType(CellType.STRING);
                                    cellValue = String.valueOf(cell.getNumericCellValue());
                                    //cellValue = cell.getStringCellValue();
                                }
                                break;
                            case ERROR:
                                System.out.print("[数据类型错误error]");
                                break;
                        }
                        //输出此字符串
                        System.out.println(cellValue);
                    }

                }
            }

        }catch (IOException e){
            e.printStackTrace();
        } finally {
            //关闭输出流
            if (fileInputStream != null){
                try {
                    fileInputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    //计算单元格数据
    @Test
    public void formulaTest(){
        FileInputStream fileInputStream = null;
        try {
            fileInputStream = new FileInputStream(path+"公式.xls");
            Workbook workbook = new HSSFWorkbook(fileInputStream);
            Sheet sheet = workbook.getSheet("sheet1");
            Row row = sheet.getRow(4);
            Cell cell = row.getCell(0);
            //得到行标题,Excel可以操作的，这里都可以操作

            //拿到计算公式对象
            FormulaEvaluator evaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);

            //输出单元格内容
            CellType cellType = cell.getCellType();
            if (cellType==CellType.FORMULA){
                //得到该单元格的计算公式
                String formula = cell.getCellFormula();
                System.out.println(formula);
                //计算
                CellValue cellValue = evaluator.evaluate(cell);

                //evaluate(Cell cell)方法：
                // 如果单元格包含公式，则计算并返回公式，否则CellValue将从单元格及其单元格类型复制相应的单元格值。

                String result = cellValue.formatAsString();
                System.out.println(result);
            }
        }catch (IOException e){
            e.printStackTrace();
        }finally {
            //关闭输出流
            if (fileInputStream != null){
                try {
                    fileInputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }


    }
}

提示：03版和07版切换仅需要切换工作簿对象的指向即可

二.写Excel文件

1.步骤

1.创建工作簿

2.创建工作表

3.将首行作为表的字段（标题头）

4.写入数据

5.通过文件输出流将工作簿写入Excel workbook.write(fos);

6.关闭流

7.清除零时文件(指向类型)workbook.dispose();

2.使用实例

package POI;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.joda.time.DateTime;
import org.junit.Test;

import java.io.FileOutputStream;

public class Write {
    String path = "E:\\Java\\代码\\poi-study\\src\\excel\\";
    //03版Excel
    @Test
    public void excelWrite03() throws Exception{
        //1.创建一个工作簿
        Workbook workbook = new HSSFWorkbook();
        //2.创建一个工作表
        Sheet sheet = workbook.createSheet("某某统计表");
        //3.创建行03版本最多有65535条
        //第一行
        //excel (1,1) -->java (0,0)
        Row row1 = sheet.createRow(0);
        //4.创建一个单元格
        Cell cell11 = row1.createCell(0);
        //设置单元内容
        cell11.setCellValue("今日人数");
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue(666);
        //第二行
        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        Cell cell22 = row2.createCell(1);
        cell21.setCellValue("统计时间");
        String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(time);
        //生成一张表 03版 以xls结尾
        //5.获取输出流
        FileOutputStream fos = new FileOutputStream(path+"李江的03统计表.xls");
        //6.输出
        workbook.write(fos);
        //7.关闭流
        fos.close();
        //8.删除零时文件
        (HSSFWorkbook)workbook.dispose();

    }
    //07版Excel
    @Test
    public void excelWrite07() throws Exception{
        //1.创建一个工作簿
        Workbook workbook = new XSSFWorkbook();
        //2.创建一个工作表
        Sheet sheet = workbook.createSheet("某某统计表");
        //3.创建行(行数无限制)
        //第一行
        Row row1 = sheet.createRow(0);
        //4.创建一个单元格
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("今日人数");
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue(666);
        //第二行
        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        Cell cell22 = row2.createCell(1);
        cell21.setCellValue("统计时间");
        String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(time);
        //生成一张表 07版 以xlsx结尾
        //5.获取输出流(建议使用try catch环绕)
        FileOutputStream fos = new FileOutputStream(path+"李江的07统计表.xlsx");
        //6.输出
        workbook.write(fos);
        //7.关闭流
        fos.close();
        //8.删除零时文件
		(XSSFWorkbook)workbook.dispose();

    }
}

**注意：**03版的最多可以写65535条数据，07版本的则可以无限制的写入

3.三种工作表对象写入测试

三种工作簿对象写入速度测试

package POI;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.FileOutputStream;

public class OverWrite{
    String path = "E:\\Java\\代码\\poi-study\\src\\excel\\";
    
    //速度最快
    @Test
    public void writeTest03() throws Exception{
        //开始时间
        Long start = System.currentTimeMillis();
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("测试特性");
        //最多65536行
        for (int i = 0; i < 65535; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(j);
            }
        }
        System.out.println("循环结束");
        FileOutputStream fos = new FileOutputStream(path+"特性测试03.xls");
        workbook.write(fos);
        fos.close();
		(HSSFWorkbook)workbook.dispose();
		
        Long end = System.currentTimeMillis();
        System.out.println((double) (end-start)/1000);//精确到秒

    }//速度最慢
    @Test
    public void writeTest07() throws Exception{
        //开始时间
        Long start = System.currentTimeMillis();
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("测试特性");
        //理论上无限多
        for (int i = 0; i < 65535; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(j);
            }
        }
        System.out.println("循环结束");
        FileOutputStream fos = new FileOutputStream(path+"特性测试07.xlsx");
        workbook.write(fos);
        fos.close();
        (XSSFWorkbook)workbook.dispose();

        Long end = System.currentTimeMillis();

        System.out.println((double) (end-start)/1000);//精确到秒

    }


    //速度较快
    @Test
    public void writeTestSuper07() throws Exception{
        //开始时间
        Long start = System.currentTimeMillis();
        //快速模式
        Workbook workbook = new SXSSFWorkbook();//使用泛型
        Sheet sheet = workbook.createSheet("测试特性");
        //理论上无限多
        for (int i = 0; i < 65535; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(j);
            }
        }
        System.out.println("循环结束");
        FileOutputStream fos = new FileOutputStream(path+"特性测试super07.xlsx");
        workbook.write(fos);
        fos.close();
        //清除零时文件
        ((SXSSFWorkbook)workbook).dispose();
        Long end = System.currentTimeMillis();

        System.out.println((double) (end-start)/1000);//精确到秒

    }
}

结论：
HSSFWorkbook 对应03版本的Excel文件 后缀为xls 速度最快
XSSFWorkbook 对应07版本的Excel文件 后缀为xlsx 速度最慢
SXSSFWorkbook 对应07版本的Excel文件 后缀为xlsx,相比较XSSFWorkbook读取速度更快

无论哪一种模式，性能与速度始终不可兼得

三.数据库(mysql)+POI实战

1.公用JDBC工具包

package dao;

import java.sql.*;

public class JDBCUtil {

        private JDBCUtil(){}
        //注册驱动
        static {
            try {
                //注册驱动8.0+版本
                Class.forName("com.mysql.cj.jdbc.Driver");
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }
        //获取数据库连接对象
        public static Connection getConnection() {
            String url = "jdbc:mysql://127.0.0.1:3306/gzpowernode?&useSSL=false";
            String user = "";
            String password = "";
            Connection connection = null;
            try {
                connection = DriverManager.getConnection(url, user, password);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return connection;
        }

        //关闭资源
        public static void closeResource(Connection conn , Statement stmt, ResultSet rs) {
            try {
                if (rs!=null){
                    rs.close();
                }
                if (stmt!=null){
                    stmt.close();
                }
                if (conn!=null){
                    conn.close();
                }
            }catch (SQLException e){
                e.printStackTrace();
            }

        }
}

2.实体类（员工）

package pojo;

import java.util.Date;

public class Emp {
    private Integer empNo;
    private String eName;
    private String job;
    private Integer Mgr;
    private Date date;
    private Double sal;
    private Double comm;
    private Integer deptNO;

    public Emp(Integer empNo, String eName, String job, Integer mgr, Date date, Double sal, Double comm, Integer deptNO) {
        this.empNo = empNo;
        this.eName = eName;
        this.job = job;
        Mgr = mgr;
        this.date = date;
        this.sal = sal;
        this.comm = comm;
        this.deptNO = deptNO;
    }

    public Emp() {
    }

    public Integer getEmpNo() {
        return empNo;
    }

    public void setEmpNo(Integer empNo) {
        this.empNo = empNo;
    }

    public String geteName() {
        return eName;
    }

    public void seteName(String eName) {
        this.eName = eName;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public Integer getMgr() {
        return Mgr;
    }

    public void setMgr(Integer mgr) {
        Mgr = mgr;
    }

    public Date getDate() {
        return date;
    }

    public void setDate(Date date) {
        this.date = date;
    }

    public Double getSal() {
        return sal;
    }

    public void setSal(Double sal) {
        this.sal = sal;
    }

    public Double getComm() {
        return comm;
    }

    public void setComm(Double comm) {
        this.comm = comm;
    }

    public Integer getDeptNO() {
        return deptNO;
    }

    public void setDeptNO(Integer deptNO) {
        this.deptNO = deptNO;
    }
}

3.dao层读写数据

package dao;

import org.junit.Test;
import pojo.Emp;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

//从数据库中读取数据
public class GetEmp {
    public static ArrayList<Emp> getEmpFromDataBase(Connection conn){
        ArrayList<Emp> empList = new ArrayList<Emp>();
        Statement stmt = null;
        ResultSet rs = null;
        try {
            if (conn!=null){
                String sql = "select * from emp";
                stmt = conn.createStatement();
                rs = stmt.executeQuery(sql);
                while (rs.next()){
                    empList.add(new Emp(rs.getInt("EMPNO"),
                            rs.getString("ENAME"),
                            rs.getString("JOB"),
                            rs.getInt("MGR"),
                            rs.getDate("HIREDATE"),
                            rs.getDouble("SAL"),
                            rs.getDouble("COMM"),
                            rs.getInt("DEPTNO")
                            ));
                }
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JDBCUtil.closeResource(null,stmt,rs);
        }
        return empList;
    }
}

package dao;

import pojo.Emp;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

public class PutEmp {
    public static boolean putEmpToDataBase(ArrayList<Emp> empList, Connection conn){
        PreparedStatement ps = null;
        if (conn != null){
            try {
                String sql = "insert into emp2(EMPNO,ENAME,JOB,HIREDATE,SAL,COMM,DEPTNO) value(?,?,?,?,?,?,?)";
                ps = conn.prepareStatement(sql);
                //将java.util.Date转换为java.sql.Date
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                for (Emp emp : empList){
                    ps.setInt(1,emp.getEmpNo());
                    ps.setString(2,emp.geteName());
                    ps.setString(3,emp.getJob());
                    //将java中的日期date转换为字符串再转换为SQL中的日期date

                    //public static Date valueOf(String s)
                    // if the date given is not in the JDBC date escape format (yyyy-[m]m-[d]d)

                    ps.setDate(4, Date.valueOf(sdf.format(emp.getDate())));
                    ps.setInt(5,emp.getEmpNo());
                    ps.setInt(6,emp.getEmpNo());
                    ps.setInt(7,emp.getEmpNo());
                    ps.executeUpdate();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                System.out.println("数据存入数据库失败");
                return false;
            }finally {
                JDBCUtil.closeResource(null,ps,null);
            }
        }
        return true;
    }
}

4.POI读写Excel

package dao;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 pojo.Emp;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.text.SimpleDateFormat;
import java.util.ArrayList;


public class POI {
        //向Excel中写入数据
    /*
    参数 员工对象集合，数据库连接对象，工作表名称，工作表的路径
     */
    public boolean writeEmpToExcel(ArrayList<Emp> empList, Connection conn, String sheetName , String filePath){

        //通过路径判断是07版本还是03版本
        Workbook workbook;
        int signal;
        String suffix = filePath.substring(filePath.lastIndexOf(".")+1);
        if ("xls".equals(suffix)){
            //03版本
            System.out.println("后缀为xls");
            workbook = new HSSFWorkbook();
            signal = 0;
        }else{
            //07版本
            System.out.println("后缀为xlsx");
            workbook = new XSSFWorkbook();
            signal = 1;
        }

        //ArrayList<String> titleList = GetEmp.getEmpTitle(conn));
        //创建工作表
        Sheet sheet = workbook.createSheet(sheetName);

        //第一行为字段名
        Row title = sheet.createRow(0);
        title.createCell(0).setCellValue("员工编号");
        title.createCell(1).setCellValue("员工姓名");
        title.createCell(2).setCellValue("员工岗位");
        title.createCell(3).setCellValue("员工领导");
        title.createCell(4).setCellValue("员工入职日期");
        title.createCell(5).setCellValue("员工薪水");
        title.createCell(6).setCellValue("员工补贴");
        title.createCell(7).setCellValue("员工部门编号");

        //输入信息
        Row data;
        int rowIndex = 1;
        for (Emp emp:empList) {
            data = sheet.createRow(rowIndex++);
            data.createCell(0).setCellValue(emp.getEmpNo());
            data.createCell(1).setCellValue(emp.geteName());
            data.createCell(2).setCellValue(emp.getJob());
            data.createCell(3).setCellValue(emp.getMgr());
            data.createCell(4).setCellValue(new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(emp.getDate()));
            data.createCell(5).setCellValue(emp.getSal());
            data.createCell(6).setCellValue(emp.getComm());
            data.createCell(7).setCellValue(emp.getDeptNO());
        }

        //通过输出流将信息写入Excel文件中
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(filePath);
            //写入
            workbook.write(fos);
            
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("写入Excel失败");
            return false;
        }finally {
            if (fos != null){
                try {
                    fos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (signal==0){
            ((HSSFWorkbook)workbook).dispose();
            }else{
            ((XSSFWorkbook)workbook).dispose();
            }
            
        }
        return true;
    }


    //从Excel中读取数据并封装成员工对象集合
    public ArrayList<Emp> readEmpFromExcel(String filePath, String sheetName){

        ArrayList<Emp> empList = new ArrayList<Emp>();
        FileInputStream in = null;
        Workbook workbook = null;

        //判断后缀
        String suffix = filePath.substring(filePath.lastIndexOf(".")+1);

        try {
            //通过输入流创建工作簿对象
            in = new FileInputStream(filePath);
            //03
            if ("xls".equals(suffix)){
                System.out.println("后缀为xls");
                workbook = new HSSFWorkbook(in);
            }else{
                //07
                System.out.println("后缀为xlsx");
                workbook = new XSSFWorkbook(in);
            }

            Sheet sheet = workbook.getSheet(sheetName);
            int rowCount = sheet.getPhysicalNumberOfRows();

            //第一行为标题头，所以i从1开始读取数据
            for (int i=1;i<rowCount;i++){
                Row row = sheet.getRow(i);
                Emp emp = new Emp();
                emp.setEmpNo((int)row.getCell(0).getNumericCellValue());
                emp.seteName(row.getCell(1).getStringCellValue());
                emp.setJob(row.getCell(2).getStringCellValue());
                emp.setMgr((int)row.getCell(3).getNumericCellValue());
                System.out.println(row.getCell(4).getCellType());
                emp.setDate(new SimpleDateFormat("yyyy/MM/dd").parse(row.getCell(4).getStringCellValue()));
                emp.setSal(row.getCell(5).getNumericCellValue());
                emp.setComm(row.getCell(6).getNumericCellValue());
                emp.setDeptNO((int)row.getCell(7).getNumericCellValue());
                empList.add(emp);
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("读取excel文件数据失败！！");
        }
        return empList;
    }
    @Test
    public void test(){
        POI poi = new POI();
        String path = "E:\\Java\\代码\\poi-study\\src\\excel\\员工表.xls";
        //数据库->Excel
        Connection conn = JDBCUtil.getConnection();
        //ArrayList<Emp> arrayList = GetEmp.getEmpFromDataBase(conn);
        //poi.writeEmpToExcel(arrayList,conn,"员工表",path);

        //Excel->数据库
        PutEmp.putEmpToDataBase(poi.readEmpFromExcel(path,"员工表"),conn);

        //关闭
        JDBCUtil.closeResource(conn,null,null);
    }
}

创作不易，请勿白嫖，谢谢！！！