Show Resultset as HTML table

There are times you simply want to show some result set as a table on the screen.
You do not need anything fancy, just show some tabular data on the screen.

In order to solve this issue I wrote a very small and lightweight Java class named: ResultSetToHtmlTable. Even through this class is doing a quite simple task it still have some nice qualities:

• It is very fast. It doesn't construct any string. It simply writes the table directly to a writer.
• It doesn't use any external libraries. All plain and simple Java.
• It is only a single class.
• It knows to format the data in each column. It supports the following formats:
• Integer - Show 1000 as 1,000
• Float - Show 0.5145235 as 0.51
• Currency - Show 5.2 as $5.2
• Percentage - Show 0.5 as 50%
• Date - Show 12/5/2014 as 5 Dec 2014
• Date and time - Show 12/5/2014 12:00:00 as 5 Dec 2014 12:00:00
• Time - Show only the time part of a date. For example: 13:22:15
• It allows to align the data to the left or right and add titles to columns.
• If no strict column information is supplied is knows to auto detect:
• Column types.
• Column titles - titles are named as column names returned in query.
• Column alignment - Numbers are aligned to the right.

The code of this class is quite simple and straightforward. Here is how it looks like:

package com.todacell.util.sql;

import java.io.IOException;
import java.io.Writer;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.text.DateFormat;
import java.text.Format;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Map;

/**
 * @author Bashan
 *         Date: 18/11/2014 15:07
 */
public class ResultSetToHtmlTable {

  public void writeTable(ResultSet rs, Writer writer, Column[] columns) throws IOException, SQLException {
    writer.write("<table>");
    // Write titles
    writer.write("\t<tr>\n");
    for (Column column : columns) {
      writer.write("\t\t<th>" + column.title + "</th>");
    }
    writer.write("\t</tr>\n");

    // Write data
    int cols = columns.length;
    while (rs.next()) {
      writer.write("\t<tr>\n");
      for (int i = 0; i < cols; i++) {
        ColumnType columnType = columns[i].columnType;
        String columnAlign = columnAlignToColumnCss.get(columns[i].columnAlign);
        writer.write("\t\t<td" + columnAlign + ">" + format(columnType, rs.getObject(i + 1)) + "</td>");
      }
      writer.write("\t</tr>");
    }

    writer.write("</table>");
  }

  private static Map<Integer, ColumnType> sqlTypeToColumnType = new HashMap<Integer, ColumnType>();
  private static Map<ColumnType, ColumnAlign> columnTypeTpColumnAlign = new HashMap<ColumnType, ColumnAlign>();
  private static Map<ColumnAlign, String> columnAlignToColumnCss = new HashMap<ColumnAlign, String>();

  static {
    // Map SQL types to column types

    // integer
    sqlTypeToColumnType.put(Types.INTEGER, ColumnType.INTEGER);
    sqlTypeToColumnType.put(Types.BIGINT, ColumnType.INTEGER);
    sqlTypeToColumnType.put(Types.SMALLINT, ColumnType.INTEGER);
    sqlTypeToColumnType.put(Types.TINYINT, ColumnType.INTEGER);
    sqlTypeToColumnType.put(Types.NUMERIC, ColumnType.INTEGER);

    // float
    sqlTypeToColumnType.put(Types.FLOAT, ColumnType.INTEGER);
    sqlTypeToColumnType.put(Types.DOUBLE, ColumnType.INTEGER);
    sqlTypeToColumnType.put(Types.DECIMAL, ColumnType.INTEGER);

    // date
    sqlTypeToColumnType.put(Types.TIME, ColumnType.DATE);
    sqlTypeToColumnType.put(Types.TIMESTAMP, ColumnType.DATE);

    // Map column type to column alignment
    columnTypeTpColumnAlign.put(ColumnType.INTEGER, ColumnAlign.RIGHT);
    columnTypeTpColumnAlign.put(ColumnType.FLOAT, ColumnAlign.RIGHT);
    columnTypeTpColumnAlign.put(ColumnType.CURRENCY, ColumnAlign.RIGHT);
    columnTypeTpColumnAlign.put(ColumnType.DATE, ColumnAlign.LEFT);
    columnTypeTpColumnAlign.put(ColumnType.STRING, ColumnAlign.LEFT);

    // Map column alignment to css style
    columnAlignToColumnCss.put(ColumnAlign.LEFT, "");
    columnAlignToColumnCss.put(ColumnAlign.RIGHT, " style=\"text-align:right\"");
    columnAlignToColumnCss.put(ColumnAlign.CENTER, " style=\"text-align:center\"");
  }

