Excel-Openxml Dropdownlist using datavalidation[Answered] RSS

• • Reply
  RajahRajah

  Member

  2 Points

  8 Posts

  Excel-Openxml Dropdownlist using datavalidation

  Nov 19, 2015 05:40 AM|RajahRajah|LINK

  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

  C

• • Reply
  Candice Zhou

  Star

  12330 Points

  2021 Posts

  Re: Excel-Openxml Dropdownlist using datavalidation

  Nov 20, 2015 12:24 AM|Candice Zhou|LINK

  Hi RajahRajah,

  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

  C

• • Reply
  RajahRajah

  Member

  2 Points

  8 Posts

  Re: Excel-Openxml Dropdownlist using datavalidation

  Nov 24, 2015 04:50 AM|RajahRajah|LINK

  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