excel 数据 jdbc 导入到数据库

在项目开发中我们经常遇到 excel 数据导入系统的需求，如下代码 通过 jdbc 方式导入excel 数据到数据库：

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Properties;

import org.apache.poi.ss.usermodel.Cell;
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;

public class readFile {
  public static void readFile(File file) {
    Properties props = null;
    Connection con = null;
    PreparedStatement ps = null;
    String url = "";
    String user = "";
    String password = "";
    try {
      try {
        String filepath = readFile.class.getClassLoader().getResource("jdbc.properties").getFile();
        InputStream in;
        String websiteURL = filepath;
        if (!System.getProperties().getProperty("os.name").equals("Linux")) {
          websiteURL = (filepath.replace("%20", " ")).replaceFirst("/", "");
        }
        try {
          in = new FileInputStream(websiteURL);
          props = new Properties();
          props.load(in);
        } catch (Exception e) {
          e.printStackTrace();
        }
        String driverClassName = props.getProperty("jdbc.driverClassName");
        url = props.getProperty("jdbc.url.preference");
        user = props.getProperty("jdbc.username");
        password = props.getProperty("jdbc.password");
        Class.forName(driverClassName);
      } catch (ClassNotFoundException e) {
        throw new ExceptionInInitializerError(e);
      }
      con = DriverManager.getConnection(url, user, password);
      InputStream is = new FileInputStream(file);
      XSSFWorkbook wb = new XSSFWorkbook(is);
      XSSFCell cell = null;
      XSSFSheet st = wb.getSheetAt(0);
            //开始读取行 1
      for (int rowIndex = 0; rowIndex <= st.getLastRowNum(); rowIndex++) {
        String sql = "insert into Table_Name values(?,?,?,?,?)";
        ps = con.prepareStatement(sql);
        System.out.println(rowIndex);
        XSSFRow row = st.getRow(rowIndex);
                // 列数 20 
        for (int i = 0; i < 20; i++) {
          cell = row.getCell(i);
          System.out.println(i + "--->" + cell);
          if (cell == null) {
            ps.setString(i + 1, "");
          } else {
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
            ps.setDouble(i + 1, cell.getNumericCellValue());
            // cell.setCellType(Cell.CELL_TYPE_STRING);
            // ps.setString(i + 1, cell.getStringCellValue());
          }

        }
        ps.executeUpdate();
        ps.close();
      }
      try {
      } catch (Exception e) {
        e.printStackTrace();
      }

    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      if (ps != null) {
        try {
          ps.close();
        } catch (Exception e2) {
        }
      }
      if (con != null) {
        try {
          con.close();
        } catch (Exception e2) {
        }
      }
    }
  }

  public static void main(String[] args) {
    readFile(new File("F:\\待导入数据1.xlsx"));
  }
}