将Excel范围转换为ADO.NET DataSet或DataTable等

Question

我有一个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并将返回的范围放回数据集中？

- user295197

Answer 1

我不知道是否有内置功能，但是自己编写应该不难。伪代码：

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
}

- Igby Largeman

---

感谢所有的回复。由于我找不到内置的解决方案，所以我继续遍历范围并即时创建了一个数据表。这里列出的所有建议都可以实现，只是我不想使用OLEDB，并且目前也不能引入第三方库。非常感谢您的快速响应。非常感激。 - user295197

Answer 2

我不知道你有什么类型的数据。但对于像这个链接http://www.freeimagehosting.net/image.php?f8d4ef4173.png中所示的Excel数据，您可以使用以下代码加载到数据表中。

    private void Form1_Load(object sender, EventArgs e)
    {   
       try
       {        
            DataTable sheetTable = loadSingleSheet(@"C:\excelFile.xls", "Sheet1$");
            dataGridView1.DataSource = sheetTable;
       }
       catch (Exception Ex)
       {
            MessageBox.Show(Ex.Message, "");
       }  
    }        

    private OleDbConnection returnConnection(string fileName)
    {
        return new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + "; Jet OLEDB:Engine Type=5;Extended Properties=\"Excel 8.0;\"");
    }

    private DataTable loadSingleSheet(string fileName, string sheetName)
    {           
        DataTable sheetData = new DataTable();
        using (OleDbConnection conn = this.returnConnection(fileName))
        {
           conn.Open();
           // retrieve the data using data adapter
           OleDbDataAdapter sheetAdapter = new OleDbDataAdapter("select * from [" + sheetName + "]", conn);
            sheetAdapter.Fill(sheetData);
        }                        
        return sheetData;
    }

- wonde

---

感谢代码和OLEDB确实使它变得容易，但是我现在需要坚持使用Excel Interop。 - user295197

Answer 3

当Excel电子表格中的同一列包含文本和数字时，此方法效果不佳。例如，如果Range("A3")=Hello并且Range("A7")=5，则它只读取Hello，而Range("A7")的值为DBNULL

private void Form1_Load(object sender, EventArgs e)
{   
   try
   {        
        DataTable sheetTable = loadSingleSheet(@"C:\excelFile.xls", "Sheet1$");
        dataGridView1.DataSource = sheetTable;
   }
   catch (Exception Ex)
   {
        MessageBox.Show(Ex.Message, "");
   }  
}        

private OleDbConnection returnConnection(string fileName)
{
    return new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + "; Jet OLEDB:Engine Type=5;Extended Properties=\"Excel 8.0;\"");
}

private DataTable loadSingleSheet(string fileName, string sheetName)
{           
    DataTable sheetData = new DataTable();
    using (OleDbConnection conn = this.returnConnection(fileName))
    {
       conn.Open();
       // retrieve the data using data adapter
       OleDbDataAdapter sheetAdapter = new OleDbDataAdapter("select * from [" + sheetName + "]", conn);
        sheetAdapter.Fill(sheetData);
    }                        
    return sheetData;

- John

Answer 4

当涉及到读/写Excel 2003 XLS文件时，值得一看NPOI。NPOI是一个救星。

我认为你需要迭代你的范围并创建DataRows放入你的DataTable。

这个StackOverflow上的问题提供了更多资源：

使用C#创建Excel（.XLS和.XLSX）文件

- Leniel Maccaferri

---

我会记住这个，留待未来的项目使用。目前为止，我不能将新的库引入我们的应用程序中。 - user295197

Answer 5

我编写了一个方法，可以从Excel中获取已经筛选好的数据，以范围格式呈现。

        Worksheet sheet = null;
        sheet = (Worksheet)context.cDocumentExcel.Sheets[requiredSheetName];

        DataTable dt = new DataTable();
        sheet.Activate();
        sheet.UsedRange.Select();
        List<Range> ranges = new List<Range>();
        Range usedrange = sheet.UsedRange;

        foreach (var oneRange in usedrange.SpecialCells(XlCellType.xlCellTypeVisible))
        {
            ranges.Add(oneRange);
        }


        dt = (_makeTableFromRange(ranges));

从范围转换为数据表

 private static DataTable _makeTableFromRange(List<Range> ranges)
    {
        var table = new DataTable();


        foreach (var range in ranges)
        {
            while (table.Columns.Count < range.Column)
            {
                table.Columns.Add();
            }

            while (table.Rows.Count < range.Row)
            {
                table.Rows.Add();
            }

            table.Rows[range.Row - 1][range.Column - 1] = range.Value2;
        }


        //clean from empty rows
        var filteredRows = table.Rows.Cast<DataRow>().
                        Where(row => !row.ItemArray.All(field => field is System.DBNull ||
                                                                 string.Compare((field as string).Trim(), string.Empty) ==
                                                                 0));
        table = filteredRows.CopyToDataTable();
        return table;
    }

- Leonid Pavlov