Java code example to export data from database to Excel file

This tutorial helps you write Java code to export data from a table in database to an Excel file – a very common task of a software program. To read data from database, we use JDBC with appropriate JDBC driver (MySQL is used in this tutorial). And to generate Excel file, we use Apache POI library.

Suppose that we have a table with the following structure:

This table contains some data like this:

I will guide you how to code simple program that exports data from this table to Excel 2007+ format (XSLX), and an advanced program that can export data from any table.

First, make sure that you specify the dependencies for MySQL JDBC driver and Apache POI API for Excel in Maven’s pom.xml file:

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.1.46</version>
	<scope>runtime</scope>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>4.1.0</version>
</dependency>

Next, let’s see the code examples.

1. Simple Java code example to export data from database to Excel file

The following code is for a simple Java program that connects to a MySQL database, reads all rows from the Review table and writes the data to an Excel file:

package net.codejava;

import java.io.*;
import java.sql.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

/**
 * A simple Java program that exports data from database to Excel file.
 * @author Nam Ha Minh
 * (C) Copyright codejava.net
 */
public class SimpleDb2ExcelExporter {

	public static void main(String[] args) {
		new SimpleDb2ExcelExporter().export();
	}
	
	public void export() {
		String jdbcURL = "jdbc:mysql://localhost:3306/sales";
		String username = "root";
		String password = "password";

		String excelFilePath = "Reviews-export.xlsx";

		try (Connection connection = DriverManager.getConnection(jdbcURL, username, password)) {
			String sql = "SELECT * FROM review";

			Statement statement = connection.createStatement();

			ResultSet result = statement.executeQuery(sql);

			XSSFWorkbook workbook = new XSSFWorkbook();
			XSSFSheet sheet = workbook.createSheet("Reviews");

			writeHeaderLine(sheet);

			writeDataLines(result, workbook, sheet);

			FileOutputStream outputStream = new FileOutputStream(excelFilePath);
			workbook.write(outputStream);
			workbook.close();

			statement.close();

		} catch (SQLException e) {
			System.out.println("Datababse error:");
			e.printStackTrace();
		} catch (IOException e) {
			System.out.println("File IO error:");
			e.printStackTrace();
		}
	}

	private void writeHeaderLine(XSSFSheet sheet) {

		Row headerRow = sheet.createRow(0);

		Cell headerCell = headerRow.createCell(0);
		headerCell.setCellValue("Course Name");

		headerCell = headerRow.createCell(1);
		headerCell.setCellValue("Student Name");

		headerCell = headerRow.createCell(2);
		headerCell.setCellValue("Timestamp");

		headerCell = headerRow.createCell(3);
		headerCell.setCellValue("Rating");

		headerCell = headerRow.createCell(4);
		headerCell.setCellValue("Comment");
	}

	private void writeDataLines(ResultSet result, XSSFWorkbook workbook, 
			XSSFSheet sheet) throws SQLException {
		int rowCount = 1;

		while (result.next()) {
			String courseName = result.getString("course_name");
			String studentName = result.getString("student_name");
			float rating = result.getFloat("rating");
			Timestamp timestamp = result.getTimestamp("timestamp");
			String comment = result.getString("comment");

			Row row = sheet.createRow(rowCount++);

			int columnCount = 0;
			Cell cell = row.createCell(columnCount++);
			cell.setCellValue(courseName);

			cell = row.createCell(columnCount++);
			cell.setCellValue(studentName);

			cell = row.createCell(columnCount++);

			CellStyle cellStyle = workbook.createCellStyle();
			CreationHelper creationHelper = workbook.getCreationHelper();
			cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
			cell.setCellStyle(cellStyle);

			cell.setCellValue(timestamp);

			cell = row.createCell(columnCount++);
			cell.setCellValue(rating);

			cell = row.createCell(columnCount);
			cell.setCellValue(comment);

		}
	}

}

Note that the writeHeaderLine() method writes the column names of the table to the first line in the Excel file. The column names are known beforehand and fixed. The first column (ID) is omitted.

The writeDataLines() method iterates over all rows in the result set returned from the database, and writes data to the Excel file. Note that there’s a datetime value so a cell style is created to format value as datetime.

Run this program, you will see the Reviews-export.xlsx file is generated in the same directory of the program. Open this file by Microsoft Excel application and you will see:

2. Advanced Java code example to export data from database to Excel file

Let’s see the code of a more advanced program that can export data from any table in the database to Excel file. Following is the full code:

package net.codejava;

import java.io.*;
import java.sql.*;
import java.text.*;
import java.util.Date;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

