Search code examples
excelxlsxepplusnamed-rangesxlsm

Why my XLSM, XLSX have to be repaired after saving with EPPLUS?

After saving a file with EPPLUS that is an XLSM, or XLSX, when opening it, I received this message:

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

After yes, it would tell me:

Removed Records: Named range from /xl/workbook.xml part (Workbook) Error

How do you prevent these errors from popping up?

Solution

  • The problem was coming from the Named Ranges found here:

    enter image description here

    In my code, I convert all cells from formulas to their values so had no need of the Named Ranges:

    foreach (var cell in worksheet.Cells.Where(cell => cell.Formula != null))
{
    cell.Value = cell.Text;
}

    I then removed all Name Ranges. This solved my problem (I get no more popups!):

    int nameCount = xlPackage.Workbook.Names.Count();
for (int i = 0;i < nameCount;)
{
    xlPackage.Workbook.Names.Remove(xlPackage.Workbook.Names[i].Name.ToString());
    nameCount--;
}

    I sure as heck hope this helps somebody! I searched for days and days!

    If there is a more efficient way to do it, feel free to leave it below.