none
OpenXml Excel: Trouble adding adding data to a table RRS feed

  • Question

  • Hi Folks,

    I'm trying to write an excel file with data formatted into a table.
    I open the excel file the table is not there.
    When I unpack the xlsx file I do see all the table and rel files required though. Please help, Thank you!

    Code zip (VS2012) - https://skydrive.live.com/redir?resid=641CF7BEEB1B64F6!1679

    Excel file output - https://skydrive.live.com/redir?resid=641CF7BEEB1B64F6!134

    Code for main function-

        public static void WriteRandomValuesSAX(string filename, int numRows, int numCols)
            {
                using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Create(filename, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
                {
                    //create workbook part
                    WorkbookPart wbp = spreadsheet.AddWorkbookPart();
                    wbp.Workbook = new Workbook();
                    Sheets sheets = wbp.Workbook.AppendChild<Sheets>(new Sheets());
    
                    //create worksheet part, and add it to the sheets collection in workbook
                    WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
                    Sheet sheet = new Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(wsp), SheetId = 1, Name = "Report_Test" };
                    sheets.Append(sheet);
                    Row r = new Row();
                    OpenXmlWriter writer = OpenXmlWriter.Create(wsp);
                    writer.WriteStartElement(new Worksheet());
                    writer.WriteStartElement(new SheetData());
    
                    var data = "Cell Data";
                    
                    for (int row = 0; row < numRows; row++)
                    {                 
                        writer.WriteStartElement(r);
                        for (int col = 0; col < numCols; col++)
                        {
                            writer.WriteElement(new Cell { CellValue = new CellValue(data), DataType = CellValues.String });                        
                        }
                        writer.WriteEndElement();                    
                    }
    
                    //Add table def 
                    var tableDefPart = wsp.AddNewPart<TableDefinitionPart>("rId2");
                    var table = new Table() { Id = 1U, Name = "Table1", DisplayName = "Table1", Reference = "A1:B11", TotalsRowShown = false };
    
                    // ReSharper disable PossiblyMistakenUseOfParamsMethod
                    table.Append(new AutoFilter() { Reference = "A1:B11" });
                    var tableColumns = new TableColumns() {Count = 2};
                    tableColumns.Append(new TableColumn() { Id = 1U, Name = "name" });
                    tableColumns.Append(new TableColumn() { Id = 2U, Name = "val" });
                    table.Append(tableColumns);
                    var tableStyleInfo = new TableStyleInfo() { Name = "TableStyleMedium9", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };
                    table.Append(tableStyleInfo);
                    
                    // ReSharper restore PossiblyMistakenUseOfParamsMethod
                    tableDefPart.Table = table;
    
                    var tableParts = new TableParts {Count = 1};
                    var tablePart = new TablePart() { Id = "rId2" };
    
                    writer.WriteStartElement(tablePart);
                    writer.WriteElement(tablePart);
                    writer.WriteEndElement(); // Table part                                
    
                    writer.WriteEndElement(); //end of SheetData
                    writer.WriteEndElement(); //end of worksheet                
                    writer.Close();                
                }
            }

    Monday, November 19, 2012 8:38 AM

All replies

  • Hi ananded,

    Thanks for posting in the MSDN Forum.

    First, you have add the TableParts node at an incorrect place. You can easy to find out it via Open XML SDK 2.0 Productivity Tool  for Microsoft Office.

    using System.Text;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using com.mksword.Net.OpenXmlTools;
    using com.mksword.OpenXmlUtil_log4netTool;
    using DocumentFormat.OpenXml;
    
    namespace _201211200001
    {
        class Class1 : ExcelTool
        {
            public override void Action()
            {
                if (_Core.HandOpenSpreadsheetDocumentOnLoacl(CreateTable, true))
                    SetLog("Succeed", OXULogType.INFO);
                else
                    SetLog("Failure", OXULogType.FATAL);
            }
    
            public bool CreateTable(SpreadsheetDocument SSD)
            {
                bool result = false;
                WorkbookPart WBP = SSD.WorkbookPart;
                Workbook WB = WBP.Workbook;
                Sheet S = WB.Descendants<Sheet>()
                    .Where(SH => SH.Name =="Report_Test").FirstOrDefault();
                WorksheetPart WSP = WBP.GetPartById(S.Id) as WorksheetPart;
                if (WSP != null)
                {
                    for (int i = 0; i < WSP.TableDefinitionParts.Count(); i++)
                    {
                        WSP.DeletePart(WSP.TableDefinitionParts.ToList()[i]);
                    }
                    TableDefinitionPart TDP = WSP.AddNewPart<TableDefinitionPart>();
                    string rId = WSP.GetIdOfPart(TDP);
                    Table T = new Table()
                    {
                        Id = (UInt32Value)1U,
                        Name = "MyTable",
                        DisplayName="MyTable",
                        Reference = "A1:B10",
                        TotalsRowShown = false
                    };
                    TableColumns TCs = new TableColumns()
                    {
                        Count = (UInt32Value)2U
                    };
                    TableColumn TC1 = new TableColumn()
                    {
                        Id = (UInt32Value)1U,
                        Name = "Column1"
                    };
                    TableColumn TC2 = new TableColumn()
                    {
                        Id = (UInt32Value)2U,
                        Name = "Column2"
                    };
                    TableStyleInfo TSI = new TableStyleInfo()
                    {
                        Name = "TableStyleMedium2",
                        ShowFirstColumn = false,
                        ShowLastColumn = false,
                        ShowRowStripes = true,
                        ShowColumnStripes = false
                    };
                    AutoFilter AF = new AutoFilter() { Reference = "A1:B10" };
                    TCs.Append(TC1);
                    TCs.Append(TC2);
                    T.Append(AF);
                    T.Append(TCs);
                    T.Append(TSI);
                    TDP.Table = T;
                    T.Save();
    
                    Worksheet WS = WSP.Worksheet;
                    TableParts TPs = new TableParts() { Count = (UInt32Value)1U };
                    TablePart TP = new TablePart() { Id = rId };
                    TPs.Append(TP);
                    WS.Append(TPs);
                    WS.Save();
                    result = true;
                }
                else
                {
                    SetLog("WSP is null", OXULogType.FATAL);
                }
                return result;
            }
        }
    }

    Following snippet will work, however it will prompt alert dialog.

    I hope it can help you.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, November 20, 2012 6:04 AM
    Moderator
  • Thanks Tom, It does seem to work, but as you mentioned it throws an alert and then repairs it.

    Is there any reason for this? I looked before and after and cant make out what is wrong, it complains about the table part being incorrect.

    Wednesday, November 21, 2012 6:27 AM
  • Can someone help out? We are not able to find out why the alert is thrown, the error log from excel is very generic.

    Is there sample code that renders an excel table without the warning?

    OR is there any way to check the detailed error from Excel?

    Thanks

    Monday, November 26, 2012 9:20 AM