Write a ResultSet to Excel with Apache POI

Write a ResultSet to Excel with Apache POI

We often face the need to export data in a human-readable and human-writable way. Export to Excel seem natural, giving access to all his tools of filtering, etc…

I had the occasion to do it a generic way, I share it here. It does not cover all cases of data found in database, up to you to complete it and maybe share your improvements.

Dump headers

First step is to dump the column headers, found in ResultSetMetaData. The cells will be styled in BOLD font.

public HSSFWorkbook dump(ResultSet resultSet) throws SQLException {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFFont boldFont = workbook.createFont();
    boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    HSSFSheet sheet = workbook.createSheet("sheet");
    HSSFRow titleRow = sheet.createRow(0);
    ResultSetMetaData metaData = resultSet.getMetaData();
    int columnCount = metaData.getColumnCount();
    for (int colIndex = 0; colIndex < columnCount; colIndex++) {
        String title = metaData.getColumnLabel(colIndex + 1);
        HSSFCell cell = HSSFCellUtil.createCell(titleRow, colIndex, title);
        HSSFCellStyle style = workbook.createCellStyle();
        style.setFont(boldFont);
        cell.setCellStyle(style);
    }
    return workbook;
}

Dump data

Then the resultSet is browsed (a call to beforeFirst() resets the cursor), and different cases of Object retrieved are covered. That does not cover all different object types the driver can return.

    private void dumpData(ResultSet resultSet, HSSFSheet sheet, int columnCount) throws SQLException {
        int currentRow = 1;
        resultSet.beforeFirst();
        while (resultSet.next()) {
            HSSFRow row = sheet.createRow(currentRow++);
            for (int colIndex = 0; colIndex < columnCount; colIndex++) {
                Object value = resultSet.getObject(colIndex + 1);
                final HSSFCell cell = row.createCell(colIndex);
                if (value == null) {
                    cell.setCellValue("");
                } else {
                    if (value instanceof Calendar) {
                        cell.setCellValue((Calendar) value);
                    } else if (value instanceof Date) {
                        cell.setCellValue((Date) value);
                    } else if (value instanceof String) {
                        cell.setCellValue((String) value);
                    } else if (value instanceof Boolean) {
                        cell.setCellValue((Boolean) value);
                    } else if (value instanceof Double) {
                        cell.setCellValue((Double) value);
                    } else if (value instanceof BigDecimal) {
                        cell.setCellValue(((BigDecimal) value).doubleValue());
                    }
                }
            }
        }
        for (int i = 0; i < columnCount; i++) {
            sheet.autoSizeColumn(i);
        }
    }

(Optional) Add cell formatting

If you want to format the cells, you can parameter a map between JDBC formats and Excel formatting. The JDBC formats will be set with regular expressions. Such a map can be initialized with a Spring file, like this :

<util:map map-class="java.util.LinkedHashMap">
    <entry key="NUMBER\(\d+,2\)" value="0.00" />
    <entry key="NUMBER\(\d+,0\)" value="0" />
    <entry key="NUMBER.*" value="0.###" />
    <entry key="DECIMAL\(\d+,2\)" value="0.00" />
    <entry key="DECIMAL\(\d+,0\)" value="0" />
    <entry key="DECIMAL.*" value="0.###" />
    <entry key=".*CHAR.*" value="text" />
    <entry key="DATE.*" value="dd/MM/yyyy" />
    <entry key="TIMESTAMP.*" value="dd/MM/yyyy" />
</util:map>

Then in the code, add the methods :


    private LinkedHashMap<String, String> mapSqlTypeExcelFormat;

    private HSSFCellStyle getDataStyle(HSSFWorkbook workbook, ResultSetMetaData metaData, int colIndex, HSSFDataFormat dataFormat) throws SQLException {
        HSSFCellStyle dataStyle = workbook.createCellStyle();
        String columnType = metaData.getColumnTypeName(colIndex + 1);
        columnType += "(" + metaData.getPrecision(colIndex + 1);
        columnType += "," + metaData.getScale(colIndex + 1) + ")";
        String excelFormat = getExcelFormat(columnType);
        final short format = dataFormat.getFormat(excelFormat);
        dataStyle.setDataFormat(format);
        return dataStyle;
    }

    private String getExcelFormat(String columnType) {
        for (Map.Entry<String, String> entry : mapSqlTypeExcelFormat.entrySet()) {
            if (Pattern.matches(entry.getKey(), columnType)) {
                return entry.getValue();
            }
        }
        return "text";
    }

Whole code

Java Class :
ResultSetToExcel

JUnit Test Class :
ResultSetToExcelTest

No comments.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>