Excel problems & solutions

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error088240_01.xml</logFileName><summary>Errors were detected in file 'C:\Users\Noname\Downloads\Test.xlsx'</summary><repairedRecords><repairedRecord>Repaired Records: Worksheet properties from /xl/workbook.xml part (Workbook)</repairedRecord><repairedRecord>Repaired Records: Cell information from /xl/worksheets/sheet1.xml part</repairedRecord></repairedRecords></recoveryLog>

Error Message 2:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error172080_02.xml</logFileName><summary>Errors were detected in file 'C:\Users\Noname\Desktop\Test.xlsx'</summary><removedRecords><removedRecord>Removed Records: Worksheet properties from /xl/workbook.xml part (Workbook)</removedRecord></removedRecords></recoveryLog>

Cause:

Duplicate sheet's name was found in ConsoleApp1 code sample project in this page.

Solution:

Generate the unique sheet's name

Excel workbooks contain individual worksheets and the number of worksheets in a workbook is limited only by the system memory of your computer. This article is all about the names you can give to a worksheet.

Naming Syntax

By default, a new workbook contains 3 worksheets and they are named; Sheet1, Sheet2, Sheet3. You can add new worksheets and clone existing ones and all the worksheets can be renamed. There are however a few rules when naming worksheets.

The name must be unique within a single workbook.
A worksheet name cannot exceed 31 characters.
You can use all alphanumeric characters but not the following special characters:
\ , / , * , ? , : , [ , ].
You can use spaces, underscores (_) and periods (.) in the name as word separators.

Important: Worksheet names cannot:

Be blank.
Contain more than 31 characters.
Contain any of the following characters: / \ ? * : [ ]

