none
VSTO Excel addin cell dropdown validation RRS feed

  • Question

  • Hi

    I'm using VS2015 and creating VSTO Excel add-in for MS Excel 2010

    I don't want my users to enter a free flow text in certain COLUMNS, I want then to select it from dropdown list (validation list) in the cell and if the database as a value I want it to be pre selected.

    var dt = new System.Data.DataTable();
                    dt.Load(dreader);
    
                    
                    Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1];
                    worksheet.Cells.ClearContents();
                                    
                    // Create a workhseet host item.
                    Worksheet extendedWorksheet = Globals.Factory.GetVstoObject(worksheet);
                    if (extendedWorksheet.Controls.IndexOf("BDetails")>-1)
                    {
                        extendedWorksheet.Controls.Remove("BDetails");                    
                    }
    
                    Microsoft.Office.Interop.Excel.Range cell = extendedWorksheet.Range["$A$1:$AZ$5"];
                    
    
    string[] ddl_DStatus = { "Yes", "No" };
                extendedWorkSheet.Range["$AP$12"].Validation.Add(XlDVType.xlValidateList, null, null, string.Join(",", ddl_DStatus));
    
                    this.sListObject = extendedWorksheet.Controls.AddListObject(cell, "BDetails");
                    this.sListObject.AutoSetDataBoundColumnHeaders = true;               
    
                    this.sListObject.SetDataBinding(
                                dt, columns list);



    • Edited by StSingh Saturday, April 8, 2017 1:05 PM
    Saturday, April 8, 2017 1:03 PM

Answers

  • Hi StSingh,

    You could get value from datatable and put them into an array list. Then you could set certain columns validation list with value from the datatable.

    Here is the example.

    var dt = new System.Data.DataTable();
                DataColumn dc1 = new DataColumn("String", Type.GetType("System.String"));
                DataColumn dc2 = new DataColumn("Int", Type.GetType("System.Int16"));
                dt.Columns.Add(dc1);
                dt.Columns.Add(dc2);
                for (int i = 0; i < 10; i++)
                {
                    DataRow dr = dt.NewRow();
                    dr["String"] = "String" + i;
                    dr["Int"] = 10 + i;
                    dt.Rows.Add(dr);
                }
                //dt.Load(dreader);
     
                Microsoft.Office.Interop.Excel.Worksheet worksheet = Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1];
                worksheet.Cells.ClearContents();
     
                // Create a workhseet host item.
                ExcelTool.Worksheet extendedWorksheet = Globals.Factory.GetVstoObject(worksheet);
                if (extendedWorksheet.Controls.IndexOf("BDetails") > -1)
                {
                    extendedWorksheet.Controls.Remove("BDetails");
                }
     
                Microsoft.Office.Interop.Excel.Range cell = extendedWorksheet.Range["$A$1:$F$5"];
     
                List<string> ddl_DStatus1 = new List<string>();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    ddl_DStatus1.Add(dt.Rows[i]["String"].ToString());
                }
                extendedWorksheet.Range["A:A"].Validation.Delete();
                extendedWorksheet.Range["A:A"].Validation.Add(XlDVType.xlValidateList, Type.Missing, Type.Missing, string.Join(",", ddl_DStatus1));
     
                List<string> ddl_DStatus2 = new List<string>();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    ddl_DStatus2.Add(dt.Rows[i]["int"].ToString());
                }
                extendedWorksheet.Range["B:B"].Validation.Delete();
                extendedWorksheet.Range["B:B"].Validation.Add(XlDVType.xlValidateList, Type.Missing, Type.Missing, string.Join(",", ddl_DStatus2));
     
                
                var sListObject = extendedWorksheet.Controls.AddListObject(cell, "BDetails");
                sListObject.AutoSetDataBoundColumnHeaders = true;
                sListObject.SetDataBinding(
                           dt);
    

    To set the pre-selected value, please assign a value to the cell.

                Range rng = extendedWorksheet.Range["A2:A30"];
                foreach (Range cel in rng)
                {
                    if (string.IsNullOrEmpty(cel.value))
                    {
                        cel.Value = ddl_DStatus1[1].ToString();
                    }
                }
    

    Regards,

    Celeste


    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.

    • Marked as answer by StSingh Tuesday, April 11, 2017 3:54 PM
    Monday, April 10, 2017 5:16 AM
    Moderator

