Designer spread sheet in smart markers

Aspose.Cells Product Family
1

Hi,

Can you please help me how to use IF function in the designer spreadsheet and not in java code.

Please provide me the sample tag or excel template asap since am blocked.

Thanks,

Asha

2

Hi,


If you need to use formulas/functions in the smart markers, you may use Repeat Dynamic formulas and Dynamic formulas. The Repeat Dynamic formula starts with “&=&=” and runs repeatedly with each row/record whereas the Dynamic formula that starts with “&==” only runs once.

Below are some examples for marker for Repeat Dynamic Formulas:

1)

&=&=IF(A{r}>=70,”yes”,”no”) … where {r} represents the current row

2)
e.g
H (this column can be hidden) J

&=DataSource.ProductName &=&=Concatenate("The color of the “, H{r}, " is red”)

3)
&=&=IF(K{r}=0,-1,(L{r}-F{r})/F{r}


Hope, this helps.

Thank you.
3

Thanks for the reply..

I have used the same syntax as mentioned by you but it is not working in my case.

I get the following exception :

Caused by: java.lang.IllegalArgumentException: You have entered too few parameters for function IF

Here is my formula in spread sheet :

&=&= IF(C{r}=1,"yes","no")

I have also attached the template which am using.

4

Hi,

Well, I have evaluated your issue a bit and found it works fine. I have updated your source template file to make it “ACPTemplate1.xls” (attached) and used my sample code (given below) to produce the output file, it works fine. The output file is fine tuned where the dynamic formulas are evaluated properly. please also find it attached here. I have used “Northwind.mdb” MS Access database to retrieve the records from Employees table for the specified markers in the template fiile.

Sample code:

//Define the Access Database URL String constant.

final String DB_URL = “jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=e:\test\Northwind.mdb”;

//Load the JDBC-ODBC bridge driver.

Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

//Define the connection.

Connection conn = DriverManager.getConnection(DB_URL);

//Create the Statement with the specified cursor type and lock option.

Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);

//Get the ResultSet executing the SQL statement.

ResultSet rs = stmt.executeQuery(“select EmployeeID,LastName from Employees”);

//Instantiating a WorkbookDesigner object

WorkbookDesigner designer = new WorkbookDesigner();

//Open a designer spreadsheet containing smart markers

Workbook workbook = new Workbook(“ACPTemplate1.xls”);

designer.setWorkbook(workbook);

//Set the data source for the designer spreadsheet

designer.setDataSource(rs);

//Process the smart markers

designer.process();

workbook.save(“out_ACPtemplate1.xls”);

Please use our latest version/fix:Aspose.Cells for Java (Latest Version) , if you still find the issue, paste your complete sample code (runnable - same as above), so that we could evaluate your issue soon.

Thank you.

5

Hi,

Thanks a lot for the reply. The problem was due to incompatible jar. I have used the jar which was shared by you and found working fine.

I have another requirement using IF function which evaluates the logical statement which is a smart tag and not the cell value. Please provide the solution for this.

Please find attached the designer spread sheet.

Thanks

Asha

6

Hi Asha,


I am afraid, our Smart Markers does not support such kind of formatting to evaluate all the data in a marker, the dynamic formulas always work with respect to the current row only or any single offset of the current row, so, you cannot use your dynamic formula in your mentioned format like:
&=&= IF(T_BUSINESS_LINE_SFL.FILTER_TYPE=“1”,“yes”,“no”)
However, if you could change it to:
&=&= IF(C{r}=“1”,“yes”,“no”)
it will work fine and accordingly.

Thanks for your understanding!

7

Thanks for the reply… I will try to find out a way in java code.

8

Hi,

Thanks for using Aspose.Cells for Java.

In case, you find any other issue, please feel free to post on Aspose.Cells forums, we will like to help you further.

Besides, you can download and try the latest offline demos of Aspose.Cells for Java, these demos contain simple runnable console examples which will help you to get familiar with Aspose.Cells for Java API (s) quickly.