public void TranslateX14DataValidationFormulas() { var file = new FileInfo("X14DataValidations.xlsx"); Assert.IsTrue(file.Exists); var tempFile = new FileInfo(Path.GetTempFileName()); try { using (var excelPackage = new ExcelPackage(file)) { var sourceWorksheet = excelPackage.Workbook.Worksheets["Source data"]; sourceWorksheet.InsertColumn(5, 3); sourceWorksheet.InsertRow(7, 1); var worksheet = excelPackage.Workbook.Worksheets["Data Validation"]; ExcelX14DataValidation firstListValidation = (ExcelX14DataValidation)worksheet.X14DataValidations.First(d => d.ValidationType.Type == eDataValidationType.List); ExcelX14DataValidation firstWholeValidation = (ExcelX14DataValidation)worksheet.X14DataValidations.First(d => d.ValidationType.Type == eDataValidationType.Whole); Assert.AreEqual("D7,J7", firstListValidation.Address.Address); Assert.AreEqual("F7", firstWholeValidation.Address.Address); Assert.AreEqual("'Source data'!$H$9:$H$10", firstListValidation.Formula); Assert.AreEqual("'Source data'!$H$9", firstWholeValidation.Formula); Assert.AreEqual("'Source data'!$H$10", firstWholeValidation.Formula2); excelPackage.SaveAs(tempFile); } using (var excelPackage = new ExcelPackage(tempFile)) { var worksheet = excelPackage.Workbook.Worksheets["Data Validation"]; ExcelX14DataValidation firstListValidation = (ExcelX14DataValidation)worksheet.X14DataValidations.First(d => d.ValidationType.Type == eDataValidationType.List); ExcelX14DataValidation firstWholeValidation = (ExcelX14DataValidation)worksheet.X14DataValidations.First(d => d.ValidationType.Type == eDataValidationType.Whole); Assert.AreEqual("D7,J7", firstListValidation.Address.Address); Assert.AreEqual("F7", firstWholeValidation.Address.Address); Assert.AreEqual("'Source data'!$H$9:$H$10", firstListValidation.Formula); Assert.AreEqual("'Source data'!$H$9", firstWholeValidation.Formula); Assert.AreEqual("'Source data'!$H$10", firstWholeValidation.Formula2); var fNode = firstListValidation.TopNode.SelectSingleNode(".//x14:formula1", worksheet.NameSpaceManager).SelectSingleNode(".//xm:f", worksheet.NameSpaceManager); Assert.AreEqual("'Source data'!$H$9:$H$10", fNode.InnerText); } } finally { tempFile.Delete(); } }
public void ReadX14DataValidations() { var file = new FileInfo("X14DataValidations.xlsx"); Assert.IsTrue(file.Exists); using (var excelPackage = new ExcelPackage(file)) { var worksheet = excelPackage.Workbook.Worksheets["Data Validation"]; Assert.AreEqual(2, worksheet.X14DataValidations.Count); Assert.AreEqual("2", worksheet.X14DataValidations.TopNode.Attributes["count"].Value); ExcelX14DataValidation firstListValidation = (ExcelX14DataValidation)worksheet.X14DataValidations.First(d => d.ValidationType.Type == eDataValidationType.List); ExcelX14DataValidation firstWholeValidation = (ExcelX14DataValidation)worksheet.X14DataValidations.First(d => d.ValidationType.Type == eDataValidationType.Whole); Assert.IsNotNull(firstListValidation); Assert.IsNotNull(firstWholeValidation); Assert.AreEqual("D7,J7", firstListValidation.Address.Address); Assert.AreEqual("F7", firstWholeValidation.Address.Address); Assert.AreEqual("'Source data'!$E$8:$E$9", firstListValidation.Formula); Assert.AreEqual("'Source data'!$E$8", firstWholeValidation.Formula); Assert.AreEqual("'Source data'!$E$9", firstWholeValidation.Formula2); } }