java - 如何使用java将excel数据（.xlsx）插入mysql数据库？

Question

是的，这听起来像是重复的。

我正在 Netbeans 上练习一点 Java，并尝试编写一个程序来将 .xlsx excel 文件导入 MySQL 数据库。重复的问题，是的，但是在互联网上拖网并没有带来太多收益。

package practice;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;

public class File2MySqlDB {
  public static void main(String[] args) throws Exception {
    try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = null;
        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/databasename", "root", "root");
        con.setAutoCommit(false);
        PreparedStatement pstm = null;
        FileInputStream input = new FileInputStream("C:\\Users\\user\\Desktop\\Student.xlsx");
        POIFSFileSystem fs = new POIFSFileSystem(input);
        Workbook workbook;
        workbook = WorkbookFactory.create(fs);
        Sheet sheet = workbook.getSheetAt(0);
        Row row;
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            row = (Row) sheet.getRow(i);
            String Name = row.getCell(0).getStringCellValue();
            String Enrolled = row.getCell(1).getStringCellValue();
            String Progress = row.getCell(2).getStringCellValue();

            String sql = "INSERT INTO Student (Name, Enrolled,Progress) VALUES('" + Name + "','" + Enrolled + "', '"+Progress+"')";
            pstm = (PreparedStatement) con.prepareStatement(sql);
            pstm.setString(1, Name);
            pstm.setString(2, Enrolled);
            pstm.setString(3, Progress);

            pstm.execute();
            System.out.println("Import rows " + i);
        }
        con.commit();
        pstm.close();
        con.close();
        input.close();
        System.out.println("Success import excel to mysql table");
    } catch (IOException e) {e.printStackTrace();}
  }
}

提示：我正在使用 apipoi-4.1.1.jar

我得到以下错误

Exception in thread "main" org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:130)
at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:117)
at org.apache.poi.poifs.filesystem.POIFSFileSystem.<init>(POIFSFileSystem.java:294)
at practice.File2MySqlDB.main(File2MySqlDB.java:20)

标签： java