/**
 * An advanced Java program that exports data from any table to Excel file.
 * @author Nam Ha Minh
 * (C) Copyright codejava.net
 */
public class AdvancedDb2ExcelExporter {

	public static void main(String[] args) {
		AdvancedDb2ExcelExporter exporter = new AdvancedDb2ExcelExporter();
		exporter.export("Review");
		exporter.export("Product");
	}

	private String getFileName(String baseName) {
		DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss");
		String dateTimeInfo = dateFormat.format(new Date());
		return baseName.concat(String.format("_%s.xlsx", dateTimeInfo));
	}

	public void export(String table) {
		String jdbcURL = "jdbc:mysql://localhost:3306/sales";
		String username = "root";
		String password = "password";

		String excelFilePath = getFileName(table.concat("_Export"));

		try (Connection connection = DriverManager.getConnection(jdbcURL, username, password)) {
			String sql = "SELECT * FROM ".concat(table);

			Statement statement = connection.createStatement();

			ResultSet result = statement.executeQuery(sql);

			XSSFWorkbook workbook = new XSSFWorkbook();
			XSSFSheet sheet = workbook.createSheet(table);

			writeHeaderLine(result, sheet);

			writeDataLines(result, workbook, sheet);

			FileOutputStream outputStream = new FileOutputStream(excelFilePath);
			workbook.write(outputStream);
			workbook.close();

			statement.close();

		} catch (SQLException e) {
			System.out.println("Datababse error:");
			e.printStackTrace();
		} catch (IOException e) {
			System.out.println("File IO error:");
			e.printStackTrace();
		}
	}

	private void writeHeaderLine(ResultSet result, XSSFSheet sheet) throws SQLException {
		// write header line containing column names
		ResultSetMetaData metaData = result.getMetaData();
		int numberOfColumns = metaData.getColumnCount();

		Row headerRow = sheet.createRow(0);

		// exclude the first column which is the ID field
		for (int i = 2; i <= numberOfColumns; i++) {
			String columnName = metaData.getColumnName(i);
			Cell headerCell = headerRow.createCell(i - 2);
			headerCell.setCellValue(columnName);
		}
	}

	private void writeDataLines(ResultSet result, XSSFWorkbook workbook, XSSFSheet sheet) 
			throws SQLException {
		ResultSetMetaData metaData = result.getMetaData();
		int numberOfColumns = metaData.getColumnCount();

		int rowCount = 1;

		while (result.next()) {
			Row row = sheet.createRow(rowCount++);

			for (int i = 2; i <= numberOfColumns; i++) {
				Object valueObject = result.getObject(i);

				Cell cell = row.createCell(i - 2);

				if (valueObject instanceof Boolean) 
					cell.setCellValue((Boolean) valueObject);
				else if (valueObject instanceof Double)
					cell.setCellValue((double) valueObject);
				else if (valueObject instanceof Float)
					cell.setCellValue((float) valueObject);
				else if (valueObject instanceof Date) {
					cell.setCellValue((Date) valueObject);
					formatDateCell(workbook, cell);
				} else cell.setCellValue((String) valueObject); 

			}

		}
	}

	private void formatDateCell(XSSFWorkbook workbook, Cell cell) {
		CellStyle cellStyle = workbook.createCellStyle();
		CreationHelper creationHelper = workbook.getCreationHelper();
		cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
		cell.setCellStyle(cellStyle);
	}
}

In this program, the name of the Excel file is generated based on table name followed by the current datetime – as you can see in the getFileName() method. The writeHeaderLine() and writeDataLines() methods use ResultSetMetaData to read column names so this program can work with any table.

Note that this program excludes the first column of the table, which is supposed to be the ID column.

And you can specify the table name when running this program, for example:

AdvancedDb2ExcelExporter exporter = new AdvancedDb2ExcelExporter();
exporter.export("Review");
exporter.export("Product");

That’s a couple of example programs that show you how to export data from database to Excel file. To learn more about writing Excel file, read this tutorial: How to Write Excel Files in Java using Apache POI

Other Java Coding Tutorials:

• Java code example to import data from Excel to database
• How to display images from database in JSP page with Java Servlet
• How to implement remember password (remember me) for Java web application
• How to code login and logout with Java Servlet, JSP and MySQL
• How to Code Hit Counter for Java web application
• 10 Common Mistakes Every Beginner Java Programmer Makes
• 10 Java Core Best Practices Every Java Programmer Should Know
• How to become a good programmer? 13 tasks you should practice now
• How to calculate MD5 and SHA hash values in Java
• Java File Encryption and Decryption Example

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.