none
How do I add UID to xl/table file using OpenXML? RRS feed

  • Question

  • I output an xlsx with a table defined using the following code:

            protected void ExcelOut(string FName, DataTable table)
            {
                // Get table schema to ensure proper column formats
                DataTable dtSchema = CommonFunctions.getSchema("WorkloadPerformanceResults_PV", UpdateEnvironment);

                MemoryStream ms = new MemoryStream();

                // Create a spreadsheet document by supplying the memorystream.
                // By default, AutoSave = true, Editable = true, and Type = xlsx.
                SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(ms, 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 = "PerformanceDataResults" };
                sheets.Append(sheet);

                // Get the sheetData cell table.
                SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

                DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

                List<String> columns = new List<string>();
                foreach (System.Data.DataColumn column in table.Columns)
                {
                    columns.Add(column.ColumnName);

                    DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                    cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                    cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
                    headerRow.AppendChild(cell);
                }

                sheetData.AppendChild(headerRow);
                int NumRows = table.Rows.Count;
                foreach (DataRow dsrow in table.Rows)
                {
                    DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                    foreach (String col in columns)
                    {
                        string dataType = "";

                        foreach (DataRow SchemaDR in dtSchema.Rows)
                        {
                            string dcName = SchemaDR["COLUMN_NAME"].ToString().ToUpper();
                            string dsrowName = col.ToString().ToUpper();
                            if (dcName == dsrowName)
                            {
                                dataType = SchemaDR["DATA_TYPE"].ToString();
                            }
                        }
                        DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                        // Need to get the data type of the col
                        switch (dataType)
                        {
                            case "bit":
                                cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Boolean;
                                break;
                            case "date":
                            case "datetime":
                            case "datetime2":
                            case "smalldatetime":
                            case "DateTime":
                                cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Date;
                                break;
                            case "bigint":
                            case "int":
                            case "decimal":
                            case "float":
                            case "money":
                            case "numeric":
                            case "smallint":
                            case "smallmoney":
                            case "tinyint":
                            case "UInt32":
                            case "UInt64":
                                cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
                                break;
                            case "nchar":
                            case "nvarchar":
                            case "text":
                                cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                                break;
                            default:
                                cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                                break;
                        }

                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
                        newRow.AppendChild(cell);
                    }

                    sheetData.AppendChild(newRow);
                }
                // Add table to sheet
                TableDefinitionPart tdp = worksheetPart.AddNewPart<TableDefinitionPart>("rId1");

                tdp.Table = GenerateTableDefinitionPart1Content(tdp, NumRows);
                
                TableParts tableparts1 = new TableParts() { Count = 1 };
                //TableParts tableparts1 = new TableParts();
                TablePart tablepart1 = new TablePart() { Id = "rId1" };
                tableparts1.Append(tablepart1);
                worksheetPart.Worksheet.Append(tableparts1);

                // Close the document.
                spreadsheetDocument.Close();

                // push memorystream to response
                Response.Clear();
                Response.ContentType = "Application/msexcel";
                Response.AddHeader("Content-Disposition", "attachment; filename=" + FName + ".xlsx");
                Response.BinaryWrite(ms.ToArray());
                // myMemoryStream.WriteTo(Response.OutputStream); //works too
                Response.Flush();
                Response.Close();
                Response.End();
            }

            private DocumentFormat.OpenXml.Spreadsheet.Table GenerateTableDefinitionPart1Content(TableDefinitionPart tableDefinitionPart1, int NumRows)
            {
                //'Table' is an ambiguous reference between 'DocumentFormat.OpenXml.Spreadsheet.Table' and 'System.Web.UI.WebControls.Table'
                DocumentFormat.OpenXml.Spreadsheet.Table table1 = new DocumentFormat.OpenXml.Spreadsheet.Table() { Id = (UInt32Value)1U, Name = "Table1", DisplayName = "Table1", Reference = "A1:M27", TotalsRowShown = false };
                AutoFilter autoFilter1 = new AutoFilter() { Reference = "A1:M" + NumRows.ToString() };

                TableColumns tableColumns1 = new TableColumns() { Count = 13 };
                TableColumn tableColumn1 = new TableColumn() { Id = (UInt32Value)1U, Name = "PlatformName" };
                TableColumn tableColumn2 = new TableColumn() { Id = (UInt32Value)2U, Name = "domain" };
                TableColumn tableColumn3 = new TableColumn() { Id = (UInt32Value)3U, Name = "industryVertical" };
                TableColumn tableColumn4 = new TableColumn() { Id = (UInt32Value)4U, Name = "engineer" };
                TableColumn tableColumn5 = new TableColumn() { Id = (UInt32Value)5U, Name = "appName" };
                TableColumn tableColumn6 = new TableColumn() { Id = (UInt32Value)6U, Name = "appVersion" };
                TableColumn tableColumn7 = new TableColumn() { Id = (UInt32Value)7U, Name = "workloadName" };
                TableColumn tableColumn8 = new TableColumn() { Id = (UInt32Value)8U, Name = "units" };
                TableColumn tableColumn9 = new TableColumn() { Id = (UInt32Value)9U, Name = "isBetter" };
                TableColumn tableColumn10 = new TableColumn() { Id = (UInt32Value)10U, Name = "nodes" };
                TableColumn tableColumn11 = new TableColumn() { Id = (UInt32Value)11U, Name = "time_Stamp" };
                TableColumn tableColumn12 = new TableColumn() { Id = (UInt32Value)12U, Name = "workloadResult" };
                TableColumn tableColumn13 = new TableColumn() { Id = (UInt32Value)13U, Name = "buildNotes" };
                tableColumns1.Append(tableColumn1);
                tableColumns1.Append(tableColumn2);
                tableColumns1.Append(tableColumn3);
                tableColumns1.Append(tableColumn4);
                tableColumns1.Append(tableColumn5);
                tableColumns1.Append(tableColumn6);
                tableColumns1.Append(tableColumn7);
                tableColumns1.Append(tableColumn8);
                tableColumns1.Append(tableColumn9);
                tableColumns1.Append(tableColumn10);
                tableColumns1.Append(tableColumn11);
                tableColumns1.Append(tableColumn12);
                tableColumns1.Append(tableColumn13);
                TableStyleInfo tableStyleInfo1 = new TableStyleInfo() { Name = "TableStyleLight17", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };

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

                //tableDefinitionPart1.Table = table1;
                return table1;
            }

    (NOTE: I know, the code can be shorter by looping.  This is just to make it functional before optimizing)

    It generates the following xl/table content:

    <?xml version="1.0" encoding="utf-8"?><x:table id="1" name="Table1" displayName="Table1" ref="A1:M27" totalsRowShown="0" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><x:autoFilter ref="A1:M26" /><x:tableColumns count="13"><x:tableColumn id="1" name="PlatformName" /><x:tableColumn id="2" name="domain" /><x:tableColumn id="3" name="industryVertical" /><x:tableColumn id="4" name="engineer" /><x:tableColumn id="5" name="appName" /><x:tableColumn id="6" name="appVersion" /><x:tableColumn id="7" name="workloadName" /><x:tableColumn id="8" name="units" /><x:tableColumn id="9" name="isBetter" /><x:tableColumn id="10" name="nodes" /><x:tableColumn id="11" name="time_Stamp" /><x:tableColumn id="12" name="workloadResult" /><x:tableColumn id="13" name="buildNotes" /></x:tableColumns><x:tableStyleInfo name="TableStyleLight17" showFirstColumn="0" showLastColumn="0" showRowStripes="1" showColumnStripes="0" /></x:table>

    You will notice it does not have any xr3:uid etc

    When I try reading the file using "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + gFName + ";Extended Properties=Excel 12.0" I get the following error:  External table is not in the expected format.

    I open the file in excel and save it back and the xl/table looks like this:

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <table xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="xr xr3" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" id="1" xr:uid="{00000000-000C-0000-FFFF-FFFF00000000}" name="Table1" displayName="Table1" ref="A1:M27" totalsRowShown="0"><autoFilter ref="A1:M27" xr:uid="{00000000-0009-0000-0100-000001000000}"/><tableColumns count="13"><tableColumn id="1" xr3:uid="{00000000-0010-0000-0000-000001000000}" name="PlatformName"/><tableColumn id="2" xr3:uid="{00000000-0010-0000-0000-000002000000}" name="domain"/><tableColumn id="3" xr3:uid="{00000000-0010-0000-0000-000003000000}" name="industryVertical"/><tableColumn id="4" xr3:uid="{00000000-0010-0000-0000-000004000000}" name="engineer"/><tableColumn id="5" xr3:uid="{00000000-0010-0000-0000-000005000000}" name="appName"/><tableColumn id="6" xr3:uid="{00000000-0010-0000-0000-000006000000}" name="appVersion"/><tableColumn id="7" xr3:uid="{00000000-0010-0000-0000-000007000000}" name="workloadName"/><tableColumn id="8" xr3:uid="{00000000-0010-0000-0000-000008000000}" name="units"/><tableColumn id="9" xr3:uid="{00000000-0010-0000-0000-000009000000}" name="isBetter"/><tableColumn id="10" xr3:uid="{00000000-0010-0000-0000-00000A000000}" name="nodes"/><tableColumn id="11" xr3:uid="{00000000-0010-0000-0000-00000B000000}" name="time_Stamp"/><tableColumn id="12" xr3:uid="{00000000-0010-0000-0000-00000C000000}" name="workloadResult"/><tableColumn id="13" xr3:uid="{00000000-0010-0000-0000-00000D000000}" name="buildNotes"/></tableColumns><tableStyleInfo name="TableStyleLight17" showFirstColumn="0" showLastColumn="0" showRowStripes="1" showColumnStripes="0"/></table>

    with all the unique IDs.

    Using this file works.

    How can I get this to happen on the initial output so the manual process of opening and re-saving doesn't have to happen?


    Wednesday, September 11, 2019 8:04 PM

All replies

  • I also changed the content-type to "Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";" at someones suggestion and it did not fix the issue.

    Joe

    Wednesday, September 11, 2019 8:16 PM