How to set Complex formulas in Aspose.cells

Aspose.Cells Product Family
1

Hi,

How can i set the complex forumla in Aspose.cells I am trying to set the below formula, but getting syntax error:

=IF(A12<>"",LEFT($A12&" ",30)&" "&TEXT(DAY($C12),"00")&"/"&TEXT(MONTH($C12),"00")&"/"&TEXT(YEAR($C12),"0000")&" "&IF($E12="","N",TEXT($E12,"0"))&" "&TEXT($G12,"00")&"."&TEXT($H12,"00")&" "&TEXT($J12,"00")&"."&TEXT($K12,"00")&" "&TEXT($M12,"00")&"."&TEXT($N12,"00")&" "&TEXT($P12,"00")&"."&TEXT($Q12,"00")&" "&$D12&" "&TEXT($S12,"00")&"."&TEXT($T12,"00"),"")

I am using 4.4.0.0 licensed version of Aspose.Cells

Please advise.

Thanks

2

Hi,


We cannot help you much if you are using some older version of the product i.e. v4.4.0, we can only recommend you to kindly use and upgrade to latest version (v7.5.1.x) of the product which is more enhanced and we incorporated lost of new and advanced features regarding MS Excel 97 - 2007/2010 file formats.

To specify the formula, you may try to use. Cell.Formula attribute, see the document for your reference:
http://www.aspose.com/docs/display/cellsnet/Using+Formulas+or+Functions+to+Process+Data

If you still find the issue, kindly give us your template Excel file (you may create it in MS Excel manually) and specify your desired formula in it, save the file and post it here. We will check it soon.

Thank you.



3

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

It is quite complex formula. You should first use this formula in MS-Excel file and see if it works fine. Then load your workbook, and check it using Debugging in Visual Studio and find out the right syntax for it.

Also we recommend you to download and use the latest version Aspose.Cells
for .NET v7.5.1.2 since your version is quite old.

4

Thanks for reply.I have attached the test file Template that i am trying to implement in C# using Aspose, please see the column - U ("For System Use Only") in the attached file.Also i am trying like below:

sheet.Cells[

"U9"].Formula = "=IF(A9<>"",LEFT($A9&" ",30)&" "&TEXT(DAY($C9),"00")&"/"&TEXT(MONTH($C9),"00")&"/"&TEXT(YEAR($C9),"0000")&" "&IF($E9="","N",TEXT($E9,"0"))&" "&TEXT($G9,"00")&"."&TEXT($H9,"00")&" "&TEXT($J9,"00")&"."&TEXT($K9,"00")&" "&TEXT($M9,"00")&"."&TEXT($N9,"00")&" "&TEXT($P9,"00")&"."&TEXT($Q9,"00")&" "&$D9&" "&TEXT($S9,"00")&"."&TEXT($T9,"00"),"")";

5

can some one provide some idea on above mentioned issue........

Thanks

6

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We have checked your complex formula and wrote the following code. It does not give any syntax error and compiles fine. We have tested it with the latest version.

