If I have data with 2 columns like these 5 records: ('A','V1'),('A','V2'),('B','V1'),('B','V2'),('B','V3'). How do I make it into a dropdownlist inside a cell of Excel? I've tried A,V1 A,V2 B,V1 B,V2 B,V3 but it crashed when I open the generated Excel File. Because I need to generate excel files totally using C# codes, I am hard to make macros to make 2 columns CA & CB, and when user choose 'A' in column CA then only 'V1' & 'V2' are available for column CB in a row. Sorry for poor English because I'm Taiwanese.
My Dropdownlist items come from:
string[] Items = db.TABLEAs.Select(x => x.TA + "," + x.TB).Distinct().ToArray();
and put it into excel by this subroutine:
private static void SetExcelDropDownList(ref ISheet TargetSheet, int ColumnOrder, string[] DropDownItems)
{
var CellRange = new CellRangeAddressList(1, 2000, ColumnOrder, ColumnOrder);
XSSFDataValidationHelper validationHelper = (XSSFDataValidationHelper)TargetSheet.GetDataValidationHelper();
XSSFDataValidationConstraint constraint = (XSSFDataValidationConstraint)validationHelper.CreateExplicitListConstraint(DropDownItems);
XSSFDataValidation dataValidation = (XSSFDataValidation)validationHelper.CreateValidation(constraint, CellRange);
TargetSheet.AddValidationData(dataValidation);
}
Then when I open the generated excel, Microsoft Office asks me if I want to let them try to recover it or not, because they found somewhere has problems with my excel. When I choose YES to the dialog, the excel is opened but all dropdownlists are gone.
I need someone guided me to solve this problem.