Filtering data generated with smart markers

Aspose.Cells Product Family
1

Hello:

What I want to achieve here is filter the data returned by smart markers using Excel filters. Is it doable?

Please take a look at the attached designer template. I created a table that has been applied a Begin With R1 filter on the first column.

Here is the program I used:

public static void TestFilters()
{
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(@“c:\temp\spreadsheets\test_filter_smart_markers.xlsx”);
Worksheet worksheet = designer.Workbook.Worksheets[0];
Cells cells = worksheet.Cells;

DataTable dt = new DataTable();
dt.Columns.Add(“Column1”, typeof(string));
dt.Columns.Add(“Column2”, typeof(string));
dt.Columns.Add(“Column3”, typeof(string));
dt.Columns.Add(“RowNumber”, typeof(int));
for (int i = 0; i < 100; i++)
{
dt.Rows.Add(String.Format(“R{0}C1”, i), String.Format(“R{0}C2”, i), String.Format(“R{0}C3”, i), i);
}

designer.SetDataSource(“DataSet”, dt.AsDataView());
designer.Process();

designer.Workbook.Worksheets[0].AutoFilter.Refresh();

designer.Workbook.Save(@“c:\temp\spreadsheets\test_filter_smart_markers_output.xlsx”);

DataTable dataTable = designer.Workbook.Worksheets[0].Cells.ExportDataTable(1, 0, 100, 4);
dataTable.TableName = “SomeData”;
dataTable.WriteXml(Console.Out);

}

There are two issues here:

1. If you run the app and open the output, you’ll see that there are no rows in table, but if I refresh the filter in excel there are rows that match the condition (BeginWith: R1). Is there an Aspose API function that will force excel to re-evaluate the filter? AutoFilter.Refresh(); doesn’t seem to do the trick.

2. This is not a issue, but more of a question. If I export the data from the spreadsheet to a DataTable, the export feature doesn’t seem to pay attention to filtering. Is there a way to export exactly the data that you’d see on the screen when you open Excel, which would take into account the filtering as well.

Thanks


2

Hi,


Thanks for providing us the template file and sample code.

1) After an initial test, I observed the issue as you mentioned by running your sample code with your template file. After refreshing the filters in the Excel file there are rows that match the condition (BeginWith: R1). AutoFilter.Refresh(); doesn’t seem to do the trick as you pointed out. We need to look into it and will do it soon.

2) This might be due to 1). We will check and help you regarding export the filtered data.

I have logged a ticket with an id “CELLSNET-41919” for your issue. We will look into it to figure it out soon.

Thank you.
3

Hi,

We have evaluated your issue(s) / queries and here are our findings/details:

a) The auto-filter is actually not Worksheet.AutoFilter, but is ListObject’s Auto-Filter. So, if you want to use Workbook.Worksheets[0].AutoFilter.Refresh(), please change your template file accordingly.

b) We will look into it to only export visible rows for your filtered data. Once we have any update on it, we will let you know here.

Thank you.

4

Hi:

1. I can confirm that using the table autofilter object works. The following code works as expected:

public static void TestFilters()
{
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(@“c:\temp\spreadsheets\test_filter_smart_markers.xlsx”);
Worksheet worksheet = designer.Workbook.Worksheets[0];
Cells cells = worksheet.Cells;

DataTable dt = new DataTable();
dt.Columns.Add(“Column1”, typeof(string));
dt.Columns.Add(“Column2”, typeof(string));
dt.Columns.Add(“Column3”, typeof(string));
dt.Columns.Add(“RowNumber”, typeof(int));
for (int i = 0; i < 100; i++)
{
dt.Rows.Add(String.Format(“R{0}C1”, i), String.Format(“R{0}C2”, i), String.Format(“R{0}C3”, i), i);
}

designer.SetDataSource(“DataSet”, dt.AsDataView());
designer.Process();

designer.Workbook.CalculateFormula(false);

// designer.Workbook.Worksheets[0].AutoFilter.Refresh();



ListObject tableObject = worksheet.ListObjects[0];

tableObject.AutoFilter.Refresh();

designer.Workbook.Save(@“c:\temp\spreadsheets\test_filter_smart_markers_output.xlsx”);


DataTable dataTable = designer.Workbook.Worksheets[0].Cells.ExportDataTable(1, 0, 100, 4);
dataTable.TableName = “SomeData”;
dataTable.WriteXml(Console.Out);
// Console.WriteLine();
}


5

Hi,


Thanks for your feedback.

1) Good to know that refreshing ListObject/Table’s auto-filters work fine now.

2) We may update you about the progress when we will do/make.

Thank you.
6

Hi,

We have fixed the issue now.

Please download and try this fix: Aspose.Cells for .NET v7.5.2.2

And, we have supported exporting filtered data. Please try the method: Cells.ExportDataTable (int firstRow, int firstColumn, int totalRows, int totalColumns, ExportTableOptions options) with
the property ExportTableOptions.PlotVisibleCells to set it to true.

Let us know your feedback.

Thank you.