CODEWITHSUNDEEP
c#.netexceldrop-down-menunpoi
HJB77
HJB77

Reputation: 13

C# Excel Drop Down List

I am trying to create a drop list, haven't had any luck finding similar sample code or documentation to follow so I'm flying solo

When I run this sample code I created

static void Main(string[] args)
    {

        using (FileStream filename = new FileStream(@"INSERTYOUROWNPATH.xlsx", FileMode.Create))
        {

            var workbook = new XSSFWorkbook();

            XSSFSheet xsheet = (XSSFSheet)workbook.CreateSheet("Validation");

            CT_DataValidation valid = new CT_DataValidation();
            valid.showDropDown = true;
            valid.allowBlank = true;

            var constraint = new XSSFDataValidationConstraint(new string[] { "0064", "0061" }); //to be used in list
            var range = new CellRangeAddressList(11, 14, 13, 13);    //apply to row 12:14 starting at column N finishing at column N
            var addingconstraint = new XSSFDataValidation(constraint, range, valid);

            addingconstraint.EmptyCellAllowed = true;
            addingconstraint.SuppressDropDownArrow = false;
            xsheet.AddValidationData(addingconstraint);

            workbook.Write(filename);

        }
    }

I get the following errors when I open the spreadsheet:

"We found a problem with some content in "Validations.xlsx" do you want use to try to recover as much as we can? If you trust the source of this workbook, click Yes"

Then I get the following repair message:

"Repaired Part: /xl/worksheets/sheet1.xml part with XML error. Load error. Line 1 column 684"

Could I please get some insight in to where this is failing?

Upvotes: 1

Views: 1956

Answers (1)

kumar chandraketu
kumar chandraketu

Reputation: 2370

your issue seems to be very similar to this problem about data validation and data constraint.

XSSFDataValidation dataValidation = null;
XSSFDataValidationConstraint dvConstraint = null;
XSSFDataValidationHelper validationHelper = null;

int DVRowLimit = (Int16.MaxValue);
XSSFCellStyle numberCellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat numberDataFormat = (XSSFDataFormat)workbook.CreateDataFormat();
numberCellStyle.SetDataFormat(numberDataFormat.GetFormat("#,###,###"));


CellRangeAddressList cellRangeFieldsType1 = new CellRangeAddressList(1, DVRowLimit, headerCount, headerCount);
dvConstraint = (XSSFDataValidationConstraint)validationHelper.CreateintConstraint(OperatorType.BETWEEN, "0", Int64.MaxValue.ToString());
dataValidation = (XSSFDataValidation)validationHelper.CreateValidation(dvConstraint, cellRangeFieldsType1);
dataValidation.ShowErrorBox = true;
dataValidation.SuppressDropDownArrow = true;
dataValidation.ErrorStyle = 0;
dataValidation.CreateErrorBox("InvalidValue", "Number Should be a integer.");
dataValidation.ShowErrorBox = true;
dataValidation.CreatePromptBox("Number Data Validation", "Enter Number.");
dataValidation.ShowPromptBox = true;
sheet.AddValidationData(dataValidation);
sheet.SetDefaultColumnStyle(column, numberCellStyle);

Upvotes: 1

Related Questions