  public void writeTable(ResultSet rs, Writer writer) throws IOException, SQLException {
    ResultSetMetaData meta = rs.getMetaData();
    int cols = meta.getColumnCount();

    Column[] columns = new Column[meta.getColumnCount()];
    for (int i = 0; i < cols; i++) {
      ColumnType columnType = sqlTypeToColumnType.get(meta.getColumnType(i + 1));
      ColumnAlign columnAlign = columnTypeTpColumnAlign.get(columnType);
      columns[i] = new Column(
          columnType != null ? columnType : ColumnType.STRING,
          meta.getColumnName(i + 1),
          columnAlign != null ? columnAlign : ColumnAlign.LEFT);
    }

    writeTable(rs, writer, columns);
  }

  public enum ColumnType {
    STRING, INTEGER, FLOAT, DATE, DATE_TIME, TIME, CURRENCY, PERCENTAGE
  }

  public enum ColumnAlign {
    LEFT, RIGHT, CENTER
  }

  private static NumberFormat integerFormat = NumberFormat.getInstance();
  private static NumberFormat floatFormat = NumberFormat.getInstance();
  private static final NumberFormat percentFormat = NumberFormat.getPercentInstance();
  private static final NumberFormat currencyFormat = NumberFormat.getCurrencyInstance();
  private static final DateFormat dateFormat = new SimpleDateFormat("dd MMM yyyy");
  private static final DateFormat dateTimeFormat = new SimpleDateFormat("dd MMM yyyy hh:mm:ss");
  private static final DateFormat timeFormat = new SimpleDateFormat("hh:mm:ss");

  static {
    floatFormat.setMaximumFractionDigits(2);
    integerFormat.setMaximumFractionDigits(0);
  }

  public String format(ColumnType columnType, Object value) {
    String result;
    if (value != null) {
      if (columnType == ColumnType.STRING) {
        result = value.toString();
      } else {
        Format format;
        if (columnType == ColumnType.FLOAT) {
          format = floatFormat;
        } else if (columnType == ColumnType.CURRENCY) {
          format = currencyFormat;
        } else if (columnType == ColumnType.PERCENTAGE) {
          format = percentFormat;
        } else if (columnType == ColumnType.DATE) {
          format = dateFormat;
        } else if (columnType == ColumnType.DATE_TIME) {
          format = dateTimeFormat;
        } else if (columnType == ColumnType.TIME) {
          format = timeFormat;
        }else {
          format = integerFormat;
        }

        result = format.format(value);
      }
    } else {
      result = "N/A";
    }

    return result;
  }

  public static class Column {
    ColumnType columnType;
    String title;
    ColumnAlign columnAlign;

    public Column(ColumnType columnType, String title, ColumnAlign columnAlign) {
      this.columnType = columnType;
      this.title = title;
      this.columnAlign = columnAlign;
    }
  }
}

