Read large spreadsheet using C# (Open XML SDK)

Open XML SDK:

Open Software Development Kit is used to manipulate XML of a spreadsheet. Considered the audience is aware of XML architecture of an EXCL document which is built using XML represents internal structure of a Workbook. Open XML SDK exposes various methods and properties by which we can manipulate internal structure of a spreadsheet.

There is an open interface provided by Microsoft that helps understanding internal structure of a Excel spreadsheet. The latest available version is 2.5 for Open XML SDK Productivity tool which can be downloaded from here. Post installation launch productivity tool and from Open File menu select any excel file, the tool will load XML structure within workspace which you can use to modify or identify desired elements or parts.

Prerequisites:

• Visual Studio 2015 or higher
• Open XML SDK 2.5
• Excel 2013 or higher

Code example:

using System;
using System.Linq;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

public static void ReadExcelFile(string fileName)
{
	string fileName = @"D:\myLargeFile.xlsx";
	// Open the document
	using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
	{
		//Create object of Workbook
		WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
		WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();

		OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
		string text;
		//Loop through each row
		foreach (Row row in sheetData.Elements<Row>())
		{
			//Loop through each column
			foreach (Cell col in row.Elements<Cell>())
			{
				text = col.CellValue.Text;
				Console.Write(text + " ");
			}
		}
		Console.WriteLine();
		Console.WriteLine("Reading file data finished successfully!!!");
	}
}

Above code is using DOM object to parse xml and iterating each element and putting data for each cell. You can pull data into any source by toggling code.

Share on Facebook

Tweet