none
Add Data validation in to a Excel file programatically. Open XML RRS feed

  • Question

  • Hello,

    I am trying to add a "data validation" to a cell in a concrete excel file,from the c# code.

    • I have the source file,and the target file.I mean,i have one excel without validations,and the same file edited by hand as i would like it is after run my program.
    • I have opened them with the productivity tool for doing a comparison and i know which is the xml to add and where to add it:
      <x:dataValidations count="2" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
       <x:dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="A4 B4">
        <x:formula1>$A$1:$A$3</x:formula1>
       </x:dataValidation>
       <x:dataValidation type="whole" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="C4">
        <x:formula1>0</x:formula1>
        <x:formula2>2</x:formula2>
       </x:dataValidation>
      </x:dataValidations>
      
      
    the place is in Excelconvalidacion.xlsx/xl/worbook.xml/xl/worksheets/sheet1.xml/HERE
    • My only desire is to be able to add the data validation programatically using OpenXML

    The problem is  that i dont manage to do that.I dont know how to add the concrete XML i have ,to this part of the sheet1.

    The solution can be only by code because after i will do few methods for adding different datavalidations.I have checked in other forums or sources and nobody gives a clear solution.

    Anyone has an idea?will be very helpful if it is accompained with code.Thanks a lot.

     

    Xabier
    • Edited by XABI_SHAREPOINT Tuesday, March 8, 2011 5:32 PM better explanation
    Tuesday, March 8, 2011 5:29 PM

