I am using Aspose Cells for Java. I am currently trying to load data into a Workbook using the SmartMarkers approach. Currently I have this data in a HashMap. Is there any way to load the data from a Java HashMap directly into the Aspose Cells Workbook by using SmartMarkers? As I see, Aspose Cells only supports loading of data through an object array or a Resultset.
Thanks,
Shashi
Hi Shashi,
Could you try to check some ways to extract data from Java HashMap and save to object array etc. Then you may use workbookdesigner.setDataSource method for your tasks.
Thank you.
Amjad,
I guess HashMap was a wrong example. I am having an Excel grid like data structure in memory (think about an object that has a JDBC ResultSet like interface). Also I have some cells scattered here and there which are set using smart markers but from different Java Objects available in memory. Converting this Grid structure to an array will be extra work and will result in inefficient code. Is there any interface (maybe on the lines of the GOF Decorator pattern) that Aspose provides that I can implement to interface with the Smartmarkers?
Thanks,
Shashi
Hi Shashi,
Thanks for your info.
Is it difficult to implement the interface ResultSet if we list all the methods which we used in the Processing smart markers?
We are looking to add interface. Could you give us more info ?
Hi Warren,
Implementing the ResultSet interface is a very time consuming job as it has dependencies on other interfaces like ResultSetMetaData, Statement and others. I need to mock the complete implementation to wrap the POJOs or a collection of POJOs. Also I dont know your internal implementation of how you are using the Resultset and hence I need to strictly adhere to the interface requirements. The only way to solve this problem is for you to give me a contract which is simpler to implement than a resultset. A very simple interface could have methods like getNextRow(), getColumn(columnname), getRow(i) etc. This one is for a collection of POJOs. For a simple POJO, you could retrieve the properties through reflection and introspection directly.
Example for smartmarker:
&=mydatasource.employeeName(beanintrospection=true)
public class Employee { private String employeeNumber; private String employeeName; public String getEmployeeNumber() { return employeeNumber; } public void setEmployeeNumber(String employeeNumber) { this.employeeNumber = employeeNumber; } public String getEmployeeName() { return employeeName; } public void setEmployeeName(String employeeName) { this.employeeName = employeeName; } }
Scenario I:Inside Aspose Cells, I would set the data source as :Employee emp = new Employee();
... Call setters to set the Employee object
designer.setDataSource("mydatasource",emp);
designer.process();In the above case, Aspose should emp.getEmployeeName and extract the data and set it inside the cell. Scenario 2:
For scenarios where there is a collection, we need to comply to your contract and put a hint in the smart
marker saying that the data is coming through this contract.
I havent thought about the interface definition in detail but I have put down my thoughts. Let me know if you have any questions.
Thanks!
Shashi
Hi Shashi,
1, A smiple POJO, please ues the smart marker : &=mydatasource.employeeName(bean)
2, POJOs, I define such an interface,
public interface AsposeDataTable
{
/**
* Gets the column's name
* @return the columns' name
*/
public String[] getColumns();
/**
* Gets the size of the records.
* In order to know how many rows/columns should be inserted or shifted.
* If you do not insert or shift rows/columns in your smart markers,
* it could not be implemented;
* @return the size of the records.
*/
public int size();
/**
* Moves the cursor to the front of this object, just before the first row.
* If the smarts of the table is not in the same row,
* we will iterate the row for serveral times.
*/
public void beforeFirst();
/**
* Returns the value of the designated column in the current row.
* @param columnIndex the first column is 1, the second is 2, ...
* @return the value of the designated column in the current row.
*/
public Object get(int columnIndex);
/**
* Moves the cursor down one row from its current position.
* @return if the new current row is valid; false if there are no more rows
*/
public boolean next();
}
Is it possible to implement this interface? Please give us more advice.
Hi Warren,
1. The POJO smartmarker syntax looks fine to me.
2. The AsposeDataTable interface looks very similar to the ResultSet interface (I guess I did not think about it much when I suggested the interface). I have the following questions related to the Interface:
Lets look at what all data can the concrete class that implements AsposeDataTable interface contain:
Scenario a) It can contain a "Collection" or any custom data structure of Strings or Double or Integers.
Scenario b) It can contain a "Collection" or any custom data structure of POJOs.
Lets analyze the AsposeDataTable interface to take care of both Scenario a) and Scenario b).
i) getColumns method would return a set of column names as per your description. What should I return in case of Scenario a) and what should I return in case of Scenario b). For Scenario b), I may return the bean property names in a particular sequence. Again because of requirement of iv) below, I need to maintain the order of the columns.
ii) getSize -looks good.
iii) beforeFirst() - In case I am wrapping a list in my custom object by implementing the AsposeDataTable interface, will it be ok if I internally reset the marker to the first element in the list or coordinate with the next() to ensure that a call to the next() method after beforeFirst always returns the first element. I guess you want it similar to the ResultSet interface.
iv) public Object get(int columnIndex); - For a set of POJOs, I need to internally maintain the order of the columns which I returned during i). What should I do for Scenario a). Should I return a dummy column name in i) and this will be a part of smartmarker syntax? I guess this would be the preferred way as I will have more power on what I want to name a particular variable.
v) public boolean next(); looks fine to me.
The smartmarker syntax inside the Excel sheet is pending (similar to what you have provided for a POJO).
Thanks!
Shashi
Hi Shashi,
1. getColumns and public Object get(int columnIndex); - Do you want to use get(string columnName) to replace the two methods? It's not effective to use get(string columnName) method because you have to reflect a same method for many times. If you think it's nothing to reflect a same method for many times, I will add a triger to indicate whether you use the two methods or get(string columnName) method.
2, beforeFirst() ; It's ok to call the next() method after beforeFirst always returns the first element.
3, the column name in scenaro A,you should return the same name as the field name you defined in the smart marker.
The attached file is a simple implemetion of the interface.
Hi Warren,
Please keep the earlier interface only. Also, I still need to use reflection whether I have a single method or multiple methods. The only way I can prevent it by using switch case statements which will be very ugly. Frankly speaking, I dont have any problem using reflection.
I could not find an attachment in your post. Can you please attach it? Can you please let me know on when are you planning to put this feature in Aspose Cells?
Thanks!
Shashi
Hi,
Sorry for miss the file. I attach it again.
Now the feature is ok if you use the earlier interface. I will attach the fix soon.
Hi Shashi,
The current interface is more fit for a row data struct is an array or known POJO type as my demo class "DataTable".
If the POJO class type is transparent of the implemented class, it's ugly to get the value by the column index.So I add the following method in the interface:
/**
* Returns the value of the designated column in the current row.
* @param columnName the property name of the POJO.
* @return the value of the designated column in the current row.
*/
public Object get(String columnName);
If getColumns() method return null ,we will get the value of the column by the get(String columnName) method.
If getColumns() method does not return null, we will get the value by the column index.
Hi,
Please try this fix.
The shift and bean option of the smart marker is supported.
POJOs are supported. It's supported to get the value by the column name.
If you don't want to insert column, you must add "noadd" option when the smart marker has "horizontal" option.
Hi Warren,
I tried testing the POJO stuff. It only accepts bean property methods that return either String, Integer, Double or Long. I had some bean properties that return a long or an int. Does this mean that I need to create explicit methods to always return objects in case of POJOs?
Overall, the POJO implementation worked fine. The new version of the code now adds columns if you dont explicty mention the noadd parameter. This has altered the default behaviour of the code (from previous versions) and may not be desirable for some of the code implmentations (where smartmarkers are used) that were written before this feature was put in.
Adding an additional method by columnname definitely improves the DataTable interface. Also it allows me to convert custom POJO objects into their String or compatible value types given the requirements of the Cell.setValue method.
If I have the name of the marker as &=mydatasource.employee.department.departmentNo then will Aspose Cells pass me the complete string after "mydatasource." to the get(columnname) method i.e. will it pass employee.department.departmentNo in the columnname?
Also I would suggest that you explicity mention in the interface that I can return only Integer, Double, String and Long. Any other value types will throw an illegal argument exception.
Thanks,
Shashi
Hi Shashi,
1,Please try this fix. In this fix ,you can return Boolean,Integer, Double, String ,Long, Date, Calendar, Short .If we accept int/long basic type as return type, we have to add many methods to the interface as ResultSet, such as getIntValue(),getDoubleValue() ..etc. And we have to get the return type of each column know which method should be used to get the value of the column.The interface will be very complex. If you use JDK5.0, you have to do nothing.If you use JDK1.4, you have to convert the basic type to Object in the interface.
2, We will update the wiki of the smart maker soon.
3, We will return string "employee.department.departmentNo",you have to process it yourself.
4, I have added the note in the Java Doc. Thanks for your advice.
Hi Warren,
Any update on this feature i.e. loading of custom collection of POJOs through AsposeDataTable interface?
Thanks,
Shashi
Dear Shashi,
We do not update this feature.
Could the attached fix in the previous post fit your need? Please show us your need.
Warren,
The previous fix solved my problem for a single in-memory POJO. But for I could not use it for a collection of objects. I will provide you with a scenario on why I require to load Aspose with Java collections:
We retrieve a lot of data from the database related to sales, production etc. A lot of number crunching is done and custom business rules (like rounding algorithms) are applied to the collection of rows.
We want to load that collection into a workbook where different R1C1 formulas are applied and then the final output is shown to the user.
Currently we are stuck at a point where we need to write a lot of code to load data into Aspose Cells because of the dynamic nature of the Designer templates (I create them dynamically in memory based on user inputs). The Smart marker feature will allow us to write cleaner code.
Let me know if you need any other information.
Thanks,
Shashi
Dear Shashi,
Please give us more info.
1,Could interface AsposeDataTable fit your need?
2,Could you post your sample code project to show your data struct ?
3,I could not understand your number crunching and dynamic nature of the Designer templates
Hi Warren,
I did not know that you had already implemented AsposeDataTable in Aspose. All this while I was waiting for this feature to be implemented. Sorry for the confusion. I will test this feature and update you.
Thanks,
Shashi
Hi,
I am using the following smart marker to fill a list of person names in the excel from an ArrayList.
&=myRecord.personName(bean).
class MyEmployeeRecords implements AsposeDataTable
{
…
public Object get(String columnName)
}
I wrote the above class that implements the AsposeDataTable interface. As per the earlier specification it was supposed to call the function get(“personName”) from the object that is implementing the AsposeDataTable interface. But in the current version it calls MyEmployeeRecords.getPersonName method and throws java.lang.NoSuchMethodException:
java.lang.NoSuchMethodException: MyEmployeeRecords.getSeriesName()
at java.lang.Class.getMethod(Class.java:1581)
at com.aspose.cells.WorkbookDesigner.process(Unknown Source)
at com.aspose.cells.WorkbookDesigner.process(Unknown Source)
…
-Shashi