Why is my Excel Table programmaticaly created with OpenXml throwing errors when open file?

Question

EDIT : For the need of my project I'll work with EPPlus (Thanks to the Tsahi Asher's comment) package which fit perfectly with what we need to manipulate Excel and wrap OpenXML.
My problem for this project is so solved, but I'm still pretty curious about the answer of my question, so feel free to answer, would be really appreciated.

I'm working on a project where I have to generate an Excel file and then email it.
In order we have problems make Interop work for now, we're using OpenXML to create our Excel files, so it's pretty new to our team (1 month or something like that).

I don't have that many problems with creating the file, which is a simple document with 3 tables of two columns.
The problem is that when I try to open the .xslx file previously generated, I got a pop up which tell me:

We found a problem with some content in 'file.xlsx'. Do you want us to try to recover as much as we can ? If you trust the source of this workbook, click Yes.

If I click "Yes", it repairs the file and it shows up exactly as I'd like to, with a message:

Repaired Records: Table from /xl/tables/table2.xml part (Table)

Here's the short code where I create this table :

SpreadsheetDocument xl = SpreadsheetDocument.Create(pathID, SpreadsheetDocumentType.Workbook);
WorkbookPart wbp = xl.AddWorkbookPart();
WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
Workbook wb = new Workbook();
DocumentFormat.OpenXml.Spreadsheet.FileVersion fv = new DocumentFormat.OpenXml.Spreadsheet.FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet ws = new Worksheet();
SheetData sd = new SheetData();

WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
wbsp.Stylesheet = CreateStylesheet();
wbsp.Stylesheet.Save();

Columns columns = new Columns();
columns.Append(CreateColumnData(1, 1, 110));
columns.Append(CreateColumnData(2, 2, 20));
ws.Append(columns);

// Insert data in cells

ws.Append(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();

CultureInfo ciCurr = CultureInfo.CurrentCulture;
sheet.Name = "Week " + ciCurr.Calendar.GetWeekOfYear(now.AddDays(-7),
  CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);

TableParts tableParts = new TableParts() { Count = (UInt32Value)3U };
tableParts.Append(new TablePart() { Id = "rId1" });
tableParts.Append(new TablePart() { Id = "rId2" });
tableParts.Append(new TablePart() { Id = "rId3" });

wsp.Worksheet.Append(tableParts);

// First Table creation, exactly same as 2nd except column names

// Beginning of second table creation
TableDefinitionPart tableDefinitionPart2 = wsp.AddNewPart<TableDefinitionPart>("rId2");
var tableDimension2 = "A" + _savedIndex1 + ":B" + (_savedIndex1 + 4);
Table table2 = new Table()
    {
        Id = (UInt32Value)2U,
        Name = "Table2",
        DisplayName = "Table2",
        Reference = tableDimension2,
        TotalsRowShown = false,
    };

TableColumns tableColumns2 = new TableColumns() { Count = (UInt32Value)2U };

TableColumn tableColumn12 = new TableColumn() { Id = (UInt32Value)1U, Name = "Product Name" };
TableColumn tableColumn22 = new TableColumn() { Id = (UInt32Value)2U, Name = "Total qty" };

tableColumns2.Append(tableColumn12);
tableColumns2.Append(tableColumn22);

TableStyleInfo tableStyleInfo2 = new TableStyleInfo()
    {
        Name = "TableStyleLight1",
        ShowFirstColumn = false,
        ShowLastColumn = false,
        ShowRowStripes = true,
        ShowColumnStripes = false,
    };

table2.Append(tableColumns2);
table2.Append(tableStyleInfo2);
tableDefinitionPart2.Table = table2;
// End of second table creation

// Third Table creation, exactly same as 2nd except column names

xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();

I already tried to directly search in xml files by unzipping the .xlxs but I didn't noticed any differences between this Table2 and Table1/Table3.
Even after Excel repaired the file, I tried to unzip again and what Excel added to Table2 was also added to Table1/Table3, so I really have no guess why this Table2 is stopping this file to open properly.
I also tried to remove Table2 and left Table1/Table3 as they are (only change id) and it worked perfectly, so I'm pretty sure that my problem really comes from Table2.

So, where does my problem come from? What's my mistakes and what have I missed?

Any hint would be appreciated as OpenXml is pretty new to me.

Answer 1

When adding a table, make sure that the names of the column headers are not repeated. If you have duplicates column names Excel throw this error and will fix it by renaming the 2nd duplicated column by adding a number a the end. See example below:

| QTY | UNIT # | QTY2 |

See that 2nd QTY was renamed to QTY2 by excel recovery process.