如何创建读取自定义数据的 Excel 加载项？

发布于 2024-08-23 05:44:10 字数 273 浏览 5 评论 0原文

我的任务是为 Excel 2007 创建一个插件，用于从自定义数据源读取数据。我已经有了读取数据的代码。它用 C# 编写并返回对象。这是针对银行系统的，我无法直接访问数据。必须向 C# DLL 提供用户 ID、密码和硬件生成的密钥才能获取最新数据。

我的老板希望数据每分钟自动更新一次，或者使用 Excel 提供的刷新功能手动更新。因此，我需要使数据显示在 Excel 中，就像来自标准数据库连接一样。

我已经四处寻找一个起点，但我正在努力寻找一些有助于满足这一非常具体的要求的信息。有人可以帮忙吗？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

只有影子陪我不离不弃 2024-08-30 05:44:10

首先下载 VSTO (Visual Studio Tools for Office）这将允许您创建 C# Excel 加载项。

在 Visual Studio 中，当您创建新项目时，您将看到 Office，并且可以从中选择 Excel。

从那里开始，一旦你这样做了，你就可以回来提出更具体的问题。

使用 Excel 的一些有用技巧。

选择活动工作表：

Excel.Worksheet sheet = this.Application.ActiveSheet as Excel.Worksheet;

选择特定范围（本例中为 A1 - B5）：

Excel.Range range = sheet.get_Range("A1", "B5") as Excel.Range;

将值设置为整个范围：

 range.Value2 = 2; //will set every cell in A1 through B5 to 2

您可以从二维数组中的范围获取值，例如：

object[,] values = range.Value2 as object[,];
//this will return an multidimensional array representing rows and cols
//as you see in the range. in this case the array is filed with "2"

然后您可以更改整个数组中的值并应用回范围：

   values[2, 2] = 4;  //will change the value to 4 in row 2, col 2 of the *range*
   range.Value2 = values; //set back the whole range to the array

您可以使用此通过首先准备一个数组然后将其设置为范围值来一次更新整个范围的技术。

要从范围内的特定单元格获取值（设置值的方式相同，但相反）。

Excel.Range cell = range.Cells[1,1] as Excel.Range; //this will take the cell from row 1, cell 1. if you used array this would be values[1,1]
string value = (cell.Value2 ?? "").ToString();

这应该允许您在 Excel 中执行基本任务，您可以设置值、获取值以及选择范围。一旦您有更具体的问题，请回来。

不要忘记，来自 Excel 的数组是从 1 开始的，而不是从 0 开始的！！！

Begin with downloading VSTO (Visual Studio Tools for Office) this will allow you to created a C# Excel Add-In.

In Visual Studio when you create a new project you will see Office and you will be able to select Excel from it.

Start from there, once you do that you can come back and ask more specific questions.

Some useful tips with working with Excel.

To Select an Active Sheet:

Excel.Worksheet sheet = this.Application.ActiveSheet as Excel.Worksheet;

To Select a specific range (A1 - B5 in this case):

Excel.Range range = sheet.get_Range("A1", "B5") as Excel.Range;

To Set a value to the whole range:

 range.Value2 = 2; //will set every cell in A1 through B5 to 2

You can get values from range in a 2 dimensional array, for instance:

object[,] values = range.Value2 as object[,];
//this will return an multidimensional array representing rows and cols
//as you see in the range. in this case the array is filed with "2"

You can then change the values in the whole array and apply back to the range:

   values[2, 2] = 4;  //will change the value to 4 in row 2, col 2 of the *range*
   range.Value2 = values; //set back the whole range to the array

You can use this technique to update the whole range at once, by preparing an array first and then setting it to the range values.

To get a value from a specific cell in your range (To set a value is the same way but reverse).

Excel.Range cell = range.Cells[1,1] as Excel.Range; //this will take the cell from row 1, cell 1. if you used array this would be values[1,1]
string value = (cell.Value2 ?? "").ToString();

This should allow you to do basic tasks in Excel, you can set values and get values and select ranges. Once you have more specific question, please come back.

Don't forget, arrays coming from Excel are 1-based not zero-based !!!

回复收藏 0 原文