Spire.XLS is a professional Excel .NET library that can be used to any type of .NET( C#, VB.NET, ASP.NET, .NET Core) application and Java (J2SE and J2EE) application.

Sun Feb 28, 2021 10:24 am

How can open a xls or xlsx file in read-only mode then apply filter and then copy the filtered data to a datagridview. Also, I would like to not show/import all of the columns that are present in the sheet and just choose which columns auto filtered data I want show in the datagridview.
How can I do that using Spire.XLS?

I was trying to get to see if I can use a console application to print the filtered data on console to check whether I was on the right path but I was unable to print the filtered data on console and it just printed the entire worksheet data

Here's my code snippet
Code: Select all
            Workbook wbk = new Workbook();
            wbk.LoadFromFile(@"D:\@@@21-2-21\data.xlsx",true);

            Worksheet sht = wbk.Worksheets[0];

            AutoFiltersCollection filters = sht.AutoFilters;
           
            filters.Range = sht.Range[1, 1, sht.LastRow, sht.LastColumn];
            filters.AddFilter(10, "Seattle");
            filters.Filter();
            foreach (var item in filters.Range)
            {

                Console.WriteLine(item.Value2);

            }

            Console.WriteLine("Done!");


Please help.

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

Mon Mar 01, 2021 4:03 am

Hello,

Thanks for your inquiry, and sorry for the late reply as weekend.

Please refer to the following code to print the filtered data on the console and copy the filtered data to a DataGridView.
Code: Select all

            Workbook wbk = new Workbook();
            wbk.LoadFromFile(@"E:\testdoc\exc1.xlsx", true);

            Worksheet sht = wbk.Worksheets[0];

            AutoFiltersCollection filters = sht.AutoFilters;

            filters.Range = sht.Range[1, 1, sht.LastRow, sht.LastColumn];
            filters.AddFilter(2, "Seattle");
            filters.Filter();

            foreach (var row in filters.Range.Rows)
            {
                if (sht.IsRowVisible(row.Row) && row.Row != 1)
                {
                   

                    string[] rowdata = new string[row.CellsCount];
                    for(int i = 0; i < row.CellsCount; i++)
                    {
                        //show in cosole
                        System.Console.WriteLine(row. CellList [i].Value2);

                        rowdata[i] = row. CellList [i].Value;
                    }
                    this.dataGridView1.Rows.Add(rowdata);
                }
            }


If the code does not match your needs, please provide us with your input file for further investigation. Thanks in advance.

Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 421
Joined: Wed Nov 04, 2020 2:29 am

Mon Mar 01, 2021 5:13 am

Hello Marcia,

Thank you for your reply.

I've applied your code like below to my project:
Code: Select all
            Workbook wbk = new Workbook();
            wbk.LoadFromFile(@"E:\pl40\bill_consumables.xlsx", true);

            Worksheet sht = wbk.Worksheets[0];

            AutoFiltersCollection filters = sht.AutoFilters;

            filters.Range = sht.Range[1, 1, sht.LastRow, sht.LastColumn];
            filters.AddFilter(3, ">15000");
            filters.Filter();

            foreach (var row in filters.Range.Rows)
            {
                if (sht.IsRowVisible(row.Row) && row.Row != 1)
                {
                   

                    string[] rowdata = new string[row.CellsCount];
                    for(int i = 0; i < row.CellsCount; i++)
                    {
                        rowdata[i] = row. CellList [i].Value;
                    }
                    this.dataGridView1.Rows.Add(rowdata);
                }
            }


But getting error System.InvalidOperationException: No row can be added to a DataGridView control that does not have columns. Columns must be added first. in the line this.dataGridView1.Rows.Add(rowdata);

Also, how can I show specific columns data in the datagridview and not all columns data?

Can you help?
Attachments
bill_consumables.rar
sample file
(71.62 KiB) Downloaded 40 times

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

Mon Mar 01, 2021 8:00 am

Hello,

Thanks for your feedback!

Please refer to the following code to show specific columns' data in the datagridview. (e.g. show the data of columns 1-5 in the datagridview where the amount >15000)

Code: Select all
            Workbook wbk = new Workbook();
            wbk.LoadFromFile(@"E:\testdoc\bill_consumables\bill_consumables.xlsx", true);

            Worksheet sht = wbk.Worksheets[0];

            //e.g. select the data of columns 1-5 where amount >15000  to data grid view

            AutoFiltersCollection filters = sht.AutoFilters;

            filters.Range = sht.Range[1, 1, sht.LastRow, sht.LastColumn];

            FilterColumn column = filters[3] as FilterColumn;
            filters.CustomFilter(column, FilterOperatorType.GreaterThan, 15000);

            filters.Filter();
            foreach (var row in filters.Range.Rows)
            {
                if (sht.IsRowVisible(row.Row))
                {
                    if(row.Row == 1)
                    {
                        this.dataGridView1.ColumnCount = 5;
                        this.dataGridView1.Columns[0].HeaderText = row.Cells[0].DisplayedText;
                        this.dataGridView1.Columns[1].HeaderText = row.Cells[1].DisplayedText;
                        this.dataGridView1.Columns[2].HeaderText = row.Cells[2].DisplayedText;
                        this.dataGridView1.Columns[3].HeaderText = row.Cells[3].DisplayedText;
                        this.dataGridView1.Columns[4].HeaderText = row.Cells[4].DisplayedText;
                    }
                    else
                    {
                        string[] rowdata = new string[5];
                        rowdata[0] = row.Cells[0].DisplayedText;
                        rowdata[1] = row.Cells[1].DisplayedText;
                        rowdata[2] = row.Cells[2].DisplayedText;
                        rowdata[3] = row.Cells[3].DisplayedText;
                        rowdata[4] = row.Cells[4].DisplayedText;
                        this.dataGridView1.Rows.Add(rowdata);
                    }
                }
            }


If the code does not match your needs, please describe in detail which columns' data you want to show in the datagridview. Thanks in advance.

Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 421
Joined: Wed Nov 04, 2020 2:29 am

Mon Mar 01, 2021 8:21 am

Thanks Marcia :D

A couple of things that I want to know further

1) How can I apply multiple filters and then get the data to datagridview e.g. Amount column value >25000.00 and Remarks column is blank?
2) Is it possible to get the column number dynamically by Name of the column e.g. Party, Amount, Due date etc so that I only show those columns filtered value in datagridview?

