'Add Dropdown Validation to an entire Excel Column using OpenXML
In Excel, I am able to add validation rules to a range of cells and limit the accepted input to a list of values displayed in a dropdown. This is done using the Data Validation tool, as pictured below:
I have some C# code that generates an excel sheet, and I would like to add this same kind of validation to one of the columns.
Using Microsoft.Office.Interop.Excel, I am able to add this kind of dropdown validation to an entire column:
string flatList = "FirstChoice,SecondChoice,ThirdChoice";
//select the entire first row as the range
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A1").EntireColumn;
//remove any previously existing validation
range.Validation.Delete();
//add new validation
range.Validation.Add(
Microsoft.Office.Interop.Excel.XlDVType.xlValidateList,
Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertInformation,
Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlBetween,
flatList,
Type.Missing);
range.Validation.IgnoreBlank = true;
range.Validation.InCellDropdown = true;
The problem is, I can't guarantee that my user has Microsoft Office installed. I therefore want to instead use DocumentFormat.OpenXML.
Is it possible to add this same type of dropdown validation using OpenXML?
I've seen some posts using DataValidation, yet have not been able to figure how to get that to work, and if that will solve my problem.
Solution 1:[1]
With a bit more digging, I was able to figure out how to use DataValidation to add dropdown validation to entire column in my Excel sheet using DocumentFormat.OpenXml:
string flatList = "FirstChoice,SecondChoice,ThirdChoice";
DataValidation dataValidation = new DataValidation
{
Type = DataValidationValues.List,
AllowBlank = true,
//Use A:A or A1:A1048576 to select the entire column A
//1048576 (2^20) is the max row number since Excel 2007.
SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A:A" },
//Set the formula to the list of dropdown values. Escape the double quotes.
Formula1 = new Formula1("\"" + flatList + "\"")
};
//Check if there are any other DataValidations already in the worksheet
DataValidations dvs = worksheet.GetFirstChild<DataValidations>();
if (dvs != null)
{
//If you already have existing validation for column A, you may need to Remove()
//or Replace() the current validation to get the new validation to show.
//Add the new DataValidation to the list of DataValidations
dvs.Count = dvs.Count + 1;
dvs.Append(dataValidation);
}
else
{
DataValidations newDVs = new DataValidations();
newDVs.Append(dataValidation);
newDVs.Count = 1;
//Append the validation to the DocumentFormat.OpenXml.SpreadSheet.Worksheet variable
worksheet.Append(newDVs);
}
Solution 2:[2]
You are quite right that you need to use the DataValidation class but you'll also need the DataValidations class too.
A WorkSheet can have zero or one DataValidations which in turn can contain one or many DataValidation's.
The following code will create the Data Validation you are looking for:
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
{
/*** GENERAL SETUP ***/
WorkbookPart workbookpart = myDoc.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
SheetData sheetData = new SheetData();
// Add a WorkbookPart to the document.
worksheetPart.Worksheet = new Worksheet(sheetData);
Sheets sheets = myDoc.WorkbookPart.Workbook.AppendChild(new Sheets());
sheets.AppendChild(new Sheet()
{
Id = myDoc.WorkbookPart.GetIdOfPart(myDoc.WorkbookPart.WorksheetParts.First()),
SheetId = 1,
Name = "Sheet1"
});
/*** DATA VALIDATION CODE ***/
DataValidations dataValidations = new DataValidations();
DataValidation dataValidation = new DataValidation()
{
Type = DataValidationValues.List,
AllowBlank = true,
SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A1:A1048576" }
};
Formula1 formula = new Formula1();
formula.Text = "\"FirstChoice,SecondChoice,ThirdChoice\"";
dataValidation.Append(formula);
dataValidations.Append(dataValidation);
worksheetPart.Worksheet.AppendChild(dataValidations);
}
Solution 3:[3]
I was able to get manual drop down and I'm making research to get dynamic excel. I was able to figure out how to use DataValidation to add dropdown validation to entire column in my Excel sheet using DocumentFormat.OpenXml.
public class DataInSheet
{
public string firstRow { get; set; }
internal static IEnumerable<object> GetDataOfSheet()
{
List<DataInSheet> dataForSheet = new List<DataInSheet>
{
new DataInSheet
{
firstRow = "Sanjay"
},
new DataInSheet
{
firstRow = "Sanjay"
},
new DataInSheet
{
firstRow = "Sanjay"
},
new DataInSheet
{
firstRow = "Sanjay"
},
new DataInSheet
{
firstRow = "Sanjay"
},
new DataInSheet
{
firstRow = "Sanjay"
},
};
return dataForSheet;
}
}
public static void CreateExcelWithDynamicHeader(string fileName, List<string> headerFields, string sheetName, string dropDownItems)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.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 = document.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
//sheets.Append(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 = document.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = sheetName };
Sheet sheet1 = new Sheet() { Id = document.WorkbookPart.GetIdOfPart(worksheetPart2), SheetId = 2, Name = dropDownItems };
sheets.Append(sheet);
sheets.Append(sheet1);
//document.WorkbookPart.Workbook.Save();
SheetData sheetData = new SheetData();
SheetData sheetData1 = new SheetData();
double width = 6;
// Constructing header
Row headerRow = new Row();
for (int i = 0; i < headerFields.Count; i++)
{
if (width < headerFields[i].Length)
{
width = headerFields[i].Length;
}
Columns columns = new Columns();
columns.Append(new Column() { Min = 1, Max = (UInt32)headerFields.Count, Width = width, CustomWidth = true });
worksheetPart.Worksheet.Append(columns);
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(headerFields[i]);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
worksheet1.Append(sheetData);
int Counter = 1;
foreach (var value in DataInSheet.GetDataOfSheet())
{
Row contentRow = CreateRowValues(Counter, value);
Counter++;
sheetData1.AppendChild(contentRow);
}
worksheet2.Append(sheetData1);
DataValidation dataValidation = new DataValidation
{
Type = DataValidationValues.List,
AllowBlank = true,
SequenceOfReferences = new ListValue<StringValue>() { InnerText = "B1" },
//Formula1 = new Formula1("'Import Face'!$A$1:$A$6")
Formula1 = new Formula1("'DropDownDataContainingSheet'!$A:$A")
};
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();
document.Close();
}
}
static string[] headerColumns = new string[] { "A", "B", "C", "D" };
private static Row CreateRowValues(int index, object value)
{
Row row = new Row();
row.RowIndex = (UInt32)index;
int i = 0;
foreach (var property in value.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(value, 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(value, 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(value, 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;
}
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 | petelids |
| Solution 3 | Tot Zam |