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.

Wed Mar 03, 2021 10:38 am

Hi,

I’m trying to create a windows form application using c# and spire.XLS where there are 2 combo boxes in a form. How do I make those combo boxes cascading like below:
Situation 1:
When the form loads the first combo box should show a drop down of the column names mentioned in the workbook(say “Sheet1” if there are more than 1 sheet).
When I select a value from the first combo box then the second combo box should show a drop down with the same column names minus the item selected in combo box 1.

Situation 2:
When the form loads the first combo box should show a drop down of the column names mentioned in the workbook(say “Sheet1” if there are more than 1 sheet).
When I select a value from the first combo box e.g. ERICOM, then the second combo box should show a drop down of the respective Bill Date in the format that is in the sheet i.e.

17 October 2020
05 December 2020
08 December 2020
07 January 2021
05 January 2021
07 January 2021
03 February 2021
05 February 2021

How can I do this?
I've done the below for Situation 1 but it's not working:
Code: Select all
      public CellRange[] crs=null;
      public Worksheet sheet=null;
      public Workbook workbook=null;
      
      void MainFormLoad(object sender, EventArgs e)
      {
         try {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"E:\ABC\bill_consumables.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
            
            CellRange[] crs = sheet.Rows[0].Cells;

            foreach (CellRange cr in crs)
            {
               if (!String.IsNullOrEmpty(cr.Value2.ToString()))
               {
                  comboBox1.Items.Add(cr.Value);
               }
               
            }
            workbook.Dispose();

         } catch (Exception ex) {
            
            MessageBox.Show(ex.Message);
         }
         
      }
      void ComboBox1SelectionChangeCommitted(object sender, EventArgs e)
      {
         try {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"E:\ABC\bill_consumables.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
            
            CellRange[] crs = sheet.Rows[0].Cells;

            foreach (CellRange cr in crs)
            {
               if (!String.IsNullOrEmpty(cr.Value2.ToString()))
               {
                  comboBox2.Items.Add(cr.Value);
               }
            }
            
            comboBox2.Items.Remove(comboBox1.Text);
            
         } catch (Exception ex) {
            
            MessageBox.Show(ex.Message);
         }

      }
Attachments
bill_consumables.zip
(85.84 KiB) Downloaded 150 times

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

Thu Mar 04, 2021 10:32 am

Hello,