Answers

  • finally i found the solution with this code,which add 3 methods for adding 3 distinct kinds of data validation

    sing System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.IO.Packaging;
    using System.Xml;
    using System.Windows.Forms;
    using System.IO;
    
    namespace DataValidation
    {
      static class DataValidations
      {
    
        public static bool addDataValidationWhole(string fileName, string sheetName, string addressName, int minNUM, int maxNUM)
        {
          // Given a file, a sheet, and a cell, insert a specified value.
          // For example: InsertNumberIntoCell("C:\Test.xlsx", "Sheet3", "C3", 14)
    
          const string documentRelationshipType = "http://schemas.openxmlformats.org/officeDocument/2006/" + "relationships/officeDocument";
          const string worksheetSchema = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
    
          // Retrieve the stream containing the requested
          // worksheet's info:
          PackagePart documentPart = null;
          Uri documentUri = null;
          bool returnValue = false;
    
          using (Package xlPackage =
           Package.Open(fileName, FileMode.Open, FileAccess.ReadWrite))
          {
            // Get the main document part (workbook.xml).
            foreach (System.IO.Packaging.PackageRelationship relationship
             in xlPackage.GetRelationshipsByType(documentRelationshipType))
            {
              // There should only be one document part in the package. 
              documentUri = PackUriHelper.ResolvePartUri(new Uri("/",
               UriKind.Relative), relationship.TargetUri);
              documentPart = xlPackage.GetPart(documentUri);
    
              // There should only be one instance, 
              // but get out no matter what.
              break;
            }
    
            if (documentPart != null)
            {
              // Load the contents of the workbook.
              XmlDocument doc = new XmlDocument();
              doc.Load(documentPart.GetStream());
    
              // Create a NamespaceManager to handle the default namespace, 
              // and create a prefix for the default namespace:
              XmlNamespaceManager nsManager =
               new XmlNamespaceManager(doc.NameTable);
              nsManager.AddNamespace("d", doc.DocumentElement.NamespaceURI);
    
              string searchString = string.Format("//d:sheet[@name='{0}']", sheetName);
              XmlNode sheetNode = doc.SelectSingleNode(searchString, nsManager);
              if (sheetNode != null)
              {
                // Get the relId attribute:
                XmlAttribute relationAttribute = sheetNode.Attributes["r:id"];
                if (relationAttribute != null)
                {
                  string relId = relationAttribute.Value;
    
                  // First, get the relation between the document and the sheet.
                  PackageRelationship sheetRelation = documentPart.GetRelationship(relId);
                  Uri sheetUri = PackUriHelper.ResolvePartUri(documentUri, sheetRelation.TargetUri);
                  PackagePart sheetPart = xlPackage.GetPart(sheetUri);
    
                  // Load the contents of the sheet into an XML document.
                  XmlDocument xDoc = new XmlDocument();
                  xDoc.Load(sheetPart.GetStream());
    
                  // Use regular expressions to get the row number.
                  // If the parameter wasn't well formed, this code
                  // will fail:
    
    
                  System.Text.RegularExpressions.Regex r = new System.Text.RegularExpressions.Regex(@"^(?<col>\D+)(?<row>\d+)");
                  string rowNumber = r.Match(addressName).Result("${row}");
    
                  XmlNodeList sheetDataValidationsNode = xDoc.GetElementsByTagName("dataValidations");
                  XmlNode sheetDataNode = xDoc.SelectSingleNode("//d:sheetData", nsManager);
    
                  //ahora creo las data validation
                  XmlElement dataValidation1 = xDoc.CreateElement("dataValidation", worksheetSchema);
                  dataValidation1.SetAttribute("type", "whole");
                  dataValidation1.SetAttribute("allowBlank", "1");
                  dataValidation1.SetAttribute("showInputMessage", "1");
                  dataValidation1.SetAttribute("showErrorMessage", "1");
                  //inserto la validacion en la direccion destino.
                  dataValidation1.SetAttribute("sqref", addressName);
    
    
                  XmlElement formula1 = xDoc.CreateElement("formula1", worksheetSchema);
                  //añado el minimo numero entero en la formula 1
                  formula1.InnerText = minNUM.ToString();
    
                  dataValidation1.AppendChild(formula1);
                  XmlElement formula2 = xDoc.CreateElement("formula2", worksheetSchema);
                  //añado el maximo numero entero en la formula 2
                  formula2.InnerText = maxNUM.ToString();
                  dataValidation1.AppendChild(formula2);
    
    
                  //si la worksheet no posee ninguna dataValidation creado previamente 
                  if (sheetDataValidationsNode.Count == 0)
                  {
                    //Creo el nodo general dataValidations puesto que no existe
                    XmlElement dataValidations = xDoc.CreateElement("dataValidations", worksheetSchema);
    
                    //Lo inicializo a 1 porque es la primera que hay
                    dataValidations.SetAttribute("count", "1");
    
                    //añado las datavalidation al datavalidations.
                    dataValidations.AppendChild(dataValidation1);
    
                    //lo inserto despues de sheetData porque siempre van ahi
                    xDoc.DocumentElement.InsertAfter(dataValidations, sheetDataNode);
    
                   
    
                    returnValue = true;
    
                  }
    
                  else//existen dataValidations previas y la variable que contiene dicho nodo es sheetDataValidationsNode
                  //y como solo hay uno deberia estar en la posicion [0]
                  {
                    comprobarValidaciones(sheetDataValidationsNode[0], addressName);
                    sheetDataValidationsNode[0].AppendChild(dataValidation1);
    
    
                    //ahora sumo 1 al count de numero de data validations
                    foreach (XmlAttribute s in sheetDataValidationsNode[0].Attributes)
                    {
    
                      if (s.Name == "count")
                      {
                        sheetDataValidationsNode[0].Attributes.Item(0).Value = Convert.ToString(Int32.Parse(sheetDataValidationsNode[0].Attributes["count"].InnerText) + 1);
    
                      }
                    }
                    returnValue = true;
    
                  }
    
                  //salvo el xml del worksheet 
                  xDoc.Save(sheetPart.GetStream(FileMode.Create, FileAccess.Write));
    
    
                }
              }
            }
          }
          return returnValue;
    
        }
    
        public static bool addDataValidationList(string fileName, string sheetName, string addressName, string inicioOrigenDatos,string finOrigenDatos)
        {
          // Given a file, a sheet, and a cell, insert a specified value.
          // For example: InsertNumberIntoCell("C:\Test.xlsx", "Sheet3", "C3", 14)
    
          const string documentRelationshipType = "http://schemas.openxmlformats.org/officeDocument/2006/" + "relationships/officeDocument";
          const string worksheetSchema = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
    
          // Retrieve the stream containing the requested
          // worksheet's info:
          PackagePart documentPart = null;
          Uri documentUri = null;
          bool returnValue = false;
    
          using (Package xlPackage =
           Package.Open(fileName, FileMode.Open, FileAccess.ReadWrite))
          {
            // Get the main document part (workbook.xml).
            foreach (System.IO.Packaging.PackageRelationship relationship
             in xlPackage.GetRelationshipsByType(documentRelationshipType))
            {
              // There should only be one document part in the package. 
              documentUri = PackUriHelper.ResolvePartUri(new Uri("/",
               UriKind.Relative), relationship.TargetUri);
              documentPart = xlPackage.GetPart(documentUri);
    
              // There should only be one instance, 
              // but get out no matter what.
              break;
            }
    
            if (documentPart != null)
            {
              // Load the contents of the workbook.
              XmlDocument doc = new XmlDocument();
              doc.Load(documentPart.GetStream());
    
              // Create a NamespaceManager to handle the default namespace, 
              // and create a prefix for the default namespace:
              XmlNamespaceManager nsManager =
               new XmlNamespaceManager(doc.NameTable);
              nsManager.AddNamespace("d", doc.DocumentElement.NamespaceURI);
    
              string searchString = string.Format("//d:sheet[@name='{0}']", sheetName);
              XmlNode sheetNode = doc.SelectSingleNode(searchString, nsManager);
              if (sheetNode != null)
              {
                // Get the relId attribute:
                XmlAttribute relationAttribute = sheetNode.Attributes["r:id"];
                if (relationAttribute != null)
                {
                  string relId = relationAttribute.Value;
    
                  // First, get the relation between the document and the sheet.
                  PackageRelationship sheetRelation = documentPart.GetRelationship(relId);
                  Uri sheetUri = PackUriHelper.ResolvePartUri(documentUri, sheetRelation.TargetUri);
                  PackagePart sheetPart = xlPackage.GetPart(sheetUri);
    
                  // Load the contents of the sheet into an XML document.
                  XmlDocument xDoc = new XmlDocument();
                  xDoc.Load(sheetPart.GetStream());
    
                  // Use regular expressions to get the row number.
                  // If the parameter wasn't well formed, this code
                  // will fail:
    
    
                  System.Text.RegularExpressions.Regex r = new System.Text.RegularExpressions.Regex(@"^(?<col>\D+)(?<row>\d+)");
                  string rowNumber = r.Match(addressName).Result("${row}");
    
                  XmlNodeList sheetDataValidationsNode = xDoc.GetElementsByTagName("dataValidations");
                  XmlNode sheetDataNode = xDoc.SelectSingleNode("//d:sheetData", nsManager);
    
                  //ahora creo las data validation
                  XmlElement dataValidation1 = xDoc.CreateElement("dataValidation", worksheetSchema);
                  dataValidation1.SetAttribute("type", "list");
                  dataValidation1.SetAttribute("allowBlank", "1");
                  dataValidation1.SetAttribute("showInputMessage", "1");
                  dataValidation1.SetAttribute("showErrorMessage", "1");
                  //inserto la validacion en la direccion destino.
                  dataValidation1.SetAttribute("sqref", addressName);
                  
                  string origendatos="$"+ inicioOrigenDatos.Substring(0,1) + "$" + inicioOrigenDatos.Substring(1,1) + ":$" + finOrigenDatos.Substring(0,1) + "$" + finOrigenDatos.Substring(1,1);
                  XmlElement formula1 = xDoc.CreateElement("formula1", worksheetSchema);
                  //añado el minimo numero entero en la formula 1
                  formula1.InnerText = origendatos;
    
                  dataValidation1.AppendChild(formula1);
                  
    
    
                  //si la worksheet no posee ninguna dataValidation creado previamente 
                  if (sheetDataValidationsNode.Count == 0)
                  {
                    //Creo el nodo general dataValidations puesto que no existe
                    XmlElement dataValidations = xDoc.CreateElement("dataValidations", worksheetSchema);
    
                    //Lo inicializo a 1 porque es la primera que hay
                    dataValidations.SetAttribute("count", "1");
    
                    //añado las datavalidation al datavalidations.
                    dataValidations.AppendChild(dataValidation1);
    
                    //lo inserto despues de sheetData porque siempre van ahi
                    xDoc.DocumentElement.InsertAfter(dataValidations, sheetDataNode);
    
    
    
                    returnValue = true;
    
                  }
    
                  else//existen dataValidations previas y la variable que contiene dicho nodo es sheetDataValidationsNode
                  //y como solo hay uno deberia estar en la posicion [0]
                  {
                    comprobarValidaciones(sheetDataValidationsNode[0], addressName);
    
                    sheetDataValidationsNode[0].AppendChild(dataValidation1);
    
    
                    //ahora sumo 1 al count de numero de data validations
                    foreach (XmlAttribute s in sheetDataValidationsNode[0].Attributes)
                    {
    
                      if (s.Name == "count")
                      {
                        sheetDataValidationsNode[0].Attributes.Item(0).Value = Convert.ToString(Int32.Parse(sheetDataValidationsNode[0].Attributes["count"].InnerText) + 1);
    
                      }
                    }
                    returnValue = true;
                  }
    
                  //salvo el xml del worksheet 
                  xDoc.Save(sheetPart.GetStream(FileMode.Create, FileAccess.Write));
                  
    
                }
              }
            }
          }
          return returnValue;
    
        }
    
        public static bool addDataValidationDate(string fileName, string sheetName, string addressName,string operador,DateTime minFecha,DateTime maxFecha,DateTime fechaObjetivo)
        {
          // Given a file, a sheet, and a cell, insert a specified value.
          // For example: InsertNumberIntoCell("C:\Test.xlsx", "Sheet3", "C3", 14)
    
          const string documentRelationshipType = "http://schemas.openxmlformats.org/officeDocument/2006/" + "relationships/officeDocument";
          const string worksheetSchema = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
    
          // Retrieve the stream containing the requested
          // worksheet's info:
          PackagePart documentPart = null;
          Uri documentUri = null;
          bool returnValue = false;
    
          using (Package xlPackage =
           Package.Open(fileName, FileMode.Open, FileAccess.ReadWrite))
          {
            // Get the main document part (workbook.xml).
            foreach (System.IO.Packaging.PackageRelationship relationship
             in xlPackage.GetRelationshipsByType(documentRelationshipType))
            {
              // There should only be one document part in the package. 
              documentUri = PackUriHelper.ResolvePartUri(new Uri("/",
               UriKind.Relative), relationship.TargetUri);
              documentPart = xlPackage.GetPart(documentUri);
    
              // There should only be one instance, 
              // but get out no matter what.
              break;
            }
    
            if (documentPart != null)
            {
              // Load the contents of the workbook.
              XmlDocument doc = new XmlDocument();
              doc.Load(documentPart.GetStream());
    
              // Create a NamespaceManager to handle the default namespace, 
              // and create a prefix for the default namespace:
              XmlNamespaceManager nsManager =
               new XmlNamespaceManager(doc.NameTable);
              nsManager.AddNamespace("d", doc.DocumentElement.NamespaceURI);
    
              string searchString = string.Format("//d:sheet[@name='{0}']", sheetName);
              XmlNode sheetNode = doc.SelectSingleNode(searchString, nsManager);
              if (sheetNode != null)
              {
                // Get the relId attribute:
                XmlAttribute relationAttribute = sheetNode.Attributes["r:id"];
                 
                
                if (relationAttribute != null)
                {
                  string relId = relationAttribute.Value;
    
                  // First, get the relation between the document and the sheet.
                  PackageRelationship sheetRelation = documentPart.GetRelationship(relId);
                  Uri sheetUri = PackUriHelper.ResolvePartUri(documentUri, sheetRelation.TargetUri);
                  PackagePart sheetPart = xlPackage.GetPart(sheetUri);
    
                  // Load the contents of the sheet into an XML document.
                  XmlDocument xDoc = new XmlDocument();
                  xDoc.Load(sheetPart.GetStream());
    
                  // Use regular expressions to get the row number.
                  // If the parameter wasn't well formed, this code
                  // will fail:
    
    
                  System.Text.RegularExpressions.Regex r = new System.Text.RegularExpressions.Regex(@"^(?<col>\D+)(?<row>\d+)");
                  string rowNumber = r.Match(addressName).Result("${row}");
    
                  XmlNodeList sheetDataValidationsNode = xDoc.GetElementsByTagName("dataValidations");
                  XmlNode sheetDataNode = xDoc.SelectSingleNode("//d:sheetData", nsManager);
    
                  //ahora creo las data validation
                  XmlElement dataValidation1 = null;
                 
    
    
                  switch (operador)
                  {
                    case ("between"):
                     
    
                      dataValidation1 = xDoc.CreateElement("dataValidation", worksheetSchema);
                      dataValidation1.SetAttribute("type", "date");
                      dataValidation1.SetAttribute("allowBlank", "1");
                      dataValidation1.SetAttribute("showInputMessage", "1");
                      dataValidation1.SetAttribute("showErrorMessage", "1");
                      //inserto la validacion en la direccion destino.
                      dataValidation1.SetAttribute("sqref", addressName);
    
                      XmlElement formula1 = xDoc.CreateElement("formula1", worksheetSchema);
                      formula1.InnerText = minFecha.ToOADate().ToString();
    
                      XmlElement formula2 = xDoc.CreateElement("formula2", worksheetSchema);
                      formula2.InnerText = maxFecha.ToOADate().ToString();
    
                      dataValidation1.AppendChild(formula1);
                      dataValidation1.AppendChild(formula2);
    
                      break;
                    case ("notBetween"):
                      
                      dataValidation1 = xDoc.CreateElement("dataValidation", worksheetSchema);
                      dataValidation1.SetAttribute("type", "date");
                      dataValidation1.SetAttribute("operator", "notBetween");
                      dataValidation1.SetAttribute("allowBlank", "1");
                      dataValidation1.SetAttribute("showInputMessage", "1");
                      dataValidation1.SetAttribute("showErrorMessage", "1");
                      //inserto la validacion en la direccion destino.
                      dataValidation1.SetAttribute("sqref", addressName);
    
                      XmlElement formula3 = xDoc.CreateElement("formula1", worksheetSchema);
                      formula3.InnerText = minFecha.ToOADate().ToString();
    
                      XmlElement formula4 = xDoc.CreateElement("formula2", worksheetSchema);
                      formula4.InnerText = maxFecha.ToOADate().ToString();
    
                      dataValidation1.AppendChild(formula3);
                      dataValidation1.AppendChild(formula4);
                      break;
    
    
                    case ("equal"):
                     
                      dataValidation1 = xDoc.CreateElement("dataValidation", worksheetSchema);
                      dataValidation1.SetAttribute("type", "date");
                      dataValidation1.SetAttribute("operator", "equal");
                      dataValidation1.SetAttribute("allowBlank", "1");
                      dataValidation1.SetAttribute("showInputMessage", "1");
                      dataValidation1.SetAttribute("showErrorMessage", "1");
                      //inserto la validacion en la direccion destino.
                      dataValidation1.SetAttribute("sqref", addressName);
    
                      XmlElement formula5 = xDoc.CreateElement("formula1", worksheetSchema);
                      formula5.InnerText = fechaObjetivo.ToOADate().ToString();
                      dataValidation1.AppendChild(formula5);
    
    
                      break;
                    case ("notEqual"):
    
                      dataValidation1 = xDoc.CreateElement("dataValidation", worksheetSchema);
                      dataValidation1.SetAttribute("type", "date");
                      dataValidation1.SetAttribute("operator", "notEqual");
                      dataValidation1.SetAttribute("allowBlank", "1");
                      dataValidation1.SetAttribute("showInputMessage", "1");
                      dataValidation1.SetAttribute("showErrorMessage", "1");
                      //inserto la validacion en la direccion destino.
                      dataValidation1.SetAttribute("sqref", addressName);
    
                      XmlElement formula6 = xDoc.CreateElement("formula1", worksheetSchema);
                      formula6.InnerText = fechaObjetivo.ToOADate().ToString();
                      dataValidation1.AppendChild(formula6);
                      break;
    
                    case ("greaterThan"):
    
                       dataValidation1 = xDoc.CreateElement("dataValidation", worksheetSchema);
                      dataValidation1.SetAttribute("type", "date");
                      dataValidation1.SetAttribute("operator", "greaterThan");
                      dataValidation1.SetAttribute("allowBlank", "1");
                      dataValidation1.SetAttribute("showInputMessage", "1");
                      dataValidation1.SetAttribute("showErrorMessage", "1");
                      //inserto la validacion en la direccion destino.
                      dataValidation1.SetAttribute("sqref", addressName);
    
                      XmlElement formula7 = xDoc.CreateElement("formula1", worksheetSchema);
                      formula7.InnerText = fechaObjetivo.ToOADate().ToString();
                      dataValidation1.AppendChild(formula7);
    
                      break;
                    case ("lessThan"):
    
                       dataValidation1 = xDoc.CreateElement("dataValidation", worksheetSchema);
                      dataValidation1.SetAttribute("type", "date");
                      dataValidation1.SetAttribute("operator", "lessThan");
                      dataValidation1.SetAttribute("allowBlank", "1");
                      dataValidation1.SetAttribute("showInputMessage", "1");
                      dataValidation1.SetAttribute("showErrorMessage", "1");
                      //inserto la validacion en la direccion destino.
                      dataValidation1.SetAttribute("sqref", addressName);
    
                      XmlElement formula8 = xDoc.CreateElement("formula1", worksheetSchema);
                      formula8.InnerText = fechaObjetivo.ToOADate().ToString();
                      dataValidation1.AppendChild(formula8);
    
                      break;
    
                    case ("greaterThanOrEqual"):
                      
                       dataValidation1 = xDoc.CreateElement("dataValidation", worksheetSchema);
                      dataValidation1.SetAttribute("type", "date");
                      dataValidation1.SetAttribute("operator", "greaterThanOrEqual");
                      dataValidation1.SetAttribute("allowBlank", "1");
                      dataValidation1.SetAttribute("showInputMessage", "1");
                      dataValidation1.SetAttribute("showErrorMessage", "1");
                      //inserto la validacion en la direccion destino.
                      dataValidation1.SetAttribute("sqref", addressName);
    
                      XmlElement formula9 = xDoc.CreateElement("formula1", worksheetSchema);
                      formula9.InnerText = fechaObjetivo.ToOADate().ToString();
                      dataValidation1.AppendChild(formula9);
                      break;
    
    
                    case ("lessThanOrEqual"):
    
                       dataValidation1 = xDoc.CreateElement("dataValidation", worksheetSchema);
                      dataValidation1.SetAttribute("type", "date");
                      dataValidation1.SetAttribute("operator", "lessThanOrEqual");
                      dataValidation1.SetAttribute("allowBlank", "1");
                      dataValidation1.SetAttribute("showInputMessage", "1");
                      dataValidation1.SetAttribute("showErrorMessage", "1");
                      //inserto la validacion en la direccion destino.
                      dataValidation1.SetAttribute("sqref", addressName);
    
                      XmlElement formula10 = xDoc.CreateElement("formula1", worksheetSchema);
                      formula10.InnerText = fechaObjetivo.ToOADate().ToString();
                      dataValidation1.AppendChild(formula10);
    
                      break;
    
                    default:
    
                      break;
                  }
    
    
                
    
    
                  //si la worksheet no posee ninguna dataValidation creado previamente 
                  if (sheetDataValidationsNode.Count == 0)
                  {
                    //Creo el nodo general dataValidations puesto que no existe
                    XmlElement dataValidations = xDoc.CreateElement("dataValidations", worksheetSchema);
    
                    //Lo inicializo a 1 porque es la primera que hay
                    dataValidations.SetAttribute("count", "1");
    
                    //añado las datavalidation al datavalidations.
                    dataValidations.AppendChild(dataValidation1);
    
                    //lo inserto despues de sheetData porque siempre van ahi
                    xDoc.DocumentElement.InsertAfter(dataValidations, sheetDataNode);
    
    
    
                    returnValue = true;
    
                  }
    
                  else//existen dataValidations previas y la variable que contiene dicho nodo es sheetDataValidationsNode
                  //y como solo hay uno deberia estar en la posicion [0]
                  {
                    comprobarValidaciones(sheetDataValidationsNode[0], addressName);
    
                    sheetDataValidationsNode[0].AppendChild(dataValidation1);
    
    
                    //ahora sumo 1 al count de numero de data validations
                    foreach (XmlAttribute s in sheetDataValidationsNode[0].Attributes)
                    {
    
                      if (s.Name == "count")
                      {
                        sheetDataValidationsNode[0].Attributes.Item(0).Value = Convert.ToString(Int32.Parse(sheetDataValidationsNode[0].Attributes["count"].InnerText) + 1);
    
                      }
                    }
                    returnValue = true;
                  }
    
                  //salvo el xml del worksheet 
                  xDoc.Save(sheetPart.GetStream(FileMode.Create, FileAccess.Write));
    
    
                }
              }
            }
          }
          return returnValue;
    
        }
    
        private static void comprobarValidaciones(XmlNode xmlNode, string addressName)
        {
    
          foreach (XmlElement xm in xmlNode.ChildNodes)
          {
            if (xm.Attributes["sqref"].Value == addressName)
            {
              xmlNode.RemoveChild(xm);
            }
          }
        }
    
    
     
    
    
      }
    }
    

    Tuesday, March 15, 2011 12:22 PM