For example, 02/17/2016 would not be a valid worksheet name, but 02-17-2016 would work fine.
Begin or end with an apostrophe ('), but they can be used in between text or numbers in a name.
Be named "History". This is a reserved word Excel uses internally.

Code to show the errors in an Excel file (source code):

https://csharp.hotexamples.com/examples/-/SpreadsheetDocument/AddWorkbookPart/php-spreadsheetdocument-addworkbookpart-method-examples.html

Errors were detected in file with an Excel file after creating it with OpenXML

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Validation;
using System;

namespace ConsoleApp1
{
 internal class Program
 {
 private static void Main(string[] args)
 {
 string filePath = @"C:\Users\nvmanh\Desktop\Test.xlsx";
 ValidateExcelDocument(filePath);

 string filePath1 = @"C:\Users\nvmanh\Desktop\Test1.xlsx";
 ValidateExcelDocument(filePath1);

 string filePath2 = @"C:\Users\nvmanh\Desktop\Test2.xlsx";
 ValidateExcelDocument(filePath2);

 Console.ReadKey();
 }

 public static void ValidateExcelDocument(string filepath)
 {
 using (SpreadsheetDocument wordprocessingDocument =
 SpreadsheetDocument.Open(filepath, true))
 {
 try
 {
 OpenXmlValidator validator = new OpenXmlValidator();
 int count = 0;
 foreach (ValidationErrorInfo error in
 validator.Validate(wordprocessingDocument))
 {
 count++;
 Console.WriteLine("Error " + count);
 Console.WriteLine("Description: " + error.Description);
 Console.WriteLine("ErrorType: " + error.ErrorType);
 Console.WriteLine("Node: " + error.Node);
 Console.WriteLine("Path: " + error.Path.XPath);
 Console.WriteLine("Part: " + error.Part.Uri);
 Console.WriteLine("-------------------------------------------");
 }

 Console.WriteLine("count={0}", count);
 }
 catch (Exception ex)
 {
 Console.WriteLine(ex.Message);
 }

 wordprocessingDocument.Close();
 }
 }
 }
}

Code sample: Create the Excel file using OpenXML

https://gist.github.com/kzelda/2facdff2d924349fe96c37eab0e9ee47

https://www.c-sharpcorner.com/article/creating-excel-file-using-openxml/

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.IO;
using X14 = DocumentFormat.OpenXml.Office2010.Excel;
using X15 = DocumentFormat.OpenXml.Office2013.Excel;

namespace ConsoleApp2
{
 /// <summary>
 /// Install DocumentFormat.OpenXml nuget package
 /// </summary>
 internal class Program
 {
 private static void Main(string[] args)
 {
 TestModelList tmList = new TestModelList();

 tmList.TestData = new List<TestModel>();
 TestModel tm = new TestModel();
 tm.TestId = 1;
 tm.TestName = "Test1";
 tm.TestDesc = "Tested 1 time";
 tm.TestDate = DateTime.Now.Date;
 tmList.TestData.Add(tm);

 TestModel tm1 = new TestModel();
 tm1.TestId = 2;
 tm1.TestName = "Test2";
 tm1.TestDesc = "Tested 2 time";
 tm1.TestDate = DateTime.Now.AddDays(-1);
 tmList.TestData.Add(tm1);

 TestModel tm2 = new TestModel();
 tm2.TestId = 3;
 tm2.TestName = "Test3";
 tm2.TestDesc = "Tested 3 time";
 tm2.TestDate = DateTime.Now.AddDays(-2);
 tmList.TestData.Add(tm2);

 TestModel tm3 = new TestModel();
 tm3.TestId = 4;
 tm3.TestName = "Test4";
 tm3.TestDesc = "Tested 4 time";
 tm3.TestDate = DateTime.Now.AddDays(-3);
 tmList.TestData.Add(tm);

 Program p = new Program();

 p.CreateExcelFile(tmList, @"C:\Users\nvmanh\Desktop\");
 }

 public class TestModel
 {
 public int TestId { get; set; }
 public string TestName { get; set; }
 public string TestDesc { get; set; }
 public DateTime TestDate { get; set; }
 }

 public class TestModelList
 {
 public List<TestModel> TestData { get; set; }
 }

 public void CreateExcelFile(TestModelList data, string outPutFileDirectory)
 {
 var datetime = DateTime.Now.ToString().Replace("/", "_").Replace(":", "_");

 string fileFullname = Path.Combine(outPutFileDirectory, "Output.xlsx");

 if (File.Exists(fileFullname))
 {
 fileFullname = Path.Combine(outPutFileDirectory, "Output_" + datetime + ".xlsx");
 }

 using (SpreadsheetDocument package = SpreadsheetDocument.Create(fileFullname, SpreadsheetDocumentType.Workbook))
 {
 CreatePartsForExcel(package, data);
 }
 }

 private void CreatePartsForExcel(SpreadsheetDocument document, TestModelList data)
 {
 SheetData partSheetData = GenerateSheetdataForDetails(data);

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

 WorkbookStylesPart workbookStylesPart1 = workbookPart1.AddNewPart<WorkbookStylesPart>("rId3");
 GenerateWorkbookStylesPartContent(workbookStylesPart1);

 WorksheetPart worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>("rId1");
 GenerateWorksheetPartContent(worksheetPart1, partSheetData);
 }

 private void GenerateWorksheetPartContent(WorksheetPart worksheetPart1, SheetData sheetData1)
 {
 Worksheet worksheet = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
 worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
 worksheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
 worksheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
 SheetDimension sheetDimension1 = new SheetDimension() { Reference = "A1" };

 SheetViews sheetViews1 = new SheetViews();

 SheetView sheetView1 = new SheetView() { TabSelected = true, WorkbookViewId = 0U };
 Selection selection1 = new Selection() { ActiveCell = "A1", SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A1" } };

 sheetView1.Append(selection1);

 sheetViews1.Append(sheetView1);
 SheetFormatProperties sheetFormatProperties1 = new SheetFormatProperties() { DefaultRowHeight = 15D, DyDescent = 0.25D };

 PageMargins pageMargins1 = new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };
 worksheet.Append(sheetDimension1);
 worksheet.Append(sheetViews1);
 worksheet.Append(sheetFormatProperties1);
 worksheet.Append(sheetData1);
 worksheet.Append(pageMargins1);
 worksheetPart1.Worksheet = worksheet;
 }

 private void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart1)
 {
 Stylesheet stylesheet1 = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
 stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
 stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");

 Fonts fonts1 = new Fonts() { Count = 2U, KnownFonts = true };

 Font font1 = new Font();
 FontSize fontSize1 = new FontSize() { Val = 11D };
 Color color1 = new Color() { Theme = 1U };
 FontName fontName1 = new FontName() { Val = "Calibri" };
 FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };
 FontScheme fontScheme1 = new FontScheme() { Val = FontSchemeValues.Minor };

 font1.Append(fontSize1);
 font1.Append(color1);
 font1.Append(fontName1);
 font1.Append(fontFamilyNumbering1);
 font1.Append(fontScheme1);

 Font font2 = new Font();
 Bold bold1 = new Bold();
 FontSize fontSize2 = new FontSize() { Val = 11D };
 Color color2 = new Color() { Theme = 1U };
 FontName fontName2 = new FontName() { Val = "Calibri" };
 FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering() { Val = 2 };
 FontScheme fontScheme2 = new FontScheme() { Val = FontSchemeValues.Minor };

 font2.Append(bold1);
 font2.Append(fontSize2);
 font2.Append(color2);
 font2.Append(fontName2);
 font2.Append(fontFamilyNumbering2);
 font2.Append(fontScheme2);

 fonts1.Append(font1);
 fonts1.Append(font2);

 Fills fills1 = new Fills() { Count = 2U };

 Fill fill1 = new Fill();
 PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None };

 fill1.Append(patternFill1);

 Fill fill2 = new Fill();
 PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 };

 fill2.Append(patternFill2);

 fills1.Append(fill1);
 fills1.Append(fill2);

 Borders borders1 = new Borders() { Count = 2U };

 Border border1 = new Border();
 LeftBorder leftBorder1 = new LeftBorder();
 RightBorder rightBorder1 = new RightBorder();
 TopBorder topBorder1 = new TopBorder();
 BottomBorder bottomBorder1 = new BottomBorder();
 DiagonalBorder diagonalBorder1 = new DiagonalBorder();

 border1.Append(leftBorder1);
 border1.Append(rightBorder1);
 border1.Append(topBorder1);
 border1.Append(bottomBorder1);
 border1.Append(diagonalBorder1);

 Border border2 = new Border();

 LeftBorder leftBorder2 = new LeftBorder() { Style = BorderStyleValues.Thin };
 Color color3 = new Color() { Indexed = 64U };

 leftBorder2.Append(color3);

 RightBorder rightBorder2 = new RightBorder() { Style = BorderStyleValues.Thin };
 Color color4 = new Color() { Indexed = 64U };

 rightBorder2.Append(color4);

 TopBorder topBorder2 = new TopBorder() { Style = BorderStyleValues.Thin };
 Color color5 = new Color() { Indexed = 64U };

 topBorder2.Append(color5);

 BottomBorder bottomBorder2 = new BottomBorder() { Style = BorderStyleValues.Thin };
 Color color6 = new Color() { Indexed = 64U };

 bottomBorder2.Append(color6);
 DiagonalBorder diagonalBorder2 = new DiagonalBorder();

 border2.Append(leftBorder2);
 border2.Append(rightBorder2);
 border2.Append(topBorder2);
 border2.Append(bottomBorder2);
 border2.Append(diagonalBorder2);

 borders1.Append(border1);
 borders1.Append(border2);

 CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = 1U };
 CellFormat cellFormat1 = new CellFormat() { NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U };

 cellStyleFormats1.Append(cellFormat1);

 CellFormats cellFormats1 = new CellFormats() { Count = 3U };
 CellFormat cellFormat2 = new CellFormat() { NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U, FormatId = 0U };
 CellFormat cellFormat3 = new CellFormat() { NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 1U, FormatId = 0U, ApplyBorder = true };
 CellFormat cellFormat4 = new CellFormat() { NumberFormatId = 0U, FontId = 1U, FillId = 0U, BorderId = 1U, FormatId = 0U, ApplyFont = true, ApplyBorder = true };

 cellFormats1.Append(cellFormat2);
 cellFormats1.Append(cellFormat3);
 cellFormats1.Append(cellFormat4);

 CellStyles cellStyles1 = new CellStyles() { Count = 1U };
 CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = 0U, BuiltinId = 0U };

 cellStyles1.Append(cellStyle1);
 DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = 0U };
 TableStyles tableStyles1 = new TableStyles() { Count = 0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16" };

 StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();

 StylesheetExtension stylesheetExtension1 = new StylesheetExtension() { Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" };
 stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
 X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles() { DefaultSlicerStyle = "SlicerStyleLight1" };

 stylesheetExtension1.Append(slicerStyles1);

 StylesheetExtension stylesheetExtension2 = new StylesheetExtension() { Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}" };
 stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
 X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles() { DefaultTimelineStyle = "TimeSlicerStyleLight1" };

 stylesheetExtension2.Append(timelineStyles1);

 stylesheetExtensionList1.Append(stylesheetExtension1);
 stylesheetExtensionList1.Append(stylesheetExtension2);

 stylesheet1.Append(fonts1);
 stylesheet1.Append(fills1);
 stylesheet1.Append(borders1);
 stylesheet1.Append(cellStyleFormats1);
 stylesheet1.Append(cellFormats1);
 stylesheet1.Append(cellStyles1);
 stylesheet1.Append(differentialFormats1);
 stylesheet1.Append(tableStyles1);
 stylesheet1.Append(stylesheetExtensionList1);

 workbookStylesPart1.Stylesheet = stylesheet1;
 }

 private void GenerateWorkbookPartContent(WorkbookPart workbookPart1)
 {
 Workbook workbook1 = new Workbook();
 Sheets sheets1 = new Sheets();
 Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = 1U, Id = "rId1" };
 sheets1.Append(sheet1);
 workbook1.Append(sheets1);
 workbookPart1.Workbook = workbook1;
 }

 private SheetData GenerateSheetdataForDetails(TestModelList data)
 {
 SheetData sheetData1 = new SheetData();
 sheetData1.Append(CreateHeaderRowForExcel());

 foreach (TestModel taktTimemodel in data.TestData)
 {
 Row partsRows = GenerateRowForChildPartDetail(taktTimemodel);
 sheetData1.Append(partsRows);
 }
 return sheetData1;
 }

 private Row CreateHeaderRowForExcel()
 {
 Row workRow = new Row();
 workRow.Append(CreateCell("Test Id", 2U));
 workRow.Append(CreateCell("Test Name", 2U));
 workRow.Append(CreateCell("Test Description", 2U));
 workRow.Append(CreateCell("Test Date", 2U));
 return workRow;
 }

 private Row GenerateRowForChildPartDetail(TestModel testmodel)
 {
 Row tRow = new Row();
 tRow.Append(CreateCell(testmodel.TestId.ToString()));
 tRow.Append(CreateCell(testmodel.TestName));
 tRow.Append(CreateCell(testmodel.TestDesc));
 tRow.Append(CreateCell(testmodel.TestDate.ToShortDateString()));
 return tRow;
 }

 private Cell CreateCell(string text)
 {
 Cell cell = new Cell();
 cell.StyleIndex = 1U;
 cell.DataType = ResolveCellDataTypeOnValue(text);
 cell.CellValue = new CellValue(text);
 return cell;
 }

 private Cell CreateCell(string text, uint styleIndex)
 {
 Cell cell = new Cell();
 cell.StyleIndex = styleIndex;
 cell.DataType = ResolveCellDataTypeOnValue(text);
 cell.CellValue = new CellValue(text);
 return cell;
 }

 private EnumValue<CellValues> ResolveCellDataTypeOnValue(string text)
 {
 int intVal;
 double doubleVal;
 if (int.TryParse(text, out intVal) || double.TryParse(text, out doubleVal))
 {
 return CellValues.Number;
 }
 else
 {
 return CellValues.String;
 }
 }
 }
}

