使用Apache POI设置Excel (*.xlsx)表。

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

我正在尝试使用Apache Poi将ResultSet写为Excel (*.xlsx)表。

Invalid Table Object Error in Office Excel

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.

然而，尽管它编写Excel文件时没有任何错误，但当我尝试在Office Excel 2013中打开它时，它会显示一个错误并删除表对象，只提供纯数据视图。

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:

更新1:

Table XML file in Excel archive if created using Apache POI

使用Apache POI创建的Excel归档中的表XML文件

<?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文件在Excel归档中，如果表是手动创建的。

<?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.

此外，如果我打开Excel归档文件，它在Apache POI创建的文件夹中没有一个主题文件夹，但它存在于Office Excel中手动创建的一个文件夹中。奇怪。

Update 2: Sample executable code (Using Netbeans):

更新2:可执行代码示例(使用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

ooxml-schemas-1.1.jar

poi-3.11-beta2-20140822.jar

poi - 3.11 - beta2 - 20140822. - jar

poi-ooxml-3.11-beta2-20140822.jar

poi - ooxml - 3.11 - beta2 - 20140822. - jar

xmlbeans-2.6.0.jar

xmlbeans-2.6.0.jar

3 个解决方案

#1

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

代码的错误之处在于只有一行代码。“cttable.setTotalsRowCount(numRows);“删除它，一切都会工作。如果有疑问，请比较在Excel中手工创建的一些工作表的XML定义和使用Apache POI创建的定义

#2

I had the same issue.

我也有同样的问题。

Digging deeply, I found that for some table XML data in the XLSX package, Excel is changing a single > to > after performing the repair. The XML from POI makes sense (use < and > to surround XML elements) so I have no idea why Microsoft chooses to break it.

深入挖掘，我发现对于XLSX包中的一些表XML数据，Excel正在更改一个>到>后进行修复。POI中的XML是有意义的(使用 <和> 包围XML元素)，所以我不知道微软为什么要打破它。

If its the same case for you, I'd not worry too much about it.

如果你也有同样的情况，我不会太担心。

If you want to see if you have this particular difference:

如果你想知道你是否有这个特别的不同:

1. Create XLSX with POI
2. 创建XLSX的原发性卵巢功能不全
3. Repair XLSX with Excel and save to new file
4. 使用Excel修复XLSX并保存到新文件中
5. Open both files with ZIP editor (e.g. 7Zip)
6. 使用ZIP编辑器打开两个文件(例如，7Zip)
7. Find xl/tables/table1.xml
8. 找到xl /表/ table1.xml
9. Export both XML files (POI and Excel-repaired)
10. 导出两个XML文件(POI和excel修复)
11. Diff the files
12. Diff文件

#3

You have not created your table correctly. Check:

您没有正确地创建表。检查:

• Did you create header columns in cttable?
• 你在cttable中创建标题列了吗?
• Did you create the same header columns through cell.setCellValue?
• 您是否通过cell.setCellValue创建了相同的标题列?

• Remove empty first header column(POI BUG) at the end

  在末尾删除空的first header列(POI BUG)

  CTTable().getTableColumns().removeTableColumn(0);

  .getTableColumns CTTable()().removeTableColumn(0);

Put debug into XSSFTable.class, method updateHeaders().

将调试投入XSSFTable。类,方法updateHeaders()。

If your table is not created properly, then

如果您的表没有正确创建，那么。

XSSFRow row = sheet.getRow(headerRow); 

will be NULL in

将零

/**
 * Synchronize table headers with cell values in the parent sheet.
 * Headers <em>must</em> be in sync, otherwise Excel will display a
 * "Found unreadable content" message on startup.
 */
@SuppressWarnings("deprecation")
public void updateHeaders(){
    XSSFSheet sheet = (XSSFSheet)getParent();
    CellReference ref = getStartCellReference();
    if(ref == null) return;

    int headerRow = ref.getRow();
    int firstHeaderColumn = ref.getCol();
    XSSFRow row = sheet.getRow(headerRow);

    if (row != null && row.getCTRow().validate()) {
        int cellnum = firstHeaderColumn;
        for (CTTableColumn col : getCTTable().getTableColumns().getTableColumnArray()) {
            XSSFCell cell = row.getCell(cellnum);
            if (cell != null) {
                col.setName(cell.getStringCellValue());
            }
            cellnum++;
        }
    }
}