CODEWITHSUNDEEP
c#excelms-officeopenxmlopenxml-sdk
Kobojunkie
Kobojunkie

Reputation: 6545

Excel found unreadable content in *.xlsx

I am working on generating an excel template from code. When I run the piece to create my WorkBook, I get no errors in code, however when I go to open the Excel document, I get an error indicating that the file is unreadable. I am able to click to open it anyway, and I get the following message

Removed Records: Worksheet properties from /xl/workbook.xml part (Workbook)

Any idea what might be wrong in my code please?

public void CreatePackage()
{
    using (SpreadsheetDocument package = SpreadsheetDocument.Create(FilePath, SpreadsheetDocumentType.Workbook))
    {
        CreateParts(package);
    }
} 
private void CreateParts(SpreadsheetDocument document)
{
    ExcelWorkBook excelworkbook = new ExcelWorkBook();
    ExcelSheetHelper excelworksheet = new ExcelSheetHelper();
    ExcelSharedStringsTable excelsharedtable = new ExcelSharedStringsTable();
    ExcelWorkSheetPartBuilder excelworksheetbuilder = new ExcelWorkSheetPartBuilder();
    ExtendedFilePropertiesPart extendedFilePropertiesPart1 = document.AddNewPart<ExtendedFilePropertiesPart>("rId3");
    ExcelWorkSheetPartBuilder.GenerateExtendedFilePropertiesPart1Content(extendedFilePropertiesPart1);

    WorkbookPart workbookPart1 = document.AddWorkbookPart();
    excelworkbook.GenerateWorkbookPartContent(workbookPart1);


    WorkbookStylesPart workbookStylesPart1 = workbookPart1.AddNewPart<WorkbookStylesPart>("rId5");
    ExcelWorkBook.GenerateWorkbookStylesPart1Content(workbookStylesPart1);

    SetPackageProperties(document);
}

public void GenerateWorkbookPartContent(WorkbookPart workbookPart1)
{
    Workbook workbook = new Workbook();
    workbook.AddNamespaceDeclaration("r", rNameSpace);
    FileVersion fileVersion1 = GenerateFileVersion();
    WorkbookProperties workbookProperties1 = GenerateWorkbookProperties();

    BookViews bookViews1 = GenerateBookViews();

    Sheets sheets1 = GenerateSheets();

    DefinedNames definedNames1 = GenerateDefinedNames();

    CalculationProperties calculationProperties1 = GenerateCalculationProperties();

    CustomWorkbookViews customWorkbookViews1 = GenerateCustomWorkbookViews();


    workbook.Append(fileVersion1);
    workbook.Append(workbookProperties1);
    workbook.Append(bookViews1);
    workbook.Append(sheets1);
    workbook.Append(definedNames1);
    workbook.Append(calculationProperties1);
    workbook.Append(customWorkbookViews1);

    workbookPart1.Workbook = workbook;
}

// Creates an FileVersion instance and adds its children.
public FileVersion GenerateFileVersion()
{
    FileVersion fileVersion1 = new FileVersion() { ApplicationName = "xl", LastEdited = "5", LowestEdited = "5", BuildVersion = "9303" };
    return fileVersion1;
}

// Creates an WorkbookProperties instance and adds its children.
public WorkbookProperties GenerateWorkbookProperties()
{
    WorkbookProperties workbookProperties1 = new WorkbookProperties() { HidePivotFieldList = true };
    return workbookProperties1;
}

// Creates an BookViews instance and adds its children.
public BookViews GenerateBookViews()
{
    BookViews bookViews1 = new BookViews();
    WorkbookView workbookView1 = new WorkbookView() { XWindow = -75, YWindow = 270, WindowWidth = (UInt32Value)15435U, WindowHeight = (UInt32Value)6930U };

    bookViews1.Append(workbookView1);
    return bookViews1;
}

// Creates an Sheets instance and adds its children.
public Sheets GenerateSheets()
{
    Sheets sheets1 = new Sheets();
    Sheet sheet1 = new Sheet() { Name = String.Format("{0}", worksheetname), SheetId = (UInt32Value)8U, Id = "rId1" };

    sheets1.Append(sheet1);
    return sheets1;
}