Thanks in advance...

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

Mon Mar 01, 2021 10:13 am

Hello,

Thanks for your feedback!

Please refer to the following code to apply multiple filters like Amount column value >25000.00 and Remarks column is blank, and then show the Party, Amount, Due date columns filtered value in the datagridview by the Name of the column.

Code: Select all
            Workbook wbk = new Workbook();
            wbk.LoadFromFile(@"E:\testdoc\bill_consumables\bill_consumables.xlsx", true);

            Worksheet sht = wbk.Worksheets[0];

            // Amount column value >25000.00 and Remarks column  Party, Amount, Due date

            AutoFiltersCollection filters = sht.AutoFilters;
            filters.Range = sht.Range[1, 1, sht.LastRow, sht.LastColumn];

            FilterColumn column = filters[3] as FilterColumn;
            filters.CustomFilter(column, FilterOperatorType.GreaterThan, 25000.00);
            filters.MatchBlanks(5);

            filters.Filter();

            this.dataGridView1.ColumnCount = 3;
            int[] columnsIndexs = new int[3];

            foreach (var row in filters.Range.Rows)
            {
                if (sht.IsRowVisible(row.Row))
                {
                    if(row.Row == 1)
                    {
                        this.dataGridView1.Columns[0].HeaderText = row.Cells[0].DisplayedText;
                        this.dataGridView1.Columns[1].HeaderText = row.Cells[3].DisplayedText;
                        this.dataGridView1.Columns[2].HeaderText = row.Cells[4].DisplayedText;


                        for (int i = 0; i < this.dataGridView1.ColumnCount; i++)
                        {
                            foreach(CellRange cell in row.Cells)
                            {
                                if (cell.DisplayedText.Equals(this.dataGridView1.Columns[i].HeaderText))
                                {
                                    columnsIndexs[i] = cell.Column;
                                }
                            }
                        }

                    }
                    else
                    {

                        string[] rowdata = new string[3];

                        for(int i = 0; i< columnsIndexs.Length; i++)
                        {
                            rowdata[i] = row.Cells[columnsIndexs[i]-1].DisplayedText;
                        }

                        this.dataGridView1.Rows.Add(rowdata);
                    }
                }
            }


If you have any other questions, please feel free to contact us.

Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 421
Joined: Wed Nov 04, 2020 2:29 am

Mon Mar 01, 2021 11:10 am

Thanks once again!!. Your code works :D

But I think you misunderstood when I meant dynamically get the column numbers for showing data in datagridview.

What I meant was say I have 30 columns in my excel file and I don't remember which column number
stores the data that I want to fetch but I know the text of the column headers in this case it is Party, Amount & Due date
So, instead of hard coding row.Cells[0], row.Cells[3] & row.Cells[4]
how do I get them dynamically by searching the Header text of those columns.

Guess it is a bit clear now.

Thanks in advance.

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

Tue Mar 02, 2021 3:54 am

Hello,

Thanks for your detailed description.

Please refer to the following code to get the sheet data dynamically by searching the header text of the DataGridView columns.

