Excel found unreadable content in *.xlsx
Solution 1
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
Solution 2
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.
Solution 3
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.
Kobojunkie
I am a dedicated Developer at heart coding and pushing my way to becoming a master. Would really appreciate if you those who are not willing to share their knowledge, without question, avoid even posting a responses to questions asked. Some of us are here to learn from those who have know.
Updated on June 05, 2022Comments
-
Kobojunkie almost 2 years
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); } }