And here is a simple example of how to use this code in a JSP file:

 <%@ page import="com.todacell.management.SessionSingleton" %>  
 <%@ page import="com.todacell.util.sql.ResultSetToHtmlTable" %>  
 <%@ page import="org.hibernate.Session" %>  
 <%@ page import="java.sql.Connection" %>  
 <%@ page import="java.sql.ResultSet" %>  
 <%@ page import="java.sql.Statement" %>  
 <%@ page contentType="text/html;charset=UTF-8" language="java" %>  
 <html>  
 <head>  
   <title>Daily Deposits</title>  
   <style type="text/css">  
     table {  
       border-collapse: collapse;  
     }  
     table, th, td {  
       border: 1px solid black;  
     }  
     td, th {  
       padding: 5px;  
     }  
   </style>  
 </head>  
 <body>  
 <jsp:include page="k2_menu.jsp" />  
 <h1>Daily Deposits</h1>  
 <%  
   Session hSession = SessionSingleton.instance();  
   Connection conn = hSession.connection();  
   try {  
     Statement stmt = conn.createStatement();  
     ResultSet rs = stmt.executeQuery("select * from k2_deposits(60)");  
     ResultSetToHtmlTable resultSetToHtmlTable = new com.todacell.util.sql.ResultSetToHtmlTable();  
     resultSetToHtmlTable.writeTable(rs, out,  
         new com.todacell.util.sql.ResultSetToHtmlTable.Column[] {  
             new ResultSetToHtmlTable.Column(ResultSetToHtmlTable.ColumnType.DATE, "Date", ResultSetToHtmlTable.ColumnAlign.LEFT),  
             new ResultSetToHtmlTable.Column(ResultSetToHtmlTable.ColumnType.CURRENCY, "Deposits", ResultSetToHtmlTable.ColumnAlign.RIGHT),  
             new ResultSetToHtmlTable.Column(ResultSetToHtmlTable.ColumnType.CURRENCY, "Allocated", ResultSetToHtmlTable.ColumnAlign.RIGHT),  
             new ResultSetToHtmlTable.Column(ResultSetToHtmlTable.ColumnType.CURRENCY, "Deallocated", ResultSetToHtmlTable.ColumnAlign.RIGHT),  
             new ResultSetToHtmlTable.Column(ResultSetToHtmlTable.ColumnType.CURRENCY, "Ad Spend", ResultSetToHtmlTable.ColumnAlign.RIGHT),  
             new ResultSetToHtmlTable.Column(ResultSetToHtmlTable.ColumnType.CURRENCY, "Potential Spend", ResultSetToHtmlTable.ColumnAlign.RIGHT),  
             new ResultSetToHtmlTable.Column(ResultSetToHtmlTable.ColumnType.INTEGER, "New Users", ResultSetToHtmlTable.ColumnAlign.RIGHT)  
         });  
   } finally {  
     if (hSession != null) {  
       hSession.close();  
     }  
   }  
 %>  
 </body>  
 </html>  

And here is how it looks on the browser:

DOWNLOAD: You can download the ResultSetToHtmlTable here

Converting ResultSet to Excel table in Java using Apache POI

You can make a great reporting system with impressive UI, but there will always be someone that will ask you: Can you export to excel?

Excel offers many ways of easily manipulating and viewing data out of the box. Therefore, no matters how your reporting system is sophisticated, exporting the result to Excel is quite a basic feature.

Using Apache POI Project to produce Excel output is an easy task. This great project offers a wide support for generating Microsoft Format Files. We will use this open source project to dynamically generate Excel file from a ResultSet.

We will construct a class named: ResultSetToExcel. It’s constructor will get: ResultSet and Excel sheet name. A function named: generate will produce Excel sheet to an OutputStream or to a file. The content of the generated Excel sheet will be a table with headers and ResultSet data.

Building the ResultSetToExcel class introduces 2 main issues:

• From where do we get the titles for the report?
• How do we format Excel cells?

This is how we will deal with these 2 issues:

• Report titles will be taken from query columns by using the ResultSetMetaData class. For example, let’s have a look at this query:

  select first_name 'First Name', last_name 'Last Name', age 'Age' from person

  The names that will be used for our report are: First Name, Last Name and Age. If no specific name is given to a column, then the column name itself will be used.
• We will use 2 ways for formatting Excel columns:
• Explicit: In the class constructor, In addition to the ResultSet, we supply a parameter named: formatTypes. This parameter is an array of the static inner enum class named: FormatType. It contains all commonly used format types:
  
  
  • Text
  • Integer
  • Float
  • Date
  • Money
  Of course, the items in this array should correspond one-to-one to the columns in our ResultSet. We use this array to format Excel cells according to their nature. This makes the resulted Excel file more neat and professional. Note that these are the most commonly and basic types used. You can add more types quite easily.
