YES, it sounds like a duplicate.
I'm practicing a bit of Java on Netbeans and tried writing a program to import a .xlsx excel file into a MySQL database. The duplicate question, yes, but trawling the internet didn't yield much.
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();}
}
}
Hint: I am using api- poi-4.1.1.jar
I am getting below error
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)