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.
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.
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");
}