在C#中加载大型Excel文件 [英] Load Large Excel file in C#

问题描述

我正尝试使用以下代码加载包含11列和485k行的大型excel文件(〜30MB):

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;

问题是，每当我运行程序并尝试加载文件时，程序就会冻结并必须关闭.该程序已经过较小的excel文件(11列100行)的测试，可以完美运行.有什么想法吗?

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?

推荐答案

具有485K行，很难说问题出在OLEDB还是DataGridView.我建议您转储OLEDB并使用本机excel库，然后将Virtual Mode与DataGridView一起使用.

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.

EPPlus库开源Excel库，该库可以读取xlsx格式(Office 2007及更高版本)和<一个href ="https://code.google.com/p/excellibrary/" rel ="nofollow"> ExcelLibrary项目，该项目可以读取旧的xls二进制格式(在Office 2007之前).

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).

检查本教程在DataGridView中实现虚拟模式，以便Grid不必一次管理屏幕和内存中的所有485K行.

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.

这篇关于在C#中加载大型Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！