Create dynamic column using smart markers

Aspose.Cells Product Family
1

I am evaluating Aspose.Cells Java Api to create dynamic columns.

In my case, I am creating my own List and send to Aspose.Cells java Api as the datasource “mydatasource” and has placed the smart markers &=mydatasource.argA, &=mydatasource.argB in static columns ArgA, ArgB and next to that I want to create dynamic columns Arg1 and Arg2. For this, I can create Arg1, Arg2… ArgN columns dynamically.

But, I am facing an issue when placing dynamic smart markers in those columns. Because I can place smart markers like &=mydatasource.arg1, &=mydatasource.arg2 but how can I inject arg1 and arg2 to MyObject runtime?

I was trying so many things for this but I didn’t get any answer. Can you please explain how can I achieve this?

2

@ayash,
Thank you for writing to us. Please provide us more information like at least two sample lists along with the expected output Excel files. You may share your sample runnable code along with the output created by Aspose.Cells. It will help us to understand the requirement and provide assistance accordingly.

3

@ahsaniqbalsidiqui Thank you for the quick reply

This is a simple scenario. In the attached designer worksheet, ABC, DEF, GHI, JKL are the static columns and ARG_1, ARG_2 … ARG_N are the dynamic columns. I have attached the sample MyObject(Attached herewith) Java Object which I am pushing as a List as the data source “record” into the DesignerWorksheet.

MyObject.zip (446 Bytes)
Designer.PNG (9.9 KB)

The issue is there is no way I found to add the dynamic data, in my case, I have dynamic data in a Map called args, but I don’t have a way to retrieve that data into the excel.

Edit:- Please note the &=records.arg_1, &=records.arg_2, &=records.arg_3 smart markers in the dynamic columns

4

@ayash,
I have reviewed the documents but no such option is found. You have to write own code where first parse through all the elements of the objects array and populate your designer workbook with the smart markers. For example you set the titles and smart markers in such a way that following lines are executed in a loop. Within this loop the strings for cell address, title and smart marker should be calculated.

worksheet.getCells().get(“A1”).putValue(“ABC”);
worksheet.getCells().get(“A2”).putValue("&=record.abc");

worksheet.getCells().get(“B1”).putValue(“DEF”);
worksheet.getCells().get(“B2”).putValue("&=record.def");

worksheet.getCells().get(“C1”).putValue(“GHI”);
worksheet.getCells().get(“C2”).putValue("&=record.ghi");

worksheet.getCells().get(“D1”).putValue(“JKL”);
worksheet.getCells().get(“D2”).putValue("&=record.jkl");
…
…
and so on.

After this you may use this designer workbook for fetching data from the objects array.

5

@ahsaniqbalsidiqui, thanks for the reply

That is exactly what I am doing right now. The issue is not dynamically creating the designer worksheet. What is the exact smart marker to retrieve data from a Map, or List or Array or any other dynamic collection of data?

For example, Suppose I have a map inside of MyObject called args, how can I retrieve from that map? For example, to retrieve data from DynamicColumn_1, is there a way to get the data like “&=record.args.get(‘DynamicColumn_1’)”

or

Let’s say this is an Array called args, then what is the way to retrieve i(th) element, for example, is there a way to retrieve that element “&=record.args[1]”

Because in your example we can put “&=record.jkl” since we have a getter for jkl ( getJkl() ) in MyObject. But for args, we have a dynamic collection inside the object. Then getters are not going to work. There should be a way that we can retrieve the data from a collection right? Otherwise, what is the point of having a dynamic designer when you can’t retrieve the data from a collection?

6

@ayash,

I have prepared a sample which fetches data from a table and different arrays. It selects a particular element as per the index given in the setDataSource(). You may please give it a try and share the feedback. Download the sample project and review the example codes here. For detailed examples follow the documentation here.

WorkbookDesigner report = new WorkbookDesigner();
Workbook wb = new Workbook(path + "Book1.xlsx");
// Get the first worksheet of the workbook.
report.setWorkbook(wb);
Worksheet w = report.getWorkbook().getWorksheets().get(1);

//CASE 1: Html strings
wb.getWorksheets().get(0).getCells().get("E1").putValue("&=$VariableArray3(HTML)");
report.setDataSource("VariableArray3",
		new String[] { "Hello <b>World</b>", "Arabic", "Hindi", "Urdu", "French" });

