Using Apache POI to generate Excel spreadsheets from database queries

Back in the 90’s when I first started working with computers professionally, I remember finding software for $49.95, and often much more, to do on Microsoft Windows what is now free.

Apache POI (“Poor Obfuscation Implementation” 🙂 ) is a perfect example of this. Always take the opportunity to send $20 via PayPal if given the opportunity. Even better, volunteer to either write code or document the software.

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

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

import javax.mail.*;
import javax.mail.internet.*;
import javax.mail.internet.MimeMessage.*;
import javax.activation.*;

public class dumpQueryAsExcel {
  public static void main(String[] args) {
    dumpQueryAsExcel dqae = new dumpQueryAsExcel();
  }

  public dumpQueryAsExcel() {
    try {

      SimpleDateFormat sdf = new SimpleDateFormat("yyyy_MM_dd");
      Calendar c = Calendar.getInstance();
      c.setTime(new java.util.Date());
      c.add(Calendar.DATE, -1);
      String output = sdf.format(c.getTime());

      Class.forName("oracle.jdbc.driver.OracleDriver");
      Connection conn = DriverManager.getConnection("jdbc:oracle:thin:user/password@database_host:1521/service_name/my-service.my_domain");
      ResultSet rst = conn.createStatement().executeQuery("select col1,col2,col3 from my_table");
      XSSFWorkbook workbook = new XSSFWorkbook();
      XSSFSheet sheet = workbook.createSheet(output);
      XSSFCellStyle my_style = workbook.createCellStyle();
      XSSFFont my_font=workbook.createFont();
      my_font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
      my_style.setFont(my_font);
      Map data = new TreeMap();
      int rownum = 0;
      int cellnum = 0;
      ResultSetMetaData rsmd = rst.getMetaData();
      Row row = sheet.createRow(rownum++);
      for (int col = 1; col <= rsmd.getColumnCount(); col++) {
        Cell cell = row.createCell(cellnum++);
        cell.setCellValue((String)rsmd.getColumnName(col));
        cell.setCellStyle(my_style);
      }
      while (rst.next()) {
        row = sheet.createRow(rownum++);
        cellnum = 0;
        for (int col = 1; col <= rsmd.getColumnCount(); col++) {
          Cell cell = row.createCell(cellnum++);
          if (rsmd.getColumnTypeName(col).equals("VARCHAR2"))
            cell.setCellValue(rst.getString(col));
          else
            cell.setCellValue(rst.getDouble(col));
        }
      }

      try {
        FileOutputStream out = new FileOutputStream(new File(output + "_fandango_tickets.xlsx"));
        workbook.write(out);
        out.close();
        emailExcelFile(output);
      }
      catch (Exception e) {
        e.printStackTrace();
      }

    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }

  private void emailExcelFile(String subject) {
    try {
      String from = "[email protected]";
      String[] to = {"[email protected]","[email protected]"};
      String[] cc = {"[email protected]"};
      String host = "your_smtp_host";

      Properties props = new Properties();

      props.put("mail.smtp.host", host);
      //props.put("mail.debug", "true");

      Session session = Session.getInstance(props);

      MimeMessage message = new MimeMessage(session);
      message.setFrom(new InternetAddress("[email protected]"));
      InternetAddress[] addressTo = new InternetAddress[to.length];
      for (int i = 0; i < to.length; i++) {
        message.addRecipient(Message.RecipientType.TO, new InternetAddress(to[i]));
      }
      for (int i = 0; i < cc.length; i++) {
        message.addRecipient(Message.RecipientType.CC, new InternetAddress(cc[i]));
      }
      message.setSubject("Orders to receive Fandango tickets for " + subject);
      MimeBodyPart messageBodyPart = new MimeBodyPart();
      messageBodyPart.setText("Attached please find the list of orders for yesterday that qualify for the Fandango ticket promotion.");
      Multipart multipart = new MimeMultipart();
      multipart.addBodyPart(messageBodyPart);
      messageBodyPart = new MimeBodyPart();
      DataSource source = new FileDataSource("/tmp/orders_to_receive_fandango_tickets.xlsx");
      messageBodyPart.setDataHandler(new DataHandler(source));
      messageBodyPart.setFileName(subject + "_fandango_tickets.xlsx");
      multipart.addBodyPart(messageBodyPart);
      message.setContent(multipart);
      Transport.send(message);
    }
    catch (Exception e2) {
      e2.printStackTrace();
    }
  }
}