Thanks for your inquiry.
Regarding the situation1, please refer to the following modified code.
Code: Select all
        void MainFormLoad(object sender, EventArgs e)
        {
            try
            {
                Workbook workbook = new Workbook();
                workbook.LoadFromFile(@"bill_consumables.xlsx");
                Worksheet sheet = workbook.Worksheets[0];
                if (workbook.Worksheets.Count > 1)
                {
                    comboBox1.Text = "Sheet1";
                }
                List<CellRange> crs = sheet.Rows[0].CellList;

                foreach (CellRange cr in crs)
                {
                    if (!String.IsNullOrEmpty(cr.Value2.ToString()))
                    {
                        comboBox1.Items.Add(cr.Value);
                    }

                }
                workbook.Dispose();

            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }

        }
        void ComboBox1SelectionChangeCommitted(object sender, EventArgs e)
        {
            try
            {
                Workbook workbook = new Workbook();
                workbook.LoadFromFile(@"bill_consumables.xlsx");
                Worksheet sheet = workbook.Worksheets[0];

                List<CellRange> crs = sheet.Rows[0].CellList;
                comboBox2.Items.Clear();
                foreach (CellRange cr in crs)
                {
                    if (!String.IsNullOrEmpty(cr.Value2.ToString()))
                    {
                        comboBox2.Items.Add(cr.Value);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        private void UpdateDropDown(object sender, EventArgs e)
        {
            comboBox2.Items.Remove(comboBox1.Text);
        }


Regarding the situation2, please refer to the following code. If this is not what you want, please describe your requirement in more detail.
Code: Select all
        void MainFormLoad(object sender, EventArgs e)
        {
            try
            {
                Workbook workbook = new Workbook();
                workbook.LoadFromFile(@"bill_consumables.xlsx");
                Worksheet sheet = workbook.Worksheets[0];
                if (workbook.Worksheets.Count > 1)
                {
                    comboBox1.Text = "Sheet1";
                }
                List<CellRange> crs = sheet.Columns[0].CellList;

                foreach (CellRange cr in crs)
                {
                    if (!string.IsNullOrEmpty(cr.Value) && !comboBox1.Items.Contains(cr.Value))
                    {
                        comboBox1.Items.Add(cr.Value);
                    }
                }
                comboBox1.Items.Remove(sheet.Columns[0].Rows[0].Text);

                workbook.Dispose();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void UpdateDropDown(object sender, EventArgs e)
        {
            comboBox2.Items.Clear();
            List<String> billDate = new List<String>();
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"bill_consumables.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
            if (!string.IsNullOrEmpty(comboBox1.Text) && !comboBox1.Text.Equals("Sheet1"))
            {
                for (int i = 0; i < 60; i++)
                {
                    if (sheet.Columns[0].Rows[i].Text == comboBox1.Text)
                    {
                        billDate.Add(sheet.Columns[2].Rows[i].Text);
                    }
                }
            }
            for (int i = 0; i < billDate.Count; i++)
            {
                for (int j = i + 1; j < billDate.Count; j++)
                {
                    if (billDate[i] == billDate[j])
                    {
                        billDate.RemoveAt(i);
                    }
                }
            }
            for (int i = 0; i < billDate.Count; i++)
            {
                comboBox2.Items.Add(billDate[i]);
            }
        }


Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Thu Mar 04, 2021 12:51 pm

Hi,

Both your code seems to work thus far.

But when I run the code(specially for situation 2) it runs extremely slow for some reason.

Is there a way to make the codes efficient?

Thanks

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

Fri Mar 05, 2021 3:57 am

Hello,

Thanks for your feedback.
Regarding the situation2, I make some adjustments to the code to improve the speed. Please give it a try.
Code: Select all
        void MainFormLoad(object sender, EventArgs e)
        {
            try
            {
                Workbook workbook = new Workbook();
                workbook.LoadFromFile(@"bill_consumables.xlsx");
                Worksheet sheet = workbook.Worksheets[0];
                if (workbook.Worksheets.Count > 1)
                {
                    comboBox1.Text = "Sheet1";
                }
                crs = sheet.Columns[0].CellList;

                crs.RemoveAt(0);
                foreach (CellRange cr in crs)
                {
                    if (!string.IsNullOrEmpty(cr.Value) && !comboBox1.Items.Contains(cr.Value))
                    {
                        comboBox1.Items.Add(cr.Value);
                    }
                }

                workbook.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void UpdateDropDown(object sender, EventArgs e)
        {
            comboBox2.Items.Clear();
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"bill_consumables.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
            crs = sheet.Columns[0].CellList;
            List<CellRange> crs2 = sheet.Columns[2].CellList;
            List<String> billDate= new List<String>();

            if (!string.IsNullOrEmpty(comboBox1.Text) && !comboBox1.Text.Equals("Sheet1"))
            {
                for (int i = 1; i < crs2.Count; i++)
                {
                    if (crs[i].Text == comboBox1.Text)
                    {
                        billDate.Add(crs2[i].Text);
                    }
                }
            }
            for (int i = 0; i < billDate.Count; i++)
            {
                for (int j = i + 1; j < billDate.Count; j++)
                {
                    if (billDate[i] == billDate[j])
                    {
                        billDate.RemoveAt(j);
                        j--;
                    }
                }
            }
            for (int i = 0; i < billDate.Count; i++)
            {
                comboBox2.Items.Add(billDate[i]);
            }
        }


Sicerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Fri Mar 05, 2021 5:42 am

Hi,

Appreciate your support!

It did improve the speed a little. However when I select an item on combo box1 then still takes few seconds to show the selected items in combo box1 text and the values in combo box2.

Also, seeing your code it seems first you are getting all the values from the column Bill Date and then removing items from them (or maybe I’m getting it wrong) which might be contributing to the slowness. Is it possible, instead of that approach if it is possible to use autofilter somehow by using the data selected in the combo box1 then just get the appropriate Bill Date from that using the value in the combo box1 as criteria or something like that?

Looking forward to your reply.

Thanks
DB

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

Fri Mar 05, 2021 11:41 am

Hello,

Thanks for your feedback.
According to the suggestions you provided, I changed to use the Dictionary collection to store the data, which would be faster. Below is the corresponding code for your reference.
Code: Select all
        public List<CellRange> crs = null;
        public Dictionary<String, List<String>> dc;
        //Situation2
        void MainFormLoad(object sender, EventArgs e)
        {
            try
            {
                Workbook workbook = new Workbook();
                workbook.LoadFromFile(@"bill_consumables.xlsx");
                Worksheet sheet = workbook.Worksheets[0];
                crs = sheet.Columns[0].CellList;
                dc = new Dictionary<String, List<String>>();
                List<CellRange> crs2 = sheet.Columns[2].CellList;
                for (int i = 1; i < crs.Count; i++)
                {
                    if (!string.IsNullOrEmpty(crs[i].Text))
                    {
                        if (dc.ContainsKey(crs[i].Text))
                        {
                            List<string> list = dc[crs[i].Text];
                            if (!Isreapet(list, crs2[i].Text))
                            {
                                list.Add(crs2[i].Text);
                            }
                        }
                        else
                        {
                            List<string> list = new List<string>();
                            list.Add(crs2[i].Text);
                            dc.Add(crs[i].Text, list);
                        }
                    }
                }

                if (workbook.Worksheets.Count > 1)
                {
                    comboBox1.Text = "Sheet1";
                }

                foreach (string key in dc.Keys)
                {
                    comboBox1.Items.Add(key);
                }

                workbook.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void UpdateDropDown(object sender, EventArgs e)
        {
            comboBox2.Items.Clear();
            List<string> list = dc[comboBox1.Text];
            foreach (string s in list)
            {
                comboBox2.Items.Add(s);
            }
        }

        private bool Isreapet(List<string> list, string value)
        {
            foreach (string s in list)
            {
                if (s.Equals(value))
                {
                    return true;
                }
            }
            return false;
        }


Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Fri Mar 05, 2021 12:58 pm

Thanks you very much for your support. It's working well :D

I'll let you know if I find any issues in future.

Thanks
DB

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

Mon Mar 08, 2021 1:12 am

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

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Return to Spire.XLS