使用工作表作为数据源的VSTO Excel的简单示例 [英] Simple Example of VSTO Excel using a worksheet as a datasource

问题描述

我想我遇到一个最简单的答案是找到最难的答案的情况，而且我没有遇到任何以直截了当的方式给我的搜索。这是针对现有VSTO（C＃）项目中的 Excel 2010 和 VS 2010 。

我有一个Excel工作表包含4列数据，我想用作DataGridView的源。有人可以提供（1）从特定工作表获取数据并使用它填充自定义对象的C＃代码片段？ （2）将对象（如IEnumerable列表）绑定到Datagridview，以及（3）网格固有的更新和删除功能的一些片段，并反馈给源工作表。

我知道我在这里要求很多，但是很多VSTO信息似乎是分开的，并不总是很容易找到。谢谢！

解决方案

编辑：很好，我只是注意到我错过了大部分的问题，获取更新并删除回工作表。我完全不知道如果这是可能的，但我认为这使我的解决方案毫无价值。我会离开这里，也许它可以帮助任何方式。

---

为什么需要VSTO？据我所知，VSTO用于Office加载项。但是，由于您希望在DataGridView中显示数据，我假设您有一个WinForms应用程序应该只是访问一个工作簿。在这种情况下，您可以使用Office Interop简单地打开工作簿。只需将Microsoft.Office.Interop.Excel的引用添加到您的项目中，并使用Microsoft.Office.Interop.Excel; 语句添加。

Excel Interop的MSDN参考文档可以在这里找到： http://msdn.microsoft.com/en-us/library/ms262200%28v=office.14%29.aspx

首先，打开Excel和工作簿：

 应用程序app = new Application（）; 
 //可选，但建议用户不要看Excel。 
 app.Visible = false; 
 app.ScreenUpdating = false; 
 // AddToMru参数是可选的，但在自动化方案中推荐。 
工作簿工作簿= app.Workbooks.Open（filepath，AddToMru：false）; 
  

然后以某种方式获取正确的工作表。您有几个可能性：

  //活动工作表（应为上次保存工作簿时处于活动状态的工作表） 。 
工作表sheet = workbook.ActiveSheet; 
 //第一张（注意第一个实际上是1而不是0）。 
工作表sheet = workbook.Worksheets [1]; 
 //具体表。 
 //注意：Excel的不同本地化版本的默认工作表名称不同。 
工作表sheet = workbook.Worksheets [Sheet1]; 
  

然后得到正确的范围。你没有指定你知道所需数据的位置，所以我假定它是固定的列。

  /如果你也知道行数。 
范围范围= sheet.Range [A1，D20]; 
 //如果要获取所有行，直到最后一行有一些数据。 
范围lastUsedCell = sheet.Cells.SpecialCells（XlCellType.xlCellTypeLastCell）; 
 string columnName =D+ lastUsedCell.Row; 
范围范围= sheet.Range [A1，columnName]; 
  

获取值：

  //返回值的可能类型：
 //如果单个单元格在范围内：根据单元格内容的不同类型
 //（string，DateTime，double ，...）
 //如果多个单元格在范围内：正好代表
的二维数组//来自Excel的范围，并且其元素中也有不同类型，具体取决于
 // Excel的单元格的值（应该总是在你的情况下）
 object [，] values = range.Value; 
  

然后可以将二维对象数组用作DataGridView的数据源。我没有使用WinForms多年，所以我不知道你是否可以直接绑定，或者首先需要获取一些特定格式的数据。

最后关闭Excel再次：

  workbook.Close（SaveChanges：false）; 
 workbook = null; 
 app.Quit（）; 
 app = null; 
 //是的，我们真的要两次调用这两个方法，以确保所有
 // COM对象和所有RCW都被收集。 
 GC.Collect（）; 
 GC.WaitForPendingFinalizers（）; 
 GC.Collect（）; 
 GC.WaitForPendingFinalizers（）; 
  

