Receiving message of issue with content in Excel File

Question

I am currently building an Excel file by hand using OpenXml. I'm in the process of adding the sheets, however, I have come across an issue. I have a loop that adds the names of each sheet in but once it runs and I try to open the file, I get the following message:

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

I think the issue might be due that I am adding in the name of each sheet using a string variable. When I take it out and add something else, it works. Below is my code where I am looping through and adding my sheets.

//Technology Areas
foreach (DataRow dr in techAreaDS.Rows)
{
     var data = dr["TechAreaName"].ToString().Split('-');
     var techArea = data[2].TrimStart();

     var techAreaSheet = new Sheet { Id = workbookPart.GetIdOfPart(worksheetPart), 
                                     SheetId = sheetId, Name = techArea };
     sheets.Append(techAreaSheet);
     sheetId++;
}

I've seen people mention it is an issue with cells having strings that can be converted into strings, but in this case, the string will always be a string. Any help would be appreciated.

EDIT: I've figured out the problem. The issue is the Name property has a Max Length of 31. One of my items has a 42 length, hence the error. I did find a cool set of code to validate my OpenXml. Link.

UPDATE: Oddly enough, someone thinks this question was about finding some code to help validate what I was doing. It was not... The question is clear: why was I receiving an error when trying to name sheets. I was not asking for validation code, though I found some.

I do ask that if you wish to help, please read the question versus assume what I was asking, and if you don't know what I wish to have answered, ask...

Answer 1

In order to find out the issue(s) causing this error, you need to validate the generated document.

Besides using the built in validation method as described here, which doesn't show you all issues as I found out, I suggest that you download and install Microsoft's Open XML SDK 2.5 for Microsoft Office.

It contains Microsoft's Open XML SDK 2.5 Productivity Tool, which is very helpful here:

1. Create a copy of the damaged XLSX file, and apply the fixes as Microsoft Excel is suggesting (suppose you have the files FileName_corrupt.xlsx and FileName_fixed.xlsx)

2. Then, run Microsoft's Open XML SDK 2.5 Productivity Tool, open FileName_corrupt.xlsx, select "Compare Files" and specify the 2nd file FileName_fixed.xlsx. This allows you to compare the XML structure of both files.

3. Let Microsoft's Open XML SDK 2.5 Productivity Tool generate C# code from both files: Open them first, then right-click on the root level and select "Reflect Code". This will create C# code which allows you to generate the same file. Save both C# code versions (i.e. FileName_corrupt.cs and FileName_fixed.cs)

4. Now you can compare the differences via Visual Studio: Either use
   devenv.exe /diff FileName_corrupt.cs FileName_fixed.cs
   to compare them, or use the batch file I've created to launch the VS compare - this is a hidden feature in Visual Studio, it allows to compare 2 local files being not part of TFS.

This way you should be able to work out the differences and allow you to fix your code.

---

NOTE: For a first validation, I do suggest to use the validation code. Only if it still fails, use the steps above. For validation you can use

   public static string ValidateOpenXmlDocument(OpenXmlPackage pXmlDoc, bool throwExceptionOnValidationFail=false)
    {
        using (var docToValidate = pXmlDoc)
        {
            var validator = new DocumentFormat.OpenXml.Validation.OpenXmlValidator();
            var validationErrors = validator.Validate(docToValidate).ToList();
            var errors = new System.Text.StringBuilder();
            if (validationErrors.Any())
            {
                var errorMessage = string.Format("ValidateOpenXmlDocument: {0} validation error(s) with document", validationErrors.Count);
                errors.AppendLine(errorMessage);
                errors.AppendLine();
            }

            foreach (var error in validationErrors)
            {
                errors.AppendLine("Description: " + error.Description);
                errors.AppendLine("ErrorType: " + error.ErrorType);
                errors.AppendLine("Node: " + error.Node);
                errors.AppendLine("Path: " + error.Path.XPath);
                errors.AppendLine("Part: " + error.Part.Uri);
                if (error.RelatedNode != null)
                {
                    errors.AppendLine("Related Node: " + error.RelatedNode);
                    errors.AppendLine("Related Node Inner Text: " + error.RelatedNode.InnerText);
                }
                errors.AppendLine();
                errors.AppendLine("==============================");
                errors.AppendLine();
            }

            if (validationErrors.Any() && throwExceptionOnValidationFail)
            {
                throw new Exception(errors.ToString());
            }
            if (errors.Length > 0)
            {
                System.Diagnostics.Debug.WriteLine(errors.ToString());
            }
            return errors.ToString();
        }

along with

public static void ValidateExcelDocument(string fileName)
{
    using (var xlsx = SpreadsheetDocument.Open(fileName, true))
    {
        ValidateOpenXmlDocument(xlsx);
    }
}

With a slight modification, you can easily use the code above for Microsoft Word validation too:

public static void ValidateWordDocument(string fileName)
{
    using (var docx = WordprocessingDocument.Open(fileName, true))
    {
        ValidateOpenXmlDocument(docx);
    }
}

Answer 2

I've figured out the problem. The issue is the Name property has a Max Length of 31 characters. The text I'm trying to use sometimes exceeds that limit (one has 42 characters). I also found a pretty cool set of code to validate my Open Xml to find out what the specific issue is. Link