locked
Excel-Openxml Dropdownlist using datavalidation RRS feed

  • Question

  • User-1779190663 posted

    DataValidations dataValidations1 = new DataValidations();

    DataValidation dataValidation2 = new DataValidation() { Type = DataValidationValues.List, AllowBlank = true, ShowInputMessage = true, ShowErrorMessage = true, SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A4:B4" } };

    Formula1 formula12 = new Formula1();

    formula12.Text = "$A$1:$A$3";

    dataValidations1.Append(dataValidation1);

    worksheet1.Append(dataValidations1);


    In the above code will add dropdownlist in excel .I've doubt for dropdown how they are adding values . pls suggest .

    if my table having raja,ram,raju,bala,guna in list how should i add this in dropdown list ?
    ListValue<StringValue>

    below link query is my expectation

    https://social.msdn.microsoft.com/Forums/office/en-US/423de3ff-e9be-45b2-b522-99e7d2002c80/aspnet-open-office-xml-create-dropdown-list-in-excel-without-using-interop?forum=oxmlsdk

    Thursday, November 19, 2015 5:40 AM

Answers

  • User614698185 posted

    Hi RajahRajah,

    In the above code will add dropdownlist in excel .I've doubt for dropdown how they are adding values . pls suggest .

    You could use DataBound event:

    protected void DropDownList1_DataBound(object sender, EventArgs e)
    {
        DropDownList1.Items.Add(new ListItem("New Item", "-1"));
    }

    Or you could use Button click event to add the item into Dropdownlist, for example:

    using Microsoft.Office.Interop.Excel ;
      protected void Button1_Click(object sender, EventArgs e)
            {
                string Filename = "samp.xls";
                Application xlsApp = new Application();
                Workbook xlsWorkbook;
                Worksheet xlsWorksheet;
                object oMissing = System.Reflection.Missing.Value;
     
                //Create new workbook
                xlsWorkbook = xlsApp.Workbooks.Add(true);
     
                //Get the first worksheet
                xlsWorksheet = (Worksheet)(xlsWorkbook.Worksheets[1]);
     
                string[] ddl_item = { "Answers", "Autos", "Finance", "Games", "Groups", "HotJobs", "Maps", "Mobile Web", "Movies", "Music", "Personals", "Real Estate", "Shopping", "Sports", "Tech", "Travel", "TV", "Yellow Pages" };
     
                Range xlsRange;
                xlsRange = xlsWorksheet.get_Range("A1", "A1");
     
                DropDowns xlDropDowns;
                DropDown xlDropDown;
                xlDropDowns = ((DropDowns)(xlsWorksheet.DropDowns(oMissing)));
                xlDropDown = xlDropDowns.Add((double)xlsRange.Left, (double)xlsRange.Top, (double)xlsRange.Width, (double)xlsRange.Height, true);
     
                //Add item into drop down list
                for (int i = 0; i < ddl_item.Length; i++)
                {
                    xlDropDown.AddItem(ddl_item[i], i + 1);
                }
     
                xlsApp.DisplayAlerts = false;
                xlsWorkbook.Close(true, Filename, null);
                xlsApp.Quit();
     
                xlsWorksheet = null;
                xlsWorkbook = null;
                xlsApp = null;
     
            }

    Best Regards,

    Candice Zhou

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 20, 2015 12:24 AM

All replies

  • User614698185 posted

    Hi RajahRajah,

    In the above code will add dropdownlist in excel .I've doubt for dropdown how they are adding values . pls suggest .

    You could use DataBound event:

    protected void DropDownList1_DataBound(object sender, EventArgs e)
    {
        DropDownList1.Items.Add(new ListItem("New Item", "-1"));
    }

    Or you could use Button click event to add the item into Dropdownlist, for example:

    using Microsoft.Office.Interop.Excel ;
      protected void Button1_Click(object sender, EventArgs e)
            {
                string Filename = "samp.xls";
                Application xlsApp = new Application();
                Workbook xlsWorkbook;
                Worksheet xlsWorksheet;
                object oMissing = System.Reflection.Missing.Value;
     
                //Create new workbook
                xlsWorkbook = xlsApp.Workbooks.Add(true);
     
                //Get the first worksheet
                xlsWorksheet = (Worksheet)(xlsWorkbook.Worksheets[1]);
     
                string[] ddl_item = { "Answers", "Autos", "Finance", "Games", "Groups", "HotJobs", "Maps", "Mobile Web", "Movies", "Music", "Personals", "Real Estate", "Shopping", "Sports", "Tech", "Travel", "TV", "Yellow Pages" };
     
                Range xlsRange;
                xlsRange = xlsWorksheet.get_Range("A1", "A1");
     
                DropDowns xlDropDowns;
                DropDown xlDropDown;
                xlDropDowns = ((DropDowns)(xlsWorksheet.DropDowns(oMissing)));
                xlDropDown = xlDropDowns.Add((double)xlsRange.Left, (double)xlsRange.Top, (double)xlsRange.Width, (double)xlsRange.Height, true);
     
                //Add item into drop down list
                for (int i = 0; i < ddl_item.Length; i++)
                {
                    xlDropDown.AddItem(ddl_item[i], i + 1);
                }
     
                xlsApp.DisplayAlerts = false;
                xlsWorkbook.Close(true, Filename, null);
                xlsApp.Quit();
     
                xlsWorksheet = null;
                xlsWorkbook = null;
                xlsApp = null;
     
            }

    Best Regards,

    Candice Zhou

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 20, 2015 12:24 AM
  • User-1779190663 posted

    Hi Candice Zhou,

    Thanks for ur help . 

    any suggestion on this below my query.

    http://forums.asp.net/t/2078399.aspx?How+to+Hide+Delete+Excel+sheet+using+Openxml

    Tuesday, November 24, 2015 4:50 AM