Daniel Daniel - 3 months ago 40
ASP.NET (C#) Question

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

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

Answer

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 ;-)