ResultSet to Excel (*.xlsx) Table Using Apache POI

Question

I am trying to write ResultSet to Excel (*.xlsx) Table using Apache Poi.

Invalid Table Object Error in Office Excel

However, even though it writes the Excel file without any error, when I try to open it in Office Excel 2013, it shows an error and removes the table object to give only plain data view.

Here is the rough Sample Code using this example:

public static void writeExcel(ResultSet rs, int sqliteRowCount, String dir) {
    System.out.println("Writing Excel(*.xlsx) File...");
    XSSFWorkbook workbook = null;
    try {
        if (rs != null) {
            // Get ResultSet MetaData
            ResultSetMetaData rsmd = rs.getMetaData();
            // Number of columns
            int numColumns = rsmd.getColumnCount();
            // Number of rows
            // + 1 for headers
            int numRows = sqliteRowCount + 1;
            workbook = new XSSFWorkbook();

            // Create Excel Table
            XSSFSheet sheet = workbook.createSheet("Text");
            XSSFTable table = sheet.createTable();
            table.setDisplayName("Test");
            CTTable cttable;
            cttable = table.getCTTable();

            // Style configurations
            CTTableStyleInfo style = cttable.addNewTableStyleInfo();
            style.setName("TableStyleMedium16");
            style.setShowColumnStripes(false);
            style.setShowRowStripes(true);

            // Set Table Span Area
            AreaReference reference = new AreaReference(new CellReference(0, 0), new CellReference(numRows - 1, numColumns - 1));
            cttable.setRef(reference.formatAsString());
            cttable.setId(1);
            cttable.setName("Test");
            cttable.setDisplayName("Test");
            cttable.setTotalsRowCount(numRows);
            cttable.setTotalsRowShown(false);

            // Create Columns
            CTTableColumns columns = cttable.addNewTableColumns();
            columns.setCount(numColumns);

            // Create Column, Row, Cell Objects
            CTTableColumn column;
            XSSFRow row;

            // Add Header and Columns
            XSSFRow headerRow = sheet.createRow(0);
            for (int i = 0; i < numColumns; i++) {
                column = columns.addNewTableColumn();
                column.setName("Column" + (i + 1));
                column.setId(i + 1);
                headerRow.createCell(i).setCellValue(rsmd.getColumnLabel(i + 1));
            }

            // Write each row from ResultSet
            int rowNumber = 1;
            while (rs.next()) {
                row = sheet.createRow(rowNumber);
                for (int y = 0; y < numColumns; y++) {
                    row.createCell(y).setCellValue(rs.getString(y + 1));
                }
                rowNumber++;
            }

            // Set AutoFilter
            CTAutoFilter fltr = CTAutoFilter.Factory.newInstance();
            fltr.setRef((new AreaReference(new CellReference(0, 0), new CellReference(numRows - 1, numColumns - 1))).formatAsString());
            cttable.setAutoFilter(fltr);
            // sheet.setAutoFilter(CellRangeAddress.valueOf((new AreaReference(new CellReference(0, 0), new CellReference(numRows - 1, numColumns - 1))).formatAsString()));
            // Freeze Pan
            sheet.createFreezePane(0, 1, 0, 2);
        }
    } catch (SQLException ex) {
        System.out.println("SQL Error while writing Excel file!");
    } finally {
        try {
        // Let's write the excel file now
            if (workbook != null) {
                String excelDir = dir + File.separator + "workbook.xlsx";
                try (final FileOutputStream out = new FileOutputStream(excelDir)) {
                    workbook.write(out);
                }
            }
        } catch (IOException ex) {
            System.out.println("IO Error while writing Excel summary file!");
        }
    }
}

I know something is wrong with my code, but can't figure it out. Any idea, why this is happening, where would be potential mistake in my code.

Update 1:

Table XML file in Excel archive if created using Apache POI

<?xml version="1.0" encoding="UTF-8"?>
<table displayName="Test" ref="A1:B881" id="1" name="Test" totalsRowCount="881" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" totalsRowShown="0"><autoFilter ref="A1:B881"/><tableColumns count="2"><tableColumn name="ID" id="1"/><tableColumn name="Name" id="2"/><tableStyleInfo name="TableStyleMedium2" showColumnStripes="true" showRowStripes="true"/></table>

Table XML file in Excel archive if table created manually

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<table xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" id="1" name="Table1" displayName="Table1" ref="A1:B881" totalsRowShown="0"><autoFilter ref="A1:B881"/><tableColumns count="2"><tableColumn id="1" name="ID"/><tableColumn id="2" name="Name"/></tableColumns><tableStyleInfo name="TableStyleLight9" showFirstColumn="0" showLastColumn="0" showRowStripes="1" showColumnStripes="0"/></table>

In addition, if I open the Excel archive, it does not have a theme folder in the one created by Apache POI but it is present in the one create manually in Office Excel. Strange.

