利用POI技术把excel数据导入mysql

最新推荐文章于 2022-06-18 16:59:09 发布

Charles0605

最新推荐文章于 2022-06-18 16:59:09 发布

阅读量1.7k

点赞数

分类专栏： POI 文章标签：数据 poi 数据库 excel

本文链接：https://blog.csdn.net/yeah898/article/details/11487829

版权

POI 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

package com.page;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConnection {
 /*
  * 功能：编写一个静态方法用于与数据库建立连接
  * 输入参数：无
  * 返回值：数据库连接对象
  * */
 public static Connection getConnection(){
  //定义一个连接对象
  Connection conn=null;
  //定义连接数据库的URL资源
  String url="jdbc:mysql://localhost:3306/blog";
  //定义连接数据库的用户名称与密码
  String username="root";
  String password="root";
  //加载数据库连接驱动
  String className="com.mysql.jdbc.Driver";
  try {
   Class.forName(className);
  } catch (ClassNotFoundException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  //获取数据库的连接对象
  try {
   conn=DriverManager.getConnection(url,username,password);
    } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  //返回连接对象
  return conn;
 } 
}

=============以上为数据库连接

package com.page;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelToDB {

 private Connection con;
 private DBConnection db;
 private PreparedStatement pst;
 private String filePath = "d:\\test.xls";

 public boolean insertDB() {

  boolean flag = true;
  db = new DBConnection();
  con = db.getConnection();
  try {
   // 文件流指向excel文件
   FileInputStream fin = new FileInputStream(filePath);
   HSSFWorkbook workbook = new HSSFWorkbook(fin);// 创建工作薄
   HSSFSheet sheet = workbook.getSheetAt(0);// 得到工作表
   HSSFRow row = null;// 对应excel的行
   HSSFCell cell = null;
   HSSFCell cell2 = null;
   HSSFCell cell3 = null;
   HSSFCell cell4 = null;
   HSSFCell cell5 = null;

   int totalRow = sheet.getLastRowNum();// 得到excel的总记录条数
   System.out.println(totalRow);
   // 以下的字段一一对应数据库表的字段

   

   for (int i = 1; i <= totalRow; i++) {
    row = sheet.getRow(i);
    cell = row.getCell(0);
    double ID = cell.getNumericCellValue();
    System.out.println(ID);
    cell2 = row.getCell(1);
    String BM = cell2.getRichStringCellValue().toString();

    cell3 = row.getCell(2);
    String AQ = cell3.getRichStringCellValue().toString();

    cell4 = row.getCell(3);

    String CQ = cell4.getRichStringCellValue().toString();

    cell5 = row.getCell(4);
    String DQ = cell5.getRichStringCellValue().toString();
    String sql = "INSERT INTO text1(ID,BM,AQ,CQ,DQ) VALUES (?,?,?,?,?)"; // "
    pst = con.prepareStatement(sql);
    pst.setDouble(1, ID);
    pst.setString(2, BM);
    pst.setString(3, AQ);
    pst.setString(4, CQ);
    pst.setString(5, DQ);

    pst.execute();
   }

  } catch (FileNotFoundException e) {
   flag = false;
   e.printStackTrace();
  } catch (IOException ex) {
   flag = false;
   ex.printStackTrace();
  } catch (SQLException exx) {
   flag = false;
   exx.printStackTrace();
  } finally {
   try {
    pst.close();
    con.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
  return flag;

 }

 public static void main(String args[]) {
  ExcelToDB toDB = new ExcelToDB();
  toDB.insertDB();
 }
}