Cross Workbook Formula | Document Solutions for Excel, .NET Edition

Cross Workbook Formula

In This Topic

Cross-workbook formulas allow you to calculate values by referring to and using data from different workbooks. For example, if there are five workbooks for different subjects, you can add the marks of all five subjects to a worksheet by using cross-workbook formulas.

DsExcel supports using cross-workbook formulas by using the folder or web path for an external workbook. The GetExcelLinkSources method can be used to get the names of linked Excel workbooks, and UpdateExcelLinks method to update the caches of Excel links.

Refer to the following example code to use the cross-workbook formula by using the folder path for the external workbook and updating the Excel links:

C#	Copy Code
//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); workbook.Worksheets[0].Range["B1"].Formula = @"='D:\[A.xlsx]Sheet1'!A1"; // create a new workbook as the intance of external workbook. var workbook2 = new GrapeCity.Documents.Excel.Workbook(); workbook2.Worksheets[0].Range["A1"].Value = "Hello, World!"; workbook2.Worksheets[0].Range["A2"].Value = "Hello"; // update the caches of external workbook data. foreach (var item in workbook.GetExcelLinkSources()) { workbook.UpdateExcelLink(item, workbook2); } //save to an Excel file workbook.Save("crossworkbookformulafolderpath.xlsx");

Copy Code

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

workbook.Worksheets[0].Range["B1"].Formula = @"='D:\[A.xlsx]Sheet1'!A1";
// create a new workbook as the intance of external workbook.
var workbook2 = new GrapeCity.Documents.Excel.Workbook();
workbook2.Worksheets[0].Range["A1"].Value = "Hello, World!";
workbook2.Worksheets[0].Range["A2"].Value = "Hello";
// update the caches of external workbook data.
foreach (var item in workbook.GetExcelLinkSources())
{
    workbook.UpdateExcelLink(item, workbook2);
}

//save to an Excel file
workbook.Save("crossworkbookformulafolderpath.xlsx");

Refer to the following example code to use the cross-workbook formula by using the web path for an external workbook and updating the Excel links:

C#	Copy Code
//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); workbook.Worksheets[0].Range["B1"].Formula = "= 'https://developer.mescius.com/dsexcel/[SourceWorkbook.xlsx]Sheet1'!A1"; // create a new workbook as the intance of external workbook. var workbook2 = new GrapeCity.Documents.Excel.Workbook(); workbook2.Worksheets[0].Range["A1"].Value = 100; // update the caches of external workbook data. foreach (var item in workbook.GetExcelLinkSources()) { workbook.UpdateExcelLink(item, workbook2); } //save to an excel file workbook.Save("crossworkbookformulawebpath.xlsx");

Copy Code

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
workbook.Worksheets[0].Range["B1"].Formula = "= 'https://developer.mescius.com/dsexcel/[SourceWorkbook.xlsx]Sheet1'!A1";
// create a new workbook as the intance of external workbook.
var workbook2 = new GrapeCity.Documents.Excel.Workbook();
workbook2.Worksheets[0].Range["A1"].Value = 100;
// update the caches of external workbook data.
foreach (var item in workbook.GetExcelLinkSources())
{
    workbook.UpdateExcelLink(item, workbook2);
}
//save to an excel file
workbook.Save("crossworkbookformulawebpath.xlsx");

Refer to the following example code to use the cross-workbook formula by using the path for an external workbook with the table and updating the Excel links:

C#	Copy Code
// Create a new workbook. var workbook = new Workbook(); // Add data to the cell. workbook.Worksheets[0].Range["A1"].Value = "Total Sales"; // Set the table formula across workbook. workbook.Worksheets[0].Range["B1"].Formula = "=SUM('[SalesTable.xlsx]'!Table1[Sales])"; workbook.Worksheets[0].Range["B1"].NumberFormat = "$#,##0.00"; workbook.Worksheets[0].Range["A:B"].ColumnWidth = 12; // Create another new workbook as the intance of external workbook. var workbook2 = new Workbook(); // Add data for the table. workbook2.ActiveSheet.Range["A1:C6"].Value = new object[,] { { "Product", "Type", "Sales" }, { "Apple", "Fruit", 25000}, { "Grape", "Fruit", 30000 }, { "Carrot", "Vegetable", 28000 }, { "Strawberry", "Fruit", 50000 }, { "Onion", "Vegetable", 23000 } }; // Create the table. workbook2.ActiveSheet.Tables.Add(workbook2.ActiveSheet.Range["A1:C6"], true); workbook2.ActiveSheet.Range["C2:C6"].NumberFormat = "$#,##0.00"; workbook2.ActiveSheet.Range["A:C"].ColumnWidthInPixel = 100; // Update the caches of external workbook data. foreach (var item in workbook.GetExcelLinkSources()) { workbook.UpdateExcelLink(item, workbook2); } // Save both Excel files. workbook.Save("CrossWorkbookTableFormula.xlsx"); workbook2.Save("SalesTable.xlsx");

Copy Code

// Create a new workbook.
var workbook = new Workbook();

// Add data to the cell.
workbook.Worksheets[0].Range["A1"].Value = "Total Sales";

// Set the table formula across workbook.
workbook.Worksheets[0].Range["B1"].Formula = "=SUM('[SalesTable.xlsx]'!Table1[Sales])";
workbook.Worksheets[0].Range["B1"].NumberFormat = "$#,##0.00";
workbook.Worksheets[0].Range["A:B"].ColumnWidth = 12;

// Create another new workbook as the intance of external workbook.
var workbook2 = new Workbook();
            
// Add data for the table.
workbook2.ActiveSheet.Range["A1:C6"].Value = new object[,]
{
{ "Product", "Type", "Sales" },
{ "Apple", "Fruit", 25000},
{ "Grape", "Fruit", 30000 },
{ "Carrot", "Vegetable", 28000 },
{ "Strawberry", "Fruit", 50000 },
{ "Onion", "Vegetable", 23000 }

};

// Create the table.
workbook2.ActiveSheet.Tables.Add(workbook2.ActiveSheet.Range["A1:C6"], true);
workbook2.ActiveSheet.Range["C2:C6"].NumberFormat = "$#,##0.00";
workbook2.ActiveSheet.Range["A:C"].ColumnWidthInPixel = 100;

// Update the caches of external workbook data.
foreach (var item in workbook.GetExcelLinkSources())
{
    workbook.UpdateExcelLink(item, workbook2);
}

// Save both Excel files.
workbook.Save("CrossWorkbookTableFormula.xlsx");
workbook2.Save("SalesTable.xlsx");

Note: You need to open the external link's workbook at the same time you open the workbook where the cross-workbook formula is used to recalculate and show the correct result.