R1C1 Formula Not Evaluating Correctly

Aspose.Cells Product Family
1

This is the line of code that's generating the formula,

worksheet.Cells(row, 13).R1C1Formula = "=R[" + CStr(row) + "]C[11]*R[" + CStr(row) + "]C[12]"

For the first time through the loop row = 3. So the expression evaluates to "=R[3]C[11]*R[3]C[12]". However, it converts this formula to "=Y7*Z7" instead of "=L4*M4".

At first I thought my freeze pane was causing the problem since I'm freezing 3 rows and the row was 3 off. However, turning it off had no effect. Also, I have no clue why the row is being sent way over to row Y.

Can someone help me figure this out?

Thanks,
Scott

2

Hi,

Could you try the attached version and let us know if it works fine. If you still find the issue, kindly post your template file(s) here to show the issue, we will check it soon.

Thank you.

3

Oh, looks like we need to renew our license. I'll ask my supervisor to do that. Also, last time we got an offer to renew at a discounted rate. Can we renew at a discounted rate again?

In the meantime, this R1C1 formula seems like it would be a basic feature that should've worked from the beginning. I'm even doing a simple formula. I'm assuming I'm not using it correctly. Can you see anything wrong with how I'm using it?

Also, I'm generating the excel file from scratch and I'm not using any template files.

Thanks,
Scott

4

Hi Scott,

Thank you for considering Aspose.

Well, you can test the latest version (by commenting your license code in the application) to verify if it works fine for you. If you still face any problem, please create a sample application to reproduce the issue and post it here. We will check it soon.

Thank You & Best Regards,

5

It didn't work with the newest version. I have attached the generated excel file.

Private Sub CreateTestReport()

Dim filePath As String = ""

If Directory.Exists(Server.MapPath("Reports")) = False Then

Directory.CreateDirectory(Server.MapPath("Reports"))

End If

filePath += "Reports/testreport.xls"

Dim excelWorkbook As New Workbook()

Dim worksheet As Worksheet = excelWorkbook.Worksheets(0)

For row As Integer = 1 To 10

worksheet.Cells(row, 11).PutValue(2)

worksheet.Cells(row, 12).PutValue(4)

worksheet.Cells(row, 13).R1C1Formula = "=R[" + CStr(row) + "]C[11]*R[" + CStr(row) + "]C[12]"

Next

excelWorkbook.Save(Server.MapPath(filePath))

HiddenIFrame.Attributes("src") = "downloadfile.aspx?FileToDownload=" + filePath

End Sub

Thanks,

6

Hi,

Thank you for considering Aspose.

Well, I checked your code with the latest version and the result is fine. Actually, I think there is a slight misunderstanding regarding R1C1 formula at your end. R1C1 reference will be applied from the cell at which you are applying the formula (in your case N2). So you reference will be R1C11 from N2 which will become Y3 (adding one row and 11 columns to N2) and same for R1C12 and it will become Z3.

Hopefully it will clear the process. If you still have any confusion, please do let us know and we will be happy to help you out.

Thank You & Best Regards,

7

Oh, ok. Now that I know that I got it to work by using,

worksheet.Cells(row, 13).R1C1Formula = "=R[0]C[-2]*R[0]C[-1]"

I reccomend updating your documentation to explain this instead of just having a single line that simply states that this property is something developers can use.

http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/using-formulasfunctions-to-process-data.html

Thanks,

8

Hi,

Thank you for considering Aspose.

Good to know that your issue is resolved. We will update the documentation soon.

Thank You & Best Regards,