Hi,
2 queries
1. Is there a way to get the table details based on cell range. ex. if my table range is A1:D5 and if have only a cell detail say B2. How can I get the ListObject from ListObjectCollection? First I would like know whether B2 belongs to any table in worksheet. if yes then would like to get the ListObject. How to achieve this?
2. When we add table in excel with header, header automatically gets into combo box where all the data from column gets populate and then user can sort or hide/unhide rows. Is there a way to get the combo box data details? and also any outofbox solution to perform sorting on table?
Do advise.
Regards,
Azhar
Hi Azhar,
Thanks for your posting and using Aspose.Cells.
There is not any direct way to know if the given cell say B2 is inside some list object. You can as a workaround iterate the Worksheet.ListObjects and then get the data range using the ListObject.DataRange property and finally finding if the given cell lies inside the list object range or not.
In order to sort the data, you can use the technique mentioned in the following article for your help.
You can also sort the list object using the ListObject.AutoFilter property.
Thanks for the reply.
When we add table in excel with header, header automatically gets into combo box where all the data from column gets populate. is there any way to get this list of combo box?
Also, I would like to populate my excel table with xml data (xml file) . What would be better approach for this other than parsing xml manually and populating table?
Regards,
Azhar
Hi Azhar,
Thanks for your posting and using Aspose.Cells.
Currently, this feature of combo box where all the data gets populated is not available. Could you please provide your sample xls/xlsx file and screenshot illustrating this feature so that we could add a new feature request in our database for investigation and implementation.
Aspose.Cells cannot import your xml and convert it to table. So you will have to parse your xml and populate your table with the xml data.
Here is the file attached which has table in it. We would need a API’s to get the details of filter combo box.
Also, I need to apply the filter on table column. As you mention there is autoFilter which does this. However, Autofilter works on worksheet and it allows only one filter at a time. I might have more than one table in my worksheet. then how to apply the filter on individual table?
Please advise since I am stuck with this.
Here is the program I am trying out.
package com.azhar.aspose;
import java.util.Iterator;
public class Table {
public static void saveTable(){
Workbook workbook = new Workbook();
//Get the List objects collection in the first worksheet.
ListObjectCollection listObjects = workbook.getWorksheets().get(0).getListObjects();
Cells cells = workbook.getWorksheets().get(0).getCells();
cells.getRows().get(1).get(0).setValue(10);
cells.getRows().get(1).get(1).setValue(20);
cells.getRows().get(1).get(2).setValue(30);
cells.getRows().get(1).get(3).setValue(40);
cells.getRows().get(1).get(4).setValue(50);
cells.getRows().get(2).get(0).setValue(60);
cells.getRows().get(2).get(1).setValue(70);
cells.getRows().get(2).get(2).setValue(80);
cells.getRows().get(2).get(3).setValue(90);
cells.getRows().get(2).get(4).setValue(100);
cells.getRows().get(3).get(0).setValue(630);
cells.getRows().get(3).get(1).setValue(750);
cells.getRows().get(3).get(2).setValue(860);
cells.getRows().get(3).get(3).setValue(903);
cells.getRows().get(3).get(4).setValue(1030);
listObjects.add(“A1”,“E5”,true);
ListObject table = listObjects.get(0);
table.setShowTotals(true);
workbook.getWorksheets().get(0).getAutoFilter().setRange(“A1:E5”);
workbook.getWorksheets().get(0).getAutoFilter().addFilter(1,“40”);
table.getListColumns().get(4).setTotalsCalculation(TotalsCalculation.SUM);
//Save the excel file.
try {
workbook.save(“table.xls”);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println(“saved”);
}
public static void main(String arg[]){
saveTable();
readTable();
}
private static void readTable() {
try{
Workbook workbook = new Workbook(“table.xls”);
ListObjectCollection listObjects = workbook.getWorksheets().get(0).getListObjects();
System.out.println(workbook.getWorksheets().get(0).getName());
System.out.println(listObjects.getCount());
ListObject table = listObjects.get(0);
AutoFilter filter = workbook.getWorksheets().get(0).getAutoFilter();
System.out.println(filter.getRange());
FilterColumnCollection columns = filter.getFilterColumnCollection();
for(int i=0;i<columns.getCount();i++){
FilterColumn column = columns.get(i);
if(column.getFilter() == null)
continue;
System.out.println(column.getFilter());
if(column.getFilter() instanceof Top10Filter){
Top10Filter f = (Top10Filter) column.getFilter();
System.out.println(f.getCriteria());
System.out.println(f.getItems());
}
if(column.getFilter() instanceof MultipleFilterCollection){
MultipleFilterCollection m = (MultipleFilterCollection) column.getFilter();
for(i=0;i<m.getCount();i++)
{
System.out.println(m.get(i));
}
}
System.out.println(column.getVisibledropdown());
System.out.println(column.getFilterType());
}
}catch(Exception e){
e.printStackTrace();
}
}
}
Hi Azhar,
Thanks for your sample code and using Aspose.Cells.
We are looking into this issue of how to apply auto filter on columns inside the tables. We will update you asap.
Hi Azhar,
Thanks for using Aspose.Cells.
There are no easy way to apply filters on individual columns. To apply filter, you need to use the ListObject.getAutoFilter().addFilter() method.
The following code applies two filters on the first and second column. It checks the value 2 and 3 in the first column and checks value 2 in second column.
You can see the source and output xlsx file for your reference.
Java
String filePath = “F:\Shak-Data-RW\Downloads\source.xlsx”;
Workbook workbook = new Workbook(filePath);
Worksheet worksheet = workbook.getWorksheets().get(0);
ListObject lobj = worksheet.getListObjects().get(0);
//Add filter in first column
lobj.getAutoFilter().addFilter(0, “2”); //Check the value 2
lobj.getAutoFilter().addFilter(0, “3”); //Check the value 3
//Add filter in second column
lobj.getAutoFilter().addFilter(1, “2”); //Check the value 2
lobj.getAutoFilter().refresh();
workbook.save(filePath + “.out.xlsx”);
Thanks for the code snippet.
Here is what I am doing.
writing to File :
workbook.getWorksheets().get(0).getAutoFilter().setRange(“A1:E1”);
workbook.getWorksheets().get(0).getAutoFilter().addFilter(1,“70”);
workbook.getWorksheets().get(0).getAutoFilter().addFilter(1,“750”);
workbook.getWorksheets().get(0).getAutoFilter().refresh();
Reading from File :
AutoFilter filter = workbook.getWorksheets().get(0).getAutoFilter();
FilterColumnCollection columns = filter.getFilterColumnCollection();
for(int i=0;i<columns.getCount();i++){
FilterColumn column = columns.get(i);
if(column.getFilter() == null)
continue;
if(column.getFilter() instanceof MultipleFilterCollection){
MultipleFilterCollection m = (MultipleFilterCollection) column.getFilter();
for(i=0;i<m.getCount();i++)
{
System.out.println(m.get(i));
}
}
}
This above code works fine. it applies the filter in file. hides/unhides the respective rows. Even prints out the Filter values which has been applied on. Perfect!!!
However, I am doing this on worksheet autofilter. As soon as I replace the worksheet filter with table filter I face the issue.
Writing to File :
table.getAutoFilter().setRange(“A1:E1”);
table.getAutoFilter().addFilter(1,“70”);
table.getAutoFilter().addFilter(1,“750”);
table.getAutoFilter().refresh();
This works fine. it applies the respective filter. But when I tries to read the filter details from table filter like this
AutoFilter filter = table.getAutoFilter();
FilterColumnCollection columns = filter.getFilterColumnCollection();
for(int i=0;i<columns.getCount();i++){
FilterColumn column = columns.get(i);
if(column.getFilter() == null)
continue;
if(column.getFilter() instanceof MultipleFilterCollection){
MultipleFilterCollection m = (MultipleFilterCollection) column.getFilter();
for(i=0;i<m.getCount();i++)
{
System.out.println(m.get(i));
}
}
}
the columns.getCount() comes as 0 and won’t return any applied filter details.
what am I doing wrong? I would like to get applied filter details from table.getAutofilter() rather than worksheet.getAutoFilter().
Please let me know If I am still not clear.
Regards,
Azhar
Hi Azhar,
Thanks for your sample code and using Aspose.Cells for Java.
I tried your code with the source file attached with this post and it works fine. I did a little modification inside it highlighted in red color.
After the execution of code, it prints out the Filter values which has been applied on. Please see its output below.
Java
String filePath = “F:\Shak-Data-RW\Downloads\source.xlsx”;
Workbook workbook = new Workbook(filePath);
Worksheet worksheet = workbook.getWorksheets().get(0);
ListObject table = worksheet.getListObjects().get(0);
AutoFilter filter = table.getAutoFilter();
FilterColumnCollection columns = filter.getFilterColumnCollection();
for(int i=0;i<columns.getCount();i++){
FilterColumn column = columns.get(i);
if(column.getFilter() == null)
continue;
if(column.getFilter() instanceof MultipleFilterCollection){
MultipleFilterCollection m = (MultipleFilterCollection) column.getFilter();
for(int j=0;j<m.getCount();j++)
{
System.out.println(m.get(j));
}
}
}
Output:
2
3
3
Thanks for the reply. Both the code looks good to me. I found out that the actual problem was with excel type. When I run the same code against “xls” it won’t work but it does with "xlsx’. any reason?
Also, I had a requirement of getting the filter combo box details. I have already provided the sample excel file. How soon I can expect that feature?
Regards,
Azhar
Hi Azhar,
Thanks for your feedback and using Aspose.Cells.
You are right. Table autofilter does not work fine with xls format but it works fine with xlsx format using the above code.
I have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.
This issue has been logged as CELLSJAVA-40743.
For your requirement regarding combo box, do you want to retrieve these values as highlighted in this screenshot. Please provide us your screenshots if your requirements are different. We will look into it and log this feature request in our database for implementation.
Screenshot:
Yes… I want the list highlighted in screenshot.
Also, I would like to apply the sorting and it should get apply to the excel.
Regards,
Azhar
Hi Azhar,
Thanks for your feedback and using Aspose.Cells.
We have added a New Feature request in our database to retrieve the checkbox list from the Table columns/combo box. We will look into it and implement this feature. Once, we will have some fix or update for you, we will let you know asap. This issue has been logged as CELLSJAVA-40745.
Regarding sorting issue, we would like to inform you that you can use Aspose.Cells APIs to sort your list object based on any column. For example, the following code sorts the list object using the second column in ascending order. I have attached the source and output xlsx files for your reference.
Java
String filePath = “F:\Shak-Data-RW\Downloads\source.xlsx”;
Workbook workbook = new Workbook(filePath);
Worksheet worksheet = workbook.getWorksheets().get(0);
ListObject lobj = worksheet.getListObjects().get(0);
//Sort list based on second column in ascending order
lobj.getAutoFilter().getSorter().setKey1(1);
lobj.getAutoFilter().getSorter().setOrder1(SortOrder.ASCENDING);
lobj.getAutoFilter().getSorter().sort();
workbook.save(filePath + “.out.xlsx”);
Yep… got it. .
Regards,
Azhar
Hi Azhar,
Thanks for using Aspose.Cells.
As you want to get the filter’s result, please get them by yourself with the
following code:
Java
public static void main(String[] args) {
// TODO Auto-generated method stub
try {
String filePath = “F:\Shak-Data-RW\Downloads\source.xlsx”;
Workbook workbook = new Workbook(filePath);
Worksheet worksheet = workbook.getWorksheets().get(0);
ListObject table = worksheet.getListObjects().get(0);
AutoFilter filter = table.getAutoFilter();
filter.addFilter(0, “10”);
filter.refresh();
String[] strs = getUniqueValue(worksheet.getCells(),
table.getStartRow() + 1, table.getEndRow(), 0);
for (int i = 0; i < strs.length; i++) {
System.out.println(strs[i]);
}
} catch (Exception e) {
e.printStackTrace();
System.out.println(e.getMessage());
}
}
private static String[] getUniqueValue(Cells cells, int startRow,
int endRow, int column) {
java.util.Hashtable table = new java.util.Hashtable();
int i = 0;
for (int row = startRow; row <= endRow; row++) {
if (cells.isRowHidden(row)) {
continue;
}
Cell cell = cells.checkCell(row, column);
String str = “(Blank)”;
if (cell != null && cell.getStringValue() != “”) {
str = cell.getStringValue();
}
if (table.get(str) == null) {
table.put(str, i);
i++;
}
}
int size = table.size();
String[] ret = new String[size];
for (Enumeration e = table.keys(); e.hasMoreElements()
{
String str = (String) e.nextElement();
int index = (Integer) table.get(str);
ret[index] = str;
}
return ret;
}
Hi,
Please try our latest version/fix: Aspose.Cells for Java v7.7.2.2.
We have fixed your issue “<span style=“font-size:12.0pt;font-family:“Calibri”,“sans-serif”;
mso-fareast-font-family:“Times New Roman”;mso-bidi-font-family:“Times New Roman”;
color:black;mso-ansi-language:EN-SG;mso-fareast-language:EN-SG;mso-bidi-language:
AR-SA”>CELLSJAVA-40743” now.
mso-fareast-font-family:“Times New Roman”;mso-bidi-font-family:“Times New Roman”;
color:black;mso-ansi-language:EN-SG;mso-fareast-language:EN-SG;mso-bidi-language:
AR-SA”>CELLSJAVA-40743” now.
Let us know your feedback.
Thank you.
Yes. This is what I am already doing. I was expecting it from aspose API’s.
I got the below mail:
Please try our latest version/fix: Aspose.Cells for Java v7.7.2.2.
We have fixed your issue “CELLSJAVA-40743” now.
Let us know your feedback.
What does this fix include? API to get the filter details? or fixed because of alternative? Regards,
Azhar
Hi,
azhar920:
What does this fix include? API to get the filter details? or fixed because of alternative?
It fixes the issue logged as "CELLSJAVA-40743" i.e., "Table auto-filter does not work in XLS format but works fine in XLSX format".
Thank you.
Ok… Got it. Thanks
Regards,
Azhar
The issues you have found earlier (filed as CELLSJAVA-40743) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by Aspose Notifier.