Excel Open XML error: "found unreadable content" when creating simple example

Question

I am getting the ambiguous "excel found unreadable content" error when I try to open the document created by the following code:

public void GenerateWorkbookFromDB()
{
    //Make a copy of the template file
    File.Copy(HttpContext.Current.Server.MapPath("ReportTemplate/test.xlsx"), HttpContext.Current.Server.MapPath("Reports/test.xlsx"), true);

    //Open up the copied template workbook
    using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(HttpContext.Current.Server.MapPath("Reports/test.xlsx"), true))
    {
        WorkbookPart workbookPart = myWorkbook.WorkbookPart;
        WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
        string origninalSheetId = workbookPart.GetIdOfPart(worksheetPart);

        WorksheetPart replacementPart = workbookPart.AddNewPart<WorksheetPart>();
        string replacementPartId = workbookPart.GetIdOfPart(replacementPart);

        OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
        OpenXmlWriter writer = OpenXmlWriter.Create(replacementPart);

        Row r = new Row();
        Cell c = new Cell();
        CellValue v = new CellValue();
        v.Text = "test";
        c.Append(v);

        while (reader.Read())
        {
            if (reader.ElementType == typeof(SheetData))
            {
                if (reader.IsEndElement)
                    continue;
                writer.WriteStartElement(new SheetData());

                for (int row = 0; row < 20; row++)
                {
                    writer.WriteStartElement(r);

                    for (int col = 0; col < 4; col++)
                    {
                        writer.WriteElement(c);
                    }

                    writer.WriteEndElement();
                }

                writer.WriteEndElement();
            }
            else
            {
                if (reader.IsStartElement)
                    writer.WriteStartElement(reader);
                else if (reader.IsEndElement)
                    writer.WriteEndElement();
            }
        }
        reader.Close();
        writer.Close();

        try
        {
            Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Id.Value.Equals(origninalSheetId)).First();
            sheet.Id.Value = replacementPartId;
            workbookPart.DeletePart(worksheetPart);
        }
        catch (Exception ex) { }
    }
}

any help or suggestions is much appreciated! :D

Curb

Where exactly do you get this error? When you look at the xlsx in the Office Open Xml Tool, what is the xml at that particular location? – Muriel

When I try to open the document after it calls GenerateWorkbookFromDB() Excel gives me the error. It gives me the option to open it anyway by clicking "Yes". I don't want the users of my application to have to click that every time though :D – Curb

Answer 1

I actually found a way to fix the error by changing the way I input the text into the cell itself. Notice in the code below where I commented out the 2 lines and what I replaced them with.

public void GenerateWorkbookFromDB()
{
    //Make a copy of the template file
    File.Copy(HttpContext.Current.Server.MapPath("ReportTemplate/test.xlsx"), HttpContext.Current.Server.MapPath("Reports/test.xlsx"), true);

    //Open up the copied template workbook
    using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(HttpContext.Current.Server.MapPath("Reports/test.xlsx"), true))
    {
        WorkbookPart workbookPart = myWorkbook.WorkbookPart;
        WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
        string origninalSheetId = workbookPart.GetIdOfPart(worksheetPart);

        WorksheetPart replacementPart = workbookPart.AddNewPart<WorksheetPart>();
        string replacementPartId = workbookPart.GetIdOfPart(replacementPart);

        OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
        OpenXmlWriter writer = OpenXmlWriter.Create(replacementPart);

        Row r = new Row();
        Cell c = new Cell();
        string txt = "test";
        c.CellValue = new CellValue(txt.ToString());
        c.DataType = new EnumValue<CellValues>(CellValues.String);
        //v.Text = "test";
        //c.Append(v);

        while (reader.Read())
        {
            if (reader.ElementType == typeof(SheetData))
            {
                if (reader.IsEndElement)
                    continue;
                writer.WriteStartElement(new SheetData());

                for (int row = 0; row < 20; row++)
                {
                    writer.WriteStartElement(r);

                    for (int col = 0; col < 4; col++)
                    {
                        writer.WriteElement(c);
                    }

                    writer.WriteEndElement();
                }

                writer.WriteEndElement();
            }
            else
            {
                if (reader.IsStartElement)
                    writer.WriteStartElement(reader);
                else if (reader.IsEndElement)
                    writer.WriteEndElement();
            }
        }
        reader.Close();
        writer.Close();

        try
        {
            Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Id.Value.Equals(origninalSheetId)).First();
            sheet.Id.Value = replacementPartId;
            workbookPart.DeletePart(worksheetPart);
        }
        catch (Exception ex) { }
    }
}

