将C＃变量绑定到excel单元格 [英] Binding a C# variable to an excel cell

问题描述

我在C＃中使用COM互操作来将数据显示到Excel电子表格中。假设我在C＃中有一个学生详细信息的数据结构，并且这在Excel中正确显示。

现在，是否可以更改特定变量的值编辑Excel中相应单元格的C＃？

例如，只要在Excel表中更改Student x的标记，那么与C＃必须更改。

如果需要，也可以使用VBA代码！我不知道这样的事情是否可行，但是如果我可以在这里找到答案，那将是很棒的。

解决方案

以下是一个如何使用事件处理程序执行此操作的示例：

  Excel.DocEvents_ChangeEventHandler eventChange_CellData; 
 int someStudentsScore = 99; 
 
 private void Load_Workbook（）
 {
 Excel.Application xlApp = new Excel.Application（）; 
 Excel.Workbook xlBook = xlApp.Workbooks.Add（）; 
 Excel.Worksheet xlSheet =（Excel.Worksheet）xlBook.Sheets.Item [1]; 
 
 xlSheet.Name =学生资料; 
 
 //您的代码在这里填充数据.... 
 
 eventChange_CellData = new Excel.DocEvents_ChangeEventHandler（DataChange）; 
 
 xlSheet.Change + = eventChange_CellData; 
 
} 
 
 private void DataChange（Excel.Range target）
 {
 if（target.Address ==$ A $ 1）
 {
 //如果A1更改，则更改变量。 
 someStudentsScore = target.Value2; 
} 
} 
  

VBA相当于这个工作表的模块：

  public someStudentsScore 
 
 Private Sub Worksheet_Change（ByVal Target As Range ）
 
如果Target.Address =$ A $ 1然后
'//你可能必须创建一个包装器来获取你的C＃
'//变量进入VBA例程... 
 someStudentsScore = Target.Value 
 End If 
 
 End Sub 
  

I'm using COM interop in C# to display data into an Excel spreadsheet. Assume I have a data structure for 'Student Details' in C# and this is displayed appropriately in Excel.

Now, is it possible to change that value of a particular variable in C# when the corresponding cell in Excel is edited?

For example, as soon as the the marks of Student x is altered in the Excel sheet the variable corresponding to marks in C# must be altered.

If required VBA code could also be used! I don't know whether such a thing is possible, but it would be great if I could find an answer here.

解决方案

Here's an example of how you can do that with an event handler:

Excel.DocEvents_ChangeEventHandler eventChange_CellData;
int someStudentsScore = 99;

private void Load_Workbook()
{
    Excel.Application xlApp = new Excel.Application();
    Excel.Workbook xlBook = xlApp.Workbooks.Add();
    Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Sheets.Item[1];

    xlSheet.Name = "Student Data";

    // Your code for populating data somewhere here....

    eventChange_CellData = new Excel.DocEvents_ChangeEventHandler(DataChange);

    xlSheet.Change += eventChange_CellData;

}

private void DataChange(Excel.Range target)
{
    if(target.Address == "$A$1")
    {
        // If A1 is changed, then change variable.
        someStudentsScore = target.Value2;
    }
}

The VBA equivalent of this would be in the Worksheet's module:

Public someStudentsScore

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
    '// You would probably have to create a wrapper of some kind to get your C#
    '// variable into the VBA routine(s)...
    someStudentsScore = Target.Value
End If

End Sub

这篇关于将C＃变量绑定到excel单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！