//CASE 2: Display 3rd element from the float array
wb.getWorksheets().get(0).getCells().get("G1").putValue("&=$FloatArray4");
float[] floatArray = new float[]{1.1f,2.2f,3.3f,4.4f,5.5f};
report.setDataSource("FloatArray4",floatArray[3]);//DISPLAY 3rd element only

Object dataTable[][] = w.getCells().exportArray(0, 0, 10, 10);

//Fetch destination worksheet
w = report.getWorkbook().getWorksheets().get(0);

//CASE 3: FETCH PARTICULAR ROW
w.getCells().get("A1").putValue("&=$VariableArray1");

// Set the DataSource for the marker(s).
report.setDataSource("VariableArray1", dataTable[3]);//It shall fetch fourth row only

//CASE 4: FETCH PARTICULAR VALUE FROM THE TABLE
w.getCells().get("C1").putValue("&=$Variable1");

// Set the DataSource for the marker(s).
report.setDataSource("Variable1", dataTable[5][6]);//It shall fetch column seven of sixth row only

// Process the markers.
report.process(false);

// Save the Excel file.
report.getWorkbook().save(path + "varaiblearray-out.xlsx");

Book1.zip (6.8 KB)
varaiblearray-out.zip (7.8 KB)

8

@ahsaniqbalsidiqui

Thanks for the sample, I really appreciate this. But, this is not what I am expecting. In my case, I don’t have control of fetching data from an array like this. Because once I push the data to Aspose.Cells, the smart markers are the once who are iterating thru the list of MyObject instances. For each and every MyObject in the List Aspose.Cells engine creates a row. So, when I am creating a dynamic data column I don’t know which row is related to which MyObject instance.

Even if I knew it, I can directly go fill the data there, no need a smart marker for the dynamic data. Since attaching things looks too confusing I add the description here.

ABC and DEF are the static columns and next to those two columns, we have dynamic columns. We don’t know how many rows are becoming dynamic, it can be zero, one, two or three. So, that is becoming ARG0, ARG1, …, ARGN. So, for the static columns, I am going and filling the smart markers manually as follows,

Under ABC header I put &=record.abc
Under DEF header I put &=record.def

Now if I pushed the List<MyObject> Aspose.Cells creates a nice looking output in which my smart marked columns are filled with the data according to my list, each and every row represents each and every object.

Now, In every MyObject instance is having dynamic data. Let’s say it has an extra column in a particular instance of run time. Since I can’t fill that in the designer worksheet manually, I am going to add that column from the Java code. So, as you explained I can read the 3rd column and fill the header, and under it I can fill a smart marker like this, I cannot fill the data or manually extract the data from java because I don’t know what row is related to what object since row iteration is done by Aspose.Cells I don’t have a control over it.

So, dynamically I can add the header, but I cannot add the data from the same instance of MyObject since I don’t know what object is being processed under what row in Aspose.Cells. That is why I am asking if there is a way to retrieve the data like,

ABC => &=records.abc
CDE => &=records.def
ARG0 => &=records.arg[0]

or any other way we can read the data from a collection using smart markers. Because in that case, Aspose.Cells knows which object is being processed now on ABC and DEF columns and get the data from the exact object and fill the arg0 rows in its processing instance of the object list.

Why your solutions like following is not working for me is,

//CASE 2: Display 3rd element from the float array wb.getWorksheets().get(0).getCells().get(“G1”).putValue("&=$FloatArray4");
float[] floatArray = new float[]{1.1f,2.2f,3.3f,4.4f,5.5f};
report.setDataSource(“FloatArray4”,floatArray[3]);

you’re directly selecting the data you want to fill as floatArray[3] from Java code. If I know which data is to select from the Java code, I can directly fill the data into the worksheet no need a smart marker there. I don’t feel any point of having dynamic smart markers when we know the data.

I really appreciate your replies again. According to this discussion, I feel like when there are dynamic columns, Aspose.Cells is a wrong approach but Apache-POI would work with much more control. I chose Aspose.Cells because templates and smart markers are smarter. But losing control because of smart markers is again becoming problematic, it looks, when dynamic columns are there.

9

@ayash,
Please spare us little time to provide feedback on this.

10

@ayash,
Could you please explain how can it be accomplished using Apache-POI as it will help us to observe the scenario. Share the sample code, steps to establish the environment and any other supporting data like images etc. for our reference. We will review the information and provide our feedback accordingly.