All replies

  • Hi Xabier,

    Thank you for posting and we are glad to help with you.

    You can use the following code snippet to insert the data validation:

    // Generates content of part.
    private void GeneratePartContent(WorksheetPart part)
    {
    Worksheet worksheet1 = new Worksheet(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x14ac" } };
    worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
    worksheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
    worksheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
    SheetDimension sheetDimension1 = new SheetDimension(){ Reference = "C4" };

    SheetViews sheetViews1 = new SheetViews();

    SheetView sheetView1 = new SheetView(){ TabSelected = true, WorkbookViewId = (UInt32Value)0U };
    Selection selection1 = new Selection(){ ActiveCell = "B4", SequenceOfReferences = new ListValue<StringValue>() { InnerText = "B4" } };

    sheetView1.Append(selection1);

    sheetViews1.Append(sheetView1);
    SheetFormatProperties sheetFormatProperties1 = new SheetFormatProperties(){ DefaultRowHeight = 15D };

    SheetData sheetData1 = new SheetData();

    Row row1 = new Row(){ RowIndex = (UInt32Value)4U, Spans = new ListValue<StringValue>() { InnerText = "3:3" } };

    Cell cell1 = new Cell(){ CellReference = "C4" };
    CellValue cellValue1 = new CellValue();
    cellValue1.Text = "0";

    cell1.Append(cellValue1);

    row1.Append(cell1);

    sheetData1.Append(row1);

    DataValidations dataValidations1 = new DataValidations(){ Count = (UInt32Value)2U };

    DataValidation dataValidation1 = new DataValidation(){ Type = DataValidationValues.Whole, AllowBlank = true, ShowInputMessage = true, ShowErrorMessage = true, SequenceOfReferences = new ListValue<StringValue>() { InnerText = "C4" } };
    Formula1 formula11 = new Formula1();
    formula11.Text = "0";
    Formula2 formula21 = new Formula2();
    formula21.Text = "2";

    dataValidation1.Append(formula11);
    dataValidation1.Append(formula21);

    DataValidation dataValidation2 = new DataValidation(){ Type = DataValidationValues.List, AllowBlank = true, ShowInputMessage = true, ShowErrorMessage = true, SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A4:B4" } };
    Formula1 formula12 = new Formula1();
    formula12.Text = "$A$1:$A$3";

    dataValidation2.Append(formula12);

    dataValidations1.Append(dataValidation1);
    dataValidations1.Append(dataValidation2);
    PageMargins pageMargins1 = new PageMargins(){ Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };

    worksheet1.Append(sheetDimension1);
    worksheet1.Append(sheetViews1);
    worksheet1.Append(sheetFormatProperties1);
    worksheet1.Append(sheetData1);
    worksheet1.Append(dataValidations1);
    worksheet1.Append(pageMargins1);

    part.Worksheet = worksheet1;
    }

    I hope this can help you and feel free to follow up after you have tried.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, March 10, 2011 10:07 AM
  •  

    Your answer is awesome,Thanks a lot.

    Now i have to face another problem, i dont know how to instanciate the existing  Worksheet

     

    Worksheet worksheet1 = new 
    Worksheet(){ MCAttributes = new
     MarkupCompatibilityAttributes(){ Ignorable = "x14ac"
     } };
    
    Because my program,reads from an existing file ".xlsx" (like a template) and it has to modify the source file ,like for example in this case for appending the DataValidations.So i dont need to create a new WorkSheet element,i would only need a reference of the existing one,but i dont know how to do that.
    Anyway thanks a lot!!

     

    Thursday, March 10, 2011 10:31 AM
  • finally i found the solution with this code,which add 3 methods for adding 3 distinct kinds of data validation

    sing System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.IO.Packaging;
    using System.Xml;
    using System.Windows.Forms;
    using System.IO;
    
    namespace DataValidation
    {
      static class DataValidations
      {
    
        public static bool addDataValidationWhole(string fileName, string sheetName, string addressName, int minNUM, int maxNUM)
        {
          // Given a file, a sheet, and a cell, insert a specified value.
          // For example: InsertNumberIntoCell("C:\Test.xlsx", "Sheet3", "C3", 14)
    
          const string documentRelationshipType = "http://schemas.openxmlformats.org/officeDocument/2006/" + "relationships/officeDocument";
          const string worksheetSchema = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
    
          // Retrieve the stream containing the requested
          // worksheet's info:
          PackagePart documentPart = null;
          Uri documentUri = null;
          bool returnValue = false;
    
          using (Package xlPackage =
           Package.Open(fileName, FileMode.Open, FileAccess.ReadWrite))
          {
            // Get the main document part (workbook.xml).
            foreach (System.IO.Packaging.PackageRelationship relationship
             in xlPackage.GetRelationshipsByType(documentRelationshipType))
            {
              // There should only be one document part in the package. 
              documentUri = PackUriHelper.ResolvePartUri(new Uri("/",
               UriKind.Relative), relationship.TargetUri);
              documentPart = xlPackage.GetPart(documentUri);
    
              // There should only be one instance, 
              // but get out no matter what.
              break;
            }
    
            if (documentPart != null)
            {
              // Load the contents of the workbook.
              XmlDocument doc = new XmlDocument();
              doc.Load(documentPart.GetStream());
    
              // Create a NamespaceManager to handle the default namespace, 
              // and create a prefix for the default namespace:
              XmlNamespaceManager nsManager =
               new XmlNamespaceManager(doc.NameTable);
              nsManager.AddNamespace("d", doc.DocumentElement.NamespaceURI);
    
              string searchString = string.Format("//d:sheet[@name='{0}']", sheetName);
              XmlNode sheetNode = doc.SelectSingleNode(searchString, nsManager);
              if (sheetNode != null)
              {
                // Get the relId attribute:
                XmlAttribute relationAttribute = sheetNode.Attributes["r:id"];
                if (relationAttribute != null)
                {
                  string relId = relationAttribute.Value;
    
                  // First, get the relation between the document and the sheet.
                  PackageRelationship sheetRelation = documentPart.GetRelationship(relId);
                  Uri sheetUri = PackUriHelper.ResolvePartUri(documentUri, sheetRelation.TargetUri);
                  PackagePart sheetPart = xlPackage.GetPart(sheetUri);
    
                  // Load the contents of the sheet into an XML document.
                  XmlDocument xDoc = new XmlDocument();
                  xDoc.Load(sheetPart.GetStream());
    
                  // Use regular expressions to get the row number.
                  // If the parameter wasn't well formed, this code
                  // will fail:
    
    
                  System.Text.RegularExpressions.Regex r = new System.Text.RegularExpressions.Regex(@"^(?<col>\D+)(?<row>\d+)");
                  string rowNumber = r.Match(addressName).Result("${row}");
    
                  XmlNodeList sheetDataValidationsNode = xDoc.GetElementsByTagName("dataValidations");
                  XmlNode sheetDataNode = xDoc.SelectSingleNode("//d:sheetData", nsManager);
    
                  //ahora creo las data validation
                  XmlElement dataValidation1 = xDoc.CreateElement("dataValidation", worksheetSchema);
                  dataValidation1.SetAttribute("type", "whole");
                  dataValidation1.SetAttribute("allowBlank", "1");
                  dataValidation1.SetAttribute("showInputMessage", "1");
                  dataValidation1.SetAttribute("showErrorMessage", "1");
                  //inserto la validacion en la direccion destino.
                  dataValidation1.SetAttribute("sqref", addressName);
    
    
                  XmlElement formula1 = xDoc.CreateElement("formula1", worksheetSchema);
                  //añado el minimo numero entero en la formula 1
                  formula1.InnerText = minNUM.ToString();
    
                  dataValidation1.AppendChild(formula1);
                  XmlElement formula2 = xDoc.CreateElement("formula2", worksheetSchema);
                  //añado el maximo numero entero en la formula 2
                  formula2.InnerText = maxNUM.ToString();
                  dataValidation1.AppendChild(formula2);
    
    
                  //si la worksheet no posee ninguna dataValidation creado previamente 
                  if (sheetDataValidationsNode.Count == 0)
                  {
                    //Creo el nodo general dataValidations puesto que no existe
                    XmlElement dataValidations = xDoc.CreateElement("dataValidations", worksheetSchema);
    
                    //Lo inicializo a 1 porque es la primera que hay
                    dataValidations.SetAttribute("count", "1");
    
                    //añado las datavalidation al datavalidations.
                    dataValidations.AppendChild(dataValidation1);
    
                    //lo inserto despues de sheetData porque siempre van ahi
                    xDoc.DocumentElement.InsertAfter(dataValidations, sheetDataNode);
    
                   
    
                    returnValue = true;
    
                  }
    
                  else//existen dataValidations previas y la variable que contiene dicho nodo es sheetDataValidationsNode
                  //y como solo hay uno deberia estar en la posicion [0]
                  {
                    comprobarValidaciones(sheetDataValidationsNode[0], addressName);
                    sheetDataValidationsNode[0].AppendChild(dataValidation1);
    
    
                    //ahora sumo 1 al count de numero de data validations
                    foreach (XmlAttribute s in sheetDataValidationsNode[0].Attributes)
                    {
    
                      if (s.Name == "count")
                      {
                        sheetDataValidationsNode[0].Attributes.Item(0).Value = Convert.ToString(Int32.Parse(sheetDataValidationsNode[0].Attributes["count"].InnerText) + 1);
    
                      }
                    }
                    returnValue = true;
    
                  }
    
                  //salvo el xml del worksheet 
                  xDoc.Save(sheetPart.GetStream(FileMode.Create, FileAccess.Write));
    
    
                }
              }
            }
          }
          return returnValue;
    
        }
    
        public static bool addDataValidationList(string fileName, string sheetName, string addressName, string inicioOrigenDatos,string finOrigenDatos)
        {
          // Given a file, a sheet, and a cell, insert a specified value.
          // For example: InsertNumberIntoCell("C:\Test.xlsx", "Sheet3", "C3", 14)
    
          const string documentRelationshipType = "http://schemas.openxmlformats.org/officeDocument/2006/" + "relationships/officeDocument";
          const string worksheetSchema = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
    
          // Retrieve the stream containing the requested
          // worksheet's info:
          PackagePart documentPart = null;
          Uri documentUri = null;
          bool returnValue = false;
    
          using (Package xlPackage =
           Package.Open(fileName, FileMode.Open, FileAccess.ReadWrite))
          {
            // Get the main document part (workbook.xml).
            foreach (System.IO.Packaging.PackageRelationship relationship
             in xlPackage.GetRelationshipsByType(documentRelationshipType))
            {
              // There should only be one document part in the package. 
              documentUri = PackUriHelper.ResolvePartUri(new Uri("/",
               UriKind.Relative), relationship.TargetUri);
              documentPart = xlPackage.GetPart(documentUri);
    
              // There should only be one instance, 
              // but get out no matter what.
              break;
            }
    
            if (documentPart != null)
            {
              // Load the contents of the workbook.
              XmlDocument doc = new XmlDocument();
              doc.Load(documentPart.GetStream());
    
              // Create a NamespaceManager to handle the default namespace, 
              // and create a prefix for the default namespace:
              XmlNamespaceManager nsManager =
               new XmlNamespaceManager(doc.NameTable);
              nsManager.AddNamespace("d", doc.DocumentElement.NamespaceURI);
    
              string searchString = string.Format("//d:sheet[@name='{0}']", sheetName);
              XmlNode sheetNode = doc.SelectSingleNode(searchString, nsManager);
              if (sheetNode != null)
              {
                // Get the relId attribute:
                XmlAttribute relationAttribute = sheetNode.Attributes["r:id"];
                if (relationAttribute != null)
                {
                  string relId = relationAttribute.Value;
    
                  // First, get the relation between the document and the sheet.
                  PackageRelationship sheetRelation = documentPart.GetRelationship(relId);
                  Uri sheetUri = PackUriHelper.ResolvePartUri(documentUri, sheetRelation.TargetUri);
                  PackagePart sheetPart = xlPackage.GetPart(sheetUri);
    
                  // Load the contents of the sheet into an XML document.
                  XmlDocument xDoc = new XmlDocument();
                  xDoc.Load(sheetPart.GetStream());
    
                  // Use regular expressions to get the row number.
                  // If the parameter wasn't well formed, this code
                  // will fail:
    
    
                  System.Text.RegularExpressions.Regex r = new System.Text.RegularExpressions.Regex(@"^(?<col>\D+)(?<row>\d+)");
                  string rowNumber = r.Match(addressName).Result("${row}");
    
                  XmlNodeList sheetDataValidationsNode = xDoc.GetElementsByTagName("dataValidations");
                  XmlNode sheetDataNode = xDoc.SelectSingleNode("//d:sheetData", nsManager);
    
                  //ahora creo las data validation
                  XmlElement dataValidation1 = xDoc.CreateElement("dataValidation", worksheetSchema);
                  dataValidation1.SetAttribute("type", "list");
                  dataValidation1.SetAttribute("allowBlank", "1");
                  dataValidation1.SetAttribute("showInputMessage", "1");
                  dataValidation1.SetAttribute("showErrorMessage", "1");
                  //inserto la validacion en la direccion destino.
                  dataValidation1.SetAttribute("sqref", addressName);
                  
                  string origendatos="$"+ inicioOrigenDatos.Substring(0,1) + "$" + inicioOrigenDatos.Substring(1,1) + ":$" + finOrigenDatos.Substring(0,1) + "$" + finOrigenDatos.Substring(1,1);
                  XmlElement formula1 = xDoc.CreateElement("formula1", worksheetSchema);
                  //añado el minimo numero entero en la formula 1
                  formula1.InnerText = origendatos;
    
                  dataValidation1.AppendChild(formula1);
                  
    
    
                  //si la worksheet no posee ninguna dataValidation creado previamente 
                  if (sheetDataValidationsNode.Count == 0)
                  {
                    //Creo el nodo general dataValidations puesto que no existe
                    XmlElement dataValidations = xDoc.CreateElement("dataValidations", worksheetSchema);
    
                    //Lo inicializo a 1 porque es la primera que hay
                    dataValidations.SetAttribute("count", "1");
    
                    //añado las datavalidation al datavalidations.
                    dataValidations.AppendChild(dataValidation1);
    
                    //lo inserto despues de sheetData porque siempre van ahi
                    xDoc.DocumentElement.InsertAfter(dataValidations, sheetDataNode);
    
    
    
                    returnValue = true;
    
                  }
    
                  else//existen dataValidations previas y la variable que contiene dicho nodo es sheetDataValidationsNode
                  //y como solo hay uno deberia estar en la posicion [0]
                  {
                    comprobarValidaciones(sheetDataValidationsNode[0], addressName);
    
                    sheetDataValidationsNode[0].AppendChild(dataValidation1);
    
    
                    //ahora sumo 1 al count de numero de data validations
                    foreach (XmlAttribute s in sheetDataValidationsNode[0].Attributes)
                    {
    
                      if (s.Name == "count")
                      {
                        sheetDataValidationsNode[0].Attributes.Item(0).Value = Convert.ToString(Int32.Parse(sheetDataValidationsNode[0].Attributes["count"].InnerText) + 1);
    
                      }
                    }
                    returnValue = true;
                  }
    
                  //salvo el xml del worksheet 
                  xDoc.Save(sheetPart.GetStream(FileMode.Create, FileAccess.Write));
                  
    
                }
              }
            }
          }
          return returnValue;
    
        }
    
        public static bool addDataValidationDate(string fileName, string sheetName, string addressName,string operador,DateTime minFecha,DateTime maxFecha,DateTime fechaObjetivo)
        {
          // Given a file, a sheet, and a cell, insert a specified value.
          // For example: InsertNumberIntoCell("C:\Test.xlsx", "Sheet3", "C3", 14)
    
          const string documentRelationshipType = "http://schemas.openxmlformats.org/officeDocument/2006/" + "relationships/officeDocument";
          const string worksheetSchema = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
    
          // Retrieve the stream containing the requested
          // worksheet's info:
          PackagePart documentPart = null;
          Uri documentUri = null;
          bool returnValue = false;
    
          using (Package xlPackage =
           Package.Open(fileName, FileMode.Open, FileAccess.ReadWrite))
          {
            // Get the main document part (workbook.xml).
            foreach (System.IO.Packaging.PackageRelationship relationship
             in xlPackage.GetRelationshipsByType(documentRelationshipType))
            {
              // There should only be one document part in the package. 
              documentUri = PackUriHelper.ResolvePartUri(new Uri("/",
               UriKind.Relative), relationship.TargetUri);
              documentPart = xlPackage.GetPart(documentUri);
    
              // There should only be one instance, 
              // but get out no matter what.
              break;
            }
    
            if (documentPart != null)
            {
              // Load the contents of the workbook.
              XmlDocument doc = new XmlDocument();
              doc.Load(documentPart.GetStream());
    
              // Create a NamespaceManager to handle the default namespace, 
              // and create a prefix for the default namespace:
              XmlNamespaceManager nsManager =
               new XmlNamespaceManager(doc.NameTable);
              nsManager.AddNamespace("d", doc.DocumentElement.NamespaceURI);
    
              string searchString = string.Format("//d:sheet[@name='{0}']", sheetName);
              XmlNode sheetNode = doc.SelectSingleNode(searchString, nsManager);
              if (sheetNode != null)
              {
                // Get the relId attribute:
                XmlAttribute relationAttribute = sheetNode.Attributes["r:id"];
                 
                
                if (relationAttribute != null)
                {
                  string relId = relationAttribute.Value;
    
                  // First, get the relation between the document and the sheet.
                  PackageRelationship sheetRelation = documentPart.GetRelationship(relId);
                  Uri sheetUri = PackUriHelper.ResolvePartUri(documentUri, sheetRelation.TargetUri);
                  PackagePart sheetPart = xlPackage.GetPart(sheetUri);
    
                  // Load the contents of the sheet into an XML document.
                  XmlDocument xDoc = new XmlDocument();
                  xDoc.Load(sheetPart.GetStream());
    
                  // Use regular expressions to get the row number.
                  // If the parameter wasn't well formed, this code
                  // will fail:
    
    
                  System.Text.RegularExpressions.Regex r = new System.Text.RegularExpressions.Regex(@"^(?<col>\D+)(?<row>\d+)");
                  string rowNumber = r.Match(addressName).Result("${row}");
    
                  XmlNodeList sheetDataValidationsNode = xDoc.GetElementsByTagName("dataValidations");
                  XmlNode sheetDataNode = xDoc.SelectSingleNode("//d:sheetData", nsManager);
    
                  //ahora creo las data validation
                  XmlElement dataValidation1 = null;
                 
    
    
                  switch (operador)
                  {
                    case ("between"):
                     
    
                      dataValidation1 = xDoc.CreateElement("dataValidation", worksheetSchema);
                      dataValidation1.SetAttribute("type", "date");
                      dataValidation1.SetAttribute("allowBlank", "1");
                      dataValidation1.SetAttribute("showInputMessage", "1");
                      dataValidation1.SetAttribute("showErrorMessage", "1");
                      //inserto la validacion en la direccion destino.
                      dataValidation1.SetAttribute("sqref", addressName);
    
                      XmlElement formula1 = xDoc.CreateElement("formula1", worksheetSchema);
                      formula1.InnerText = minFecha.ToOADate().ToString();
    
                      XmlElement formula2 = xDoc.CreateElement("formula2", worksheetSchema);
                      formula2.InnerText = maxFecha.ToOADate().ToString();
    
                      dataValidation1.AppendChild(formula1);
                      dataValidation1.AppendChild(formula2);
    
                      break;
                    case ("notBetween"):
                      
                      dataValidation1 = xDoc.CreateElement("dataValidation", worksheetSchema);
                      dataValidation1.SetAttribute("type", "date");
                      dataValidation1.SetAttribute("operator", "notBetween");
                      dataValidation1.SetAttribute("allowBlank", "1");
                      dataValidation1.SetAttribute("showInputMessage", "1");
                      dataValidation1.SetAttribute("showErrorMessage", "1");
                      //inserto la validacion en la direccion destino.
                      dataValidation1.SetAttribute("sqref", addressName);
    
                      XmlElement formula3 = xDoc.CreateElement("formula1", worksheetSchema);
                      formula3.InnerText = minFecha.ToOADate().ToString();
    
                      XmlElement formula4 = xDoc.CreateElement("formula2", worksheetSchema);
                      formula4.InnerText = maxFecha.ToOADate().ToString();
    
                      dataValidation1.AppendChild(formula3);
                      dataValidation1.AppendChild(formula4);
                      break;
    
    
                    case ("equal"):
                     
                      dataValidation1 = xDoc.CreateElement("dataValidation", worksheetSchema);
                      dataValidation1.SetAttribute("type", "date");
                      dataValidation1.SetAttribute("operator", "equal");
                      dataValidation1.SetAttribute("allowBlank", "1");
                      dataValidation1.SetAttribute("showInputMessage", "1");
                      dataValidation1.SetAttribute("showErrorMessage", "1");
                      //inserto la validacion en la direccion destino.
                      dataValidation1.SetAttribute("sqref", addressName);
    
                      XmlElement formula5 = xDoc.CreateElement("formula1", worksheetSchema);
                      formula5.InnerText = fechaObjetivo.ToOADate().ToString();
                      dataValidation1.AppendChild(formula5);
    
    
                      break;
                    case ("notEqual"):
    
                      dataValidation1 = xDoc.CreateElement("dataValidation", worksheetSchema);
                      dataValidation1.SetAttribute("type", "date");
                      dataValidation1.SetAttribute("operator", "notEqual");
                      dataValidation1.SetAttribute("allowBlank", "1");
                      dataValidation1.SetAttribute("showInputMessage", "1");
                      dataValidation1.SetAttribute("showErrorMessage", "1");
                      //inserto la validacion en la direccion destino.
                      dataValidation1.SetAttribute("sqref", addressName);
    
                      XmlElement formula6 = xDoc.CreateElement("formula1", worksheetSchema);
                      formula6.InnerText = fechaObjetivo.ToOADate().ToString();
                      dataValidation1.AppendChild(formula6);
                      break;
    
                    case ("greaterThan"):
    
                       dataValidation1 = xDoc.CreateElement("dataValidation", worksheetSchema);
                      dataValidation1.SetAttribute("type", "date");
                      dataValidation1.SetAttribute("operator", "greaterThan");
                      dataValidation1.SetAttribute("allowBlank", "1");
                      dataValidation1.SetAttribute("showInputMessage", "1");
                      dataValidation1.SetAttribute("showErrorMessage", "1");
                      //inserto la validacion en la direccion destino.
                      dataValidation1.SetAttribute("sqref", addressName);
    
                      XmlElement formula7 = xDoc.CreateElement("formula1", worksheetSchema);
                      formula7.InnerText = fechaObjetivo.ToOADate().ToString();
                      dataValidation1.AppendChild(formula7);
    
                      break;
                    case ("lessThan"):
    
                       dataValidation1 = xDoc.CreateElement("dataValidation", worksheetSchema);
                      dataValidation1.SetAttribute("type", "date");
                      dataValidation1.SetAttribute("operator", "lessThan");
                      dataValidation1.SetAttribute("allowBlank", "1");
                      dataValidation1.SetAttribute("showInputMessage", "1");
                      dataValidation1.SetAttribute("showErrorMessage", "1");
                      //inserto la validacion en la direccion destino.
                      dataValidation1.SetAttribute("sqref", addressName);
    
                      XmlElement formula8 = xDoc.CreateElement("formula1", worksheetSchema);
                      formula8.InnerText = fechaObjetivo.ToOADate().ToString();
                      dataValidation1.AppendChild(formula8);
    
                      break;
    
                    case ("greaterThanOrEqual"):
                      
                       dataValidation1 = xDoc.CreateElement("dataValidation", worksheetSchema);
                      dataValidation1.SetAttribute("type", "date");
                      dataValidation1.SetAttribute("operator", "greaterThanOrEqual");
                      dataValidation1.SetAttribute("allowBlank", "1");
                      dataValidation1.SetAttribute("showInputMessage", "1");
                      dataValidation1.SetAttribute("showErrorMessage", "1");
                      //inserto la validacion en la direccion destino.
                      dataValidation1.SetAttribute("sqref", addressName);
    
                      XmlElement formula9 = xDoc.CreateElement("formula1", worksheetSchema);
                      formula9.InnerText = fechaObjetivo.ToOADate().ToString();
                      dataValidation1.AppendChild(formula9);
                      break;
    
    
                    case ("lessThanOrEqual"):
    
                       dataValidation1 = xDoc.CreateElement("dataValidation", worksheetSchema);
                      dataValidation1.SetAttribute("type", "date");
                      dataValidation1.SetAttribute("operator", "lessThanOrEqual");
                      dataValidation1.SetAttribute("allowBlank", "1");
                      dataValidation1.SetAttribute("showInputMessage", "1");
                      dataValidation1.SetAttribute("showErrorMessage", "1");
                      //inserto la validacion en la direccion destino.
                      dataValidation1.SetAttribute("sqref", addressName);
    
                      XmlElement formula10 = xDoc.CreateElement("formula1", worksheetSchema);
                      formula10.InnerText = fechaObjetivo.ToOADate().ToString();
                      dataValidation1.AppendChild(formula10);
    
                      break;
    
                    default:
    
                      break;
                  }
    
    
                
    
    
                  //si la worksheet no posee ninguna dataValidation creado previamente 
                  if (sheetDataValidationsNode.Count == 0)
                  {
                    //Creo el nodo general dataValidations puesto que no existe
                    XmlElement dataValidations = xDoc.CreateElement("dataValidations", worksheetSchema);
    
                    //Lo inicializo a 1 porque es la primera que hay
                    dataValidations.SetAttribute("count", "1");
    
                    //añado las datavalidation al datavalidations.
                    dataValidations.AppendChild(dataValidation1);
    
                    //lo inserto despues de sheetData porque siempre van ahi
                    xDoc.DocumentElement.InsertAfter(dataValidations, sheetDataNode);
    
    
    
                    returnValue = true;
    
                  }
    
                  else//existen dataValidations previas y la variable que contiene dicho nodo es sheetDataValidationsNode
                  //y como solo hay uno deberia estar en la posicion [0]
                  {
                    comprobarValidaciones(sheetDataValidationsNode[0], addressName);
    
                    sheetDataValidationsNode[0].AppendChild(dataValidation1);
    
    
                    //ahora sumo 1 al count de numero de data validations
                    foreach (XmlAttribute s in sheetDataValidationsNode[0].Attributes)
                    {
    
                      if (s.Name == "count")
                      {
                        sheetDataValidationsNode[0].Attributes.Item(0).Value = Convert.ToString(Int32.Parse(sheetDataValidationsNode[0].Attributes["count"].InnerText) + 1);
    
                      }
                    }
                    returnValue = true;
                  }
    
                  //salvo el xml del worksheet 
                  xDoc.Save(sheetPart.GetStream(FileMode.Create, FileAccess.Write));
    
    
                }
              }
            }
          }
          return returnValue;
    
        }
    
        private static void comprobarValidaciones(XmlNode xmlNode, string addressName)
        {
    
          foreach (XmlElement xm in xmlNode.ChildNodes)
          {
            if (xm.Attributes["sqref"].Value == addressName)
            {
              xmlNode.RemoveChild(xm);
            }
          }
        }
    
    
     
    
    
      }
    }
    

    Tuesday, March 15, 2011 12:22 PM
  • what is the value of address parameter?
    Monday, August 29, 2011 7:06 AM