Exporting a MySQL table to Excel XLS in Java

I decided to get things rolling on this blog with a short but useful example using JDBC and Apache POI to output a MySQL database table to an Excel spreadsheet. You’ll need two jars to get this code compiled and running: POI 3.0 and the MySQL java connector. Of course, you’ll also need a MySQL database… but that’s a whole other bag of worms.

I tried to keep this code as simple as possible. The MysqlToXls object is initialized with the database name, user name, and password. Its generateXls() function takes the name of the table to output and the filename to output to. Finally, a close() method closes the database connection… and that’s all there is to it! I included a main method that uses this object to connect to a database named test as user root with an empty password. It then dumps the person table as person.xls.

import java.io.*;
import java.sql.*;
import org.apache.poi.hssf.usermodel.*;
import java.util.*;

public class MysqlToXls {

  private Connection connection = null;

  public MysqlToXls(String database, String user, String password)
  	throws ClassNotFoundException, SQLException {

		// Create MySQL database connection
    Class.forName("com.mysql.jdbc.Driver");

    String url = "jdbc:mysql://localhost:3306/" + database;
    connection = DriverManager.getConnection(url, user, password);
  }

  public void generateXls(String tablename, String filename)
  	throws SQLException, FileNotFoundException, IOException {

    // Create new Excel workbook and sheet
    HSSFWorkbook xlsWorkbook = new HSSFWorkbook();
    HSSFSheet xlsSheet = xlsWorkbook.createSheet();
    short rowIndex = 0;

    // Execute SQL query
    PreparedStatement stmt =
    connection.prepareStatement("select * from " + tablename);
    ResultSet rs = stmt.executeQuery();

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

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

    // Save all the data from the database table rows
    while (rs.next()) {
      HSSFRow dataRow = xlsSheet.createRow(rowIndex++);
      short colIndex = 0;
      for (String colName : colNames) {
        dataRow.createCell(colIndex++).setCellValue(
          new HSSFRichTextString(rs.getString(colName)));
      }
    }

    // Write to disk
    xlsWorkbook.write(new FileOutputStream(filename));
  }

  // Close database connection
  public void close() throws SQLException {
    connection.close();
  }

  public static void main(String[] args) {
    try {
      MysqlToXls mysqlToXls = new MysqlToXls("test", "root", "");
      mysqlToXls.generateXls("person", "person.xls");
      mysqlToXls.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

So there you have it! Take your data out of a great storage mechanism and throw it into a crappy spreadsheet! This code could easily be ported to Postgres or Oracle with the proper JDBC connector.

You might also want to play around with setting the column type in POI. The HSSFCell.setCellValue() can take an int, a boolean, a Calendar object, a Date, or a double instead of just a string.

A few things you might want to watch out for: POI has an unholy obsession with using shorts. Also, JDBC refers to columns starting with #1 instead of the usual 0 index. Finally, I have no idea where these column width numbers come from… but 4000 looked good to me!