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

问题描述

我想我遇到的情况最简单的答案是最难的人找到我还没有碰到过，在一个简单的方法把这个给我任何搜索来的。这是对于 Excel 2010中和 VS 2010 现有VSTO（C＃）项目中。

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.

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

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.

我知道，我问了很多在这里，但这么多的VSTO信息似乎是DIS-节理，而不是总是很容易找到。 ！谢谢

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!

推荐答案

编辑：太好了，我只注意到我错过了你的问题的重要组成部分，获得更新和删除回工作表。我完全不知道，如果这是可能的，但我认为这使我的解决方案一文不值。我会离开这里也无妨，说不定能以任何方式帮助。

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.

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

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.

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

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

我。我给你的Excel的一部分，也许别人能做到休息

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

首先，打开Excel和工作簿：

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

然后以某种方式得到正确的工作表。你有几个possiblities：

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;

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

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.

最后关闭Excel再次：

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

使用互操作后无法正确关闭Excel是一个任务本身，因为你必须确保COM的所有引用对象已被释放。我发现这样做最简单的方法是做所有的工作，除了开在一个单独的方法关闭Excel和该工作簿（所以我的第一个和最后一个代码块）。这确保了该方法所用的COM对象超出范围时，退出被调用。

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