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.

Mon Mar 22, 2021 11:11 am

Hello,

Thanks for your feedback.
I made some adjustments to the code to improve the speed. Please download the sample project from the following link and give it a try. If there is any question, just feel free to contact us.
link: http://www.e-iceblue.com/downloads/demo/24771Demo.zip

Sincerely,
Elena
E-iceblue support team
User avatar

Elena.Zhang
 
Posts: 279
Joined: Thu Jul 23, 2020 1:18 am

Tue Mar 23, 2021 4:40 pm

Hi,

The speed did not increase that much, could the whole approach be different to make it faster like almost instantaneous, I mean the file does not have much data at all.

Thanks and Regards

DB

DB_007
 
Posts: 36
Joined: Sat Sep 08, 2018 4:45 pm

Wed Mar 24, 2021 10:05 am

Hello,

Thanks for your feedback.
How long did it take you to test my project? Based the file “bill_consumables.xlsx” you provided, I increased records to 3000 and tested it, but it takes less than 1 second to filter new data each time. How much time do you expect it to take each time?

Besides, if you have made other changes to the project I provided, please share the modified project for further investigation. Thanks in advance.

Sincerely,
Elena
E-iceblue support team
User avatar

Elena.Zhang
 
Posts: 279
Joined: Thu Jul 23, 2020 1:18 am

Wed Mar 24, 2021 1:21 pm

Hi,

I've added my screen recording video of the project. I've also added my project for your reference.

I don't understand why is it running so slow?
Attachments
TestProject1.zip
(37.7 KiB) Downloaded 97 times
TestProject.zip
(1.3 MiB) Downloaded 90 times

DB_007
 
Posts: 36
Joined: Sat Sep 08, 2018 4:45 pm

Thu Mar 25, 2021 10:04 am

Hello,

Thanks for your feedback.
I made some adjustments based on your project. Please run it directly on your side to have a test. Feel free to contact us if you have further questions.
link: https://www.e-iceblue.com/downloads/demo/24771Demo2.zip

Sincerely,
Elena
E-iceblue support team
User avatar

Elena.Zhang
 
Posts: 279
Joined: Thu Jul 23, 2020 1:18 am

Fri Mar 26, 2021 10:49 am

Hi,

Please find my updated project (using your last updated code).

I have added a button to load data to datagridview instead of comboBox1_SelectedIndexChanged event. But when I hit the said button it works for the first time when I select an item from combo box but when I select another one after that it is showing no data in datagridview. The data in datagridview should update every time I select a different item in combo box and hit the button. But it is not happening.

Please help.

Thanks in advance
Attachments
Forwarding.zip
(46.71 KiB) Downloaded 93 times

DB_007
 
Posts: 36
Joined: Sat Sep 08, 2018 4:45 pm

Mon Mar 29, 2021 10:17 am

Hello,

Thanks for your feedback.
I tested with your project and did observe the behavior you mentioned. However, I used the same code and settings to create a new project and tested it, and found that it could work well. I suggest you can create a new project to try again. Also, here is my test project for your kind reference.

Sincerely,
Elena
E-iceblue support team
User avatar

Elena.Zhang
 
Posts: 279
Joined: Thu Jul 23, 2020 1:18 am

Mon Mar 29, 2021 10:39 am

Hi,

If I change the string in 'wbk.LoadFromFile' i.e. the file path, the changes do not effect. Furthermore, if I delete the file in bin directory I get 'System.IO.FileNotFoundException: Could not find file'.

DB_007
 
Posts: 36
Joined: Sat Sep 08, 2018 4:45 pm

Tue Mar 30, 2021 9:55 am

Hello,

Thanks for your feedback.
In my test project, I put the input file "bill_consumables.xlsx" in the "bin\Debug" directory, so after you delete the file in the bin directory and then run the project, it will throw the error "System.IO.FileNotFoundException: Could not find the file". In response to this situation, I have added a new button "Open File" to the project to open the document. You can click it to select the file you want to open. Please download the updated project from the link below and have a test. Feel free to contact us if you have further questions.
link: https://www.e-iceblue.com/downloads/demo/24771Demo4.zip

Sincerely,
Elena
E-iceblue support team
User avatar

Elena.Zhang
 
Posts: 279
Joined: Thu Jul 23, 2020 1:18 am

Wed Mar 31, 2021 5:26 am

I think I now figured out what was wrong in my project and why the datagridview data was not updating when I select different item on combo box. It seems it has something to do with Freespire.XLS dll. When I copied your dll from your project to mine everything seem to work fine.


IIs this a bug in Freespire.XLS?

DB_007
 
Posts: 36
Joined: Sat Sep 08, 2018 4:45 pm

Wed Mar 31, 2021 9:36 am

Hello,

Thanks for your feedback.
After further analysis and debugging, I found there is a bug in the free version. The filters.Filter() method did not filter the correct value. And this issue doesn’t exist in the dll (paid version) I used.
To avoid this bug when using the free version, I re-adjusted the code. Please download the updated project from the following link. Feel free to contact us if you have further questions.
link: https://www.e-iceblue.com/downloads/demo/24771Demo5.zip

Sincerely,
Elena
E-iceblue support team
User avatar

Elena.Zhang
 
Posts: 279
Joined: Thu Jul 23, 2020 1:18 am

Wed Mar 31, 2021 1:30 pm

Hi,

Thanks for your help.

I did run your re-adjusted code & so far no issues. I even added an extra filter filters.MatchBlanks(5); after filters.AddFilter(0, this.comboBox1.Text); and did seem to work. I'll let you know if I find any issues with it.

You did mention that the '
filters.Filter() method did not filter the correct value
', however you used that method in your re-adjusted code, so I'm a bit confused. Can you clarify a little?
Also, do you mind showing/explaining all the changes you made in your new code?... I can spot a few but will help if you can explain the changes.

Thanks

DB_007
 
Posts: 36
Joined: Sat Sep 08, 2018 4:45 pm

Thu Apr 01, 2021 9:06 am

Hello,

Thanks for your feedback.
In the previous project I provided, I used the code “filters.Range = sht.Range[1, 1, sht.LastRow, sht.LastColumn]” to set the filter range, which includes many empty rows (the value of sht.LastRow is 65536). When using the free version, after executing the "filters.Filter() method" to filter the data, some empty rows are incorrectly filtered out (as shown below), which is the main reason for the slow speed.
screenshot-1.png
screenshot-1.png (30.12 KiB) Viewed 178 times

To avoid this issue, I used the following code to find the last non-empty row, and then changed the filter range to “filters.Range = sht.Range[1, 1, lastRow, sht.LastColumn]”. To help you better understand the changes, I have attached the commented code for your reference.
Code: Select all
                    Worksheet sheet = wbk.Worksheets[0];
                    //Get the first columns
                    List<CellRange> crs = sheet.Columns[0].CellList;

                    for (int i = crs.Count - 1; i > 0; i--)
                    {
                        //Get the last non-empty row
                        if (!string.IsNullOrEmpty(crs[i].DisplayedText))
                        {
                            lastRow = i + 1;
                            break;
                        }
                    }

MainForm1(1).zip
(1.44 KiB) Downloaded 82 times


Sincerely,
Elena
E-iceblue support team
User avatar

Elena.Zhang
 
Posts: 279
Joined: Thu Jul 23, 2020 1:18 am

Return to Spire.XLS