Creating ExcelSheet with pre populated dropdown list using C# and OpenXML.
4.56/5 (7 votes)
Creating ExcelSheet with dropdown containing pre populated dropdown data of its own excelsheet.
Introduction
Working with excel through code, we can come up with scenerio where our requirement is to create such excel sheet of data which containing a dropdown list too in that sheet. In Excel term we call it data validation. In this artical we will see how to create a excel sheet and that excel sheet containing dropdown list on any particular cell which we want according to our need with the help of OpenXML. The data of that dropdown list can be any hard coded data or any dynamic data. We will populate dropdown with dynamic data of another sheet of the same excel file.
Using the code
We will create a console application to demonstrate this example.
Steps
1. Using Visual Studio, create a console application(File ->New->Project->Console Application(From Visual C# Templates)) and name it as CreateDropDownInExcel.
2. Install OpenXML from nuget package manager. go to Tools-> Nuget Package Manager->Manage Nuget Packages for Solution.
3. Search for OpenXml in the search bar. Click install option of DocumentFormat.OpenXml.
Now add a class DataInSheet.cs . This class is for creating data for both the sheets of created Excel file .
using System;
using System.Collections.Generic;
namespace CreateDropDownInExcel
{
public class DataInSheet
{
public string firstRow { get; set; }
public string secondRow { get; set; }
public string thirdRow { get; set; }
public string fourthRow { get; set; }
public static List<DataInSheet> GetDataOfSheet1()
{
List<DataInSheet> dataForSheet = new List<DataInSheet>
{
new DataInSheet
{
firstRow = "CONDITION",
secondRow = "CONDITION",
thirdRow = "CONDITION",
fourthRow = "Assingment"
},
new DataInSheet
{
firstRow = "Num1.Char1",
secondRow = "List2",
thirdRow = "Size",
fourthRow = "BikeFrames"
},
new DataInSheet
{
firstRow = "MountainBikes",
secondRow = "Silver",
thirdRow = "38",
fourthRow = "FR-M94S-34"
},
new DataInSheet
{
firstRow = "MountainBikes",
secondRow = "Silver",
thirdRow = "36",
fourthRow = "FR-M94S-38"
},
new DataInSheet
{
firstRow = "MountainBikes",
secondRow = "Silver",
thirdRow = "40",
fourthRow = "FR-M94S-31"
},
new DataInSheet
{
firstRow = "MountainBikes",
secondRow = "Silver",
thirdRow = "38",
fourthRow = "FR-M94S-37"
},
new DataInSheet
{
firstRow = "MountainBikes",
secondRow = "Silver",
thirdRow = "38",
fourthRow = "FR-M94S-37"
},
new DataInSheet
{
firstRow = "MountainBikes",
secondRow = "Silver",
thirdRow = "57",
fourthRow = "FR-M94S-45"
},
};
return dataForSheet;
}
public static List<DataInSheet> GetDataOfSheet2()
{
List<DataInSheet> dataForSecondSheet = new List<DataInSheet>
{
new DataInSheet
{
firstRow = "Roshan",
secondRow = "Rahul",
thirdRow = "gautam",
fourthRow = "Sudripto"
},
new DataInSheet
{
firstRow = "Anand",
secondRow = "Gourav",
thirdRow = "Josep",
fourthRow = "Mathew"
},
new DataInSheet
{
firstRow = "Mohit",
secondRow = "Vimal",
thirdRow = "Sumitra",
fourthRow = "Hamid"
},
new DataInSheet
{
firstRow = "Uma",
secondRow = "Maity",
thirdRow = "Shubh",
fourthRow = "Raja"
},
new DataInSheet
{
firstRow = "Dinesh",
secondRow = "Narang",
thirdRow = "Jonathan",
fourthRow = "Andre"
},
new DataInSheet
{
firstRow = "Kawie",
secondRow = "Tom",
thirdRow = "Nies",
fourthRow = "FR-M94S-37"
},
new DataInSheet
{
firstRow = "MountainBikes",
secondRow = "Silver",
thirdRow = "38",
fourthRow = "FR-M94S-37"
},
new DataInSheet
{
firstRow = "MountainBikes",
secondRow = "Silver",
thirdRow = "57",
fourthRow = "FR-M94S-45"
},
};
return dataForSecondSheet;
}
}
}
You can create data according to your choice.
Create another class with name ExcelOperations.cs . This class will contain all the buisness logic to create Excel and dropdown in excel sheet. We will discuss this code briefly.
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace CreateDropDownInExcel
{
class ExcelOprations
{
public static void CreatingExcelAndDrowownInExcel()
{
var filepath = @"D:\Projects\Testing.xlsx";
SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookpart = myWorkbook.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
WorksheetPart worksheetPart2 = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart2.Worksheet = new Worksheet(new SheetData());
Sheets sheets = myWorkbook.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
Worksheet worksheet1 = new Worksheet()
{ MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
worksheet1.AddNamespaceDeclaration
("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet1.AddNamespaceDeclaration
("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet1.AddNamespaceDeclaration
("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
Worksheet worksheet2 = new Worksheet()
{ MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
worksheet2.AddNamespaceDeclaration
("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet2.AddNamespaceDeclaration
("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet2.AddNamespaceDeclaration
("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
Sheet sheet = new Sheet()
{
Id = myWorkbook.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "DropDownContainingSheet"
};
Sheet sheet1 = new Sheet()
{
Id = myWorkbook.WorkbookPart.GetIdOfPart(worksheetPart2),
SheetId = 2,
Name = "DropDownDataContainingSheet"
};
sheets.Append(sheet);
sheets.Append(sheet1);
SheetData sheetData = new SheetData();
SheetData sheetData1 = new SheetData();
int Counter1 = 1;
foreach (var value in DataInSheet.GetDataOfSheet1())
{
Row contentRow = CreateRowValues(Counter1, value);
Counter1++;
sheetData.AppendChild(contentRow);
}
worksheet1.Append(sheetData);
int Counter2 = 1;
foreach (var value in DataInSheet.GetDataOfSheet2())
{
Row contentRow = CreateRowValues(Counter2, value);
Counter2++;
sheetData1.AppendChild(contentRow);
}
worksheet2.Append(sheetData1);
DataValidation dataValidation = new DataValidation
{
Type = DataValidationValues.List,
AllowBlank = true,
SequenceOfReferences = new ListValue<StringValue>() { InnerText = "B1" },
Formula1 = new Formula1("'DropDownDataContainingSheet'!$A$1:$A$3")
};
DataValidations dataValidations = worksheet1.GetFirstChild<DataValidations>();
if (dataValidations != null)
{
dataValidations.Count = dataValidations.Count + 1;
dataValidations.Append(dataValidation);
}
else
{
DataValidations newdataValidations = new DataValidations();
newdataValidations.Append(dataValidation);
newdataValidations.Count = 1;
worksheet1.Append(newdataValidations);
}
worksheetPart.Worksheet = worksheet1; ;
worksheetPart2.Worksheet = worksheet2;
workbookpart.Workbook.Save();
myWorkbook.Close();
}
static string[] headerColumns = new string[] { "A", "B", "C", "D" };
private static Row CreateRowValues(int index, DataInSheet objToInsert)
{
Row row = new Row();
row.RowIndex = (UInt32)index;
int i = 0;
foreach (var property in objToInsert.GetType().GetProperties())
{
Cell cell = new Cell();
cell.CellReference = headerColumns[i].ToString() + index;
if (property.PropertyType.ToString()
.Equals("System.string", StringComparison.InvariantCultureIgnoreCase))
{
var result = property.GetValue(objToInsert, null);
if (result == null)
{
result = "";
}
cell.DataType = CellValues.String;
InlineString inlineString = new InlineString();
Text text = new Text();
text.Text = result.ToString();
inlineString.AppendChild(text);
cell.AppendChild(inlineString);
}
if (property.PropertyType.ToString()
.Equals("System.int32", StringComparison.InvariantCultureIgnoreCase))
{
var result = property.GetValue(objToInsert, null);
if (result == null)
{
result = 0;
}
CellValue cellValue = new CellValue();
cellValue.Text = result.ToString();
cell.AppendChild(cellValue);
}
if (property.PropertyType.ToString()
.Equals("System.boolean", StringComparison.InvariantCultureIgnoreCase))
{
var result = property.GetValue(objToInsert, null);
if (result == null)
{
result = "False";
}
cell.DataType = CellValues.InlineString;
InlineString inlineString = new InlineString();
Text text = new Text();
text.Text = result.ToString();
inlineString.AppendChild(text);
cell.AppendChild(inlineString);
}
row.AppendChild(cell);
i = i + 1;
}
return row;
}
}
}
Above is the code to create excel file with two sheets in it.
var filepath = @"D:\Projects\Testing.xlsx";
This is the path where your excel file will be created in your machine. You can change it accordingly.
SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
Note: SpreadsheetDocumnet class needs windowsBase dll reference to be added. Else it will give build error.
SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookpart = myWorkbook.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
WorksheetPart worksheetPart2 = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart2.Worksheet = new Worksheet(new SheetData());
Sheets sheets = myWorkbook.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
Worksheet worksheet1 = new Worksheet()
{ MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
worksheet1.AddNamespaceDeclaration
("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet1.AddNamespaceDeclaration
("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet1.AddNamespaceDeclaration
("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
Worksheet worksheet2 = new Worksheet()
{ MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
worksheet2.AddNamespaceDeclaration
("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet2.AddNamespaceDeclaration
("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet2.AddNamespaceDeclaration
("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
Above written code is to create firstly Excel sheet, adding workbookpart to it and adding worksheet to the workbook. here we will create two sheets so we will have two worksheet object added to the worksheetpart.
Create Sheets object and append both the sheets sheet and sheet1 to it.
Sheet sheet = new Sheet()
{
Id = myWorkbook.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "DropDownContainingSheet"
};
Sheet sheet1 = new Sheet()
{
Id = myWorkbook.WorkbookPart.GetIdOfPart(worksheetPart2),
SheetId = 2,
Name = "DropDownDataContainingSheet"
};
sheets.Append(sheet);
sheets.Append(sheet1);
Here name property of the sheet class is to specify the name you want to assign the sheets that will be created.
Till now both the sheets have been created. its time to add the data into the sheets. Now we will call the method GetDataOfSheet1 and GetDataOfSheet2 we have cretaed earlier in the DataInSheet class to populate both the sheets with data.
int Counter1 = 1;
foreach (var value in DataInSheet.GetDataOfSheet1())
{
Row contentRow = CreateRowValues(Counter1, value);
Counter1++;
sheetData.AppendChild(contentRow);
}
worksheet1.Append(sheetData);
int Counter2 = 1;
foreach (var value in DataInSheet.GetDataOfSheet2())
{
Row contentRow = CreateRowValues(Counter2, value);
Counter2++;
sheetData1.AppendChild(contentRow);
}
worksheet2.Append(sheetData1);
By calling getDataOfSheet1 and getDataOfSheet2 methods we will get only data which will be populated in to the sheets, but binding data to the sheet will be done by the method CreateRowValues which has not come in to picture yet. This method is as below:
static string[] headerColumns = new string[] { "A", "B", "C", "D" };
private static Row CreateRowValues(int index, DataInSheet objToInsert)
{
Row row = new Row();
row.RowIndex = (UInt32)index;
int i = 0;
foreach (var property in objToInsert.GetType().GetProperties())
{
Cell cell = new Cell();
cell.CellReference = headerColumns[i].ToString() + index;
if (property.PropertyType.ToString()
.Equals("System.string", StringComparison.InvariantCultureIgnoreCase))
{
var result = property.GetValue(objToInsert, null);
if (result == null)
{
result = "";
}
cell.DataType = CellValues.String;
InlineString inlineString = new InlineString();
Text text = new Text();
text.Text = result.ToString();
inlineString.AppendChild(text);
cell.AppendChild(inlineString);
}
if (property.PropertyType.ToString()
.Equals("System.int32", StringComparison.InvariantCultureIgnoreCase))
{
var result = property.GetValue(objToInsert, null);
if (result == null)
{
result = 0;
}
CellValue cellValue = new CellValue();
cellValue.Text = result.ToString();
cell.AppendChild(cellValue);
}
if (property.PropertyType.ToString()
.Equals("System.boolean", StringComparison.InvariantCultureIgnoreCase))
{
var result = property.GetValue(objToInsert, null);
if (result == null)
{
result = "False";
}
cell.DataType = CellValues.InlineString;
InlineString inlineString = new InlineString();
Text text = new Text();
text.Text = result.ToString();
inlineString.AppendChild(text);
cell.AppendChild(inlineString);
}
row.AppendChild(cell);
i = i + 1;
}
return row;
}
This method is doing nothing but just adding data to the cell of the sheet. On first look, this method may look so complex but its nothing, just a validation for all kind of data, wheather data may be of string or int or boolean type. We can add more validation to it acording to our need to handel more datatypes . That only making this mathod look so complex one.
After adding data to the cell, append the data in to the Row and return the Row. That Row is appended to the sheetdata and that sheetdata is being appended to the worksheet object.
Now here we will see the code which is responsible for creating dropdown in the sheet.
DataValidation dataValidation = new DataValidation
{
Type = DataValidationValues.List,
AllowBlank = true,
SequenceOfReferences = new ListValue<StringValue>() { InnerText = "B1" },
Formula1 = new Formula1("'DropDownDataContainingSheet'!$A$1:$A$3")
};
DataValidations dataValidations = worksheet1.GetFirstChild<DataValidations>();
if (dataValidations != null)
{
dataValidations.Count = dataValidations.Count + 1;
dataValidations.Append(dataValidation);
}
else
{
DataValidations newdataValidations = new DataValidations();
newdataValidations.Append(dataValidation);
newdataValidations.Count = 1;
worksheet1.Append(newdataValidations);
}
worksheetPart.Worksheet = worksheet1; ;
worksheetPart2.Worksheet = worksheet2;
workbookpart.Workbook.Save();
myWorkbook.Close();
Here type property Datavalidation class is of list type which telling it to create dropdown list. It could be of date type, decimal type etc. sequenceOfRefrence property is telling on which cell dropdown will come. Here it will come on B1 cell. Formula type Property is telling from where to where data from another sheet need to be populated in the dropdown. Here it will populate from cell A1 to A3 .
In the end both the worksheet will be added to their corrosponding worksheetpart. After saving myWorkbook will be made close.
Call CreatingExcelAndDrowownInExcel method of ExcelOprations.cs class in the main method of Program class whic is our entry point of application.
class Program
{
static void Main(string[] args)
{
ExcelOprations.CreatingExcelAndDrowownInExcel();
}
}
So finally our excel sheet is ready as shown below.
This is the data of colum A1 to A3 which will be populated from sheet1 to the dropdown of Sheet2 named as DropDownDataContainingSheet.
here is the Dropdown on colum B1.
Points of Interest
Make sure to add windowsBase dll in the reference section.