officefileapi-116623-excel-export-library-formulas-how-to-create-a-cell-formula.md
This example demonstrates how to create cell formulas using three different methods.
In this code, the exporter instance is created with the XlExport.CreateExporter method which also creates the formula parser. A worksheet contains a heading row and four rows populated with data. The last column in each data row contains a formula created by supplying a formula string to the IXlCell.SetFormula method.
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat, new XlFormulaParser());
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
// Create worksheet columns and set their widths.
for (int i = 0; i < 4; i++) {
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 80;
}
}
// Generate data for the document.
string[] header = new string[] { "Description", "QTY", "Price", "Amount" };
string[] product = new string[] { "Camembert", "Gorgonzola", "Mascarpone", "Mozzarella" };
int[] qty = new int[] { 12, 15, 25, 10 };
double[] price = new double[] { 23.25, 15.50, 12.99, 8.95 };
double discount = 0.2;
// Create the header row.
using (IXlRow row = sheet.CreateRow()) {
for (int i = 0; i < 4; i++) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = header[i];
}
}
}
// Create data rows using string formulas.
for (int i = 0; i < 4; i++) {
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = product[i];
}
using (IXlCell cell = row.CreateCell()) {
cell.Value = qty[i];
}
using (IXlCell cell = row.CreateCell()) {
cell.Value = price[i];
}
using (IXlCell cell = row.CreateCell()) {
// Set the formula to calculate the amount
// applying 20% quantity discount on orders more than 15 items.
cell.SetFormula(String.Format("=IF(B{0}>15,C{0}*B{0}*(1-{1}),C{0}*B{0})", i + 2, discount));
}
}
}
}
}
' Create an exporter instance.
Dim exporter As IXlExporter = XlExport.CreateExporter(documentFormat, New XlFormulaParser())
' Create a new document.
Using document As IXlDocument = exporter.CreateDocument(stream)
document.Options.Culture = CultureInfo.CurrentCulture
' Create a worksheet.
Using sheet As IXlSheet = document.CreateSheet()
' Create worksheet columns and set their widths.
For i As Integer = 0 To 3
Using column As IXlColumn = sheet.CreateColumn()
column.WidthInPixels = 80
End Using
Next i
' Generate data for the document.
Dim header() As String = { "Description", "QTY", "Price", "Amount" }
Dim product() As String = { "Camembert", "Gorgonzola", "Mascarpone", "Mozzarella" }
Dim qty() As Integer = { 12, 15, 25, 10 }
Dim price() As Double = { 23.25, 15.50, 12.99, 8.95 }
Dim discount As Double = 0.2
' Create the header row.
Using row As IXlRow = sheet.CreateRow()
For i As Integer = 0 To 3
Using cell As IXlCell = row.CreateCell()
cell.Value = header(i)
End Using
Next i
End Using
' Create data rows using string formulas.
For i As Integer = 0 To 3
Using row As IXlRow = sheet.CreateRow()
Using cell As IXlCell = row.CreateCell()
cell.Value = product(i)
End Using
Using cell As IXlCell = row.CreateCell()
cell.Value = qty(i)
End Using
Using cell As IXlCell = row.CreateCell()
cell.Value = price(i)
End Using
Using cell As IXlCell = row.CreateCell()
' Set the formula to calculate the amount
' applying 20% quantity discount on orders more than 15 items.
cell.SetFormula(String.Format("=IF(B{0}>15,C{0}*B{0}*(1-{1}),C{0}*B{0})", i + 2, discount))
End Using
End Using
Next i
End Using
End Using
This code snippet creates an IXlFormulaParameter expression from a combination of constants, operators and functions. Constants are transformed into the IXlFormulaParameter objects with the XlFunc.Param method. Operators are static methods of the XlOper object and functions are static methods of the XlFunc object.
When an expression is created, the IXlCell.SetFormula method is used to enter expression into a worksheet cell as the cell formula.
// Create the total row using IXlFormulaParameter.
using (IXlRow row = sheet.CreateRow()) {
row.SkipCells(2);
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Total:";
cell.ApplyFormatting(totalRowFormatting);
}
using (IXlCell cell = row.CreateCell()) {
// Set the formula to calculate the total amount plus 10 handling fee.
// =SUM($D$2:$D$5)+10
IXlFormulaParameter const10 = XlFunc.Param(10);
IXlFormulaParameter sumAmountFunction = XlFunc.Sum(XlCellRange.FromLTRB(cell.ColumnIndex, 1, cell.ColumnIndex, row.RowIndex - 1).AsAbsolute());
cell.SetFormula(XlOper.Add(sumAmountFunction, const10));
cell.ApplyFormatting(totalRowFormatting);
}
}
' Create the total row using IXlFormulaParameter.
Using row As IXlRow = sheet.CreateRow()
row.SkipCells(2)
Using cell As IXlCell = row.CreateCell()
cell.Value = "Total:"
cell.ApplyFormatting(totalRowFormatting)
End Using
Using cell As IXlCell = row.CreateCell()
' Set the formula to calculate the total amount plus 10 handling fee.
' =SUM($D$2:$D$5)+10
Dim const10 As IXlFormulaParameter = XlFunc.Param(10)
Dim sumAmountFunction As IXlFormulaParameter = XlFunc.Sum(XlCellRange.FromLTRB(cell.ColumnIndex, 1, cell.ColumnIndex, row.RowIndex - 1).AsAbsolute())
cell.SetFormula(XlOper.Add(sumAmountFunction, const10))
cell.ApplyFormatting(totalRowFormatting)
End Using
End Using
This code snippet show how to create an expression “from scratch” by adding formula tokens (aka PTGs, “parsed things”) to the XlExpression instance which is a List<T><DevExpress.Export.Xl.XlPtgBase,> list of tokens arranged in Reverse-Polish Notation order. Subsequently, the expression is passed to the IXlCell.SetFormula method to specify a cell formula.
// Create a formula using XlExpression.
using (IXlRow row = sheet.CreateRow()) {
row.SkipCells(2);
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Mean value:";
cell.ApplyFormatting(totalRowFormatting);
}
using (IXlCell cell = row.CreateCell()) {
// Set the formula to calculate the mean value.
// =$D$6/4
XlExpression expression = new XlExpression();
expression.Add(new XlPtgRef(new XlCellPosition(cell.ColumnIndex, row.RowIndex - 1, XlPositionType.Absolute, XlPositionType.Absolute)));
expression.Add(new XlPtgInt(row.RowIndex - 2));
expression.Add(new XlPtgBinaryOperator(XlPtgTypeCode.Div));
cell.SetFormula(expression);
cell.ApplyFormatting(totalRowFormatting);
}
}
' Create a formula using XlExpression.
Using row As IXlRow = sheet.CreateRow()
row.SkipCells(2)
Using cell As IXlCell = row.CreateCell()
cell.Value = "Mean value:"
cell.ApplyFormatting(totalRowFormatting)
End Using
Using cell As IXlCell = row.CreateCell()
' Set the formula to calculate the mean value.
' =$D$6/4
Dim expression As New XlExpression()
expression.Add(New XlPtgRef(New XlCellPosition(cell.ColumnIndex, row.RowIndex - 1, XlPositionType.Absolute, XlPositionType.Absolute)))
expression.Add(New XlPtgInt(row.RowIndex - 2))
expression.Add(New XlPtgBinaryOperator(XlPtgTypeCode.Div))
cell.SetFormula(expression)
cell.ApplyFormatting(totalRowFormatting)
End Using
End Using