I'm having problems applying a formula to a cell when the formula contains the if function. Example:
dim f as Formula = Formula.Parse("=if(1>1, 0, 1)", CellReferenceMode.R1C1)
f.ApplyTo(worksheet.rows(1).cells(1))
Everything complies and runs fine. But when I open the excel file the cell value is #N/A. I'm using Infragistics35.WebUI.Excel.v9.2. Is there something wrong with my formula or the assembly?
Thanks,
Joe
Hi Joe,
I tried this out and I get the same results. It appears to only happen when you specify the third parameter to the 'if' function. If you do "=if(1>1, 0)", it works okay. Of course, that makes the 'if' function pretty limited.
It also looks like this only happens if you export to the older Excel format. If you export to Excel2007 format, it works correctly.
I'm going to forward this thread over to Infragistics Developer Support so they can write it up for development review and get this fixed.
HI,
Iam facing the same issue with the "if" statement. Iam using the formula "=IF(B1>=C1,0,(C1-B1)*D1)" for evaluating values in infragistics. The problem is that when i enter any values between 0-9 it works good, but anything above 9 it evaluates to zero everytime. I dont know why it works only between 0-9 values.
Please help me with this.
Mahesh
Hi Mahesh,
The problem you describe doesn't seem to be related to the original issue here at all, it seems completely new.
There's not much here to go on, either. What version of the grid are you using?
Can you provide a small sample project demonstrating the issue?
Hi,
Thanks for the reply..Actually iam using Infragistics Web datagrid to bind the data. The requirement is giving an excel type functioanlity on the webgrid. So, for this we read the excel sheet, the values & formulas, save it to the database & populate it on the page. After that the user can edit values on the columns & depending on that value, the other formula columns should be changed.
We use datatable to bind the webdatagrid. So when the user edit any value on the page, the datasource (datatable) is taken & an infragistics Excel workbook iinstance is created. The new set of values sets in the workbook instance to recalculate values. Then again converts to datatable & binds it.
Here is the code being used:
private
void CalculateUsingExcel()
{
Infragistics.Excel.
Workbook book = new Infragistics.Excel.Workbook();
Worksheet sheet = book.Worksheets.Add("Supplier");
book.SuspendCalculations();
sheet.Rows[rowIndex + 2].Cells[2 + colIndex].ApplyFormula(c.Formula);
}
else
sheet.Rows[rowIndex + 2].Cells[2 + colIndex].Value = c.Value;
book.ResumeCalculations();
System.Diagnostics.
Debug.WriteLine(string.Format("CalculateUsingExcel: Row={0} Col={1}",rowIndex, colIndex));
if
(o != null)
c.Value = o.ToString();
--------------------------
This method recalculates the value of the formula cells.
But when recalculating the formula "=IF(C10>=D10,0,(D10-C10)*E10)" (for the whole sequence of column), it doesnt reads the false condition.I mean it always reads as zero,and if u swap the values, it will evaluate "(D10-C10)*E10" everytime.
Also i observed a strange behaviour, if i enter any values betwwen 0-9 it works great but when u enter values above 9 it always gives zero. Regardless of what ever value is there in comparison.
So basically the issue is while calculation of the formula "=IF(C10>=D10,0,(D10-C10)*E10)" .
PLease let me know if u need more clarification.
I'm afraid I have no explanation for that. You might want to try creating a small sample project which demonstrates this and post it over on the WebGrid forum and someone there should be able to check it out.