Load Large Excel file in C#

Question

I am trying to load a large excel file (~30MB) with 11 columns and 485k rows using the following code:

                    OpenFileDialog ofd = new OpenFileDialog();NameOfFile = ofd.SafeFileName;
                if (NameOfFile.Contains("xlsx"))
                {
                    NameOfFile = NameOfFile.Substring(0, NameOfFile.Length - 5);
                }
                else
                {
                    NameOfFile = NameOfFile.Substring(0, NameOfFile.Length - 4);
                }
                string dbConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + ofd.FileName + ";Extended Properties=" + "Excel 12.0;";
                OleDbConnection con = new OleDbConnection(dbConn);
                OleDbDataAdapter da = new OleDbDataAdapter("select * from [Contacts$]", con); //Contacts$ -> onoma filou sto excel
                da.Fill(dataBase1);
                dataGridView1.DataSource = dataBase1.Tables[0];
                dataGridView1.Columns[0].Width = 35;
                dataGridView1.Columns[1].Width = 35;
                dataGridView1.Columns[2].Width = 35;
                dataGridView1.Columns[3].Width = 35;
                dataGridView1.Columns[4].Width = 35;
                dataGridView1.Columns[5].Width = 40;
                dataGridView1.Columns[9].Width = 55;
                dataGridView1.Columns[10].Width = 60;

The problem is whenever I run my program and try to load the file the program freezes and has to be shut down. The program has been tested with smaller excel file (11 columns and 100 rows) and runs perfectlly. Any ideas?

Answer 1

I have faced the same problem with big excel files and solved it by using Excel Data Reader library:
https://exceldatareader.codeplex.com/
It is lightweight, fast and free library written in C#.

Usage:

        FileStream stream = File.Open( file, FileMode.Open, FileAccess.Read );

        IExcelDataReader excelReader = null;
        if (file.ToLower().EndsWith("xls"))
            //1. Reading from a binary Excel file ('97-2003 format; *.xls) 
            excelReader = ExcelReaderFactory.CreateBinaryReader( stream );
        else
            //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
            excelReader = ExcelReaderFactory.CreateOpenXmlReader( stream );

        DataSet result = excelReader.AsDataSet();

and then you can do anything you want with the DataSet.

Answer 2

With 485K rows, hard to say if the problem is OLEDB or the DataGridView. I suggest you dump OLEDB and use a native excel library and then use Virtual Mode with the DataGridView.

The EPPlus library open source Excel library that can read the xlsx format (Office 2007 and later) and the ExcelLibrary project that can read the older xls binary format (prior to Office 2007).

Check this tutorial for implementing virtual mode in the DataGridView so that the Grid doesn't have to manage all 485K rows on the screen and in memory at once.

Answer 3

Using OLEDB to load large Excel files can be a problem. Take a look on this code sample that loads the Excel file and fill the DataGridView:

http://www.easyxls.com/manual/FAQ/import-excel-to-datagridview.html

It uses EasyXLS Excel library. If your DataGridView cannot display so big volume of data, you can adjust the code to load ranges from the Excel file and display data page by page or only the data that shows interest.