title: ResultSet to Excel (*.xlsx) Table Using Apache POI
body: <p>I am trying to write <strong>ResultSet to Excel (*.xlsx) Table</strong> using Apache Poi.</p>
  
  <p><strong>Invalid Table Object Error in Office Excel</strong></p>
  
  <p>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.</p>
  
  <p><img src="https://i.stack.imgur.com/c1VXc.png" alt="Message while opening file"></p>
  
  <p><img src="https://i.stack.imgur.com/HVM3G.png" alt="Message after removing errors"></p>
  
  <p><strong>Here is the rough Sample Code <a href="http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateTable.java" rel="noreferrer">using this example</a>:</strong></p>
  
  <pre><code>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 &lt; 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 &lt; 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!");
          }
      }
  }
  </code></pre>
  
  <p>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.</p>
  
  <p><strong>Update 1:</strong> </p>
  
  <p>Table XML file in Excel archive if created using Apache POI</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;table displayName="Test" ref="A1:B881" id="1" name="Test" totalsRowCount="881" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" totalsRowShown="0"&gt;&lt;autoFilter ref="A1:B881"/&gt;&lt;tableColumns count="2"&gt;&lt;tableColumn name="ID" id="1"/&gt;&lt;tableColumn name="Name" id="2"/&gt;&lt;tableStyleInfo name="TableStyleMedium2" showColumnStripes="true" showRowStripes="true"/&gt;&lt;/table&gt;
  </code></pre>
  
  <p>Table XML file in Excel archive if table created manually</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8" standalone="yes"?&gt;
  &lt;table xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" id="1" name="Table1" displayName="Table1" ref="A1:B881" totalsRowShown="0"&gt;&lt;autoFilter ref="A1:B881"/&gt;&lt;tableColumns count="2"&gt;&lt;tableColumn id="1" name="ID"/&gt;&lt;tableColumn id="2" name="Name"/&gt;&lt;/tableColumns&gt;&lt;tableStyleInfo name="TableStyleLight9" showFirstColumn="0" showLastColumn="0" showRowStripes="1" showColumnStripes="0"/&gt;&lt;/table&gt;
  </code></pre>
  
  <p>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. </p>
  
  <p><strong>Update 2:</strong>
  <strong>Sample executable code (Using Netbeans):</strong></p>
  
  <pre><code>/*
   * 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&lt;String, String&gt; dataMap = new HashMap&lt;&gt;();
  
              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&lt;String, String&gt; 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&lt;String, String&gt; 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!");
                  }
              }
          }
  
      }
  </code></pre>
  
  <p><strong>Libraries Used:</strong></p>
  
  <p>ooxml-schemas-1.1.jar</p>
  
  <p>poi-3.11-beta2-20140822.jar</p>
  
  <p>poi-ooxml-3.11-beta2-20140822.jar</p>
  
  <p>xmlbeans-2.6.0.jar</p>