如何将大数据加载到datatable [英] How to load large data to datatable

问题描述

我正在将数据从excel加载到datatable。

I am loading data from excel to datatable.

public DataView LoadFromExcel()
       {
           Microsoft.Office.Interop.Excel.Application application = new Microsoft.Office.Interop.Excel.Application();
           Workbook workbook = null;
           Worksheet worksheet = null;
           string filename = null;
           OpenFileDialog file = new OpenFileDialog();
           if (true == file.ShowDialog())
           {
               filename = file.FileName;
           }
           workbook = application.Workbooks.Open(filename, true, true);
           worksheet = workbook.Sheets[1];
           Range range = worksheet.UsedRange;
           int row = range.Rows.Count;
           int columns = range.Columns.Count;
           System.Data.DataTable dt = new System.Data.DataTable();
           for (int i = 1; i <= columns; i++)
           {
               dt.Columns.Add((range.Cells[1, i] as Range).Value2.ToString());
           }
           for (row = 2; row <= range.Rows.Count; row++)
           {
               DataRow dr = dt.NewRow();
               for (int column = 1; column <= range.Columns.Count; column++)
               {
                   dr[column - 1] = (range.Cells[row, column] as Microsoft.Office.Interop.Excel.Range).Value2.ToString();
               }
               dt.Rows.Add(dr);
               dt.AcceptChanges();
           }
           workbook.Close(true, Missing.Value, Missing.Value);
           application.Quit();
           return dt.DefaultView;
       }

但是当我从中加载数据时包含8000-10000行的excel ..需要大量的时间。我可以用任何其他方式解决这个问题....

But when i am loading data from excel which contains 8000-10000 rows..it takes large amount of time.can i solve this in any other way....

推荐答案

如果你只是需要在加载大量数据时使 GUI 响应，然后使用单独的线程。另一方面，如果你需要提高代码性能，那么任务会更难，但是，无论如何，你的朋友 Google [ ^ ]。

If you simply need to make the GUI responsive while loading the large amount of data then use a separate thread for it. On the other hand, if you need to improve code performance then the task would be a bit harder, but, anyway, there''s your friend Google[^].

我愿意sugest你使用OLEDB而不是逐行读取Excel数据;）它比你的方法快得多。

http://msdn.microsoft.com/en-US/library/ms175866%28v=sql.105%29.aspx [ ^ ]

使用OLEDB读取和写入Excel [ ^ ]

使用OLEDB将Excel文件导入C＃[ ^ ]

用于Microsoft Excel 2007的Microsoft OLEDB适配器 [ ^ ]

I would sugest you to use OLEDB instead reading Excel data row by row, column by column ;) It is much, much faster than your method.
http://msdn.microsoft.com/en-US/library/ms175866%28v=sql.105%29.aspx[^]
Reading and Writing Excel using OLEDB[^]
Import excel file to C# using OLEDB[^]
Microsoft OLEDB Adapter for Microsoft Excel 2007[^]

这篇关于如何将大数据加载到datatable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！