[Solved] Reading large record from Excel using ClosedXML is very slow in ASP.Net

Last Reply 5 months ago By dharmendr

Posted 5 months ago

Hi sir,

earlier i asked to import excel to database using closed xml its working

but very slow working when i go for 10 Lakh record of 15 column.

can you please give me exact soluton to work with large file in asp.net core.

please help.

You are viewing reply posted by: dharmendr 5 months ago.
Posted 5 months ago

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;
                    }
                }
            }
        }
    }
}