I hope this helps anyone else who might be experiencing the same issue or something similar.

Thanks to those who tried to answer ;-)

Curb

Was looking around for ages for this, cheers Daniel, my team leader was getting very annoyed at me :( but i'm very happy I found this – Glorygloryofthesnow

Any idea how to fix the error for this answer: #19529936... Although when I click YES to open the file, everything is as is but I can't do any other modification to the file. – Designer

Answer 2

I hope it might be useful for somebody.

I got the same error message and in my case the reason turned out to be a too long name of a worksheet.

Excel kept truncating it to 31 character. So once I limited the worksheet name I assigned in code to 31 characters the problem had been resolved.

Earley

Thanks a lot for pointing this out, saved me at least an hour or more. – Hat

Answer 3

What I found was that you need to make sure that when you add the Cell objects to the row collection, that you place them in the proper order. They must appear in the same order that they will show on the spreadsheet. e.g. A2, B2, C2 ... Z2, AA2, AB2. Note that if you try to compare the values of the columns to place them they will sort with AA2 between A2 and B2 which will cause an error whey you try to open the sheet.

Bavardage

Answer 4

I corrected it by setting the correct type on Cell. For instance, in my case I have two types of values: Numerics and Strings.

 public static void WriteValueOnCell(Cell cell, object value)
    {
        var sValue = value = x.ToString();
        var isValueNumeric = value.GetType().IsNumeric();
        cell.DataType = (isValueNumeric)? CellValues.Number : CellValues.String;
        cell.CellValue = new CellValue(sValue);
    }
    //This example uses this Helper. It informs if an object type is Numeric ;-)
    public static class TypeHelper
    {
        private static readonly HashSet<Type> NumericTypes = new HashSet<Type>
        {
            typeof(int),  typeof(double),  typeof(decimal),
            typeof(long), typeof(short),   typeof(sbyte),
            typeof(byte), typeof(ulong),   typeof(ushort),
            typeof(uint), typeof(float)
        };

        public static bool IsNumeric(this Type myType)
        {
            return NumericTypes.Contains(Nullable.GetUnderlyingType(myType) ?? myType);
        }
    }

Mealie

Any idea how to fix the error for this answer: #19529936... Although when I click YES to open the file, everything is as is but I can't do any other modification to the file. – Designer

Answer 5

in the workbook, are there any characters that are reserved in the data fields? such as '<' or '>'? I've seen that happen with XML parsing, so it could be an illegal character. I don't know if your circumstances permit it, but would HTTPUtility.HTMLEncode work?

Jaramillo

The workbook it is copying and putting everything in is from a blank template. I don't see how anything would get put into any cells but "test". – Curb

good catch Daniel. I don't know either, I was just taking a stab in the dark in regards to XML. – Jaramillo

Answer 6

What worked for me was to modify the web.config file.

I put in a maxRequestLength and executionTimeout and it worked fine after that!

under System.Web, put the following:

httpRuntime requestValidationMode="2.0" maxRequestLength="1048576" executionTimeout="600"

Give it a try and see if it helps.

Yerkovich

Answer 7

I had this issue and after using the SDK tool I discovered that CellValues.Date isn't actually supported. If you're trying to format your cells to have the appropriate DataType and you're getting this message, try leaving your date cells as CellValues.String.

Subtype