DocumentFormat.OpenXml.Spreadsheet while copying from existing excel apply validation if existing excel has hyperlinks download file not opened
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);