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