Free Support Forum -

Dinamic rows and colums- formula error

Hi every one, i´m new using aspose.cells and i thing it´s a great component :-); but i´m having some trouble calculating dinamic formulas.

I have a dinamic query in SQL Server and returns a table with dinamic columns and rows, i fill a dataset then export the dataset to Excel using aspose.cells.

the problem comes when more than 24 columns return in the datatable because i´m using ascii code to generate formulas here is my code:

j = DsNetos.Tables(“ImportesNetos”).Rows.Count + 4
For i = 0 To DsNetos.Tables(“ImportesNetos”).Columns.Count - 6
ArchExcel.Worksheets(2).Cells(j, 2 + i).Formula = “=SUM(” + Chr(Val(67 + i)) + (2).ToString() + “:” + Chr(Val(67 + i)) + (j).ToString() + ")"


There is any way to avoid ascii to generate formula?


Well, since you are ASCII code to set formulas dynamically. I think the problem is with the carrot sign " ^ " which does not parse to render standard ASCII characters accordingly for the Excel formula.

I think in your formula if we simply provide i as 27, the formula would become e.g, "=SUM(^2:^14)" which is an invalid formula and the error would be displayed.

ArchExcel.Worksheets(2).Cells(j, 2 + i).Formula = "=SUM(" + Chr(Val(67 + i)) + (2).ToString() + ":" + Chr(Val(67 + i)) + (j).ToString() + ")"

So, I think you should avoid using ascii formula code for this case.

Thank you.

Please use CellsHelper.CellIndexToName method to convert row/column index to cell name.

Thanks for your support, it was very helpful :slight_smile: