Help with formulas has quotation marks and Data Validation

Aspose.Cells Product Family
1

Hi,The formulas I am trying to pass over to aspose cells would be like this:

=IF(A2="","-",'Data Validation'!L4);

String formula = How would I write it in this string so I can put into setFormula? I am trying to replicate a excel file that has a formula. The L4 cell looks like it is empty.

Cell cell = cells.getCell(row, col);

cell.setFormula(formula);

Please help as I have been restarting my test environment non stop trying to guess how to do this.

2

Hi,

Thanks for your posting and using Aspose.Cells.

We tested your formula with the following code using the latest version: Aspose.Cells for Java (Latest Version) and it works fine.

Kindly assign the formula in this way: “=IF(A2="","-",‘Data Validation’!L4)”

I have attached the output file generated by the code for your reference.

Java

Workbook workbook = new Workbook();

//Add Data Validation sheet
workbook.getWorksheets().add("Data Validation");

Worksheet worksheet = workbook.getWorksheets().get(0);

Cell cell = worksheet.getCells().get("A1");

cell.setFormula("=IF(A2=\"\",\"-\",'Data Validation'!L4)");

workbook.save("output.xlsx");

3

Hi I am so sorry,in my environment the " character can go through it looks like it struggling with 'DataValidation'!L4.

I get a "unsupported formula" message for the 'data validation'. I am very new to this and I can find where the JDK folder is located

I have the following folders in my Jdk folder:

bin, db, include, jre, lib. I am not really sure where to put those updated aspose cells 8.0.0.1 java.zip file as first I do not remember which JDK version I am on. I installed it last year and had the latest file at that time. I also can't seem to find a php folder anywhere.

any ideas would be appreciated

Thank you

4

Hi,

Thanks for your posting and using Aspose.Cells.

First, please try the code as it is in your own version and see if it works fine. If you get any issue and you need to update the Aspose.Cells for Java version …

Then please use the version found in JDK 1.6 (this version will work with any JDK greater than 1.6 and most probably, you would have JDK 6 or higher)

You will find the version in lib directory, namely aspose-cells-8.0.0.1.jar file.

If you want to check which version of Java is installed, then you can check this article.

( http://www.mkyong.com/java/how-to-check-current-java-version-installed-in-your-computer )

Besides aspose-cells-8.0.0.1.jar you will need to add the class path to bcprov-jdk16-146.jar file. If you do not have this jar file, then you can download the major version: Aspose.Cells for Java (Latest Version) and get it from there.

So, now you only need these two files in your classpath. Please see the screenshot of Eclipse where I have added the reference to these jar files.

  • aspose-cells-8.0.0.1.jar
  • bcprov-jdk16-146.jar

5

Hi!

Thank you for the screenshot as it was very informative to what I should do with the data. I tried it, rebooted the systems and no dice. The log that has the error is coming from another program called Archibus which uses aspose cells to generate files off SQL databases. I am guessing Archibus can’t handle the ‘Data Validation’ formula.

I never did anything that php folder and pom.xml though because I did not understand where it fits in my system.

org.apache.catalina.connector.RequestFacade@52fda6
version = 2.0
Result code = ruleFailed
Result message = Exception thrown by com.CBEspace.eventhandler.common.SpaceExcelExport.export().
Result details = null
]
java.lang.IllegalArgumentException: Unsupported formula : =IF($C$9="",0,IF($E$9<7,'Data Validation'!L4,IF($C$9=7,"", IF($E$9<10,'Data Validation'!L5,IF($C$9="K",'Data Validation'!L6,IF($C$9<9,'Data Validation'!L6,IF($C$9>8,'Data Validation'!L7,0))))))).
at com.aspose.cells.Cell.a(Unknown Source)

is there an alternative way I can rewrite the formula so it does not use the Data Validation function but does the same thing?

Thank you

Save Edit

6

Hi,

Thanks for using Aspose.Cells.

You may try another approach. You can first create (complex) formula in some cell using MS-Excel and save it as xls/xlsx file. Then you can read that file using Aspose.Cells and find out how your formula looks like.

Then you can use the same formula to write it using code with Aspose.Cells setFormula() method. Hopefully, it will help you a bit.

As per my understanding, ‘Data Validation’ is a sheet name, if there are any double quotes in your formula, then you need to escape it using backslash. For example, change " with ". For single quote character, you do not need to escape it.