Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Thu Nov 07, 2019 11:15 am

Hello,

is there a way to apply row filtering to a worksheet prior to calling worksheet.SaveTo*? The desired result is a CSV file containing only the filtered rows. I've already tried using AutoFilters, but, understandably enough, AutoFilters are ignored when exporting a sheet's data to a CSV file.

KR,
Gerhard

GerhardFinee
 
Posts: 26
Joined: Thu Nov 07, 2019 7:57 am

Fri Nov 08, 2019 6:08 am

Hi,

Thanks for your inquiry.
Kindly note Spire.XLS follows the rules of MS Excel. If you save an Excel file which contains filter to CSV file using MS Excel directly, all of the Excel data(not only the filtered rows) will be saved into CSV file. So Spire do the same behavior. Hope you could understand.

Best wishes,
Amber
E-iceblue support team
User avatar

Amber.Gu
 
Posts: 525
Joined: Tue Jun 04, 2019 3:16 am

Fri Nov 08, 2019 8:24 am

Hi Amber,

thanks for your quick response. Of course i understand this default behaviour. Would be a cool feature for a future version to provide an overload for worksheet.SaveTo* with an extra parameter like "bool applyAutoFilters".

KR,
Gerhard

GerhardFinee
 
Posts: 26
Joined: Thu Nov 07, 2019 7:57 am

Fri Nov 08, 2019 9:44 am

Hi,

Thanks for your reply.
We will consider adding this new feature into our upgrade schedule in the future.
Once there is any progress, we will inform you. Sorry for the inconvenience caused.

Best wishes,
Amber
E-iceblue support team
User avatar

Amber.Gu
 
Posts: 525
Joined: Tue Jun 04, 2019 3:16 am

Tue Nov 26, 2019 10:17 am

Hi,

Hope you are doing well.
Glad to inform you that the previous issue has resolved in Spire.XLS Pack(Hotfix) Version:9.11.14. Welcome to download and test it from the following links.
Our website link: https://www.e-iceblue.com/Download/download-excel-for-net-now.html
NuGet link: https://www.nuget.org/packages/Spire.XLS/9.11.14

And below is the code for you.
Code: Select all
            //Convert Excel file to Csv file and only save the filtered data.
            sheet.SaveToFile("result.csv","   ", false);

            //Convert Excel file to Csv file and only save the filtered data.
            //You need to apply the license file using this method.
            workbook.SaveToFile("result.csv", FileFormat.CSV, false);

Best wishes,
Amber
E-iceblue support team
User avatar

Amber.Gu
 
Posts: 525
Joined: Tue Jun 04, 2019 3:16 am

Tue Nov 26, 2019 1:46 pm

Hi Amber,

thanks for the update. I tested the new feature. Unfortunately there are still two major drawbacks preventing me from using it.

1. I am working in-memory, so I would need the feature to be implemented for the SaveToStream method - the current version only works for SaveToFile.

2. The AutoFiltersCollection.Filter() method works correctly, but _way_ too slow. Applying a simple text filter to a sheet containing 100 000 rows of mockup data, the method is outperformed by my "dirty" workaround by ~13 times - on my machine. My workaround first extracts the worksheet's contents to a System.Data.DataView, then applies a DataView.RowFilter and inserts the content of the View to a new temporary worksheet, which then is exported as CSV.

The mockup Excel looks like that:
Code: Select all
"id";"first_name";"last_name";"email";"gender";"ip_address"
1;"Mose";"Gwilliams";"mgwilliams0@mediafire.com";"Male";"48.78.23.183"
2;"Frank";"Rydeard";"frydeard1@people.com.cn";"Male";"7.144.103.62"
3;"Lezlie";"Mackriell";"lmackriell2@weather.com";"Female";"32.255.130.141"
4;"Nataline";"Blamphin";"nblamphin3@salon.com";"Female";"112.11.174.141"
[...~100000 more]


Using built-in Filter method => avg 200 secs execution time on my machine
Code: Select all
AutoFiltersCollection filters = worksheet.AutoFilters;
filters.Range = worksheet.Range[1, 1, worksheet.LastRow, worksheet.LastColumn];
filters.AddFilter(4, "Male");
filters.Filter();
worksheet.SaveToFile("TestFile.txt", ";", false);


C# DataView filtering => avg 15 secs execution time on my machine
Code: Select all
//create view applying the specified row filter
DataView dataView = worksheet.AsDataView();

// apply row filter
dataView.RowFilter = "Column5 = 'Male'";

// create new helper worksheet containing filtered DataView records and export to file
newWorksheet = worksheet.Workbook.Worksheets.Add("HelpSheetAsCSV");
newWorksheet.InsertDataTable(dataView.ToTable(), false, 1, 1);
newWorksheet.SaveToFile("TestFile.txt", ";", false);


KR,
Gerhard

GerhardFinee
 
Posts: 26
Joined: Thu Nov 07, 2019 7:57 am

Wed Nov 27, 2019 8:30 am

Hi,

Thanks for your reply.

1. We will consider adding this new feature(only save the filtered data while converting Excel file to Csv file) for SaveToStream method. Once there is any progress, we will inform you. Sorry for the inconvenience caused.

2. I made an initial test and did notice that the speed of the Filter() method is much slower than the speed of DataView filtering. To help us better record and solve it, could you please offer us your input Excel file?
You could upload it here or send us(support@e-iceblue.com) via email.

Best wishes,
Amber
E-iceblue support team
User avatar

Amber.Gu
 
Posts: 525
Joined: Tue Jun 04, 2019 3:16 am

Wed Nov 27, 2019 11:48 am

Hi Amber,

I sent you the input Excel file via email.

KR,
Gerhard

GerhardFinee
 
Posts: 26
Joined: Thu Nov 07, 2019 7:57 am

Thu Nov 28, 2019 6:51 am

Hi,

Thanks for your information via email.
I tested your file and did notice the issue(the speed of the Filter() method is much slower than the speed of DataView filtering) you mentioned. I have logged it into our bug tracking system. Once there is any progress, we will inform you.
Sorry for the inconvenience caused.

Best wishes,
Amber
E-iceblue support team
User avatar

Amber.Gu
 
Posts: 525
Joined: Tue Jun 04, 2019 3:16 am

Thu Dec 19, 2019 10:43 am

Hi,

Hope you are doing well.
Glad to tell you that the new feature(only save the filtered data while converting Excel file to Csv file) for SaveToStream method has been finished in Spire.XLS Pack(Hotfix) Version:9.12.20, and the new version has improved the speed of the Filter() method. Welcome to download and test it from the following links.
Our website link: https://www.e-iceblue.com/Download/download-excel-for-net-now.html
NuGet link: https://www.nuget.org/packages/Spire.XLS/9.12.20

Below is the code of SaveToStream method for you.
Code: Select all
            //Convert Excel file to Csv file and only save the filtered data using SaveToStream method.
            sheet.SaveToStream(outputStream, " ", false);

Best wishes,
Amber
E-iceblue support team
User avatar

Amber.Gu
 
Posts: 525
Joined: Tue Jun 04, 2019 3:16 am

Wed Dec 25, 2019 9:57 am

Hi,

Greetings from E-iceblue.
Have you tried Spire.XLS Pack(Hotfix) Version:9.12.20? Does it solve your issue?
Could you please give us some feedback at your convenience?

Best wishes,
Amber
E-iceblue support team
User avatar

Amber.Gu
 
Posts: 525
Joined: Tue Jun 04, 2019 3:16 am

Return to Spire.XLS