Blog

WinGrid Dropdown Column to Excel Dropdown List

Tom Puglisi / Tuesday, November 22, 2011

Ever wanted to export your WinGrid to a Microsoft™ Excel™ file complete with your dropdown columns intact so that your end users could select items from the same list in Excel? Well with the new Infragistics Excel Library ListDataValidationRule feature, part of the Excel Data Validation feature set, YOU CAN!

How this is done:

The sample application contains a WinGrid control bound to the Products table in the Northwind database. The WinGrid’s CategoryID column is wired up to the WinDropdown control, which essentially provides a dropdown list of values for the CategoryID column. The WinDropdown control is bound to the Category table in the Northwind database.

End users can change the value of the CategoryID column by picking from the list of values available from the WinDropdown control.

We use the WinGridExcelExporter component to easily export WinGrid into an Excel file.

The goal of this blog post is to provide our end users with the same experience within the Excel file that we export with WinGridExcelExporter – a dropdown list of values on the CategoryID column so that end users can pick from the same list of values available in WinGrid.

The following class wraps up the logic used to create a ListValidationRule object and apply it to each WorkBookCell object.

view plaincopy to clipboardprint
  1. using System;   
  2. using System.Collections.Generic;   
  3. using System.Linq;   
  4. using System.Text;   
  5. using Infragistics.Documents.Excel;   
  6. using Infragistics.Win.UltraWinGrid.ExcelExport;   
  7.   
  8. namespace NewExcelFeatures   
  9. {   
  10.     public class ExcelListConverter   
  11.     {   
  12.         private UltraGridExcelExporter _theExcelExporter;   
  13.         private string _theItemsListString;   
  14.         private string _theColumnName;   
  15.         private Dictionary<int, string> CellReferenceDictionary = new Dictionary<int, string>();   
  16.   
  17.         public ExcelListConverter(UltraGridExcelExporter theExcelExporter, string theItemsListString, string theColumnName)   
  18.         {   
  19.             _theExcelExporter = theExcelExporter;   
  20.             _theItemsListString = theItemsListString;   
  21.             _theColumnName = theColumnName;   
  22.   
  23.             _theExcelExporter.CellExported += new CellExportedEventHandler(_theExcelExporter_CellExported);   
  24.   
  25.             //Used to convert a column index into an Excel Cell Column Address Reference Character   
  26.             CellReferenceDictionary.Add(0, "A");   
  27.             CellReferenceDictionary.Add(1, "B");   
  28.             CellReferenceDictionary.Add(2, "C");   
  29.             CellReferenceDictionary.Add(3, "D");   
  30.             CellReferenceDictionary.Add(4, "E");   
  31.             CellReferenceDictionary.Add(5, "F");   
  32.             CellReferenceDictionary.Add(6, "G");   
  33.             CellReferenceDictionary.Add(7, "H");   
  34.             CellReferenceDictionary.Add(8, "I");   
  35.         }   
  36.   
  37.         /// <summary>   
  38.         /// While each cell is exported, we add a list validation rule to the appropriate Excel Column   
  39.         /// </summary>   
  40.         /// <param name="sender"></param>   
  41.         /// <param name="e"></param>   
  42.         private void _theExcelExporter_CellExported(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.CellExportedEventArgs e)   
  43.         {   
  44.             if (e.GridColumn.Key == _theColumnName)   
  45.             {   
  46.                 WorksheetCell c = e.CurrentWorksheet.Rows[e.CurrentRowIndex].Cells[e.CurrentColumnIndex] as WorksheetCell;   
  47.   
  48.                 string currentRowIndex = (e.CurrentRowIndex + 1).ToString();   
  49.   
  50.                 this.SetListValidationRule(e.CurrentWorksheet, _theItemsListString, CellReferenceDictionary[e.CurrentColumnIndex] + currentRowIndex);   
  51.             }   
  52.         }   
  53.   
  54.         private void SetListValidationRule(Worksheet theWorksheet, string theListItems, string theCellAddress)   
  55.         {   
  56.             // Create a variable for the base data validation rule   
  57.             DataValidationRule dataRule = null;   
  58.             // Create a variable for the worksheet reference collection   
  59.             WorksheetReferenceCollection cellCollection = null;   
  60.   
  61.             // Create a new list data validation rule   
  62.             ListDataValidationRule ld = new ListDataValidationRule();   
  63.             // Copy the validation rule reference to the base data rule variable   
  64.             dataRule = ld;   
  65.             // Allow the worksheet cell to contain null values   
  66.             ld.AllowNull = true;   
  67.             // Allow the showing of the drop down for valid cell values   
  68.             ld.ShowDropdown = true;   
  69.             // Set the cell's valid values   
  70.             string theListOfItemsExcelString = string.Format("=\"{0}\"", theListItems);   
  71.             ld.SetValuesFormula(theListOfItemsExcelString, theCellAddress);   
  72.             // Reference the worksheet cell in the collection   
  73.             cellCollection = new WorksheetReferenceCollection(theWorksheet, theCellAddress);   
  74.   
  75.             // Add the error message information   
  76.             dataRule.ErrorMessageDescription = "Invalid value entered.";   
  77.             dataRule.ErrorMessageTitle = "Validation Error";   
  78.             dataRule.ErrorStyle = DataValidationErrorStyle.Stop;   
  79.   
  80.             // Add the input message information   
  81.             dataRule.InputMessageDescription = "Type or select a value from the list.";   
  82.             dataRule.InputMessageTitle = "Value Selection";   
  83.   
  84.             // Set whether or not to display the error message after an invalid value was entered   
  85.             dataRule.ShowErrorMessageForInvalidValue = true;   
  86.             // Set whether or not to display the input message   
  87.             dataRule.ShowInputMessage = true;   
  88.   
  89.             // Add the data validation to the worksheet   
  90.             theWorksheet.DataValidationRules.Add(dataRule, cellCollection);   
  91.         }   
  92.   
  93.     }   
  94.   
  95. }  

 

A simple example of using this class is shown here, in the button_click event handler found on the application’s main form:

view plaincopy to clipboardprint
  1. private void btnExport_Click(object sender, EventArgs e)   
  2. {   
  3.   
  4.     string validationList = string.Empty;   
  5.   
  6.     //create a comma delimited list of values (no crazy chars allowed!)   
  7.     foreach (DataRow d in this.nwindDataSet.Categories.Rows)   
  8.     {   
  9.         validationList += d["CategoryName"].ToString() + ",";   
  10.     }   
  11.   
  12.     validationList = validationList.TrimEnd(new char[] { ',' });   
  13.   
  14.     ExcelListConverter c = new ExcelListConverter(this.ultraGridExcelExporter1, validationList, "CategoryID");   
  15.   
  16.     string theFile = Application.StartupPath + @"\theFile.xls";   
  17.     this.ultraGridExcelExporter1.Export(this.ultraGrid1, theFile);   
  18.     System.Diagnostics.Process.Start(theFile);   
  19.   
  20.     c = null;   
  21. }  

Now your end users can select from the same list of items in your newly generated Excel file!

You can download the sample application attached to this blog post.

NewExcelFeatures.zip