Free Support Forum - aspose.com

How to Sum whole column in Excel worksheet in .NET

I am using a Cells to generate a spreadsheet from a series of datatables. I am using the Putvalue method to place that values into the cells. Also several of these cells are formulas that I am also writing out based on other values.

There will be possible 75 to 100 rows written out and I need to create a sum off several of these rows. Currently I am using the following code to create the formulas in for the cells in the row.

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

cell(currentrow, currentcolumn).PutValue(Data.GetFtype(dtTest.Rows(i)("Ftype")))
cell(currentrow, currentcolumn + 4).PutValue(dtTest.Rows(i)("QtyQA"))
cell(currentrow, currentcolumn + 5).PutValue(dtTest.Rows(i)("QtyPkg"))
cell(currentrow, currentcolumn + 6).PutValue(dtTest.Rows(i)("QtyBasing"))
cell(currentrow, currentcolumn + 7).PutValue(dtTest.Rows(i)("QtyStartQueue"))
cell(currentrow, currentcolumn + 8).PutValue(dtTest.Rows(i)("QtyPretest"))
cell(currentrow, currentcolumn + 17).PutValue(dtTest.Rows(i)("NVGBal"))
cell(currentrow, currentcolumn + 18).PutValue(dtTest.Rows(i)("SpareBal"))

'CalcuLations

QACount = workbook.GetCellName(currentrow, currentcolumn + 4)
PkgCount = workbook.GetCellName(currentrow, currentcolumn + 5)
BasCount = workbook.GetCellName(currentrow, currentcolumn + 6)
StartCount = workbook.GetCellName(currentrow, currentcolumn + 7)
PreCount = workbook.GetCellName(currentrow, currentcolumn + 8)
BalanceCount = workbook.GetCellName(currentrow, currentcolumn + 9)
RequiredStartsCount = workbook.GetCellName(currentrow, currentcolumn + 13)
NVGCount = workbook.GetCellName(currentrow, currentcolumn + 17)
SpareCount = workbook.GetCellName(currentrow, currentcolumn + 18)

cell(currentrow, currentcolumn + 9).Formula = "=Sum(" & NVGCount & "+" &SpareCount & ")"
cell(currentrow, currentcolumn + 10).Formula = "=IF(SIGN(" & BalanceCount & ")=-1,0,IF(SIGN(" & BalanceCount & ")=1,(" & BalanceCount & "), IF(SIGN(" & BalanceCount & ")=0,0)))"
cell(currentrow, currentcolumn + 11).Formula = "=(" & BalanceCount & "-" & QACount & ")"
cell(currentrow, currentcolumn + 13).Formula = "=(" & QACount & "+" & PkgCount & "+" & BasCount & "+" & StartCount & "+" & PreCount & ")"
cell(currentrow, currentcolumn + 14).Formula = "=(" & BalanceCount & "-" & RequiredStartsCount & ")"

currentrow = currentrow + 1
Next

Is there a way to sum each column with creating a string for each cell and then using the Sum formula?

Thanks

To sum cells in a column, you can try:

'Sum a whole column

cell.Formula = "=SUM(A:A)"

or

'Sum cells in a column

cell.Formula = "=SUM(A1:A100)"