将Excel范围转换为ADO.NET DataSet或DataTable等 [英] Convert Excel Range to ADO.NET DataSet or DataTable, etc

问题描述

我有一个Excel电子表格可以坐在网络共享驱动器上。 Winforms C＃3.0应用程序需要访问它（许多用户可能正在使用该应用程序并同时打到该电子表格）。一个工作表上有很多数据。这些数据被分解成我被命名为范围的区域。我需要能够单独访问这些范围，将每个范围作为数据集返回，然后将其绑定到网格。

我发现使用OLE并具有让这些工作。但是，我已经看到关于使用这种方法的一些警告，另外在工作中我们一直在使用Microsoft.Office.Interop.Excel作为标准。除了我必须，我真的不想偏离这个。我们的用户将尽可能地使用Office 2003。

我可以使用以下代码获取我需要的范围：

  MyDataRange =（Microsoft.Office.Interop.Excel.Range）
 MyWorkSheet.get_Range（MyExcelRange，Type.Missing）; 
  

OLE方式很好，因为它会占用我的第一行，并将其转换成列。我的范围（共12个）在列数中大部分不同。不知道这个信息是否会影响任何建议。

有没有办法使用Interop并将返回的范围返回到数据集？

解决方案

我不知道一个内置的功能，但不要自己写这个难度。伪代码：

  DataTable MakeTableFromRange（范围范围）
 {
 table = new DataTable 
 for每个范围内的列
 {
向表
添加新列
对于范围
 {
中的每一行，将新的数据行添加到表
对于范围内的每一列
 {
 table.cells [column，row] .value = range [column，row] .value 
} 
} 
 return table 
} 
  

I have an Excel spreadsheet that will sit out on a network share drive. It needs to be accessed by my Winforms C# 3.0 application (many users could be using the app and hitting this spreadsheet at the same time). There is a lot of data on one worksheet. This data is broken out into areas that I have named as ranges. I need to be able to access these ranges individually, return each range as a dataset, and then bind it to a grid.

I have found examples that use OLE and have got these to work. However, I have seen some warnings about using this method, plus at work we have been using Microsoft.Office.Interop.Excel as the standard thus far. I don't really want to stray from this unless I have to. Our users will be using Office 2003 on up as far as I know.

I can get the range I need with the following code:

MyDataRange = (Microsoft.Office.Interop.Excel.Range)
    MyWorkSheet.get_Range("MyExcelRange", Type.Missing);

The OLE way was nice as it would take my first row and turn those into columns. My ranges (12 total) are for the most part different from each other in number of columns. Didn't know if this info would affect any recommendations.

Is there any way to use Interop and get the returned range back into a dataset?

解决方案

I don't know about a built-in function, but it shouldn't be difficult to write it yourself. Pseudocode:

DataTable MakeTableFromRange(Range range)
{
   table = new DataTable
   for every column in range
   {
      add new column to table
   }
   for every row in range
   {
      add new datarow to table
      for every column in range
      {
         table.cells[column, row].value = range[column, row].value
      }
   }
   return table
}

这篇关于将Excel范围转换为ADO.NET DataSet或DataTable等的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！