Excel files can import into MySQL database using some library files. Actually not the excel file but excel file content by reading the excel and import into MySQL database. This article will help you to do so. Let’s start this with dividing this into two part. Part one will do import Excel files into a jTable first. Part two will do a bulk upload the data contains in jTable. Let’s start.
What kinds of files can be imported?
Here we are dealing with excel files only. (.xls, .xlsx, .xlsm files )
Step 01 : Import Excel File into jTable
To import data from excel to java jTable you need Apache POI(Apache POI – the Java API for Microsoft Documents) library added to your project libraries. Download and extract the compressed files. This application selects excel file from the computer and displays it on java jTable.
Add POI Libraries
POI Libraries which we downloaded (.jar files ) needs to add on the Library section of your project. Right click on Library and “Add Jar/Folder” as below.
Add POI Library .jar files
We are using one excel file to import which contains 5 columns, if your excel file contains more columns or less change the code accordingly.
Sample Excel Files
Click import excel button, then choose excel file you want to import, then file will be imported and displayed on the JTable. Below code is for a function which help to import Excel file, once you added the Library files copy and paste below function into your project and call it whenever required or button click of an “import Excel” button.
public void importExcelToJtableJava() {
DefaultTableModel model= (DefaultTableModel) boq_jtable.getModel();
File excelFile;
FileInputStream excelFIS = null;
BufferedInputStream excelBIS = null;
XSSFWorkbook excelImportToJTable = null;
String defaultCurrentDirectoryPath = "C:\\Users\\Authentic\\Desktop";
JFileChooser excelFileChooser = new JFileChooser(defaultCurrentDirectoryPath);
excelFileChooser.setDialogTitle("Select Excel File");
FileNameExtensionFilter fnef = new FileNameExtensionFilter("EXCEL FILES", "xls", "xlsx", "xlsm");
excelFileChooser.setFileFilter(fnef);
int excelChooser = excelFileChooser.showOpenDialog(null);
if (excelChooser == JFileChooser.APPROVE_OPTION) {
try {
excelFile = excelFileChooser.getSelectedFile();
jExcelFilePath.setText(excelFile.toString());
excelFIS = new FileInputStream(excelFile);
excelBIS = new BufferedInputStream(excelFIS);
excelImportToJTable = new XSSFWorkbook(excelBIS);
XSSFSheet excelSheet = excelImportToJTable.getSheetAt(0);
for (int row = 0; row < excelSheet.getLastRowNum(); row++) {
XSSFRow excelRow = excelSheet.getRow(row);
XSSFCell excelLineNum = excelRow.getCell(0);
XSSFCell excelItemName = excelRow.getCell(1);
XSSFCell excelDescription = excelRow.getCell(2);
XSSFCell excelServiceDuration = excelRow.getCell(3);
XSSFCell excelQuantity = excelRow.getCell(4);
model.addRow(new Object[]{excelLineNum, excelItemName, excelDescription, excelServiceDuration,excelQuantity});
}
JOptionPane.showMessageDialog(null, "Imported Successfully !!.....");
} catch (IOException iOException) {
JOptionPane.showMessageDialog(null, iOException.getMessage());
} finally {
try {
if (excelFIS != null) {
excelFIS.close();
}
if (excelBIS != null) {
excelBIS.close();
}
if (excelImportToJTable != null) {
excelImportToJTable.close();
}
} catch (IOException iOException) {
JOptionPane.showMessageDialog(null, iOException.getMessage());
}
}
}
} Step 02: Upload jTable data into MySQL Database
Now, jTable is ready with data and it is ready to bulk insert to MySQL database. Let’s use “executeBatch()” function. Read jTable raws one by one using for loop and execute a batch upload.
Remember that Database must be connected to MySQL before doing this step. If you don’t know how to do it, please check this link. Below code is function to execute batch update when clicking a submit or save button.
Remember that Database must be connected to MySQL before doing this step. If you don’t know how to do it, please check this link. Below code is function to execute batch update when clicking a submit or save button.
private void jButtonSaveActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
DefaultTableModel bulkModel = (DefaultTableModel) boq_jtable.getModel();
Class.forName("com.mysql.jdbc.Driver");
Connection con=null;
con= (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/smartlead","root","");
bulkStmt = con.createStatement();
if(con==null)
JOptionPane.showMessageDialog(this, "Unable to Connect to server!!!");
for(int i = 0; i < bulkModel.getRowCount(); i++){
String tempClientId = bulkModel.getValueAt(i, 0).toString();
String tempLineNum = bulkModel.getValueAt(i, 1).toString();
String tempItemName= bulkModel.getValueAt(i, 2).toString();
String tempDescription = bulkModel.getValueAt(i, 3).toString();
String tempServiceDuration = bulkModel.getValueAt(i, 4).toString();
String tempQuantity = bulkModel.getValueAt(i, 5).toString();
bulkQuery="INSERT INTO `boq_network`( `client_id`, `line_number`, `item_name`, `description`, `service_duration`, `quantiry`) VALUES ('"+tempClientId+"','"+tempLineNum+"','"+tempItemName+"','"+tempDescription+"','"+tempServiceDuration+"','"+tempQuantity+"')";
bultStmt.addBatch(bulkQuery);
}
int[] rowsInserted = bultStmt.executeBatch();
System.out.println("Data Inserted");
System.out.println("rowsInserted Count = " + rowsInserted.length);
JOptionPane.showMessageDialog(null, "Saved");
}
Hope this article is useful & Thanks you.