Hi telldurges,

Try to use OpenXML.

Read and Import Excel data to DataTable using OpenXml in ASP.Net with C# and VB.Net

Reading a Excel file is easy using the SpreadsheetDocument and SheetData classes but this method uses a lot of memory if the Excel file is big.

This is due to the framework loads the entire file into memory.

To read huge files it is better to use the OpenXmlReader.

Check this example. Now please take its reference and correct your code.

Namespaces

using System.Collections.Generic;
using System.Data;
using System.Linq;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

C#

protected void Save(object sender, EventArgs e)
{
    IEnumerable<List<string>> rows = ReadData(FileUpload1.PostedFile.FileName);
    DataTable dt = new DataTable();
    bool firstRow = true;
    foreach (List<string> rowData in rows)
    {
        // Loop the Excel rows and do your task.
        if (firstRow)
        {
            foreach (string cell in rowData)
            {
                dt.Columns.Add(cell);
            }
            firstRow = false;
        }
        else
        {
            dt.Rows.Add();
            int i = 0;
            foreach (string cell in rowData)
            {
                dt.Rows[dt.Rows.Count - 1][i] = cell;
                i++;
            }
        }
    }
}

public static IEnumerable<List<string>> ReadData(string fileName)
{
    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
    {
        WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
        WorksheetPart worksheetPart = workbookPart.WorksheetParts.FirstOrDefault();
        if (workbookPart != null)
        {
            using (OpenXmlReader oxr = OpenXmlReader.Create(worksheetPart))
            {
                IEnumerable<SharedStringItem> sharedStrings = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>();
                while (oxr.Read())
                {
                    if (oxr.ElementType == typeof(Row))
                    {
                        oxr.ReadFirstChild();
                        List<string> rowData = new List<string>();
                        do
                        {
                            if (oxr.ElementType == typeof(Cell))
                            {
                                Cell c = (Cell)oxr.LoadCurrentElement();
                                string cellValue;
                                if (c.DataType != null && c.DataType == CellValues.SharedString)
                                {
                                    SharedStringItem ssi = sharedStrings.ElementAt(int.Parse(c.CellValue.InnerText));
                                    cellValue = ssi.Text.Text;
                                }
                                else
                                {
                                    cellValue = c.CellValue != null ? c.CellValue.InnerText : "";
                                }
                                rowData.Add(cellValue);
                            }
                        }
                        while (oxr.ReadNextSibling());
                        yield return rowData;
                    }
                }
            }
        }
    }
}