使用poi将excel数据插入到数据库中

1、实现逻辑：

• 第一步我们需要获取excel的数据，然后把每行的数据用一个对象存起来，然后都添加到list中。（读取excel流程是首先读取工作簿，然后读取shell，然后服务行row，然后读取column列，在一个个set到对象中。）
• 第二步就是链接数据库编写批量新增的方法，传入list。
• 第三步成功失败逻辑判断，成功返回成功条数，失败返回失败信息即可。

2、maven依赖包

<dependency>
    <groupId>net.sourceforge.jexcelapi</groupId>
    <artifactId>jxl</artifactId>
    <version>2.6.12</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-collections4 -->
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-collections4</artifactId>
    <version>4.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17-beta1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.17-beta1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.xmlbeans/xmlbeans -->
<dependency>
    <groupId>org.apache.xmlbeans</groupId>
    <artifactId>xmlbeans</artifactId>
    <version>2.6.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17-beta1</version>
</dependency>
 

3、实现代码

2.1 TestExcelToDb 类代码

package com.it.intelligent.list.dto;

import java.sql.*;
import java.util.List;

public class TestExcelToDb {

    String url ="jdbc:postgresql://10.43.225.69:5432/it?currentSchema=it";
    String user="11";
    String pwd="11";
    Connection conn=null;
    ResultSet rs=null;

    public void dataBase() {
        try {
            Class.forName("org.postgresql.Driver");
            conn= DriverManager.getConnection(url, user, pwd);
        } catch (ClassNotFoundException e) {
            System.out.println("装载JDBC 驱动程序失败");
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("无法连接数据库");
            e.printStackTrace();
        }
    }

    //增删修改

    public int addU(String sql,String str[]) {
        int a=0;
        try {
            PreparedStatement pst=conn.prepareStatement(sql);
            if (str!=null) {
                for (int i = 0; i < str.length; i++) {
                    pst.setString(i+1, str[i]);
                }
            }
            a=pst.executeUpdate();
        } catch (Exception e) {
            // TODO: handle exception
        }

        return a;

    }
    public static void main(String[] args) {
        //得到表格中所有的数据
        List<TempData> listExcel=StuService.getAllByExcel("D:/l4/百货部分数据.xlsx");
        TestExcelToDb testExcelToDb =new TestExcelToDb();
        testExcelToDb.dataBase();
        System.out.println(listExcel.size());
        int sum=0;
        for (TempData tempData : listExcel) {
                String sql="insert into temp_data(l4_gds_grp_cd,l4_gds_grp_nm,gds_cd,vendor_cd,gds_nm)values(?,?,?,?,?)";
                String[] str=new String[] {tempData.getL4GdsGrpCd(),tempData.getL4GdsGrpNm(),tempData.getGdsCd(),tempData.getVendorCd(),tempData.getGdsNm()+""};
                 testExcelToDb.addU(sql, str);
                 sum++;
        }
        System.out.println(sum);
    }
}

2.2 StuService 代码

package com.it.intelligent.list.dto;
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 java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class StuService {
    //查询指定目录中电子表格中所有的数据
    public static List<TempData> getAllByExcel(String file) {
        Workbook wb = null;
        Sheet sheet = null;
        Row row = null;
        List<TempData> list = null;
        wb = readExcel(file);
        if (wb != null) {
            //用来存放表中数据
            list =new ArrayList<>();
            //获取第一个sheet
            sheet = wb.getSheetAt(0);
            //获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();
            //获取第一行
            row = sheet.getRow(0);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            for (int i = 1; i < rownum; i++) {
                row = sheet.getRow(i);
                if (row != null) {
                    for (int j = 0; j < colnum; j++) {
                        Cell cell = row.getCell(j);
                         String  cellValue = cell.getRichStringCellValue().getString();
                        String[] strings = cellValue.split("#NLP#");
                        System.out.println(strings.length);
                        TempData tempData = new TempData();
                        tempData.setL4GdsGrpCd(strings[0]);
                        tempData.setL4GdsGrpNm(strings[1]);
                        tempData.setGdsCd(strings[2]);
                        tempData.setVendorCd(strings[4]);
                        tempData.setGdsNm(strings[3]);
                        list.add(tempData);
                    }
                } else {
                    break;
                }
            }
        }
        return list;
    }

    //读取excel
    public static Workbook readExcel(String filePath) {
        Workbook wb = null;
        if (filePath == null) {
            return null;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if (".xls".equals(extString)) {
                return wb = new HSSFWorkbook(is);
            } else if (".xlsx".equals(extString)) {
                return wb = new XSSFWorkbook(is);
            } else {
                return wb = null;
            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }
}

2.3 TempData 代码

package com.it.intelligent.list.dto;

public class TempData {
    private String l4GdsGrpCd;
    private String l4GdsGrpNm;
    private String gdsCd;
    private String vendorCd;
    private String categoryCd;
    private String categoryNm;
    private String gdsNm;
    private String listWords;
    private String updateTime;
    private String etlTime;

    public String getL4GdsGrpCd() {
        return l4GdsGrpCd;
    }

    public void setL4GdsGrpCd(String l4GdsGrpCd) {
        this.l4GdsGrpCd = l4GdsGrpCd;
    }

    public String getL4GdsGrpNm() {
        return l4GdsGrpNm;
    }

    public void setL4GdsGrpNm(String l4GdsGrpNm) {
        this.l4GdsGrpNm = l4GdsGrpNm;
    }

    public String getGdsCd() {
        return gdsCd;
    }

    public void setGdsCd(String gdsCd) {
        this.gdsCd = gdsCd;
    }

    public String getVendorCd() {
        return vendorCd;
    }

    public void setVendorCd(String vendorCd) {
        this.vendorCd = vendorCd;
    }

    public String getCategoryCd() {
        return categoryCd;
    }

    public void setCategoryCd(String categoryCd) {
        this.categoryCd = categoryCd;
    }

    public String getCategoryNm() {
        return categoryNm;
    }

    public void setCategoryNm(String categoryNm) {
        this.categoryNm = categoryNm;
    }

    public String getGdsNm() {
        return gdsNm;
    }

    public void setGdsNm(String gdsNm) {
        this.gdsNm = gdsNm;
    }

    public String getListWords() {
        return listWords;
    }

    public void setListWords(String listWords) {
        this.listWords = listWords;
    }

    public String getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(String updateTime) {
        this.updateTime = updateTime;
    }

    public String getEtlTime() {
        return etlTime;
    }

    public void setEtlTime(String etlTime) {
        this.etlTime = etlTime;
    }
}

4、实现的结果