Cross workbook formulas allow you to calculate values by referring and using data from different workbooks. For example, if there are 5 workbooks for different subjects, you can add the marks of all five subjects in a worksheet by using cross workbook formulas.
DsExcel supports using cross-workbook formulas by using the folder or web path for external workbook. The GetExcelLinkSources property 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 cross workbook formula by using the folder path for external workbook and update 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"); |
Refer to the following example code to use cross workbook formula by using the web path for external workbook and update 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"); |