• Automatic: If no formatTypes parameter supplied, we try to “guess” the type of the column, by examining the Java class used for every specific column. We do it by using the private method: getFormatType. Pay attention to 2 important things:
• The getFormatType method is pretty basic, it doesn’t handle all possible java classes, but you can easily refine it.
• The “automatic guessing” way is less preferable, because it is not able to give us more fine grained control over the way our cell is formatted. For example: we cannot format a cell as Money or Percentage.

2 last things before moving to code is:

• The method: writeCell. This method is used to write an excel cell in a formatted way. It formats the cell according to the values in the parameter: formatType. Note, that in order to format the cell the class: HSSFCellUtil is used. This class is not at the core of the POI project, but in additional JAR file named: POI Contrib. This class allows formatting Excel cells in a way that existing formats are automatically reused. It prevents from Excel to generate a new format for each cell causing an Excel formatting error in cases of big tables.
• After writing ResultSet cells we use the method: autoSizeColumn of the class: HSSFSheet. This method automatically sets the size of each column according to the longest (in that column). This makes the resulted Excel file ready to be viewed.
  

Well, finally, let’s have a look at the code of our ResultSetToExcel class:

package com.bashan.blog.excel;
import org.apache.commons.lang.exception.NestableException;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.usermodel.contrib.HSSFCellUtil;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Timestamp;
public class ResultSetToExcel {
  private HSSFWorkbook workbook;
  private HSSFSheet sheet;
  private HSSFFont boldFont;
  private HSSFDataFormat format;
  private ResultSet resultSet;
  private FormatType[] formatTypes;
  public ResultSetToExcel(ResultSet resultSet, FormatType[] formatTypes, String sheetName) {
    workbook = new HSSFWorkbook();
    this.resultSet = resultSet;
    sheet = workbook.createSheet(sheetName);
    boldFont = workbook.createFont();
    boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    format = workbook.createDataFormat();
    this.formatTypes = formatTypes;
  }
  public ResultSetToExcel(ResultSet resultSet, String sheetName) {
    this(resultSet, null, sheetName);
  }
  private FormatType getFormatType(Class _class) {
    if (_class == Integer.class || _class == Long.class) {
      return FormatType.INTEGER;
    } else if (_class == Float.class || _class == Double.class) {
      return FormatType.FLOAT;
    } else if (_class == Timestamp.class || _class == java.sql.Date.class) {
      return FormatType.DATE;
    } else {
      return FormatType.TEXT;
    }
  }
  public void generate(OutputStream outputStream) throws Exception {
    try {
      ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
      if (formatTypes != null && formatTypes.length != resultSetMetaData.getColumnCount()) {
        throw new IllegalStateException("Number of types is not identical to number of resultset columns. " +
            "Number of types: " + formatTypes.length + ". Number of columns: " + resultSetMetaData.getColumnCount());
      }
      int currentRow = 0;
      HSSFRow row = sheet.createRow(currentRow);
      int numCols = resultSetMetaData.getColumnCount();
      boolean isAutoDecideFormatTypes;
      if (isAutoDecideFormatTypes = (formatTypes == null)) {
        formatTypes = new FormatType[numCols];
      }
      for (int i = 0; i < numCols; i++) {
        String title = resultSetMetaData.getColumnName(i + 1);
        writeCell(row, i, title, FormatType.TEXT, boldFont);
        if (isAutoDecideFormatTypes) {
          Class _class = Class.forName(resultSetMetaData.getColumnClassName(i + 1));
          formatTypes[i] = getFormatType(_class);
        }
      }
      currentRow++;
      // Write report rows
      while (resultSet.next()) {
        row = sheet.createRow(currentRow++);
        for (int i = 0; i < numCols; i++) {
          Object value = resultSet.getObject(i + 1);
          writeCell(row, i, value, formatTypes[i]);
        }
      }
      // Autosize columns
      for (int i = 0; i < numCols; i++) {
        sheet.autoSizeColumn((short) i);
      }
      workbook.write(outputStream);
    }
    finally {
      outputStream.close();
    }
  }
  public void generate(File file) throws Exception {
    generate(new FileOutputStream(file));
  }
  private void writeCell(HSSFRow row, int col, Object value, FormatType formatType) throws NestableException {
    writeCell(row, col, value, formatType, null, null);
  }
  private void writeCell(HSSFRow row, int col, Object value, FormatType formatType, HSSFFont font) throws NestableException {
    writeCell(row, col, value, formatType, null, font);
  }
  private void writeCell(HSSFRow row, int col, Object value, FormatType formatType,
                         Short bgColor, HSSFFont font) throws NestableException {
    HSSFCell cell = HSSFCellUtil.createCell(row, col, null);
    if (value == null) {
      return;
    }
    if (font != null) {
      HSSFCellStyle style = workbook.createCellStyle();
      style.setFont(font);
      cell.setCellStyle(style);
    }
    switch (formatType) {
      case TEXT:
        cell.setCellValue(value.toString());
        break;
      case INTEGER:
        cell.setCellValue(((Number) value).intValue());
        HSSFCellUtil.setCellStyleProperty(cell, workbook, HSSFCellUtil.DATA_FORMAT,
            HSSFDataFormat.getBuiltinFormat(("#,##0")));
        break;
      case FLOAT:
        cell.setCellValue(((Number) value).doubleValue());
        HSSFCellUtil.setCellStyleProperty(cell, workbook, HSSFCellUtil.DATA_FORMAT,
            HSSFDataFormat.getBuiltinFormat(("#,##0.00")));
        break;
      case DATE:
        cell.setCellValue((Timestamp) value);
        HSSFCellUtil.setCellStyleProperty(cell, workbook, HSSFCellUtil.DATA_FORMAT,
            HSSFDataFormat.getBuiltinFormat(("m/d/yy")));
        break;
      case MONEY:
        cell.setCellValue(((Number) value).intValue());
        HSSFCellUtil.setCellStyleProperty(cell, workbook,
            HSSFCellUtil.DATA_FORMAT, format.getFormat("($#,##0.00);($#,##0.00)"));
        break;
      case PERCENTAGE:
        cell.setCellValue(((Number) value).doubleValue());
        HSSFCellUtil.setCellStyleProperty(cell, workbook,
            HSSFCellUtil.DATA_FORMAT, HSSFDataFormat.getBuiltinFormat("0.00%"));
    }
    if (bgColor != null) {
      HSSFCellUtil.setCellStyleProperty(cell, workbook, HSSFCellUtil.FILL_FOREGROUND_COLOR, bgColor);
      HSSFCellUtil.setCellStyleProperty(cell, workbook, HSSFCellUtil.FILL_PATTERN, HSSFCellStyle.SOLID_FOREGROUND);
    }
  }
  public enum FormatType {
    TEXT,
    INTEGER,
    FLOAT,
    DATE,
    MONEY,
    PERCENTAGE
  }
}

This class can be downloaded here.

The JARs for this class can be downloaded from: Apache POI Project. Both POI and POI Contrib Jars are needed.

This is a small example of how this class can be used:

  public void createExecl(Connection conn) throws Exception
  {
    PreparedStatement stmt = conn.prepareStatement("select first_name 'First Name', last_name 'Last Name', age 'Age', " +
        "salary 'Salary'");
    ResultSet resultSet = stmt.executeQuery();
    ResultSetToExcel resultSetToExcel = new ResultSetToExcel(resultSet,
        new ResultSetToExcel.FormatType[] { ResultSetToExcel.FormatType.TEXT, ResultSetToExcel.FormatType.TEXT,
            ResultSetToExcel.FormatType.INTEGER, ResultSetToExcel.FormatType.MONEY }, "Employee List");
    resultSetToExcel.generate(new File("c:\\employees.xls"));
  }