Hi readers,

If you want to write your MySQL table data to excel sheet. Then by using Apache's POI libraries and MySQL connector you can actually do this. Have a look on the below simple example. It May give you an idea how to proceed.

In the below example I have created a table named as users inside usersdb database.

Java Program to create excel file for this table

import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

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;

public class ExcelDatabaseExport {
	public static void main(String[] args) {

		try {

			Class.forName("com.mysql.jdbc.Driver");

			Connection con = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/usersdb", "root", "");

			Statement stmt = con.createStatement();

			ResultSet resultSet = stmt.executeQuery("select * from users");

			XSSFWorkbook workbook = new XSSFWorkbook();

			XSSFSheet spreadsheet = workbook.createSheet("employedb");
			XSSFRow row = spreadsheet.createRow(1);
			XSSFCell cell;
			cell = row.createCell(0);
			cell.setCellValue("EMP ID");
			cell = row.createCell(1);
			cell.setCellValue("EMP NAME");
			cell = row.createCell(2);
			cell.setCellValue("DEG");
			cell = row.createCell(3);
			cell.setCellValue("Email");

			int i = 2;

			while (resultSet.next()) {
				row = spreadsheet.createRow(i);
				cell = row.createCell(0);
				cell.setCellValue(resultSet.getInt("user_id"));
				cell = row.createCell(1);
				cell.setCellValue(resultSet.getString("name"));
				cell = row.createCell(2);
				cell.setCellValue(resultSet.getString("designation"));
				cell = row.createCell(3);
				cell.setCellValue(resultSet.getString("email"));
				i++;
			}

			FileOutputStream out = new FileOutputStream(new File(
					"exceldatabase.xlsx"));
			workbook.write(out);
			out.close();
			System.out.println("File Successfully created");
			con.close();
		} catch (Exception e) {
			System.out.println(e);
		}

	}
}

After successfully running the above program an excel file with name exceldatabase.xlsx will be created in your current directory.

Here is a snapshot of the excel file created by the program.

Thanks for reading.