Fix corrupt excel file due to Defined Names C#

Defined Name:

By using names, you can make your formulas much easier to understand and maintain. You can define a name for a cell range, function, constant, or table. Once you adopt the practice of using names in your workbook, you can easily update, audit, and manage these names.

Defined names are growing objects built inside the Excel XLSX (xml internal structure), They always become leftover when we are working in any XLSX file and cause Excel file gets corrupted or not working as expected. Having less contents but takes too much time to process a request or opening itself.

Manually Excel xml parts fixing:

1. Rename your file with zip extension by putting .zip extension at the end of the file
2. Open zip browser and look for workbook.xml
3. Copy workbook.xml in local window and open
4. Scan through xml and look for <DefinedNames> tag most probably that is culprit. If you see lots of DefinedName child tags under <DefinedNames> tag it means you got the error area
5. Closely open xml in a xml editor and remove all <DefinedName> or <DefinedNames> parent tag
6. Save the modified xml and drop in the zip from where you extracted it from
7. Rename excel file back by removing zip
8. Open excel and now you see it is very fast and your problem is gone

If you want you can refer below video to perform above steps:

Let’s write some code to remove defined name cause excel got corrupt. To play with internal structure of Excel we need to have some libraries which will directly play with internal structure of excel. Microsoft offers OpenXML APIs which can directly interact with internal structure of Excel file.

To bring OpenXML in your project, first we need to install OpenXML SDK (2.5 version is the latest currently). Once you install it, you will have to add two important references in your project.

1. Microsoft OpenXML
2. WindowsBase

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

public static void RemoveDefinedNames()
{
	string fileName=@"C:\\test.xlsx";
	using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
    {
		WorkbookPart wbPart = document.WorkbookPart;
		// Retrieve a reference to the defined names collection.
		DefinedNames definedNames = wbPart.Workbook.DefinedNames;
		//remove direct parent node which will delete all defined names from the document
		if(definedNames!=null)
			definedNames.Remove();
		document.Save();
		document.Close();
	}
}

Note: after performing any internal structural change on any excel file. The excel file must be open at least once in the Excel Application environment to adjust the structure back by parsing using Excel Engine.

Next>> Ways of retrieving values from an object (Cells or Range)

Share on Facebook

Tweet