Excel Found Unreadable Content - Recover Truncates

Question

I am building an Excel report in Visual Studio 2017 with EPPLus and one of my requirements is to attach all numbers linked with a certain ID to a single field, but when I try I get the following error:

"Excel found unreadable content in '[filenameHere.xlsx]'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes"

When I do so, it returns another info box that states:

"Excel was able to open the file by repairing or removing the unreadable content. Repaired Records: String properties from /xl/sharedString.xml part (Strings)" and it offers a link to log files which reads:

"

error178480_01.xmlErrors were detected in file 'C:[filenamehere].xlsx'Repaired Records: String properties from /xl/sharedStrings.xml part (Strings) " I am pulling about 3,000 records into the field but only about 1800 show up when I run the code and the information is truncated. Here is an example:

StringBuilder groupString = new StringBuilder();
        for (int i = 0; i <= 3000;i++)
            {
                groupString.Append("sampleNumber "+i.ToString()+",");
            }
        ExcelPackage exclPck = new ExcelPackage();
        ExcelWorksheet exclWs = exclPck.Workbook.Worksheets.Add("example");
        exclWs.Cells["A2"].Value= groupString.ToString();
        exclPck.SaveAs(new System.IO.FileInfo("C:\\filenamehere.xlsx"));

Coleman_M Coleman_M · Accepted Answer · 2017-10-03T20:24:20

The problem here lies in the fact that Excel has a character limit per cell of 32,767 characters. In returning 3000 numbers(if they're the same length as "samplenumber0000," or 17 characters) you'd be able to return around 1700 or so records per cell. By breaking it into a separate cell you could fix it by dividing up the results to accommodate the character limit. Here's an example of how I made Excel behave:

ExcelPackage exclPck = new ExcelPackage();
        ExcelWorksheet exclWs = exclPck.Workbook.Worksheets.Add("example");
        StringBuilder groupString = new StringBuilder();
        StringBuilder groupString2 = new StringBuilder();
        for (int i = 0; i <= 3000;i++)
            {
            if (groupString.Length <= 31740) {
                groupString.Append("sampleNumber" + i.ToString() + ",");
            }
            else
            {
                groupString2.Append("sampleNumber" + i.ToString() + ",");
            }
        }
        exclWs.Cells["A2"].Value= groupString.ToString();
        exclWs.Cells["A3"].Value = groupString2.ToString();
        exclPck.SaveAs(new System.IO.FileInfo("filenameHere.xlsx"));

As a general rule of data hygiene, I'd recommend splitting these types of results off into either their own attached workbook with said associated id in another column if you will be pulling back this number of results, you should leverage excels more forgiving total row limit (1,048,576) and separate these results out for better readability.

Excel Found Unreadable Content - Recover Truncates

1 Answers