Import Data from Excel Sheet into MySQL Database using Apache POI

Import Data from Excel Sheet into MySQL Database using Apache POI

Import Data from Excel Sheet into MySQL Database

Sometimes it is necessary to Import Data from Excel Sheet into MySQL Database using Apache POI, and this can be done by different API’s provided by different companies, two of which are jExcel and the Apache POI. The two API’s are the most commonly used in Software Engineering to import an Excel sheet into a database. But jExcel has a downside; it can only read data from Excel 95, 97, 2000, which makes it very limited. Apache is broader and has a larger support as it is managed by a known Company (Apache Foundation). The wonderful thing about the Apache POI is that it can read from Word, Powerpoint, Excel etc. documents. The only advantage jExcel has over Apache is its lightweight nature.

Let’s begin. To download Apache POI click here

After downloading, you should extract the zip folder (Learn how to Extract). You will also need to add the following jar files to project, the files can be found in the downloaded folder

poi-ooxml-3.10-FINAL-20140208.jar
poi-ooxml-schemas-3.10-FINAL-20140208.jar
poi-3.10-FINAL-20140208.jar
xmlbeans-2.3.0.jar
dom4j-1.6.1.jar

After adding the jar files to your project, insert the needed import statement into your class


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;

In the constructor of your class get the connection and perform your query. I have used prepared statements to do mine in the example because it has more security on sql injection


try {
connection = DriverManager.getConnection("URL", "Username", "Password");
importd = connection.prepareStatement("insert into table (a, b, c, d, e, f, i, j, k, l, m, n) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
}

catch(SQLException sqlException) {
sqlException.printStackTrace();
System.exit(1);
}

The number of column in your excel document should be taking note of by you


String directory = getExcelFile();

ArrayList <String> Values = new ArrayList<>();
try {
FileInputStream fileInputStream = new FileInputStream(directory);
XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
XSSFSheet worksheet = workbook.getSheetAt(0);
for(int i = 2; i < worksheet.getLastRowNum(); i++) { for(int j = 0; j < 12; j++) { XSSFRow row1 = worksheet.getRow(i); XSSFCell cellA1 = row1.getCell(j); try { cellA1.setCellType(1); Values.add(cellA1.getStringCellValue()); } catch(NullPointerException n) { JOptionPane.showDisplayMessage(this, “Import Complete”); return; } } for(int k = 0, l = 1; k < Values.size(); k++, l++) { try { importd.setString(l, Values.get(k)); } catch(SQLException sqlException) { sqlException.printStackTrace(); System.exit(1); } } try { importd.executeUpdate(); } catch(SQLException sqlException) { sqlException.printStackTrace(); System.exit(1); } Values.clear(); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); }

The code snippet above begins by getting the directory of the excel file, it call the method get directory that uses a JFileChooser to search for the file, filtering the data to only show Excel sheets,


private String getExcelFile() {
String directory = "";
final JFileChooser fileChooser = new JFileChooser();
fileChooser.setFileFilter(new FileFilter() {
@Override
public boolean accept(File f) {
if (f.isDirectory()) {
return true;
}
final String name = f.getName();
return name.endsWith(".xlsx");
}

@Override
public String getDescription() {
return “*.xlsx”;
}
});

int result = fileChooser.showOpenDialog(this);

if (result != JFileChooser.CANCEL_OPTION) {
File name = fileChooser.getSelectedFile();
directory = name.getAbsolutePath();
}
return directory;
}

then the code creates a generic Arraylist of type string as a local variable, it then assigns the file directory to the FileInputStream object, a workbook object is created assigning the object of FileInputStream to it. After which a worksheet is obtained using the method workbook.getSheet(0);

the above method gets the first sheet in the excel file

the system then uses the for loop to iterate through the rows and columns, converts each value to a String and assigns the values to the ArrayList earlier created.

The system iterates through the ArrayList and inserts each value into the database using the prepared statement method executeUpdate();

The system them clears the ArrayList and performs the same action for another row.

The process continues until all excel rows are inserted or the system encounters a null value

Thank you for reading my post, if you have any questions or complaints kindly post bellows, if you also have a Software Project you wish to outsource please go to our home page and fill the request for Demo form
Thanks