Dinamic rows and colums- formula error

Aspose.Cells Product Family
1

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() + ")"

Next


There is any way to avoid ascii to generate formula?

2

Hi,

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.

3

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

4

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