Excel API Library for .NET - Sample Browser | Document Solutions | Cross Workbook Formula(Use Case)

[フレーム]

Refer to the following example code to see how to use cross workbook formula and update the excel links.


 
 //create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
workbook.Open(this.GetResourceStream("xlsx\\Urban Centers 2017.xlsx"));
var flints = new GrapeCity.Documents.Excel.Workbook();
flints.Open(this.GetResourceStream("xlsx\\Flints.xlsx"));
var jackson = new GrapeCity.Documents.Excel.Workbook();
jackson.Open(this.GetResourceStream("xlsx\\Jackson.xlsx"));
var petrosky = new GrapeCity.Documents.Excel.Workbook();
petrosky.Open(this.GetResourceStream("xlsx\\Petrosky.xlsx"));
workbook.Worksheets[0].Range["B6:E11"].Formula = "='[Flints.xlsx]Sheet1'!B7+'[Jackson.xlsx]Sheet1'!B6+'[Petrosky.xlsx]Sheet1'!B7";
workbook.UpdateExcelLink("Flints.xlsx", flints);
workbook.UpdateExcelLink("Jackson.xlsx", jackson);
workbook.UpdateExcelLink("Petrosky.xlsx", petrosky);
workbook.Calculate();
 
// Save to an excel file
workbook.Save("CrossWorkbookFormula2.xlsx");


 
 ' Create a new Workbook
Dim workbook As New Workbook
workbook.Open(GetResourceStream("xlsx\Urban Centers 2017.xlsx"))
Dim flints = New Excel.Workbook()
flints.Open(GetResourceStream("xlsx\Flints.xlsx"))
Dim jackson = New Excel.Workbook()
jackson.Open(GetResourceStream("xlsx\Jackson.xlsx"))
Dim petrosky = New Excel.Workbook()
petrosky.Open(GetResourceStream("xlsx\Petrosky.xlsx"))
workbook.Worksheets(0).Range("B6:E11").Formula = "='[Flints.xlsx]Sheet1'!B7+'[Jackson.xlsx]Sheet1'!B6+'[Petrosky.xlsx]Sheet1'!B7"
workbook.UpdateExcelLink("Flints.xlsx", flints)
workbook.UpdateExcelLink("Jackson.xlsx", jackson)
workbook.UpdateExcelLink("Petrosky.xlsx", petrosky)
workbook.Calculate()
 
' save to an excel file
workbook.Save("CrossWorkbookFormula2.xlsx")