Update 2: Sample executable code (Using Netbeans):

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package apachepoi_exceltest;

    import java.io.File;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.util.HashMap;
    import java.util.Map;
    import org.apache.poi.ss.util.AreaReference;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.ss.util.CellReference;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFTable;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTable;
    import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn;
    import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumns;
    import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableStyleInfo;

    /**
     *
     */
    public class ApachePOI_ExcelTest {

        /**
         * @param args the command line arguments
         */
        public static void main(String[] args) {

            String outputDir = "Your Local Directory Here";

            // TODO code application logic here
            HashMap<String, String> dataMap = new HashMap<>();

            dataMap.put("ID 1", "Dummy Name 1");
            dataMap.put("ID 2", "Dummy Name 2");
            dataMap.put("ID 3", "Dummy Name 3");
            dataMap.put("ID 4", "Dummy Name 4");

            writeExcel(dataMap, outputDir);

        }

        private static void writeExcel(HashMap<String, String> dataMap, String outputDir) {
            System.out.println("Writing Excel(*.xlsx) Summary File...");
            XSSFWorkbook workbook = null;
            try {

                // Number of columns
                int numColumns = 2; // ID and Name
                // Number of rows
                int numRows = dataMap.size() + 1; // +1 for header

                // Create Workbook
                workbook = new XSSFWorkbook();

                // Create Excel Table
                XSSFSheet sheet = workbook.createSheet("Summary");
                XSSFTable table = sheet.createTable();
                table.setDisplayName("Test");
                CTTable cttable;
                cttable = table.getCTTable();

                // Style configurations
                CTTableStyleInfo style = cttable.addNewTableStyleInfo();
                style.setName("TableStyleMedium16");
                style.setShowColumnStripes(false);
                style.setShowRowStripes(true);

                // Set Tabel Span Area
                AreaReference reference = new AreaReference(new CellReference(0, 0), new CellReference(numRows - 1, numColumns - 1));
                cttable.setRef(reference.formatAsString());
                cttable.setId(1);
                cttable.setName("Test");
                cttable.setDisplayName("Test");
                cttable.setTotalsRowCount(numRows);
                cttable.setTotalsRowShown(false);

                // Create Columns
                CTTableColumns columns = cttable.addNewTableColumns();
                columns.setCount(numColumns);

                // Create Column, Row, Cell Objects
                CTTableColumn column;
                XSSFRow row;

                // Add ID Header
                column = columns.addNewTableColumn();
                column.setName("Column" + (1));
                column.setId(1);

                // Add Name Header
                column = columns.addNewTableColumn();
                column.setName("Column" + (1));
                column.setId(1);

                // Add Header Row
                XSSFRow headerRow = sheet.createRow(0);
                headerRow.createCell(0).setCellValue("ID");
                headerRow.createCell(1).setCellValue("Name");

                int rowNumber = 1;
                for (Map.Entry<String, String> entry : dataMap.entrySet()) {
                    String id = entry.getKey();
                    String name = entry.getValue();
                    row = sheet.createRow(rowNumber);
                    row.createCell(0).setCellValue(id);
                    row.createCell(1).setCellValue(name);
                    rowNumber++;
                }

                // Set Filter (Below three lines code somehow not working in this example, so setting AutoFilter to WorkSheet)
    //             CTAutoFilter fltr = CTAutoFilter.Factory.newInstance();
    //             fltr.setRef((new AreaReference(new CellReference(0, 0), new CellReference(numRows - 1, numColumns - 1))).formatAsString());
    //             cttable.setAutoFilter(fltr);
                sheet.setAutoFilter(CellRangeAddress.valueOf((new AreaReference(new CellReference(0, 0), new CellReference(numRows - 1, numColumns - 1))).formatAsString()));

                // Freeze First Row as header Row
                sheet.createFreezePane(0, 1, 0, 2);

            } catch (Exception ex) {
                System.out.println("Error while writing Excel summary file!");
            } finally {
                try {
                    // Lets write the Excel File Now
                    if (workbook != null) {
                        String excelDir = outputDir + File.separator + "workbook.xlsx";
                        try (final FileOutputStream out = new FileOutputStream(excelDir)) {
                            workbook.write(out);
                        }
                    }
                } catch (IOException ex) {
                    System.out.println("IO Error while writing Excel summary file!");
                }
            }
        }

    }

Libraries Used:

ooxml-schemas-1.1.jar

poi-3.11-beta2-20140822.jar

poi-ooxml-3.11-beta2-20140822.jar

xmlbeans-2.6.0.jar

Solution

What's wrong with your code is a presence of a single line. "cttable.setTotalsRowCount(numRows);" Remove it and everything will work. If in doubt, compare the XML definitions of some working table created manually in Excel and the definitions created with Apache POI

Comments

[Gagravarr]

Everything should be being escaped when written by Apache POI (it uses a XML library that ought to be doing that). What is the element that you're having to change?

[Eric S.]

The instance I investigated involved no changes to the data. It was a read from an Excel-created XLSX file & save to new file action (no other actions performed). POI read in the > converting to >, but Excel saw that as a bad change.

[Gagravarr]

Can you post the snippet of xml from before and after, so we can see which xml element it is happening for?

[Indigo]

@EricS.: I can't compare both table simply repairing the file. Because Excel is actually removing this table completely. So it is taking the table as an invalid object. Still I have updated the question with the excel before removing the table.