All replies

  • Hi StSingh,

    You could get value from datatable and put them into an array list. Then you could set certain columns validation list with value from the datatable.

    Here is the example.

    var dt = new System.Data.DataTable();
                DataColumn dc1 = new DataColumn("String", Type.GetType("System.String"));
                DataColumn dc2 = new DataColumn("Int", Type.GetType("System.Int16"));
                dt.Columns.Add(dc1);
                dt.Columns.Add(dc2);
                for (int i = 0; i < 10; i++)
                {
                    DataRow dr = dt.NewRow();
                    dr["String"] = "String" + i;
                    dr["Int"] = 10 + i;
                    dt.Rows.Add(dr);
                }
                //dt.Load(dreader);
     
                Microsoft.Office.Interop.Excel.Worksheet worksheet = Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1];
                worksheet.Cells.ClearContents();
     
                // Create a workhseet host item.
                ExcelTool.Worksheet extendedWorksheet = Globals.Factory.GetVstoObject(worksheet);
                if (extendedWorksheet.Controls.IndexOf("BDetails") > -1)
                {
                    extendedWorksheet.Controls.Remove("BDetails");
                }
     
                Microsoft.Office.Interop.Excel.Range cell = extendedWorksheet.Range["$A$1:$F$5"];
     
                List<string> ddl_DStatus1 = new List<string>();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    ddl_DStatus1.Add(dt.Rows[i]["String"].ToString());
                }
                extendedWorksheet.Range["A:A"].Validation.Delete();
                extendedWorksheet.Range["A:A"].Validation.Add(XlDVType.xlValidateList, Type.Missing, Type.Missing, string.Join(",", ddl_DStatus1));
     
                List<string> ddl_DStatus2 = new List<string>();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    ddl_DStatus2.Add(dt.Rows[i]["int"].ToString());
                }
                extendedWorksheet.Range["B:B"].Validation.Delete();
                extendedWorksheet.Range["B:B"].Validation.Add(XlDVType.xlValidateList, Type.Missing, Type.Missing, string.Join(",", ddl_DStatus2));
     
                
                var sListObject = extendedWorksheet.Controls.AddListObject(cell, "BDetails");
                sListObject.AutoSetDataBoundColumnHeaders = true;
                sListObject.SetDataBinding(
                           dt);
    

    To set the pre-selected value, please assign a value to the cell.

                Range rng = extendedWorksheet.Range["A2:A30"];
                foreach (Range cel in rng)
                {
                    if (string.IsNullOrEmpty(cel.value))
                    {
                        cel.Value = ddl_DStatus1[1].ToString();
                    }
                }
    

    Regards,

    Celeste


    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.

    • Marked as answer by StSingh Tuesday, April 11, 2017 3:54 PM
    Monday, April 10, 2017 5:16 AM
    Moderator
  • Many thanks for the detailed response.

    When I flatend the list with below code, it populates like "Yes,No" on the excel cell instead of each line item separately in the drop downlist.


     string.Join(",", ddl_DStatus2));

    • Edited by StSingh Monday, April 10, 2017 10:43 AM Adding image for clarity
    Monday, April 10, 2017 7:27 AM
  • I have added but still it doesn't show as a drop down list.

                cell.Validation.IgnoreBlank = true;
                cell.Validation.InCellDropdown = true;

    Tuesday, April 11, 2017 10:58 AM
  • Thanks @Celeste Li I figured out that its a problem with my excel version( copy).
    • Edited by StSingh Tuesday, April 11, 2017 3:55 PM
    Tuesday, April 11, 2017 3:54 PM