将 Excel 范围转换为 ADO.NET 数据集或数据表等

Convert Excel Range to ADO.NET DataSet or DataTable, etc(将 Excel 范围转换为 ADO.NET 数据集或数据表等)

问题描述

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

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.

我找到了使用 OLE 的示例，并且已经让这些示例发挥作用.但是，我已经看到了一些关于使用这种方法的警告，而且在工作中我们一直使用 Microsoft.Office.Interop.Excel 作为标准.我真的不想偏离这个，除非我必须这样做.据我所知，我们的用户将使用 Office 2003.

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

OLE 方式很好，因为它会占用我的第一行并将它们变成列.我的范围(总共 12 个)在大多数情况下在列数上彼此不同.不知道此信息是否会影响任何建议.

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.

有什么方法可以使用 Interop 并将返回的范围返回到数据集中?

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 数据集或数据表等的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！