Need help adding OpenXML dataValidation to existing Excel file

Question

I need to modify an Excel file with a dropdown list using OpenXML dataValidation.

For some reason, I can create the file with the dropdown but I can't add 1 using Open() method.

The result is an empty excel file without dropdown.

This is the method I used to create the file:

public static void addCreateValidator(string path)
{
    using (SpreadsheetDocument myDoc = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
    {

        WorkbookPart workbookpart = myDoc.AddWorkbookPart();
        workbookpart.Workbook = new Workbook();

        WorksheetPart worksheetPart = workbookpart.AddNewPart();
        SheetData sheetData = new SheetData();

        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"
        });

        DataValidations dataValidations = new DataValidations();
        DataValidation dataValidation = new DataValidation()
        {
            Type = DataValidationValues.List,
            AllowBlank = true,
            SequenceOfReferences = new ListValue() { InnerText = "A1:A1048576" }
        };
        Formula1 formula = new Formula1();
        formula.Text = "\"FirstChoice,SecondChoice,ThirdChoice\"";

        dataValidation.Append(formula);
        dataValidations.Append(dataValidation);

        worksheetPart.Worksheet.AppendChild(dataValidations);
    }
}

This is the method used to modify the file:

public static void addValidation(string filepath)
    {
            using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filepath, true))
            {

            WorkbookPart wbPart = myDoc.WorkbookPart;

            var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
            WorksheetPart worksheetPart =
                      GetWorksheetPartByName(myDoc, "Sheet1");

            DataValidations dataValidations = new DataValidations();
            DataValidation dataValidation = new DataValidation()
            {
                Type = DataValidationValues.List,
                AllowBlank = true,
                SequenceOfReferences = new ListValue() { InnerText = "h1" }
            };
            Formula1 formula = new Formula1();
            formula.Text = "\"FirstChoice,SecondChoice,ThirdChoice\"";

            dataValidation.Append(formula);
            dataValidations.Append(dataValidation);

            worksheetPart.Worksheet.AppendChild(dataValidations);
            //worksheetPart.Worksheet.Save();
        }


    }

Can you see any error or suggest a workaround?

Need help adding OpenXML dataValidation to existing Excel file

Answers (1)

Related Questions