To create an spreadsheet document in OPEN XML and and add an TABLE to it

Question

I am trying to create an spread sheet by open xml with an table. But while opening the created excel file always it returns the error "Excel found unreadable content in test1.xlsx, do u want to recover. Why this error always happens? When the excel is created without table, i didnt get any error. Please find the below sample code which i used to create.

            using (SpreadsheetDocument spreadsheetdocument = SpreadsheetDocument.Create(@"c://test.xlsx", SpreadsheetDocumentType.Workbook))
        {
            // add a workbookpart to the document.
            WorkbookPart workbookpart = spreadsheetdocument.AddWorkbookPart();
            workbookpart.Workbook = new Workbook();

            // add a worksheetpart to the workbookpart.
            WorksheetPart worksheetpart = workbookpart.AddNewPart<WorksheetPart>();
            worksheetpart.Worksheet = new Worksheet(new SheetData());

            // add sheets to the workbook.
            Sheets sheets = spreadsheetdocument.WorkbookPart.Workbook.
                AppendChild<Sheets>(new Sheets());

            // append a new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet()
            {
                Id = spreadsheetdocument.WorkbookPart.
                    GetIdOfPart(worksheetpart),
                SheetId = 1,
                Name = "mysheet"
            };
            sheets.Append(sheet);

             TableDefinitionPart tableDefinitionPart = worksheetpart.AddNewPart<TableDefinitionPart>("rId1");

            GenerateTablePartContent(ref tableDefinitionPart);

            TableParts tableParts1 = new TableParts() { Count = (UInt32Value)2U };
            TablePart tablePart1 = new TablePart() { Id = "rId1" };
            tableParts1.Append(tablePart1);
            worksheetpart.Worksheet.Append(tableParts1);
             spreadsheetdocument.Close();
} 
//Added spaces to get the code in one block
 private void GenerateTablePartContent(TableDefinitionPart part)
    {
        Table table1 = new Table() { Id = (UInt32Value)1U, Name = "Table1", DisplayName = "Table1", Reference = "A1:C3", TotalsRowShown = false };
        AutoFilter autoFilter1 = new AutoFilter() { Reference = "A1:C3" };

        TableColumns tableColumns1 = new TableColumns() { Count = (UInt32Value)3U };
        TableColumn tableColumn1 = new TableColumn() { Id = (UInt32Value)1U, Name = "Column1" };
        TableColumn tableColumn2 = new TableColumn() { Id = (UInt32Value)2U, Name = "Column2" };
        TableColumn tableColumn3 = new TableColumn() { Id = (UInt32Value)3U, Name = "Column3" };

        tableColumns1.Append(tableColumn1);
        tableColumns1.Append(tableColumn2);
        tableColumns1.Append(tableColumn3);
        TableStyleInfo tableStyleInfo1 = new TableStyleInfo() { Name = "TableStyleMedium2", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };

        table1.Append(autoFilter1);
        table1.Append(tableColumns1);
        table1.Append(tableStyleInfo1);

        part.Table = table1;
    }

score 1 · Answer 1 · answered Jun 06 '12 at 08:39

use Open XML SDK Productivity Tool for it. Create an excel file using normal way and put same data there as well and compare that file with the file you are creating with your code. You will see the changes there. I am also facing same issue and I am trying to fix the changes using that tool. Lets see....

and If you have solved the issue please guide me how to do it.

To create an spreadsheet document in OPEN XML and and add an TABLE to it

1 Answers1