DocumentFormat.OpenXml.Spreadsheet while copying from existing excel apply validation if existing excel has hyperlinks download file not opened

Hari Krishna 1 Reputation point
2022-08-06T17:57:40.553+00:00

228795-capture.png

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 datavalidation please help how to achieve data validation with hyperlinks

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);  
Microsoft 365 and Office | Development | Other
Developer technologies | C#
Developer technologies | C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
0 comments No comments
{count} votes

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.