Click here to Skip to main content
15,938,218 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
0


in existing xlsm file externalurl has hyperlinks and project columns need to apply drop down so i have used datavlidation but while downloading file corrupting if i remove hyperlinks its working fine with datavalidtion please help how to achieve data validation with hyperlinks
<a href="https://i.stack.imgur.com/Yn7Wu.png">excel image</a>
;


What I have tried:

string MyFile = @"C:\Users\Desktop\temp\sample.xlsm";
            using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(MyFile, true))
            {
                WorkbookPart wbPart = myDoc.WorkbookPart;
                Hyperlinks hyperlinks1 = new Hyperlinks();
                //var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
                WorksheetPart worksheetPart = GetWorksheetPartByName(myDoc, "Test");

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

                //insert the dataValidations to the correct place
                worksheetPart.Worksheet.InsertBefore(dataValidations, worksheetPart.Worksheet.Descendants<PageMargins>().FirstOrDefault());
            }
            Console.WriteLine("File Saved to " + MyFile)
Posted

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900