使用C＃写入Excel文件最好最快的方法是什么？ [英] What is the best and fastest way to write into Excel file using C#?

问题描述

我正在尝试使用OLEDB（无自动化）写入Excel文件。
我有大约500行的数据，我从一些其他应用程序，然后写入Excel文件逐个使用'INSERT INTO ..'查询。
我确信从其他应用程序读取数据是没有延迟的。我检查了
大约3分钟内写入excel文件500行的总时间。
这太多了。这绝对是因为文件写入操作。

什么是最快的方法来做到这一点？
我应该用其他一些技巧来写？
我应该尝试自动化技术吗？

http://support.microsoft.com/kb/306023
此链接显示许多技术，但不确定要使用哪种技术。

解决方案

如果您可以将COM转换成excel，则可以通过COM直接从excel进行查询，或者创建数据数组，并将其直接放入等于数组大小的范围内。即使excel对于小型COM呼叫来说不是很好，但是对于很少的大量COM调用来说，它的工作效果非常好。）

  DataSet ds =新的DataSet（）; 
 da.Fill（ds）; 
 int width = ds.Tables [0] .Columns.Count; 
 int height = ds.Tables [0] .Rows.Count; 
 object [，] retList = new object [height，width]; （int i = 0; i< height; i ++）
 
 {
 DataRow r = ds.Tables [0] .Rows [i]; 
 for（int j = 0; j< width; j ++）
 retList [i，j] = r.ItemArray [j]; 
} 
 Excel.Range范围= mWs.get_Range（destination，mWs.Cells [destination.Row + height  -  1，destination.Column + width-1]）; 
 range.set_Value（Missing.Value，retList）; 
 System.Runtime.InteropServices.Marshal.ReleaseComObject（range）; 
 range = null; 
  

这是一个在一个COM调用中获取数据并将其作为数组插入到excel中的示例。 p>

I am trying to write into excel file using OLEDB (without automation). I have around 500 rows of data which I get from some other application and then write into Excel file one by one using 'INSERT INTO..' Query. I am sure that there is no delay in reading data from the other application. I checked that. The total time taken to write into the excel file for 500 rows in around 3 minutes. This is too much. This is definitely because of the file write operation.

What would be the best way to make this fast? Should I use some other technique for writing? Should I try a technique with automation?

http://support.microsoft.com/kb/306023 This link show many techniques, but not sure which one to use.

解决方案

If you can COM into excel, you can query directly from excel via COM, or create an array of data and drop it directly into a range equal to the size of your array. Even though excel isn't great for small COM calls, it works rather well with few large COM calls :)

DataSet ds = new DataSet();
          da.Fill(ds);
          int width = ds.Tables[0].Columns.Count;
          int height = ds.Tables[0].Rows.Count;
          object[,] retList = new object[height, width];
          for (int i = 0; i < height; i++)
          {
            DataRow r = ds.Tables[0].Rows[i];
            for (int j = 0; j < width; j++)
              retList[i, j] = r.ItemArray[j];
          }
          Excel.Range range = mWs.get_Range(destination, mWs.Cells[destination.Row + height - 1, destination.Column + width - 1]);
          range.set_Value(Missing.Value, retList);
          System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
          range = null;

This is an example of getting data and inserting it as an array into excel in one COM call

这篇关于使用C＃写入Excel文件最好最快的方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！