Free Support Forum - aspose.com

Creating Formula Inside a Loop

I am trying to create a formula inside a loop to add several columns together. Because of the way my data is structured I am not able to just import my data table and I am using the putvalue method. Below is my code

For i = 0 To dtTest.Rows.Count - 1

cell(currentrow, currentcolumn).PutValue(Data.GetFtype(dtTest.Rows(i)("Ftype")))
cell(currentrow, currentcolumn + 1).PutValue(dtTest.Rows(i)("QtyQA"))
cell(currentrow, currentcolumn + 2).PutValue(dtTest.Rows(i)("QtyBasing"))
cell(currentrow, currentcolumn + 3).PutValue(dtTest.Rows(i)("QtyStartQueue"))
cell(currentrow, currentcolumn + 4).PutValue(dtTest.Rows(i)("QtyPretest"))

'Total In Progress
calCel1 = workbook.GetCellName(currentrow, currentcolumn + 1)
calcel2 = workbook.GetCellName(currentrow, currentcolumn + 2)
calcel3 = workbook.GetCellName(currentrow, currentcolumn + 3)
calcel4 = workbook.GetCellName(currentrow, currentcolumn + 4)

cell(currentrow, currentcolumn + 5).Formula = "=sum(" & calCel1 & "+" & calcel2 & "+" & calcel3 & "+" & calcel4 & ")"
currentrow = currentrow + 1
Next

Creating the formula with the string works but it would seem to me that there has to be a better or more efficient solution. Does anyone have suggestions?

Whenever you set a formula to a cell, Aspose.Cells will parse the string into formula tokens. So I think this is the best way to set formulas.