worksheet.Cells[“U9”].Formula = “=IF(A9<>”",LEFT($A9&" “,30)&” “&TEXT(DAY($C9),“00”)&”/"&TEXT(MONTH($C9),“00”)&"/"&TEXT(YEAR($C9),“0000”)&" “&IF($E9=”",“N”,TEXT($E9,“0”))&" “&TEXT($G9,“00”)&”."&TEXT($H9,“00”)&" “&TEXT($J9,“00”)&”."&TEXT($K9,“00”)&" “&TEXT($M9,“00”)&”."&TEXT($N9,“00”)&" “&TEXT($P9,“00”)&”."&TEXT($Q9,“00”)&" “&$D9&” “&TEXT($S9,“00”)&”."&TEXT($T9,“00”),"")";

7

Thanks for reply, i have implemented using the R1C1 formula for the above code its working fine

string

r1c1formula = "=IF(RC[-20]<>\"\",LEFT(RC1&\" \",30)&\" \"&TEXT(DAY(RC3),\"00\")&\"/\"&TEXT(MONTH(RC3),\"00\")&\"/\"&TEXT(YEAR(RC3),\"0000\")&\" \"&IF(RC5=\"\",\"N\",TEXT(RC5,\"0\"))&\" \"&TEXT(RC7,\"00\")&\".\"&TEXT(RC8,\"00\")&\" \"&TEXT(RC10,\"00\")&\".\"&TEXT(RC11,\"00\")&\" \"&TEXT(RC13,\"00\")&\".\"&TEXT(RC14,\"00\")&\" \"&TEXT(RC16,\"00\")&\".\"&TEXT(RC17,\"00\")&\" \"&RC4&\" \"&TEXT(RC19,\"00\")&\".\"&TEXT(RC20,\"00\"),\"\")";

for (int row = startRow; row <= endRow; row++)

{

sheet.Cells[row, 20].R1C1Formula = r1c1formula;

}

***************************************

but i have another similar kind of formula that i need to implemented in C#, its working fine in EXCEL, but while using R1C1Formula in C# using Aspose its giving error:

string

r1c1formula = "=LEFT(RC3&\" \",30)&\" \"&LEFT(RC1&\" \",3)&\" \"&RC[-4]&\" \"&TEXT(RC6*100,\"0000000000 \")&TEXT(RC8*100,\"0000000000\")&\" 0000000000 \"&TEXT(DAY(R7C4),\"00\")&\"/\"&TEXT(MONTH(R7C4),\"00\")&\"/\"&TEXT(YEAR(R7C4),\"0000\")&\" 00.00 \"&R6C4";

for (int row = 12; row <= maxRow; row++)

{

sheet.Cells[row, 8].R1C1Formula = r1c1formula;

}

Please find the attached test file that i am testing...and column 9 (resultvalues) shows the formula results.

can any one please advise on this?

8

whether issue is because of formatting?, plz advise…for implementing the below formula using R1C1?



string

r1c1formula = “=LEFT(RC3&” “,30)&” “&LEFT(RC1&” “,3)&” “&RC[-4]&” “&TEXT(RC6100,"0000000000 ")&TEXT(RC8100,“0000000000”)&” 0000000000 “&TEXT(DAY(R7C4),“00”)&”/"&TEXT(MONTH(R7C4),“00”)&"/"&TEXT(YEAR(R7C4),“0000”)&" 00.00 “&R6C4”;

for (int row = 12; row <= maxRow; row++)

{

sheet.Cells[row, 8].R1C1Formula = r1c1formula;

}

9

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please use the following R1C1 formula for your needs.

worksheet.Cells[11, 8].R1C1Formula = “=LEFT(RC3&” “,30)&” “&LEFT(RC1&” “,3)&” “&RC[-4]&” “&TEXT(RC6100,"0000000000 ")&TEXT(RC8100,“0000000000”)&” 0000000000 “&TEXT(DAY(R7C4),“00”)&”/"&TEXT(MONTH(R7C4),“00”)&"/"&TEXT(YEAR(R7C4),“0000”)&" 00.00 “&R6C4”;

10

Hi,

I have used the following formula suggested by you:

"=LEFT(RC3&\" \",30)&\" \"&LEFT(RC1&\" \",3)&\" \"&RC[-4]&\" \"&TEXT(RC6*100,\"0000000000 \")&TEXT(RC8*100,\"0000000000\")&\" 0000000000 \"&TEXT(DAY(R7C4),\"00\")&\"/\"&TEXT(MONTH(R7C4),\"00\")&\"/\"&TEXT(YEAR(R7C4),\"0000\")&\" 00.00 \"&R6C4";

Please find the below error that i am getting, please advise....

{Aspose.Cells.CellsException: Error in Cell: I13-Invalid formula:"=LEFT($C13&" ",30)&" "&LEFT($A13&" ",3)&" "&E13&" "&TEXT($F13*100,"0000000000 ")&TEXT($H13*100,"0000000000")&" 0000000000 "&TEXT(DAY($D$7),"00")&"/"&TEXT(MONTH($D$7),"00")&"/"&TEXT(YEAR($D$7),"0000")&" 00.00 "&$D$6".
at Aspose.Cells.x40937ad35b1cf5f7.x1f490eac106aee12(Cell xe6de5e5fa2d44af5)
at Aspose.Cells.Cell.set_Formula(String value)
at Aspose.Cells.Cell.set_R1C1Formula(String value)
at Results.Builder.RNET.GenerateCREmployeeIncomesAndDeductions.setR1C1Formula(Worksheet sheet, Int32 maxRow) in C:\Documents and Settings\uday.elluru\Desktop\DevReports_20120621\Results.Builder\RNET\GenerateCREmployeeIncomesAndDeductions.cs:line 121
at Results.Builder.RNET.GenerateCREmployeeIncomesAndDeductions.buildWorkbook(Workbook workbook) in C:\Documents and Settings\uday.elluru\Desktop\DevReports_20120621\Results.Builder\RNET\GenerateCREmployeeIncomesAndDeductions.cs:line 78}{Aspose.Cells.CellsException: Error in Cell: I13-Invalid formula:"=LEFT($C13&" ",30)&" "&LEFT($A13&" ",3)&" "&E13&" "&TEXT($F13*100,"0000000000 ")&TEXT($H13*100,"0000000000")&" 0000000000 "&TEXT(DAY($D$7),"00")&"/"&TEXT(MONTH($D$7),"00")&"/"&TEXT(YEAR($D$7),"0000")&" 00.00 "&$D$6".
at Aspose.Cells.x40937ad35b1cf5f7.x1f490eac106aee12(Cell xe6de5e5fa2d44af5)
at Aspose.Cells.Cell.set_Formula(String value)
at Aspose.Cells.Cell.set_R1C1Formula(String value)
at Results.Builder.RNET.GenerateCREmployeeIncomesAndDeductions.setR1C1Formula(Worksheet sheet, Int32 maxRow) in C:\Documents and Settings\uday.elluru\Desktop\DevReports_20120621\Results.Builder\RNET\GenerateCREmployeeIncomesAndDeductions.cs:line 121
at Results.Builder.RNET.GenerateCREmployeeIncomesAndDeductions.buildWorkbook(Workbook workbook) in C:\Documents and Settings\uday.elluru\Desktop\DevReports_20120621\Results.Builder\RNET\GenerateCREmployeeIncomesAndDeductions.cs:line 78}

11

can any one please suggest, whtz the error i am doing....because its bit high priority.....

Thanks

12

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We have tested the above formula with the following code using the latest version Aspose.Cells
for .NET v7.5.1.2, it works fine. Please download and use the latest version for your needs.

We have also attached the output file for your reference.

C#


Workbook workbook = new Workbook();


Worksheet worksheet = workbook.Worksheets[0];


Cell cell = worksheet.Cells[11, 8];

cell.R1C1Formula = “=LEFT(RC3&" ",30)&" "&LEFT(RC1&" ",3)&" "&RC[-4]&" "&TEXT(RC6100,"0000000000 ")&TEXT(RC8100,"0000000000")&" 0000000000 "&TEXT(DAY(R7C4),"00")&"/"&TEXT(MONTH(R7C4),"00")&"/"&TEXT(YEAR(R7C4),"0000")&" 00.00 "&R6C4”;


workbook.Save(“output.xlsx”);