// Creates an DefinedNames instance and adds its children.
public DefinedNames GenerateDefinedNames()
{
    DefinedNames definedNames1 = new DefinedNames();
    DefinedName definedName1 = new DefinedName() { Name = "_xlnm._FilterDatabase", LocalSheetId = (UInt32Value)0U, Hidden = true };
    definedName1.Text = String.Format("\'{0}\'!$A$6:$EO$1269", worksheetname);
    DefinedName definedName2 = new DefinedName() { Name = "Z_32BE30F1_B609_44A0_A38A_666CEFFB64E2_.wvu.Cols", LocalSheetId = (UInt32Value)0U, Hidden = true };
    definedName2.Text = String.Format("\'{0}\'!#REF!", worksheetname);
    DefinedName definedName3 = new DefinedName() { Name = "Z_32BE30F1_B609_44A0_A38A_666CEFFB64E2_.wvu.FilterData", LocalSheetId = (UInt32Value)0U, Hidden = true };
    definedName3.Text = String.Format("\'{0}\'!#REF!", worksheetname);
    DefinedName definedName4 = new DefinedName() { Name = "Z_5098B70B_692A_450A_8DAE_5172C296966E_.wvu.FilterData", LocalSheetId = (UInt32Value)0U, Hidden = true };
    definedName4.Text = String.Format("\'{0}\'!#REF!", worksheetname);
    DefinedName definedName5 = new DefinedName() { Name = "Z_7C00A233_927A_41FE_802C_48F5F9E9D5B6_.wvu.FilterData", LocalSheetId = (UInt32Value)0U, Hidden = true };
    definedName5.Text = String.Format("\'{0}\'!#REF!",worksheetname);
    DefinedName definedName6 = new DefinedName() { Name = "Z_AC112ED6_0017_40BF_884A_9B7959C37BF0_.wvu.FilterData", LocalSheetId = (UInt32Value)0U, Hidden = true };
    definedName6.Text = String.Format("\'{0}\'!#REF!", worksheetname);
    DefinedName definedName7 = new DefinedName() { Name = "Z_E444BF53_6DCE_4910_823C_F60AE88C96EE_.wvu.FilterData", LocalSheetId = (UInt32Value)0U, Hidden = true };
    definedName7.Text = String.Format("\'{0}\'!#REF!",worksheetname);

    definedNames1.Append(definedName1);
    definedNames1.Append(definedName2);
    definedNames1.Append(definedName3);
    definedNames1.Append(definedName4);
    definedNames1.Append(definedName5);
    definedNames1.Append(definedName6);
    definedNames1.Append(definedName7);
    return definedNames1;
}

// Creates an CalculationProperties instance and adds its children.
public CalculationProperties GenerateCalculationProperties()
{
    CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)125725U };
    return calculationProperties1;
}

// Creates an CustomWorkbookViews instance and adds its children.
public CustomWorkbookViews GenerateCustomWorkbookViews()
{
    CustomWorkbookViews customWorkbookViews1 = new CustomWorkbookViews();
    CustomWorkbookView customWorkbookView1 = new CustomWorkbookView() { Name = "A - Personal View", Guid = "{5098B70B-692A-450A-8DAE-5172C296966E}", MergeInterval = (UInt32Value)0U, PersonalView = true, Maximized = true, XWindow = 1, YWindow = 1, WindowWidth = (UInt32Value)1366U, WindowHeight = (UInt32Value)494U, ActiveSheetId = (UInt32Value)3U };
    CustomWorkbookView customWorkbookView2 = new CustomWorkbookView() { Name = "B - Personal View", Guid = "{7C00A233-927A-41FE-802C-48F5F9E9D5B6}", MergeInterval = (UInt32Value)0U, PersonalView = true, Maximized = true, XWindow = 1, YWindow = 1, WindowWidth = (UInt32Value)1024U, WindowHeight = (UInt32Value)487U, ActiveSheetId = (UInt32Value)3U };
    CustomWorkbookView customWorkbookView3 = new CustomWorkbookView() { Name = "C - Personal View", Guid = "{32BE30F1-B609-44A0-A38A-666CEFFB64E2}", MergeInterval = (UInt32Value)0U, PersonalView = true, Maximized = true, XWindow = 1, YWindow = 1, WindowWidth = (UInt32Value)1280U, WindowHeight = (UInt32Value)481U, ActiveSheetId = (UInt32Value)3U };

    customWorkbookViews1.Append(customWorkbookView1);
    customWorkbookViews1.Append(customWorkbookView2);
    customWorkbookViews1.Append(customWorkbookView3);
    return customWorkbookViews1;
}

