EXCEL file reading from C# code

There is require installing of DocumentFormat.OpenXml from nuget in visual studio or download DocumentFormat.OpenXml.dll and use in the project.

Below is the code for reading Excel file and stored data in dataset.

Namespaces:

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.IO;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Text.RegularExpressions;

Method for Reading EXCEL file

public static DataSet ReadExcelFile(string filePath)

        {

            try

            {

                var ds = new DataSet();

                using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filePath, false))

                {

                    WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;

                    string sheetName = “sheetName”;

                    var strSheets = workbookPart.Workbook.Descendants<Sheet>();

                    Sheet Plantsheet = strSheets.FirstOrDefault();

                    var wp = (WorksheetPart)workbookPart.GetPartById(Plantsheet.Id);

                    SheetData sheetData = wp.Worksheet.Elements<SheetData>().First();

                    IEnumerable<Row> rows = sheetData.Descendants<Row>();

                    //add Columns

                    ds.Tables.Add(sheetName);

                    ds.Tables[sheetName].Columns.Add(“ColumnName1”);

                    ds.Tables[sheetName].Columns.Add(“ColumnName2”);

                    ds.Tables[sheetName].Columns.Add(“ColumnName3”);

                    ds.Tables[sheetName].Columns.Add(“ColumnName4”);

                    // add column name as per requirement of as per Excel header

                    //add rows

                    foreach (Row r in sheetData.Elements<Row>())

                    {

                        DataRow dr = ds.Tables[sheetName].NewRow();

                        for (int k = 0; k < r.Descendants<Cell>().Count(); k++)

                        {

                            Cell cell = r.Descendants<Cell>().ElementAt(k);

                            dr[k] = GetCellValue(spreadsheetDocument, cell);

                        }

                        ds.Tables[sheetName].Rows.Add(dr);

                    }

                }

                return ds;

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

Method for reading Excel Cell data:

private static string GetCellValue(SpreadsheetDocument document, Cell cell)

        {

            try

            {

                SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;

                string value = cell.CellValue?.InnerXml;

                if (!string.IsNullOrEmpty(value) && cell.DataType != null && cell.DataType.Value == CellValues.SharedString)

                {

                    return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;

                }

                else

                {

                    return value;

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s