Exporting Data from MySQL to Excel using Java

Exporting Data from MySQL to Excel using Java

Exporting data from mysql to excel using java

In a previous post we discussed importing an Excel sheet into MySQL database. Today the reverse is the case, we will focus on Exporting data from mysql to excel using java.

To be successful in Exporting Data from MySQL to Excel using Java, you will first of all get the data from the database into a resultset object, then use the data in the resultSet object to export to an Excel (xlsx) file. The snippet for the Exportation is as follows;


XSSFWorkbook xlsxWorkbook = new XSSFWorkbook();
XSSFSheet xlsSheet = xlsxWorkbook.createSheet();
short rowIndex = 1;

try {
// Get the list of column names and store them as the first
// row of the spreadsheet.
ResultSetMetaData colInfo = rs.getMetaData();
ArrayList<String> colNames = new ArrayList<>();
XSSFRow titleRow = xlsSheet.createRow(rowIndex++);

for (int i = 1; i <= colInfo.getColumnCount(); i++) {
colNames.add(colInfo.getColumnName(i));
titleRow.createCell((short) (i-1)).setCellValue(
new XSSFRichTextString(colInfo.getColumnName(i)));
xlsSheet.setColumnWidth((short) (i-1), (short) 4000);
}

// Save all the data from the database table rows
while (rs.next()) {
XSSFRow dataRow = xlsSheet.createRow(rowIndex++);
short colIndex = 0;
for (String colName : colNames) {
dataRow.createCell(colIndex++).setCellValue(
new XSSFRichTextString(rs.getString(colName)));
}
}
// Write to disk
xlsxWorkbook.write(new FileOutputStream(“data.xlsx”));
Message.informationMessage(this, “Data Exported Successfully”);
}
catch(SQLException | IOException e) {
e.printStackTrace();
System.exit(1);
}

Remember to add the required jars to your classpath. Down load Apache POI 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;

Thank you for reading this post, if you have any comments, please post below