References:

Errors were detected in file - with an Excel file after creating it with OpenXML

https://www.c-sharpcorner.com/forums/errors-were-detected-in-file-withan-excel-file-after-creating-it-with-openxml

Validate a word processing document (Open XML SDK)

https://docs.microsoft.com/en-us/office/open-xml/how-to-validate-a-word-processing-document

Validating of OpenXml generated documents or The file cannot be opened because there are problems with contents

https://tech.trailmax.info/2014/04/validating-of-openxml-generated-documents-or-the-file-cannot-be-opened-because-there-are-problems-with-contents/

Edit Word Documents using OpenXML and C# Without Automation/Interop

https://www.codingame.com/playgrounds/11047/edit-word-documents-using-openxml-and-c-without-automationinterop

Word Document (Code Sample)

http://omegacoder.com/?p=555

https://www.codeproject.com/Articles/36694/Creation-of-a-Word-2007-document-using-the-Open-XM

https://docs.microsoft.com/en-us/office/open-xml/how-to-insert-a-table-into-a-word-processing-document?redirectedfrom=MSDN

Tags: Excel problems, Open XML SDK 2.5, Open XML SDK, Open XML, Excel solutions
Categories: Excel problems | Open XML SDK | Open XML SDK 2.5 | Open XML | Excel solutions
comment

@manhng

Excel problems & solutions

Excel problems & solutions (edit)

Libraries:

Excel problem 04: Zip some Excel files

Excel problem 03: Worksheet's name cannot contain more than 31 characters

Excel problem 02: Excel Open XML error

Excel problem 01: Error when opening the Excel file

Cause:

Solution:

Naming Syntax

Important: Worksheet names cannot:

Code to show the errors in an Excel file (source code):

Code sample: Create the Excel file using OpenXML

References:

Recent posts

@manhng

Excel problems & solutions

Excel problems & solutions (edit)

Libraries:

Excel problem 04: Zip some Excel files

Excel problem 03: Worksheet's name cannot contain more than 31 characters

Excel problem 02: Excel Open XML error

Excel problem 01: Error when opening the Excel file

Cause:

Solution:

Naming Syntax

Important: Worksheet names cannot:

Code to show the errors in an Excel file (source code):

Code sample: Create the Excel file using OpenXML

References:

Categories

Recent posts

Popular tags