Filtering table data and return object without writing into excel

Aspose.Cells Product Family
1

I want to read an excel which holds multiple table in it, where header are not at the first row, I want to read table from cell range and then filter it based on selection of a value from available options in selected column and export it in either json string or object in java.

2

@manijauhari

To manipulate tables in template file, please see apis of ListObject and the document Create and manage tables of Microsoft Excel files.|Documentation.

To filter data in tables, you may use AutoFilter for specific ListObject. For using filter, please see Data Filtering|Documentation.

If you have any question about using those apis and features, please don’t hesitate to reach out and we will be glad to provide suggestions about the solution.

3

@manijauhari,

I have written an example to accomplish your task. Please find attached a sample (source) Excel file and try the sample code with it. I also exported the filtered range to JSON data for your needs. Please refer to the code segment below and write/update your own code accordingly for your needs for your reference. Moreover, the attached zipped archive also contains the output JSON file and output Excel file for your reference.
e.g.,
Sample code:

//Load the Excel file
Workbook workbook = new Workbook("d:\\files\\Bk_data1.xlsx");

//Access the worksheet by name or index
Worksheet worksheet = workbook.getWorksheets().get(0);

//Filter the data based on your selected column 
int colIndex = 2; //Column to be filtered, which is D column
String filterValue = "myval"; // Value to filter by

//Set the data range for which auto-filters are to be applied
worksheet.getAutoFilter().setRange("B10:F20");
worksheet.getAutoFilter().filter(colIndex, filterValue);
worksheet.getAutoFilter().refresh();

//Define the cell range where your table is located (e.g., B10:F20)
Range sourceRange = worksheet.getCells().createRange(worksheet.getAutoFilter().getRange().toString());
int lastRow = sourceRange.getRowCount() + sourceRange.getFirstRow();

//Remove hidden rows before exporting to JSON
for (int row = lastRow; row >= sourceRange.getFirstRow(); row--)
{
    Row sourceRow = worksheet.getCells().getRows().get(row);
    if (sourceRow.isHidden())
    {
          worksheet.getCells().deleteRow(row);
    }
}

//Export the filtered workbook to JSON
JsonSaveOptions jsonOptions = new JsonSaveOptions();
jsonOptions.setHasHeaderRow(true);
jsonOptions.setSkipEmptyRows(true);

jsonOptions.setExportArea(CellArea.createCellArea("B10", "F20"));
workbook.save("d:\\files\\filtered_data1.json", jsonOptions);
workbook.save("d:\\files\\out1.xlsx");

files1.zip (14.8 KB)

Hope, this helps a bit.