How to fix error when I open my xlsx file creating with openXML : "repaired records : sheet properties in /xl/workbook.xml?

Question

I'm creating an xlsx file in my application with openXML. The file is created but when I want to open it with excel 2019 (I didn't try another excel) I have an error message that there is a problem. Thus I say "Yes" to repair the file and thus excel says "repaired record : sheet properties in the /xl/workbook.xml part".

Note that I use the almost same code to create xlsx file in an other part of the app and it works without error. the difference is that I'm working on a List> in the new part.

I tried to replace append by appendChild but I have an exception error. I tried to rearrange code lines to change the order creation of the different parts but always excel error.

public static byte[] ToOpenXml(HttpContext context, List<List<String>> data, String fileName, Boolean hasCount)
{
 Shared sharedClass = new Shared();
 using (MemoryStream memoryStream = new MemoryStream())
 {
  using (SpreadsheetDocument xlsxDocument = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook))
  {
    WorkbookPart workbookPart = xlsxDocument.AddWorkbookPart();
    Workbook workbook = new Workbook();
    workbookPart.Workbook = workbook;
    Sheets sheets = new Sheets();
    workbook.Append(sheets);
    Sheet sheet = new Sheet()
    {
      Name = fileName,
      SheetId = 1,
      Id = "rId1"
    };
    sheets.Append(sheet);
    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>("rId1");
    Worksheet worksheet = new Worksheet();
    SheetData sheetData = new SheetData();
    worksheet.Append(sheetData);
    worksheetPart.Worksheet = worksheet;

    //Index initialization
    UInt32 rowIndex = 1;
    Row row = new Row()
    {
      RowIndex = rowIndex
    };
    String value = String.Empty;
    foreach (List<String> datum in data)
    {
      UInt32 columnDataIndex = 1;
      row = new Row()
      {
        RowIndex = rowIndex
      };
      for (Int32 i = 0; i < datum.Count; i++)
      {
        Cell cell = CreateCell(columnDataIndex, rowIndex, CellValues.String, datum[i]);
        row.Append(cell);
        columnDataIndex = columnDataIndex + 1;
      }
      sheetData.Append(row);
      rowIndex = rowIndex + 1;
    }
  }
  return memoryStream.ToArray();
 }
}



public static Cell CreateCell(uint columnDataIndex, uint rowIndex, CellValues format, object value)
{
  string columnDataLetter = ColumnIndexToColumnLetter(columnDataIndex);
  String cellValue = "";
  Cell cell = new Cell()
  {
    CellReference = columnDataLetter + rowIndex,
    DataType = CellValues.String
  };
  switch (format)
  {
    case CellValues.Date:
      DateTime date = new DateTime();
      try
      {
        date = (DateTime)value;
      }
      catch (Exception e)
      {
      }
      cellValue = date.ToString(CultureInfo.InvariantCulture);
      break;
    default:
      cellValue = (String)value;
      break;
  }
  cell.CellValue = new CellValue(cellValue);
  return cell;
}

The xlsx file is created but when I open it I have to repair the file and "repaired record : sheet properties in the /xl/workbook.xml part". I would like to open in excel 2019 (the only one I tested) without error.

Thank you for helping me.

Answer 1

Finally, I solved the problem. The problem was due to the sheet name. I put the filename in it but the file name was longer than 31 characters which is the max length of a sheet name in excel file. I added this code line before the first using :

String sheetName = fileName.Substring(0,30);

and I put it in the declaration sheet :

Sheet sheet = new Sheet()
{
    Name = sheetName,
    SheetId = 1,
    Id="rId1"
};