使用Interop后正确关闭Excel是一项任务，因为您必须确保对COM的所有引用物品已经被释放。我找到的最简单的方法是进行所有的工作，除了打开和关闭Excel和工作簿（所以我的第一个和最后一个代码块）在一个单独的方法。这样可以确保调用退出时，该方法中使用的所有COM对象都超出范围。

I think I'm running into a case of "the easiest answers are the hardest ones to find" and I haven't come across any searches that give this to me in a straightforward way. This is for Excel 2010 and VS 2010 within an existing VSTO (C#) project.

I have an Excel worksheet that contains 4 columns of data that I would like to use as a source for a DataGridView. Can someone please provide C# code snippets for (1) getting the data from a particular worksheet and populating a custom object with it? (2) binding the object (like an IEnumerable list) to a Datagridview and (3) some snippets for the update and delete functionality that would be inherent to the grid and feed back to the source worksheet.

I know I'm asking for a lot here, but so much of the VSTO information seems to be dis-jointed and not always easy to find. Thanks!

解决方案

Edit: Great, I just noticed that I missed a big part of your question, getting updates and deletes back to the worksheet. I have absolutely no idea if that is possible but I think that makes my solution worthless. I'll leave it here anyway, maybe it can help in any way.

---

Why do you need VSTO? As far as I know VSTO is used for Office Add-Ins. But since you want to show the data in a DataGridView I assume that you have a WinForms application that should just access a workbook. In this case you can simply open the workbook by using Office Interop. Just add a reference to Microsoft.Office.Interop.Excel to your project and add a using Microsoft.Office.Interop.Excel; statement.

MSDN reference documentation for Excel Interop can be found here: http://msdn.microsoft.com/en-us/library/ms262200%28v=office.14%29.aspx

I'll give you the Excel part, maybe someone else can do the rest.

First, open Excel and the workbook:

Application app = new Application();
// Optional, but recommended if the user shouldn't see Excel.
app.Visible = false;
app.ScreenUpdating = false;
// AddToMru parameter is optional, but recommended in automation scenarios.
Workbook workbook = app.Workbooks.Open(filepath, AddToMru: false);

Then somehow get the correct worksheet. You have a few possiblities:

// Active sheet (should be the one which was active the last time the workbook was saved).
Worksheet sheet = workbook.ActiveSheet;
// First sheet (notice that the first is actually 1 and not 0).
Worksheet sheet = workbook.Worksheets[1];
// Specific sheet.
// Caution: Default sheet names differ for different localized versions of Excel.
Worksheet sheet = workbook.Worksheets["Sheet1"];

Then get the correct range. You didn't specify how you know where the needed data is, so I'll assume it is in fixed columns.

// If you also know the row count.
Range range = sheet.Range["A1", "D20"];
// If you want to get all rows until the last one that has some data.
Range lastUsedCell = sheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell);
string columnName = "D" + lastUsedCell.Row;
Range range = sheet.Range["A1", columnName];

Get the values:

// Possible types of the return value:
// If a single cell is in the range: Different types depending on the cell content
// (string, DateTime, double, ...)
// If multiple cells are in the range: Two dimensional array that exactly represents
// the range from Excel and also has different types in its elements depending on the
// value of the Excel cell (should always be that one in your case)
object[,] values = range.Value;

That two dimensional object array can then be used as a data source for your DataGridView. I haven't used WinForms for years so I don't know if you can bind it directly or first need to get the data into some specific format.

Finally close Excel again:

workbook.Close(SaveChanges: false);
workbook = null;
app.Quit();
app = null;
// Yes, we really want to call those two methods twice to make sure all
// COM objects AND all RCWs are collected.
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();

Correctly closing Excel after using Interop is a task itself because you have to make sure that all references to COM objects have been released. The easiest way I have found to do this is to do all the work except opening and closing Excel and the workbook (so my first and last code block) in a seperate method. This ensures that all COM objects used in that method are out of scope when Quit is called.

这篇关于使用工作表作为数据源的VSTO Excel的简单示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！