public void ValidateDocument()
{
    try
    {
        OpenXmlValidator validator = new OpenXmlValidator();
        int count = 0;
        IDictionary<String, String> ErrorLog = new Dictionary<String, String>();

        using (StreamWriter f = new StreamWriter("Errolog.txt"))
        {
            foreach (ValidationErrorInfo error in validator.Validate(WordprocessingDocument.Open(FilePath, true)))
            {
                count++;
                f.WriteLine("Error " + count);
                f.WriteLine("Description: " + error.Description);
                f.WriteLine("Path: " + error.Path.XPath);
                f.WriteLine("Part: " + error.Part.Uri);
                f.WriteLine("-------------------------------------------");
                f.WriteLine("-------------------------------------------");
                f.WriteLine("-------------------------------------------");
            }
            f.Flush();
        }

    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

Upvotes: 7

Views: 19601

Answers (8)

Jeremy Styers
Jeremy Styers

Reputation: 520

Just in case someone finds this. It may also be because your sheet names are not unique. My issue was that, because of the char limit, 3 sheets wound up with the exact same name. When I repaired the file Excel renamed the duplicate sheets.

So if the above answers don't work, repair the file then look for a sheet named Recovered_Sheet1. That will tell you what sheet name has the issue (duplicate in my case).

Upvotes: 0

Artie Leech
Artie Leech

Reputation: 387

In our case, some of our worksheet names contained square brackets, e.g.:

Students [Primary]
Students [Secondary]

Replacing square brackets with round brackets fixed it:

Students (Primary)
Students (Secondary)

(For some reason, our customer didn't want these worksheets called Primary Students, Secondary Students, hence using brackets)

For us, the error message when opening the workbook in Excel was:

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

Clicking Yes then allowed Excel to open the workbook, and it displayed the message

Repaired Records: Worksheet properties from /xl/workbook.xml part (Workbook)

Upvotes: 0

chenop
chenop

Reputation: 5143

In my case the tab name consists single quote, i.e.: "my tab's".
I just omitted them:

title = title.replace(/'/g, "");

Upvotes: 0

Krishna
Krishna

Reputation: 8516

Yes, As Murugesan Said in the above comment, i fixed this error by renaming the file name of my excel document. For example: In my case , the name of the document was 'My Sample Export Data Sheet'. I have renamed the file to 'MySampleExportDataSheet' and everything worked well later.

Upvotes: 0

Glorfindel
Glorfindel

Reputation: 22641

I had this error as well, because I was using 0 as my SheetId, something like:

var sheet1 = new Sheet() { Name = "Test", SheetId = (UInt32Value)0U, Id = "rId1" };

Excel starts counting from 1, not 0. This is not only true for row numbers, it applies to sheet numbers as well.

Upvotes: 0

Andre Pena
Andre Pena

Reputation: 59336

In my specific case, I was having this problem because of too long Worksheet names. This is what was happening to me:

  • On the first run of my program, I was trying to creating Worksheets with really big names.
  • Excel automatically cropped the big names to 31 characters. No exception was thrown. That is, I thought I was saving the original big name but I was actually saving the 31 characters long cropped string.
  • On the second run of my program, I was checking if this specific Worksheet existed already, but I thought it didn't exist because the existing one was cropped.
  • I was saving the Worksheet again. No exception thrown, but then, the new one was also cropped and now the XML contains 2 definitions of the same worksheet.
  • This causes Excel to attempt to repair the resulting Spreadsheet. Even though it works properly, I think it was simply throwing away the second one and using the first, which wasn't what I wanted.

I fixed the problem by cropping the 31 characters beforehand, before all the comparisons. Now it works perfectly

Upvotes: 18

Murugesan
Murugesan

Reputation: 161

I know this question asked long ago, i thought my experience will solve someones problem. So am posting my answer here.

I had a similar problem. It occurs due the no. of characters in a sheet name exceeds the limit 31. Sheet name characters must be <= 31.

It won't throw any exception while creating, but gives error while opening in Microsoft Excel.

Upvotes: 9

Mike Zboray
Mike Zboray

Reputation: 40818

The Open XML SDK does not constrain you to producing a valid document. However, there is the OpenXmlValidator class which you can use to report any errors in the generated document. See this, which has a good example.

Upvotes: 2

Related Questions