Excel API Library for .NET - Sample Browser | Document Solutions | Use table formulas

[フレーム]

Table formula refers to a formula that is used as a structured reference in the worksheet instead of using it as an explicit cell reference. Refer to the following example code to set table formula in your spreadsheets.


 
 //create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A:E"].ColumnWidth = 15;
worksheet.Range["A1:E3"].Value = new object[,]
{
 {"SalesPerson", "Region", "SalesAmount", "ComPct", "ComAmt"},
 {"Joe", "North", 260, 0.10, null},
 {"Nia", "South", 660, 0.15, null},
};
worksheet.Tables.Add(worksheet.Range["A1:E3"], true);
worksheet.Tables[0].Name = "DeptSales";
worksheet.Tables[0].Columns["ComPct"].DataBodyRange.NumberFormat = "0%";
//Use table formula in table range.
worksheet.Tables[0].Columns["ComAmt"].DataBodyRange.Formula = "=[@ComPct]*[@SalesAmount]";
//Use table formula out of table range.
worksheet.Range["F2"].Formula = "=SUM(DeptSales[@SalesAmount])";
worksheet.Range["G2"].Formula = "=SUM(DeptSales[[#Data],[SalesAmount]])";
worksheet.Range["H2"].Formula = "=SUM(DeptSales[SalesAmount])";
worksheet.Range["I2"].Formula = "=SUM(DeptSales[@ComPct], DeptSales[@ComAmt])";
//judge if Range F2:I2 have formula.
for (int i = 5; i <= 8; i++)
{
 if (worksheet.Range[1, i].HasFormula)
 {
 worksheet.Range[1, i].Interior.Color = Color.LightBlue;
 }
}
 
// Save to an excel file
workbook.Save("UseTableFormula.xlsx");


 
 ' Create a new Workbook
Dim workbook As New Workbook
Dim worksheet As IWorksheet = workbook.Worksheets(0)
worksheet.Range("A:E").ColumnWidth = 15
worksheet.Range("A1:E3").Value = New Object(,) {
 {"SalesPerson", "Region", "SalesAmount", "ComPct", "ComAmt"},
 {"Joe", "North", 260, 0.1, Nothing},
 {"Nia", "South", 660, 0.15, Nothing}
}
worksheet.Tables.Add(worksheet.Range("A1:E3"), True)
worksheet.Tables(0).Name = "DeptSales"
worksheet.Tables(0).Columns("ComPct").DataBodyRange.NumberFormat = "0%"
'Use table formula in table range.
worksheet.Tables(0).Columns("ComAmt").DataBodyRange.Formula = "=[@ComPct]*[@SalesAmount]"
'Use table formula out of table range.
worksheet.Range!F2.Formula = "=SUM(DeptSales[@SalesAmount])"
worksheet.Range!G2.Formula = "=SUM(DeptSales[[#Data],[SalesAmount]])"
worksheet.Range!H2.Formula = "=SUM(DeptSales[SalesAmount])"
worksheet.Range!I2.Formula = "=SUM(DeptSales[@ComPct], DeptSales[@ComAmt])"
'judge if Range F2:I2 have formula.
For i As Integer = 5 To 8
 If worksheet.Range(1, i).HasFormula Then
 worksheet.Range(1, i).Interior.Color = Color.LightBlue
 End If
Next i
 
' save to an excel file
workbook.Save("UseTableFormula.xlsx")