Post
Ask Question
joe murawski

joe murawski

  • NA
  • 22
  • 0

adding uid in excel file using open xml

Sep 12 2019 3:22 PM
I output an xlsx with a table defined using the following code:
  1. protected void ExcelOut(string FName, DataTable table)  
  2. {  
  3. // Get table schema to ensure proper column formats  
  4. DataTable dtSchema = CommonFunctions.getSchema("WorkloadPerformanceResults_PV", UpdateEnvironment);  
  5. MemoryStream ms = new MemoryStream();  
  6. // Create a spreadsheet document by supplying the memorystream.  
  7. // By default, AutoSave = true, Editable = true, and Type = xlsx.  
  8. SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);  
  9. // Add a WorkbookPart to the document.  
  10. WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();  
  11. workbookpart.Workbook = new Workbook();  
  12. // Add a WorksheetPart to the WorkbookPart.  
  13. WorksheetPart worksheetPart = workbookpart.AddNewPart();  
  14. worksheetPart.Worksheet = new Worksheet(new SheetData());  
  15. // Add Sheets to the Workbook.  
  16. Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets());  
  17. // Append a new worksheet and associate it with the workbook.  
  18. Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "PerformanceDataResults" };  
  19. sheets.Append(sheet);  
  20. // Get the sheetData cell table.  
  21. SheetData sheetData = worksheetPart.Worksheet.GetFirstChild();  
  22. DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();  
  23. List columns = new List();  
  24. foreach (System.Data.DataColumn column in table.Columns)  
  25. {  
  26. columns.Add(column.ColumnName);  
  27. DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();  
  28. cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;  
  29. cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);  
  30. headerRow.AppendChild(cell);  
  31. }  
  32. sheetData.AppendChild(headerRow);  
  33. int NumRows = table.Rows.Count;  
  34. foreach (DataRow dsrow in table.Rows)  
  35. {  
  36. DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();  
  37. foreach (String col in columns)  
  38. {  
  39. string dataType = "";  
  40. foreach (DataRow SchemaDR in dtSchema.Rows)  
  41. {  
  42. string dcName = SchemaDR["COLUMN_NAME"].ToString().ToUpper();  
  43. string dsrowName = col.ToString().ToUpper();  
  44. if (dcName == dsrowName)  
  45. {  
  46. dataType = SchemaDR["DATA_TYPE"].ToString();  
  47. }  
  48. }  
  49. DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();  
  50. // Need to get the data type of the col  
  51. switch (dataType)  
  52. {  
  53. case "bit":  
  54. cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Boolean;  
  55. break;  
  56. case "date":  
  57. case "datetime":  
  58. case "datetime2":  
  59. case "smalldatetime":  
  60. case "DateTime":  
  61. cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Date;  
  62. break;  
  63. case "bigint":  
  64. case "int":  
  65. case "decimal":  
  66. case "float":  
  67. case "money":  
  68. case "numeric":  
  69. case "smallint":  
  70. case "smallmoney":  
  71. case "tinyint":  
  72. case "UInt32":  
  73. case "UInt64":  
  74. cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;  
  75. break;  
  76. case "nchar":  
  77. case "nvarchar":  
  78. case "text":  
  79. cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;  
  80. break;  
  81. default:  
  82. cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;  
  83. break;  
  84. }  
  85. cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //  
  86. newRow.AppendChild(cell);  
  87. }  
  88. sheetData.AppendChild(newRow);  
  89. }  
  90. // Add table to sheet  
  91. TableDefinitionPart tdp = worksheetPart.AddNewPart("rId1");  
  92. tdp.Table = GenerateTableDefinitionPart1Content(tdp, NumRows);  
  93. TableParts tableparts1 = new TableParts() { Count = 1 };  
  94. //TableParts tableparts1 = new TableParts();  
  95. TablePart tablepart1 = new TablePart() { Id = "rId1" };  
  96. tableparts1.Append(tablepart1);  
  97. worksheetPart.Worksheet.Append(tableparts1);  
  98. // Close the document.  
  99. spreadsheetDocument.Close();  
  100. // push memorystream to response  
  101. Response.Clear();  
  102. Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";  
  103. Response.AddHeader("Content-Disposition", "attachment; filename=" + FName + ".xlsx");  
  104. Response.BinaryWrite(ms.ToArray());  
  105. // myMemoryStream.WriteTo(Response.OutputStream); //works too  
  106. Response.Flush();  
  107. Response.Close();  
  108. Response.End();  
  109. }  
  110. private DocumentFormat.OpenXml.Spreadsheet.Table GenerateTableDefinitionPart1Content(TableDefinitionPart tableDefinitionPart1, int NumRows)  
  111. {  
  112. //'Table' is an ambiguous reference between 'DocumentFormat.OpenXml.Spreadsheet.Table' and 'System.Web.UI.WebControls.Table'  
  113. DocumentFormat.OpenXml.Spreadsheet.Table table1 = new DocumentFormat.OpenXml.Spreadsheet.Table() { Id = (UInt32Value)1U, Name = "Table1", DisplayName = "Table1", Reference = "A1:M27", TotalsRowShown = false };  
  114. AutoFilter autoFilter1 = new AutoFilter() { Reference = "A1:M" + NumRows.ToString() };  
  115. TableColumns tableColumns1 = new TableColumns() { Count = 13 };  
  116. TableColumn tableColumn1 = new TableColumn() { Id = (UInt32Value)1U, Name = "PlatformName" };  
  117. TableColumn tableColumn2 = new TableColumn() { Id = (UInt32Value)2U, Name = "domain" };  
  118. TableColumn tableColumn3 = new TableColumn() { Id = (UInt32Value)3U, Name = "industryVertical" };  
  119. TableColumn tableColumn4 = new TableColumn() { Id = (UInt32Value)4U, Name = "engineer" };  
  120. TableColumn tableColumn5 = new TableColumn() { Id = (UInt32Value)5U, Name = "appName" };  
  121. TableColumn tableColumn6 = new TableColumn() { Id = (UInt32Value)6U, Name = "appVersion" };  
  122. TableColumn tableColumn7 = new TableColumn() { Id = (UInt32Value)7U, Name = "workloadName" };  
  123. TableColumn tableColumn8 = new TableColumn() { Id = (UInt32Value)8U, Name = "units" };  
  124. TableColumn tableColumn9 = new TableColumn() { Id = (UInt32Value)9U, Name = "isBetter" };  
  125. TableColumn tableColumn10 = new TableColumn() { Id = (UInt32Value)10U, Name = "nodes" };  
  126. TableColumn tableColumn11 = new TableColumn() { Id = (UInt32Value)11U, Name = "time_Stamp" };  
  127. TableColumn tableColumn12 = new TableColumn() { Id = (UInt32Value)12U, Name = "workloadResult" };  
  128. TableColumn tableColumn13 = new TableColumn() { Id = (UInt32Value)13U, Name = "buildNotes" };  
  129. tableColumns1.Append(tableColumn1);  
  130. tableColumns1.Append(tableColumn2);  
  131. tableColumns1.Append(tableColumn3);  
  132. tableColumns1.Append(tableColumn4);  
  133. tableColumns1.Append(tableColumn5);  
  134. tableColumns1.Append(tableColumn6);  
  135. tableColumns1.Append(tableColumn7);  
  136. tableColumns1.Append(tableColumn8);  
  137. tableColumns1.Append(tableColumn9);  
  138. tableColumns1.Append(tableColumn10);  
  139. tableColumns1.Append(tableColumn11);  
  140. tableColumns1.Append(tableColumn12);  
  141. tableColumns1.Append(tableColumn13);  
  142. TableStyleInfo tableStyleInfo1 = new TableStyleInfo() { Name = "TableStyleLight17", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };  
  143. table1.Append(autoFilter1);  
  144. table1.Append(tableColumns1);  
  145. table1.Append(tableStyleInfo1);  
  146. //tableDefinitionPart1.Table = table1;  
  147. return table1;  
  148. }  
(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?