Code: Select all
            Workbook wbk = new Workbook();
            wbk.LoadFromFile(@"E:\testdoc\bill_consumables\bill_consumables.xlsx", true);
            Worksheet sht = wbk.Worksheets[0];

            // Amount column value >25000.00 and Remarks column  Party, Amount, Due date

            AutoFiltersCollection filters = sht.AutoFilters;
            filters.Range = sht.Range[1, 1, sht.LastRow, sht.LastColumn];

            FilterColumn column = filters[3] as FilterColumn;
            filters.CustomFilter(column, FilterOperatorType.GreaterThan, 25000.00);
            filters.MatchBlanks(5);

            filters.Filter();

            //show in dataGrid View

            //columnHeader
            string[] headers = new string[] { "Party", "Amount", "Due date" };
            this.dataGridView1.ColumnCount = headers.Length;

            //save the index of columnHeader
            int[] columnsIndexs = new int[headers.Length];

            foreach (var row in filters.Range.Rows)
            {
                if (sht.IsRowVisible(row.Row))
                {
                    if(row.Row == 1)
                    {

                        for (int i = 0; i < headers.Length; i++)
                        {
                            //add the headertext of the columns
                            this.dataGridView1.Columns[i].HeaderText = headers[i];

                            //loop to get the index of the headertext in the first row
                            foreach(CellRange cell in row.CellList)
                            {
                                if (cell.DisplayedText.Equals(this.dataGridView1.Columns[i].HeaderText))
                                {
                                    columnsIndexs[i] = cell.Column;
                                }
                            }
                        }

                    }
                    else
                    {

                        string[] rowdata = new string[headers.Length];

                        for(int i = 0; i< columnsIndexs.Length; i++)
                        {
                            rowdata[i] = row.CellList[columnsIndexs[i]-1].DisplayedText;
                        }

                        this.dataGridView1.Rows.Add(rowdata);
                    }
                }
            }


If you have any other questions, please feel free to contact us.

Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 421
Joined: Wed Nov 04, 2020 2:29 am

Tue Mar 02, 2021 4:35 am

Thank you for your continuous assistance. Really appreciate it.

One last thing. Why are blank cells showing up in datagridview after the filtered data ends?

Also, do I need to close the workbook after I use the LoadFromFile to read the file?

Thanks

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

Tue Mar 02, 2021 6:44 am

Hello,

Thanks for your feedback!

For the first issue: Please set this.dataGridView1.AllowUserToAddRows = false; to make the blank cells do not show up.

For the second issue: If you do not need to read the data in the workbook any longer, you can free up memory resources through the WorkBook.Dispose() method.

If you encounter any issues related to our product in the future, just feel free to contact us.

Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 421
Joined: Wed Nov 04, 2020 2:29 am

Tue Mar 02, 2021 7:22 am

Hi,

I've set this.dataGridView1.AllowUserToAddRows = false; to make the blank cells do not show up as advised by you but the blank cells are still appearing.

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

Tue Mar 02, 2021 8:25 am

Hello,

Thanks for your feedback!

Actually, this requirement has nothing to do with our product and I don’t know much about this knowledge. But I can make the blank cells do not show up with set this.dataGridView1.AllowUserToAddRows = false, I also attached my result screenshots.

res.png
res.png (31.16 KiB) Viewed 406 times

For your issue, I recommend that you find the relevant help documentation online.

If you encounter any issues related to our product in the future, just feel free to contact us.

Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 421
Joined: Wed Nov 04, 2020 2:29 am

Tue Mar 02, 2021 8:35 am

Hi,

Thanks for your help!

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

Tue Mar 02, 2021 8:46 am

Hello,

You are welcome.

If you encounter any issues related to our products in the future, please feel free to contact us.

Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 421
Joined: Wed Nov 04, 2020 2:29 am

Sun Mar 21, 2021 2:45 pm

Hi,

I've tried to add filtered data to a datagridview using below code:

Code: Select all
         Workbook wbk = new Workbook();
         wbk.LoadFromFile(@"D:\PPl\bill_consumables.xlsx", true);
         Worksheet sht = wbk.Worksheets[0];

         AutoFiltersCollection filters = sht.AutoFilters;
         filters.Range = sht.Range[1, 1, sht.LastRow, sht.LastColumn];
         
         filters.AddFilter(0,comboBox1.Text);
         filters.MatchBlanks(5);

         filters.Filter();

         //show in dataGrid View

         //columnHeader
         string[] headers = new string[] { "Party", "Amount", "Due date" };
         this.dataGridView1.ColumnCount = headers.Length;

         //save the index of columnHeader
         int[] columnsIndexs = new int[headers.Length];

         foreach (var row in filters.Range.Rows)
         {
            if (sht.IsRowVisible(row.Row))
            {
               if(row.Row == 1)
               {

                  for (int i = 0; i < headers.Length; i++)
                  {
                     //add the headertext of the columns
                     this.dataGridView1.Columns[i].HeaderText = headers[i];

                     //loop to get the index of the headertext in the first row
                     foreach(CellRange cell in row.CellList)
                     {
                        if (cell.DisplayedText.Equals(this.dataGridView1.Columns[i].HeaderText))
                        {
                           columnsIndexs[i] = cell.Column;
                        }
                     }
                  }

               }
               else
               {

                  string[] rowdata = new string[headers.Length];

                  for(int i = 0; i< columnsIndexs.Length; i++)
                  {
                     rowdata[i] = row.CellList[columnsIndexs[i]-1].DisplayedText;
                  }

                  this.dataGridView1.Rows.Add(rowdata);
               }
            }
         }


But the code takes a lot of time to show data in the datagridview, I mean its extremely slow even though my file(which I have already uploaded in this post previously) does not have too much data. How can I make it faster ? Should I ditch the filtered data approach? What other way can this be achieved if the file is very big?

Thanks.

DB

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

Return to Spire.XLS