I think I may be miss-understanding the way in which formulas work... I have put together a very simple example of what I am trying to do
int columnIndex = 1; int rowIndex = 1; decimal value1 = 10.2M; decimal value2 = 5.1M;
WorksheetRow row = _worksheet.Rows[rowIndex]; row.Cells[1].Value = value1; row.Cells[2].Value = value2;
string col1Label = ExcelColumnFromNumber(1); string col2Label = ExcelColumnFromNumber(2);
Formula formulae = Formula.Parse($"=SUM(${col1Label}${rowIndex}:${col2Label}${rowIndex})", CellReferenceMode.A1); row.Cells[4].CellFormat.SetFormatting(CurrencyCellFormat); formulae.ApplyTo(row.Cells[4]);
What I find when I look in my generated .xlsx file is that my value1 is in B2, value2 is in C2, my formula is in E2 as expected however the formula is =SUM($A$1:$B$1) whereas it should be =SUM($B$2:$C$2). What am I doing wron as this seems so simple. I have tried various ways of setting the formula for the cell but the row and cell are always 1 out
Hello Chris,
The values you plot are correctly translated to B2 and C2, as arrays would start from index 0, thus Rows[1].Cells[1] is B2 and Rows[1].Cells[2] is C2.
However, it would seem that the following method ExcelColumnFromNumber(1) returns 'A'. Since your Formula is taking ExcelColumnFromNumber(1) + rowIndex, and rowIndex is 1, the result is A1.
The formula string is literal, so passing 1 will convert to 1, not the second element. Indexes in Excel start from 1, so you may want to add + 1 to rows. Maybe the issue in your method is the same?
Please let me know if you still have any further questions.
Sincerely,Tihomir TonevAssociate Software DeveloperInfragistics
Thanks I think I have got it working as you suggest - it just seems odd that the rows especially seem to start at 0 when setting a value but 1 when used in a formula
I am glad to hear that you have found my suggestion helpful.
Thank you for using Infragistics Components.