locked
Large excel RRS feed

  • Question

  • User-189004138 posted

    Hi

    I have large excel which is about 30mb file. I just want to read its header and I am using EPPLus dll. When I try to load worksheet it throws System.OutOfMemory.

    What will be the best way to read just column headers from excel.

    Awaiting for your reply

    Monday, January 1, 2018 8:50 AM

All replies

  • User269602965 posted

    Try OLEDB and load into an EXCEL XLSX datasheet.

    Then you can select the header row.

    Tuesday, January 2, 2018 3:03 AM
  • User1400794712 posted

    Hi keyurn,

    How about trying to use IExcelDataReader to load the file?

    First using Nuget Package to install it:

    Then the code:

    public class showHeader
    {
        public string TableName { get; set; }
        public object[] Columns { get; set; }
    }
    public ActionResult ReadExcelFile()
    {
        IExcelDataReader reader = null;
        string FilePath = @"D:\Daisy\new1228.xlsx";
        //Load file into a stream
        using (FileStream stream = System.IO.File.Open(FilePath, FileMode.Open, FileAccess.Read))
        {
            //Must check file extension to adjust the reader to the excel file type
            if (Path.GetExtension(FilePath).Equals(".xls"))
                reader = ExcelReaderFactory.CreateBinaryReader(stream);
            else if (Path.GetExtension(FilePath).Equals(".xlsx"))
                reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            if (reader != null)
            {
                //Fill DataSet
                DataSet content = reader.AsDataSet();
                //Read....
                var tables = content.Tables
                                    .Cast<DataTable>()
                                    .Select(t => new showHeader
                                    {
                                        TableName = t.TableName,
                                        Columns = t.Rows.Cast<DataRow>().First().ItemArray
                                    });
                return View(tables);
            }
        }
        return View();
    }

    View:

    @model IEnumerable<Demo.Controllers.ReadExcelController.showHeader>
    @foreach(var item in Model)
    {
        <p>@item.TableName</p>
        <p>
            @foreach (var header in item.Columns)
            {
                <span>@header</span>
            }
        </p>
    }

    Best Regards,

    Daisy

    Tuesday, January 2, 2018 9:47 AM
  • User-189004138 posted

    Thanks for response

    Dose this required excel to be installed on server ? because my application hosted on Azure. so I am unable to install office over there. so i need solution without office installed.

    But let me try this solutions.

    Tuesday, January 2, 2018 9:55 AM
  • User269602965 posted

    OLEDB does not require Excel to be installed, but of course the OLEDB driver needs to be installed as with any data access capability.

    Wednesday, January 3, 2018 2:43 AM