@manhnguyenv

Welcome to my blog!

Excel problems & solutions

May 8, 2020 13:38

Excel problems & solutions (edit)

Libraries:

  • Open XML SDK 2.5
  • SpreadsheetLight

C:\Program Files (x86)\Open XML SDK\V2.5\...

http://www.microsoft.com/en-in/download/details.aspx?id=30425

https://spreadsheetlight.com/

https://docs.microsoft.com/en-us/office/open-xml/open-xml-sdk

https://docs.microsoft.com/en-us/office/open-xml/getting-started

https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=oxmlsdk

  • DocumentFormat.OpenXml
  • WindowsBase
  • Validate an OpenXML document
  • Worksheets: Naming Conventions
  • Rename a worksheet
  • Rename a workbook

Excel problem 04: Zip some Excel files

https://docs.microsoft.com/en-us/dotnet/api/documentformat.openxml.packaging.worksheetpart?view=openxml-2.8.1

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

https://www.codeproject.com/script/Content/ViewAssociatedFile.aspx?rzp=%2FKB%2Foffice%2F366446%2F%2FGridToExcel.zip&zep=GridToExcel%2FGridToExcel%2FHelper%2FExcelHelper.cs&obid=366446&obtid=2&ovid=5

http://cstruter.com/blog/291

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

https://support.office.com/en-us/article/rename-a-worksheet-3f1f7148-ee83-404d-8ef0-9ff99fbad1f9

http://www.excelcodex.com/2012/06/worksheets-naming-conventions/

https://www.accountingweb.com/technology/excel/seven-characters-you-cant-use-in-worksheet-names

Excel problem 02: Excel Open XML error

https://stackoverflow.com/questions/6021608/excel-open-xml-error-found-unreadable-content-when-creating-simple-example

https://stackoverflow.com/questions/19529436/trying-to-sort-excel-range-by-two-columns-using-c-sharp

Also refer below links.

https://social.msdn.microsoft.com/Forums/office/en-US/07a84785-7130-4d91-ad8b-b5887c088b67/the-specified-package-is-invalid-the-main-part-is-missing?forum=oxmlsdk

https://stackoverflow.com/questions/41672818/the-specified-package-is-invalid-the-main-part-is-missing

https://stackoverflow.com/questions/11212374/specified-part-does-not-exist-in-the-package

https://social.msdn.microsoft.com/Forums/office/en-US/0c6d7eff-14d2-4a0b-a440-30ee933e73f2/the-specified-package-is-invalid-the-main-part-is-missing?forum=oxmlsdk

https://social.msdn.microsoft.com/Forums/en-US/66fd77ca-8b86-470b-ad99-48f574ce2d67/the-specified-package-is-invalid-the-main-part-is-missing?forum=os_binaryfile

Excel problem 01: Error when opening the Excel file

https://stackoverflow.com/questions/18539267/xlsx-error-removed-records-named-range-from-xl-workbook-xml-part-when-tried

Error Message 1:

<?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.

  1. The name must be unique within a single workbook.
  2. A worksheet name cannot exceed 31 characters.
  3. You can use all alphanumeric characters but not the following special characters:
    \ , / , * , ? , : , [ , ].
  4. 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

Categories

Recent posts