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

问题描述

我想用OLEDB（无自动化）写入到Excel文件。
我身边有500行数据，这是我从其他应用程序中获取，然后写入到Excel中一个使用INSERT INTO ..查询文件之一。
我肯定有从其他应用程序读取数据无延迟。我检查。
写入到约有3分钟500行Excel文件所用的总时间。
这实在是太多了。这肯定是因为该文件的写操作的。

什么是使这个快速的最好方法？
我应该使用写一些其他的技术？
我应该尝试的技术与自动化？

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

解决方案

如果你能COM到Excel，您可以直接从Excel中通过COM查询，或创建一个数据数组和直接拖放到等于你的数组大小的范围。虽然Excel是不是很大的小型COM调用，它的工作原理相当好与几个大的COM调用：）

 的DataSet DS =新的DataSet（）; 
 da.Fill（DS）; 
 INT宽度= ds.Tables [0] .Columns.Count; 
 INT高度= ds.Tables [0] .Rows.Count; 
对象[，] retList =新对象[高度，宽度] 
的for（int i = 0; I<高度;我++）
 {
的DataRow R = ds.Tables [0] .Rows [I] 
为（INT J = 0; J<宽度; J ++）
 retList [I，J] = r.ItemArray [J]。 
} 
 Excel.Range范围= mWs.get_Range（目的地，mWs.Cells [destination.Row +高 -  1，destination.Column +宽度 -  1]）; 
 range.set_Value（Missing.Value，retList）; 
 System.Runtime.InteropServices.Marshal.ReleaseComObject（范围）; 
范围= NULL; 
  

这是获得数据并将其插入作为数组到Excel在一个COM调用

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屋！