Read Excel file using Open XML in ASP.Net C#

Open XML SDK provides tools for working with Office Word, Excel, and PowerPoint documents. This library supports many platforms. There are builds for .NET 3.5, .NET 4.0, .NET 4.6, and .NET Standard 1.3.

This topic shows how to use the classes in the Open XML  for Office to retrieve the values of cells in a spreadsheet document.

Prerequisites

You must install Open XML library from NuGet Package Manager

Once you load this dll and build it. You will get the following error.

It’s the dll where the System.IO.Packaging API lives, and you have to add a reference to it in your project before you can use the packaging API. It is used for unzipping and opening the compressed .docx/.xlsx/.pptx as an OPC (Open packaging Conventions) document.

To fix this issue, you need to add WindowsBase.dll. There is a known issue in WindowsBase that causes crashes when handling large data sources. This is fixed in later versions of the library, based on the platform availability of the  System.IO.Packaging package. When possible, we use this package instead of WindowsBase.

If your are using  later version of .Net framework (>4), then you can use System.IO.Packaging library. Otherwise you need to use WindowsBase.dll.

You must also use the following using directives to compile the code

Namespace

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

C# Code

protected void ImportExcel(HttpPostedFileBase httpPostedfile)
{

string basePath = string.Format("{0}/{1}", AppDomain.CurrentDomain.BaseDirectory, "FileRepository/ImportExport/Excel");
string path = string.Format("{0}/{1}", basePath, httpPostedfile.FileName);
if (!Directory.Exists(basePath))
{
Directory.CreateDirectory(basePath);
}
httpPostedfile.SaveAs(path);
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(path, false))
{
//Read the first Sheet from Excel file.
Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild<Sheet>();
//Get the Worksheet instance.
Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet;
//Fetch all the rows present in the Worksheet.
IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Descendants<Row>();
DataTable dt = new DataTable();
//Loop through the Worksheet rows.
foreach (Row row in rows)
{
//Use the first row to add columns to DataTable.
if (row.RowIndex.Value == 1)
{
foreach (Cell cell in row.Descendants<Cell>())
{
dt.Columns.Add(GetValue(doc, cell));
}
}
else
{
//Add rows to DataTable.
dt.Rows.Add();
int i = 0;
foreach (Cell cell in row.Descendants<Cell>())
{
dt.Rows[dt.Rows.Count - 1][i] = GetValue(doc, cell);
i++;
}
}
}
}
}

private string GetValue(SpreadsheetDocument doc, Cell cell)
{
string value = string.Empty;
if (cell.CellValue != null)
{
value = cell.CellValue.InnerText;
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(int.Parse(value)).InnerText;
}
}return value;
}

Sample excel file

We will get a extracted excel content in DataTable  object dt. The datatable content has shown below. First row is treated as a column header name, and then remaining  items comes under datatable Rows.

Thanks 🙂