title: Adding Table to openxml spreadsheet causes excel to repair workbook when opening
body: <p>I'm working on a small excel export. All the data is there but adding a spreadsheet table for the range in question to provide quick and easy access to sort&amp;filter when the user opens the file in excel causes a problem. The problem is that when the file is opened in excel <code>we found a problem with some content in...</code> and a query if they <code>should try to recover as much as we can</code>.</p>
  <p>After repairing it excel states that it <code>was able to open the file by repairing or removing the unreadable content</code>. with <code>Repaired Records: Table from /xl/tables/table1.xml part (Table)</code> listed in a text field below. After this the workbook behaves as I intended, with a table.</p>
  <p>I have searched and tried almost everything I've stumbled upon. <a href="https://stackoverflow.com/questions/53440352/openxml-table-creation-how-do-i-create-tables-without-requiring-excel-to-repa">This SO question</a> is very similar but the proposed answer does not work for me.</p>
  <p>A minimal example with the problem is <a href="https://github.com/CalculationCycle/openxml-spreadsheet-with-table" rel="nofollow noreferrer">here</a>. It generates two excel files, one with the table added and one without. The one without is previewable in Explorer, the problematic one  displays the message:</p>
  <blockquote>
  <p><em>The file can't be previewed because of an error in the Microsoft Excel previewer.</em></p>
  </blockquote>
  <p>Nuget used: DocumentFormat.OpenXml version 2.14.0. Excel version is office365, desktop version. Running under Windows 10.</p>
  <p>What I have tried:</p>
  <ul>
  <li>Various openxml validation tools, no errors found</li>
  <li>Saved the (by excel) repaired file and compared the content of those two. I don't find any difference that in my head can serve as an explanation</li>
  </ul>
  <p>It would be very nice to have the table already in place when the excel file is generated.</p>
  <p>Program.cs</p>
  <pre><code>using System;
  using System.Collections.Generic;
  using System.IO;
  using ExcelGenerator;
  
  namespace ConsoleAppOpenXmlSpreadsheetWithTable
  {
      class Program
      {
          static void Main(string[] args)
          {
              var headerRow = new List&lt;string&gt; { &quot;Company&quot;, &quot;Country&quot;, &quot;Fruit&quot; };
              var exportRowList = new List&lt;ExcelExportRow&gt;();
              exportRowList.Add(new ExcelExportRow() { Company =&quot;Spanish Oranges A&quot;, Country = &quot;Spain&quot;, Fruit =&quot;Oranges&quot;});
              exportRowList.Add(new ExcelExportRow() { Company = &quot;Mexican Oranges Ltd&quot;, Country = &quot;Mexico&quot;, Fruit = &quot;Oranges&quot; });
              exportRowList.Add(new ExcelExportRow() { Company = &quot;Medit. Bananas&quot;, Country = &quot;Spain&quot;, Fruit = &quot;Bananas&quot; });
              exportRowList.Add(new ExcelExportRow() { Company = &quot;Mexican Bananas&quot;, Country = &quot;Mexico&quot;, Fruit = &quot;Bananas&quot; });
              exportRowList.Add(new ExcelExportRow() { Company = &quot;Mexican Sweet Oranges&quot;, Country = &quot;Mexico&quot;, Fruit = &quot;Oranges&quot; });
              using (var memoryStream = ExcelFileGenerator.GenerateArticleListWithImagesExcelFile(headerRow, exportRowList, false))
              {
                  using (var fileStream = new FileStream(&quot;OpenXmlSpreadsheet.xlsx&quot;, FileMode.Create))
                  {
                      memoryStream.CopyTo(fileStream);
                  }
              }
              using (var memoryStream = ExcelFileGenerator.GenerateArticleListWithImagesExcelFile(headerRow, exportRowList, true))
              {
                  using (var fileStream = new FileStream(&quot;OpenXmlSpreadsheetWithTable.xlsx&quot;, FileMode.Create))
                  {
                      memoryStream.CopyTo(fileStream);
                  }
              }
          }
      }
  }
  </code></pre>
  <p>ExcelFileGenerator.cs</p>
  <pre><code>using System;
  using System.Collections.Generic;
  using System.IO;
  using DocumentFormat.OpenXml;
  using DocumentFormat.OpenXml.Packaging;
  using DocumentFormat.OpenXml.Spreadsheet;
  
  namespace ExcelGenerator
  {
      public class ExcelExportRow
      {
          public string Company { get; set; }
          public string Country { get; set; }
          public string Fruit { get; set; }
      }
  
      public static class ExcelFileGenerator
      {
          private const int normalFontStyleIndex = 0;
          private const int boldFontStyleIndex = 1;
  
          public static MemoryStream GenerateArticleListWithImagesExcelFile(List&lt;string&gt; headerRow, List&lt;ExcelExportRow&gt; excelExportRoListw, bool insertTable)
          {
              MemoryStream memoryStream = new MemoryStream();
  
              using (SpreadsheetDocument document = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook))
              {
                  WorkbookPart workbookPart = document.AddWorkbookPart();
                  Workbook workbook = new Workbook();
                  workbookPart.Workbook = workbook;
  
                  WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart&lt;WorkbookStylesPart&gt;(&quot;rIdStyles&quot;);
  
                  Stylesheet stylesheet = StyleSheet();
                  workbookStylesPart.Stylesheet = stylesheet;
                  workbookStylesPart.Stylesheet.Save();
                  WorksheetPart worksheetPart = workbookPart.AddNewPart&lt;WorksheetPart&gt;();
                  Worksheet worksheet = new Worksheet();
                  SheetData sheetData = new SheetData();
  
                  Sheets sheets = new Sheets();
  
                  //get the string name of the columns
                  string[] excelColumnNamesTitle = new string[5];
                  for (int n = 0; n &lt; 5; n++)
                      excelColumnNamesTitle[n] = GetExcelColumnName(n);
  
                  var columns = new Columns();
                  columns.Append(new Column() { Min = 1, Max = 1, CustomWidth = true, Width = 24 });
                  columns.Append(new Column() { Min = 2, Max = 3, CustomWidth = true, Width = 12 });
                  worksheet.Append(columns);
  
                  if (excelExportRoListw is not null)
                  {
                      Row rowTitle = new Row() { RowIndex = (UInt32Value)1 };
                      for (int c = 0; c &lt; headerRow.Count; c++)
                      {
                          AppendTextCell(excelColumnNamesTitle[c], headerRow[c], rowTitle, boldFontStyleIndex);
                      }
                      sheetData.Append(rowTitle);
  
                      uint rowNumber = 2;
                      foreach (var excelExportRow in excelExportRoListw)
                      {
                          var spreadsheetRow = new Row() { RowIndex = (UInt32Value)rowNumber };
                          AppendTextCell(excelColumnNamesTitle[0], excelExportRow.Company, spreadsheetRow, normalFontStyleIndex);
                          AppendTextCell(excelColumnNamesTitle[2], excelExportRow.Country, spreadsheetRow, normalFontStyleIndex);
                          AppendTextCell(excelColumnNamesTitle[3], excelExportRow.Fruit, spreadsheetRow, normalFontStyleIndex);
                          sheetData.Append(spreadsheetRow);
                          rowNumber++;
                      }
  
                      worksheet.Append(sheetData);
                      worksheetPart.Worksheet = worksheet;
                  }
  
                  // Table
                  if (insertTable)
                      DefineTable(worksheetPart, 1, 1 + excelExportRoListw.Count, 0, 2);
  
                  // Wrap up and save
                  worksheetPart.Worksheet.Save();
  
                  Sheet sheet = new Sheet() { Name = &quot;Export&quot;, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) };
                  sheets.Append(sheet);
                  workbook.Append(sheets);
                  workbook.Save();
                  document.Close();
              }
  
              memoryStream.Position = 0;
              return memoryStream;
          }
  
  
          private static void DefineTable(WorksheetPart worksheetPart, int rowMin, int rowMax, int colMin, int colMax)
          {
              #region Table
              string rangeReference = $&quot;{GetExcelColumnName(colMin)}{rowMin}:{GetExcelColumnName(colMax)}{rowMax}&quot;;
              int tableNo = 1;
  
              Table table = new Table()
              {
                  Id = (UInt32)tableNo,
                  Name = &quot;Table&quot; + tableNo,
                  DisplayName = &quot;Table&quot; + tableNo,
                  Reference = rangeReference,
                  TotalsRowShown = false
              };
  
              AutoFilter autoFilter = new AutoFilter() { Reference = rangeReference };
  
              TableColumns tableColumns = new TableColumns() { Count = (UInt32)(colMax - colMin + 1) };
              for (int i = 0; i &lt; (colMax - colMin + 1); i++)
              {
                  tableColumns.Append(new TableColumn() { Id = (UInt32)(i + 1), Name = &quot;Column&quot; + i });
              }
  
              TableStyleInfo tableStyleInfo = new TableStyleInfo()
              {
                  Name = &quot;TableStyleMedium1&quot;,
                  ShowFirstColumn = false,
                  ShowLastColumn = false,
                  ShowRowStripes = true,
                  ShowColumnStripes = false
              };
  
              table.Append(autoFilter);
              table.Append(tableColumns);
              table.Append(tableStyleInfo);
              #endregion
  
              string tableRefId = &quot;rIdTable1&quot;;
  
              // Add table to worksheet - two different things implictly linked by using the same refId
              TableDefinitionPart tableDefinitionPart = worksheetPart.AddNewPart&lt;TableDefinitionPart&gt;(tableRefId);
              tableDefinitionPart.Table = table;
              
              TableParts tableParts = new TableParts();
              TablePart tablePart = new TablePart() { Id = tableRefId };
              tableParts.Append(tablePart);
              tableParts.Count = 1;
              worksheetPart.Worksheet.Append(tableParts);
          }
  
          #region Excel Helper funcs
          private static void AppendTextCell(string cellReference, string cellStringValue, Row excelRow, UInt32Value styleIndex)
          {
              //  Add a new Excel Cell to our Row
              Cell cell = new Cell() { CellReference = cellReference, DataType = CellValues.String };
              CellValue cellValue = new CellValue();
              cellValue.Text = cellStringValue;
              cell.StyleIndex = styleIndex;
              cell.Append(cellValue);
              excelRow.Append(cell);
          }
  
          private static string GetExcelColumnName(int columnIndex)
          {
              if (columnIndex &lt; 26)
                  return ((char)('A' + columnIndex)).ToString();
  
              char firstChar = (char)('A' + (columnIndex / 26) - 1);
              char secondChar = (char)('A' + (columnIndex % 26));
  
              return string.Format(&quot;{0}{1}&quot;, firstChar, secondChar);
          }
          #endregion Helper funcs
  
          #region Stylesheet
          private static Stylesheet StyleSheet()
          {
              Stylesheet styleSheet = new Stylesheet();
  
              Fonts fonts = new Fonts();
              // 0 - normal fonts
              Font myFont = new Font()
              {
                  FontSize = new FontSize() { Val = 11 },
                  Color = new Color() { Rgb = HexBinaryValue.FromString(&quot;FF000000&quot;) },
                  FontName = new FontName() { Val = &quot;Arial&quot; }
              };
              fonts.Append(myFont);
  
              //1 - font bold
              myFont = new Font()
              {
                  Bold = new Bold(),
                  FontSize = new FontSize() { Val = 11 },
                  Color = new Color() { Rgb = HexBinaryValue.FromString(&quot;FF000000&quot;) },
                  FontName = new FontName() { Val = &quot;Arial&quot; }
              };
              fonts.Append(myFont);
  
  
              Fills fills = new Fills();
              //default fill
              Fill fill = new Fill()
              {
                  PatternFill = new PatternFill() { PatternType = PatternValues.None }
              };
              fills.Append(fill);
  
              Borders borders = new Borders();
              //normal borders
              Border border = new Border()
              {
                  LeftBorder = new LeftBorder(),
                  RightBorder = new RightBorder(),
                  TopBorder = new TopBorder(),
                  BottomBorder = new BottomBorder(),
                  DiagonalBorder = new DiagonalBorder()
              };
              borders.Append(border);
  
              CellFormats cellFormats = new CellFormats();
              //0- normalFontStyleIndex for normal cells
              CellFormat cellFormat = new CellFormat()
              {
                  FontId = 0,
                  FillId = 0,
                  BorderId = 0,
                  ApplyFill = false
              };
              cellFormats.Append(cellFormat);
  
              //1 - boldFontStyleIndex for header row
              cellFormat = new CellFormat()
              {
                  FontId = 1,
                  FillId = 0,
                  BorderId = 0,
                  ApplyFill = false
              };
              cellFormats.Append(cellFormat);
  
              styleSheet.Append(fonts);
              styleSheet.Append(fills);
              styleSheet.Append(borders);
              styleSheet.Append(cellFormats);
  
              TableStyles tableStyles = new TableStyles()
              {
                  Count = 0,
                  DefaultTableStyle = &quot;TableStyleMedium1&quot;,
                  DefaultPivotStyle = &quot;PivotStyleLight16&quot;
              };
              styleSheet.Append(tableStyles);
  
              return styleSheet;
          }
          #endregion Stylesheet
      }
  }
  </code></pre>