locked
Assistance with reading a drop down in excel using c# RRS feed

  • Question

  • I am writing a program in C# that reads and randomly populates an excel spreadsheet with various types of cells including drop-downs (the workbooks are all different and I never know where they are going to put the drop down. I don't really care what data is in the drop-down I just need to select an item from the list and populate the cell). I am using Microsoft.Office.Interop.Excel and have functionality working for the most part but am struggling on how to locate the drop downs and then populate with the default or any value in the drop-down list.  My code is as follows. (be kind I am still new to c# :-))

    Thank you in advance for any assistance you can provide.

    private void button1_Click(object sender, EventArgs e)
            {
                
                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                Excel.Range range;
                Excel.DropDowns xlDropDowns;
                Excel.DropDown xlDropDown;

                string path = @"folder path foes here"; //will need to use path once folders are set up permanently
                string outputPath = @"folder path goes here"
                string fn = "";
                string str;

                Random random = new Random(100);
                int rand = random.Next(100, 5000);

                DirectoryInfo dI = new DirectoryInfo(@"path goes here");//will need to use path once folders are set up permanently
                FileInfo[] fileName = dI.GetFiles("*.xls*");

                foreach (FileInfo file in fileName)
                {
                    fn = file.Name;
                    //Console.WriteLine("GetFilename = " + fn);
                }

                xlApp = new Excel.Application();
                completedLabel.Text = "working";
                xlWorkBook = xlApp.Workbooks.Open(@"path goes here" + fn, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // this is getting 1 - how to get more?

                int rCnt;
                int cCnt;
                int wsCount = xlWorkBook.Worksheets.Count;
                Console.WriteLine("# ws: " + wsCount);

                range = xlWorkBook.ActiveSheet.UsedRange; // this is to find the 'range' of used cells in the worksheet
                xlDropDowns = ((Excel.DropDowns)(xlWorkBook.ActiveSheet.DropDowns(Type.Missing)));

                 <g class="gr_ gr_736 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="736" id="736">foreach</g> (Excel<g class="gr_ gr_738 gr-alert gr_gramm gr_inline_cards gr_run_anim Style replaceWithoutSep" data-gr-id="738" id="738">.Worksheet</g> <g class="gr_ gr_737 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" data-gr-id="737" id="737">xlworksheet</g> in xlWorkBook.Worksheets)
                {

                int cellCnt = 0;
                int rw = 0;
                int cl = 0;
                rw = range.Rows.Count;
                cl = range.Columns.Count; 

                for (rCnt = 1; rCnt <= rw; rCnt++) // loop through row
                {
                    for (cCnt = 1; cCnt <= cl; cCnt++) //loop though column
                    {
                        // **** TODO **********************************
                        //if (is a dropdownlist cell) //if (default exists) // use default //else //use list<1>
                        //***********************************************

                        str = Convert.ToString((range.Cells[rCnt, cCnt] as Excel.Range).Value2);
                        
                        if (range.Cells[rCnt, cCnt].Locked == false && string.IsNullOrEmpty(str) && !range.Cells[rCnt, cCnt].HasFormula)
                        //True if the object is locked; False if the object can be modified when the sheet is protected. Returns Null if the specified range contains both locked and unlocked cells.
                        {
                            range.Cells[rCnt, cCnt] = rand.ToString();
                            rand++;
                            cellCnt++;
                        }
                    }
                    rowCountLabel.Text = "Row Count is : " + rCnt;
                    colCountLabel.Text = "Column Count is : " + cCnt;
                    cellCountLabel.Text = "Unlocked Cell Count is : " + cellCnt;  // add in a cell count
                }
                completedLabel.Text = "completed";
                xlWorkBook.SaveAs(outputPath + fn); // Save workbook to output file
                xlWorkBook.Close(true, null, null);
                xlApp.Quit();

                Marshal.ReleaseComObject(xlWorkSheet);
                Marshal.ReleaseComObject(xlWorkBook);
                Marshal.ReleaseComObject(xlApp);
            }
            private void closeButton_Click(object sender, EventArgs e)
            {
                System.Windows.Forms.Application.Exit();
            }
        }

    Tuesday, August 14, 2018 12:46 AM

All replies

  • Hi Spitfyre586,

    Since your question is more related to excel, I will move it to VSTO forum for suitable support.

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=vsto&filter=alltypes&sort=lastpostdesc

    The Visual C# forum discuss and ask questions about the C# programming language, IDE, libraries, samples, and tools.

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 14, 2018 5:34 AM
  • Excel.Dropdown? No, as far as I know, there is no such object in Excel Interop dll, are you sure using the Excel Interop dll?

    Usually, what we said dropdown in cell is an Validation object and it does not provide a function to select an item in it. You need get the item list of it and then set one of them as the value of the Validation's corresponding cell. Surely, you need check if the cell has an dropdown validation first. Check the Validation.Type property, it will return 3 if the cell has an dropdown list validation and please note it may cause error if it does not contain any validation(at lease in vba, it caused error).

    Tuesday, August 14, 2018 9:18 AM
  • I checked your code carefully and found a lot of problems(Code is a mess). As you said , since you might be new to C#. 

    For your reference, please see this post : How to read Comboboxes Value from Excel using C#

    Code Snippet:

    Read value from excel combobox
                        Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
                        Microsoft.Office.Interop.Excel._Workbook oWB;
                        Microsoft.Office.Interop.Excel._Worksheet oSheet;
                        Microsoft.Office.Interop.Excel.Range oRng;
    
                        //Get a new workbook.
                        oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Open("C:\\TopicUpload_2017October14.xls"));
                        //3rd Sheet
                        oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.Sheets.get_Item(1);
    
                        Microsoft.Office.Interop.Excel.DropDowns allDropDowns = oSheet.DropDowns(Type.Missing);
                        Microsoft.Office.Interop.Excel.DropDown oneDropdown = allDropDowns.Item("1"); // first combo
                        string selectedText = oneDropdown.get_List(oneDropdown.ListIndex); 
        #endregion

    To help you understanding, you should be test it step by step. 

    For C# information, please see Get started with C#

    Hope it helps you.

    Thanks,

    Simon


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.


    Tuesday, August 14, 2018 10:59 AM
  • Any updates for this? Did you resolved your issue? 

    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Friday, August 17, 2018 2:44 AM