Merge pull request #98 from janhuehne/data_validation

Data validation

6 files changed, 598 insertions, 0 deletions

diff --git a/examples/data_validation.rb b/examples/data_validation.rb
new file mode 100644
index 00000000..56248ec3
--- /dev/null
+++ b/examples/data_validation.rb
@@ -0,0 +1,50 @@
+#!/usr/bin/env ruby -w -s
+# -*- coding: utf-8 -*-
+$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib"
+require 'axlsx'
+
+p = Axlsx::Package.new
+p.workbook.add_worksheet do |ws| 
+  ws.add_data_validation("A10", { 
+    :type => :whole, 
+    :operator => :between, 
+    :formula1 => '5', 
+    :formula2 => '10', 
+    :showErrorMessage => true, 
+    :errorTitle => 'Wrong input', 
+    :error => 'Only values between 5 and 10', 
+    :errorStyle => :information, 
+    :showInputMessage => true, 
+    :promptTitle => 'Be carful!', 
+    :prompt => 'Only values between 5 and 10'})
+  
+  ws.add_data_validation("B10", { 
+    :type => :textLength, 
+    :operator => :greaterThan, 
+    :formula1 => '10', 
+    :showErrorMessage => true, 
+    :errorTitle => 'Text is too long', 
+    :error => 'Max text length is 10 characters', 
+    :errorStyle => :stop, 
+    :showInputMessage => true, 
+    :promptTitle => 'Text length', 
+    :prompt => 'Max text length is 10 characters'})
+  
+  8.times do |i|
+    ws.add_row [nil, nil, i*2]
+  end
+  
+  ws.add_data_validation("C10", { 
+    :type => :list, 
+    :formula1 => 'C1:C8', 
+    :showDropDown => false,
+    :showErrorMessage => true, 
+    :errorTitle => '', 
+    :error => 'Only values from C1:C8', 
+    :errorStyle => :stop, 
+    :showInputMessage => true, 
+    :promptTitle => '', 
+    :prompt => 'Only values from C1:C8'})
+end
+
+p.serialize 'data_validation.xlsx'
\ No newline at end of file
diff --git a/lib/axlsx/util/validators.rb b/lib/axlsx/util/validators.rb
index 2f43a121..27d895e8 100644
--- a/lib/axlsx/util/validators.rb
+++ b/lib/axlsx/util/validators.rb
@@ -217,4 +217,25 @@ module Axlsx
     RestrictionValidator.validate :table_element_type, [:wholeTable, :headerRow, :totalRow, :firstColumn, :lastColumn, :firstRowStripe, :secondRowStripe, :firstColumnStripe, :secondColumnStripe, :firstHeaderCell, :lastHeaderCell, :firstTotalCell, :lastTotalCell, :firstSubtotalColumn, :secondSubtotalColumn, :thirdSubtotalColumn, :firstSubtotalRow, :secondSubtotalRow, :thirdSubtotalRow, :blankRow, :firstColumnSubheading, :secondColumnSubheading, :thirdColumnSubheading, :firstRowSubheading, :secondRowSubheading, :thirdRowSubheading, :pageFieldLabels, :pageFieldValues], v
   end
 
+  # Requires that the value is a valid data_validation_error_style
+  # :information, :stop, :warning
+  # @param [Any] v The value validated
+  def self.validate_data_validation_error_style(v)
+    RestrictionValidator.validate :validate_data_validation_error_style, [:information, :stop, :warning], v
+  end
+  
+  # Requires that the value is valid data validation operator.
+  # valid operators must be one of lessThan, lessThanOrEqual, equal,
+  # notEqual, greaterThanOrEqual, greaterThan, between, notBetween
+  # @param [Any] v The value validated
+  def self.validate_data_validation_operator(v)
+    RestrictionValidator.validate :data_validation_operator, [:lessThan, :lessThanOrEqual, :equal, :notEqual, :greaterThanOrEqual, :greaterThan, :between, :notBetween], v
+  end
+  
+  # Requires that the value is valid data validation type.
+  # valid types must be one of custom, data, decimal, list, none, textLength, time, whole
+  # @param [Any] v The value validated
+  def self.validate_data_validation_type(v)
+    RestrictionValidator.validate :data_validation_type, [:custom, :data, :decimal, :list, :none, :textLength, :time, :whole], v
+  end
 end
diff --git a/lib/axlsx/workbook/workbook.rb b/lib/axlsx/workbook/workbook.rb
index fe3128c9..f0b926db 100644
--- a/lib/axlsx/workbook/workbook.rb
+++ b/lib/axlsx/workbook/workbook.rb
@@ -20,6 +20,7 @@ require 'axlsx/workbook/worksheet/sheet_protection.rb'
 require 'axlsx/workbook/worksheet/worksheet.rb'
 require 'axlsx/workbook/shared_strings_table.rb'
 require 'axlsx/workbook/worksheet/table.rb'
+require 'axlsx/workbook/worksheet/data_validation.rb'
 
   # The Workbook class is an xlsx workbook that manages worksheets, charts, drawings and styles.
   # The following parts of the Office Open XML spreadsheet specification are not implimented in this version.
diff --git a/lib/axlsx/workbook/worksheet/data_validation.rb b/lib/axlsx/workbook/worksheet/data_validation.rb
new file mode 100644
index 00000000..8eca9375
--- /dev/null
+++ b/lib/axlsx/workbook/worksheet/data_validation.rb
@@ -0,0 +1,245 @@
+# encoding: UTF-8
+module Axlsx
+  # Data validation allows the validation of cell data
+  #
+  # @note The recommended way to manage data validations is via Worksheet#add_data_validation
+  # @see Worksheet#add_data_validation
+  class DataValidation
+    
+    # instance values that must be serialized as their own elements - e.g. not attributes.
+    CHILD_ELEMENTS = [:formula1, :formula2]
+    
+    # Formula1
+    # Available for type whole, decimal, date, time, textLength, list, custom
+    # @see type
+    # @return [String]
+    # @default nil
+    attr_reader :formula1
+    
+    # Formula2
+    # Available for type whole, decimal, date, time, textLength
+    # @see type
+    # @return [String]
+    # @default nil
+    attr_reader :formula2
+    
+    # Allow Blank
+    # A boolean value indicating whether the data validation allows the use of empty or blank
+    # entries. 1 means empty entries are OK and do not violate the validation constraints.
+    # Available for type whole, decimal, date, time, textLength, list, custom
+    # @see type
+    # @return [Boolean]
+    # @default true
+    attr_reader :allowBlank
+    
+    # Error Message
+    # Message text of error alert.
+    # Available for type whole, decimal, date, time, textLength, list, custom
+    # @see type
+    # @return [String]
+    # @default nil
+    attr_reader :error
+    
+    # Error Style (ST_DataValidationErrorStyle)
+    # The style of error alert used for this data validation.
+    # Options are:
+    #  * information: This data validation error style uses an information icon in the error alert.
+    #  * stop: This data validation error style uses a stop icon in the error alert.
+    #  * warning: This data validation error style uses a warning icon in the error alert.
+    # Available for type whole, decimal, date, time, textLength, list, custom
+    # @see type
+    # @return [Symbol]
+    # @default :stop
+    attr_reader :errorStyle
+    
+    # Error Title
+    # Title bar text of error alert.
+    # Available for type whole, decimal, date, time, textLength, list, custom
+    # @see type
+    # @return [String]
+    # @default nil
+    attr_reader :errorTitle
+    
+    # Operator (ST_DataValidationOperator)
+    # The relational operator used with this data validation.
+    # Options are:
+    #  * between: Data validation which checks if a value is between two other values.
+    #  * equal: Data validation which checks if a value is equal to a specified value.
+    #  * greater_than: Data validation which checks if a value is greater than a specified value.
+    #  * greater_than_or_equal: Data validation which checks if a value is greater than or equal to a specified value.
+    #  * less_than: Data validation which checks if a value is less than a specified value.
+    #  * less_than_or_equal: Data validation which checks if a value is less than or equal to a specified value.
+    #  * not_between: Data validation which checks if a value is not between two other values.
+    #  * not_equal: Data validation which checks if a value is not equal to a specified value.
+    # Available for type whole, decimal, date, time, textLength
+    # @see type
+    # @return [Symbol]
+    # @default nil
+    attr_reader :operator
+    
+    # Input prompt
+    # Message text of input prompt.
+    # Available for type whole, decimal, date, time, textLength, list, custom
+    # @see type
+    # @return [String]
+    # @default nil
+    attr_reader :prompt
+    
+    # Prompt title
+    # Title bar text of input prompt.
+    # Available for type whole, decimal, date, time, textLength, list, custom
+    # @see type
+    # @return [String]
+    # @default nil
+    attr_reader :promptTitle
+    
+    # Show drop down
+    # A boolean value indicating whether to display a dropdown combo box for a list type data
+    # validation. Be careful: false shows the dropdown list!
+    # Available for type list
+    # @see type
+    # @return [Boolean]
+    # @default false
+    attr_reader :showDropDown
+    
+    # Show error message
+    # A boolean value indicating whether to display the error alert message when an invalid
+    # value has been entered, according to the criteria specified.
+    # Available for type whole, decimal, date, time, textLength, list, custom
+    # @see type
+    # @return [Boolean]
+    # @default false
+    attr_reader :showErrorMessage
+    
+    # Show input message
+    # A boolean value indicating whether to display the input prompt message.
+    # Available for type whole, decimal, date, time, textLength, list, custom
+    # @see type
+    # @return [Boolean]
+    # @default false
+    attr_reader :showInputMessage
+    
+    # Range over which data validation is applied, in "A1:B2" format
+    # Available for type whole, decimal, date, time, textLength, list, custom
+    # @see type
+    # @return [String]
+    # @default nil
+    attr_reader :sqref
+    
+    # The type (ST_DataValidationType) of data validation.
+    # Options are:
+    #  * custom: Data validation which uses a custom formula to check the cell value.
+    #  * date: Data validation which checks for date values satisfying the given condition.
+    #  * decimal: Data validation which checks for decimal values satisfying the given condition.
+    #  * list: Data validation which checks for a value matching one of list of values.
+    #  * none: No data validation.
+    #  * textLength: Data validation which checks for text values, whose length satisfies the given condition.
+    #  * time: Data validation which checks for time values satisfying the given condition.
+    #  * whole: Data validation which checks for whole number values satisfying the given condition.
+    # @return [Symbol]
+    # @default none
+    attr_reader :type
+    
+    # Creates a new {DataValidation} object
+    # @option options [String] formula1
+    # @option options [String] formula2
+    # @option options [Boolean] allowBlank - A boolean value indicating whether the data validation allows the use of empty or blank entries.
+    # @option options [String] error - Message text of error alert.
+    # @option options [Symbol] errorStyle - The style of error alert used for this data validation.
+    # @option options [String] errorTitle - itle bar text of error alert.
+    # @option options [Symbol] operator - The relational operator used with this data validation.
+    # @option options [String] prompt - Message text of input prompt.
+    # @option options [String] promptTitle - Title bar text of input prompt.
+    # @option options [Boolean] showDropDown - A boolean value indicating whether to display a dropdown combo box for a list type data validation
+    # @option options [Boolean] showErrorMessage - A boolean value indicating whether to display the error alert message when an invalid value has been entered, according to the criteria specified.
+    # @option options [Boolean] showInputMessage - A boolean value indicating whether to display the input prompt message.
+    # @option options [String] sqref - Range over which data validation is applied, in "A1:B2" format.
+    # @option options [Symbol] type - The type of data validation.
+    def initialize(options={})
+      # defaults
+      @formula1 = @formula2 = @error = @errorTitle = @operator = @prompt = @promptTitle = @sqref = nil
+      @allowBlank = @showErrorMessage = true
+      @showDropDown = @showInputMessage = false
+      @type = :none
+      @errorStyle = :stop
+      
+      options.each do |o|
+        self.send("#{o[0]}=", o[1]) if self.respond_to? "#{o[0]}="
+      end
+    end
+    
+    # @see formula1
+    def formula1=(v); Axlsx::validate_string(v); @formula1 = v end
+    
+    # @see formula2
+    def formula2=(v); Axlsx::validate_string(v); @formula2 = v end 
+    
+    # @see allowBlank
+    def allowBlank=(v); Axlsx::validate_boolean(v); @allowBlank = v end
+    
+    # @see error
+    def error=(v); Axlsx::validate_string(v); @error = v end
+    
+    # @see errorStyle
+    def errorStyle=(v); Axlsx::validate_data_validation_error_style(v); @errorStyle = v end
+    
+    # @see errorTitle
+    def errorTitle=(v); Axlsx::validate_string(v); @errorTitle = v end
+    
+    # @see operator
+    def operator=(v); Axlsx::validate_data_validation_operator(v); @operator = v end
+    
+    # @see prompt
+    def prompt=(v); Axlsx::validate_string(v); @prompt = v end
+    
+    # @see promptTitle
+    def promptTitle=(v); Axlsx::validate_string(v); @promptTitle = v end
+    
+    # @see showDropDown
+    def showDropDown=(v); Axlsx::validate_boolean(v); @showDropDown = v end
+    
+    # @see showErrorMessage
+    def showErrorMessage=(v); Axlsx::validate_boolean(v); @showErrorMessage = v end
+    
+    # @see showInputMessage
+    def showInputMessage=(v); Axlsx::validate_boolean(v); @showInputMessage = v end
+        
+    # @see sqref
+    def sqref=(v); Axlsx::validate_string(v); @sqref = v end
+    
+    # @see type
+    def type=(v); Axlsx::validate_data_validation_type(v); @type = v end
+    
+    # Serializes the data validation
+    # @param [String] str
+    # @return [String]
+    def to_xml_string(str = '')
+      valid_attributes = get_valid_attributes
+      
+      str << '<dataValidation '
+      str << instance_values.map { |key, value| '' << key << '="' << value.to_s << '"' if (valid_attributes.include?(key.to_sym) and not CHILD_ELEMENTS.include?(key.to_sym)) }.join(' ')
+      str << '>'
+      str << '<formula1>' << self.formula1 << '</formula1>' if @formula1 and valid_attributes.include?(:formula1)
+      str << '<formula2>' << self.formula2 << '</formula2>' if @formula2 and valid_attributes.include?(:formula2)
+      str << '</dataValidation>'
+    end
+    
+  private
+    def get_valid_attributes
+      attributes = [:allowBlank, :error, :errorStyle, :errorTitle, :prompt, :promptTitle, :showErrorMessage, :showInputMessage, :sqref, :type ]
+      
+      if [:whole, :decimal, :data, :time, :textLength].include?(@type)
+        attributes << [:operator, :formula1]
+        attributes << [:formula2] if [:between, :notBetween].include?(@operator)
+      elsif @type == :list
+        attributes << [:showDropDown, :formula1]
+      elsif @type == :custom
+        attributes << [:formula1]
+      else
+        attributes = []
+      end
+      
+      attributes.flatten!
+    end
+  end
+end
\ No newline at end of file
diff --git a/lib/axlsx/workbook/worksheet/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb
index bffd8c49..56c17f32 100644
--- a/lib/axlsx/workbook/worksheet/worksheet.rb
+++ b/lib/axlsx/workbook/worksheet/worksheet.rb
@@ -164,6 +164,7 @@ module Axlsx
       @merged_cells = []
       @auto_fit_data = []
       @conditional_formattings = []
+      @data_validations = []
       @comments = Comments.new(self)
       @selected = false
       @show_gridlines = true
@@ -202,6 +203,19 @@ module Axlsx
       cf.add_rules rules
       @conditional_formattings << cf
     end
+    
+    # Add data validation to this worksheet.
+    #
+    # @param
+    # @example
+    #
+    # @see
+    # @see
+    def add_data_validation(cells, data_validation)
+      dv = DataValidation.new(data_validation)
+      dv.sqref = cells
+      @data_validations << dv
+    end
 
     # Creates merge information for this worksheet.
     # Cells can be merged by calling the merge_cells method on a worksheet.
@@ -504,6 +518,14 @@ module Axlsx
      @conditional_formattings.each do |cf|
        str.concat cf.to_xml_string
      end
+     
+     unless @data_validations.empty?
+       str.concat "<dataValidations count=\"#{@data_validations.size}\">"
+       @data_validations.each do |df|
+         str.concat df.to_xml_string
+       end
+       str.concat '</dataValidations>'
+     end
      str + '</worksheet>'
     end
 
diff --git a/test/workbook/worksheet/tc_data_validation.rb b/test/workbook/worksheet/tc_data_validation.rb
new file mode 100644
index 00000000..c30ce251
--- /dev/null
+++ b/test/workbook/worksheet/tc_data_validation.rb
@@ -0,0 +1,259 @@
+# encoding: UTF-8
+require 'tc_helper.rb'
+
+
+class TestDataValidation < Test::Unit::TestCase
+  def setup
+    #inverse defaults
+    @boolean_options = { :allowBlank => false, :showDropDown => true, :showErrorMessage => false, :showInputMessage => true }
+    @nil_options = { :formula1 => 'foo',  :formula2 => 'foo', :errorTitle => 'foo', :operator => :lessThan, :prompt => 'foo', :promptTitle => 'foo', :sqref => 'foo' }
+    @type_option = { :type => :whole }
+    @error_style_option = { :errorStyle => :warning }
+    
+    @string_options = { :formula1 => 'foo', :formula2 => 'foo', :error => 'foo', :errorTitle => 'foo', :prompt => 'foo', :promptTitle => 'foo', :sqref => 'foo' }
+    @symbol_options = { :errorStyle => :warning, :operator => :lessThan, :type => :whole}
+    
+    @options = @boolean_options.merge(@nil_options).merge(@type_option).merge(@error_style_option)
+    
+    @dv = Axlsx::DataValidation.new(@options)
+  end
+  
+  def test_initialize
+    dv = Axlsx::DataValidation.new
+    
+    @boolean_options.each do |key, value|
+      assert_equal(!value, dv.send(key.to_sym), "initialized default #{key} should be #{!value}")
+      assert_equal(value, @dv.send(key.to_sym), "initialized options #{key} should be #{value}")
+    end
+    
+    @nil_options.each do |key, value|
+      assert_equal(nil, dv.send(key.to_sym), "initialized default #{key} should be nil")
+      assert_equal(value, @dv.send(key.to_sym), "initialized options #{key} should be #{value}")
+    end
+    
+    @type_option.each do |key, value|
+      assert_equal(:none, dv.send(key.to_sym), "initialized default #{key} should be :none")
+      assert_equal(value, @dv.send(key.to_sym), "initialized options #{key} should be #{value}")
+    end
+    
+    @error_style_option.each do |key, value|
+      assert_equal(:stop, dv.send(key.to_sym), "initialized default #{key} should be :stop")
+      assert_equal(value, @dv.send(key.to_sym), "initialized options #{key} should be #{value}")
+    end
+  end
+  
+  def test_boolean_attribute_validation
+    @boolean_options.each do |key, value|
+      assert_raise(ArgumentError, "#{key} must be boolean") { @dv.send("#{key}=".to_sym, 'A') }
+      assert_nothing_raised { @dv.send("#{key}=".to_sym, true) }
+    end
+  end
+  
+  def test_string_attribute_validation
+    @string_options.each do |key, value|
+      assert_raise(ArgumentError, "#{key} must be string") { @dv.send("#{key}=".to_sym, :symbol) }
+      assert_nothing_raised { @dv.send("#{key}=".to_sym, "foo") }
+    end
+  end
+  
+  def test_symbol_attribute_validation
+    @symbol_options.each do |key, value|
+      assert_raise(ArgumentError, "#{key} must be symbol") { @dv.send("#{key}=".to_sym, "foo") }
+      assert_nothing_raised { @dv.send("#{key}=".to_sym, value) }
+    end
+  end
+  
+  def test_formula1
+    assert_raise(ArgumentError) { @dv.formula1 = 10 }
+    assert_nothing_raised { @dv.formula1 = "=SUM(A1:A1)" }
+    assert_equal(@dv.formula1, "=SUM(A1:A1)")
+  end
+  
+  def test_formula2
+    assert_raise(ArgumentError) { @dv.formula2 = 10 }
+    assert_nothing_raised { @dv.formula2 = "=SUM(A1:A1)" }
+    assert_equal(@dv.formula2, "=SUM(A1:A1)")
+  end
+  
+  def test_allowBlank
+    assert_raise(ArgumentError) { @dv.allowBlank = "foo´" }
+    assert_nothing_raised { @dv.allowBlank = false }
+    assert_equal(@dv.allowBlank, false)
+  end
+  
+  def test_error
+    assert_raise(ArgumentError) { @dv.error = :symbol }
+    assert_nothing_raised { @dv.error = "This is a error message" }
+    assert_equal(@dv.error, "This is a error message")
+  end
+  
+  def test_errorStyle
+    assert_raise(ArgumentError) { @dv.errorStyle = "foo" }
+    assert_nothing_raised { @dv.errorStyle = :information }
+    assert_equal(@dv.errorStyle, :information)
+  end
+  
+  def test_errorTitle
+    assert_raise(ArgumentError) { @dv.errorTitle = :symbol }
+    assert_nothing_raised { @dv.errorTitle = "This is the error title" }
+    assert_equal(@dv.errorTitle, "This is the error title")
+  end
+  
+  def test_operator
+    assert_raise(ArgumentError) { @dv.operator = "foo" }
+    assert_nothing_raised { @dv.operator = :greaterThan }
+    assert_equal(@dv.operator, :greaterThan)
+  end
+  
+  def test_prompt
+    assert_raise(ArgumentError) { @dv.prompt = :symbol }
+    assert_nothing_raised { @dv.prompt = "This is a prompt message" }
+    assert_equal(@dv.prompt, "This is a prompt message")
+  end
+  
+  def test_promptTitle
+    assert_raise(ArgumentError) { @dv.promptTitle = :symbol }
+    assert_nothing_raised { @dv.promptTitle = "This is the prompt title" }
+    assert_equal(@dv.promptTitle, "This is the prompt title")
+  end
+  
+  def test_showDropDown
+    assert_raise(ArgumentError) { @dv.showDropDown = "foo´" }
+    assert_nothing_raised { @dv.showDropDown = false }
+    assert_equal(@dv.showDropDown, false)
+  end
+  
+  def test_showErrorMessage
+    assert_raise(ArgumentError) { @dv.showErrorMessage = "foo´" }
+    assert_nothing_raised { @dv.showErrorMessage = false }
+    assert_equal(@dv.showErrorMessage, false)
+  end
+  
+  def test_showInputMessage
+    assert_raise(ArgumentError) { @dv.showInputMessage = "foo´" }
+    assert_nothing_raised { @dv.showInputMessage = false }
+    assert_equal(@dv.showInputMessage, false)
+  end
+  
+  def test_sqref
+    assert_raise(ArgumentError) { @dv.sqref = 10 }
+    assert_nothing_raised { @dv.sqref = "A1:A1" }
+    assert_equal(@dv.sqref, "A1:A1")
+  end
+  
+  def test_type
+    assert_raise(ArgumentError) { @dv.type = "foo" }
+    assert_nothing_raised { @dv.type = :list }
+    assert_equal(@dv.type, :list)
+  end
+  
+  def test_whole_decimal_data_time_textLength_to_xml
+    p = Axlsx::Package.new
+    @ws = p.workbook.add_worksheet :name=>"data_validation"
+    @ws.add_data_validation("A1", { :type => :whole, :operator => :between, :formula1 => '5', :formula2 => '10', 
+        :showErrorMessage => true, :errorTitle => 'Wrong input', :error => 'Only values between 5 and 10', 
+        :errorStyle => :information, :showInputMessage => true, :promptTitle => 'Be carful!', 
+        :prompt => 'Only values between 5 and 10'})
+    
+    doc = Nokogiri::XML.parse(@ws.to_xml_string)
+    
+    #test attributes
+    assert_equal(1, doc.xpath("//xmlns:worksheet/xmlns:dataValidations[@count='1']/xmlns:dataValidation[@sqref='A1']
+      [@promptTitle='Be carful!'][@prompt='Only values between 5 and 10'][@operator='between'][@errorTitle='Wrong input']
+      [@error='Only values between 5 and 10'][@showErrorMessage='true'][@allowBlank='true'][@showInputMessage='true'][@type='whole']
+      [@errorStyle='information']").size)
+    assert doc.xpath("//xmlns:worksheet/xmlns:dataValidations[@count='1']/xmlns:dataValidation[@sqref='A1']
+      [@promptTitle='Be carful!'][@prompt='Only values between 5 and 10'][@operator='between'][@errorTitle='Wrong input']
+      [@error='Only values between 5 and 10'][@showErrorMessage='true'][@allowBlank='true'][@showInputMessage='true']
+      [@type='whole'][@errorStyle='information']")
+    
+    #test forumula1
+    assert_equal(1, doc.xpath("//xmlns:worksheet/xmlns:dataValidations/xmlns:dataValidation/xmlns:formula1").size)
+    assert doc.xpath("//xmlns:worksheet/xmlns:dataValidations/xmlns:dataValidation/xmlns:formula1='5'")
+    
+    #test forumula2
+    assert_equal(1, doc.xpath("//xmlns:worksheet/xmlns:dataValidations/xmlns:dataValidation/xmlns:formula2").size)
+    assert doc.xpath("//xmlns:worksheet/xmlns:dataValidations/xmlns:dataValidation/xmlns:formula2='10'")
+  end
+  
+  def test_list_to_xml
+    p = Axlsx::Package.new
+    @ws = p.workbook.add_worksheet :name=>"data_validation"
+    @ws.add_data_validation("A1", { :type => :list, :formula1 => 'A1:A5',
+        :showErrorMessage => true, :errorTitle => 'Wrong input', :error => 'Only values from list', 
+        :errorStyle => :stop, :showInputMessage => true, :promptTitle => 'Be carful!', 
+        :prompt => 'Only values from list', :showDropDown => true})
+    
+    doc = Nokogiri::XML.parse(@ws.to_xml_string)
+    
+    #test attributes
+    assert_equal(1, doc.xpath("//xmlns:worksheet/xmlns:dataValidations[@count='1']/xmlns:dataValidation[@sqref='A1']
+      [@promptTitle='Be carful!'][@prompt='Only values from list'][@errorTitle='Wrong input'][@error='Only values from list']
+      [@showErrorMessage='true'][@allowBlank='true'][@showInputMessage='true'][@showDropDown='true'][@type='list']
+      [@errorStyle='stop']").size)
+    assert doc.xpath("//xmlns:worksheet/xmlns:dataValidations[@count='1']/xmlns:dataValidation[@sqref='A1']
+      [@promptTitle='Be carful!'][@prompt='Only values from list'][@errorTitle='Wrong input'][@error='Only values from list']
+      [@showErrorMessage='true'][@allowBlank='true'][@showInputMessage='true'][@showDropDown='true'][@type='list'][@errorStyle='stop']")
+    
+    #test forumula1
+    assert_equal(1, doc.xpath("//xmlns:worksheet/xmlns:dataValidations/xmlns:dataValidation/xmlns:formula1").size)
+    assert doc.xpath("//xmlns:worksheet/xmlns:dataValidations/xmlns:dataValidation/xmlns:formula1='A1:A5'")
+  end
+  
+  def test_custom_to_xml
+    p = Axlsx::Package.new
+    @ws = p.workbook.add_worksheet :name=>"data_validation"
+    @ws.add_data_validation("A1", { :type => :custom, :formula1 => '=5/2',
+        :showErrorMessage => true, :errorTitle => 'Wrong input', :error => 'Only values corresponding formula', 
+        :errorStyle => :stop, :showInputMessage => true, :promptTitle => 'Be carful!', 
+        :prompt => 'Only values corresponding formula'})
+    
+    doc = Nokogiri::XML.parse(@ws.to_xml_string)
+    
+    #test attributes
+    assert_equal(1, doc.xpath("//xmlns:worksheet/xmlns:dataValidations[@count='1']/xmlns:dataValidation[@sqref='A1'][@promptTitle='Be carful!']
+      [@prompt='Only values corresponding formula'][@errorTitle='Wrong input'][@error='Only values corresponding formula'][@showErrorMessage='true']
+      [@allowBlank='true'][@showInputMessage='true'][@type='custom'][@errorStyle='stop']").size)
+    assert doc.xpath("//xmlns:worksheet/xmlns:dataValidations[@count='1']/xmlns:dataValidation[@sqref='A1'][@promptTitle='Be carful!']
+      [@prompt='Only values corresponding formula'][@errorTitle='Wrong input'][@error='Only values corresponding formula']
+      [@showErrorMessage='true'][@allowBlank='true'][@showInputMessage='true'][@type='custom'][@errorStyle='stop']")
+    
+    #test forumula1
+    assert_equal(1, doc.xpath("//xmlns:worksheet/xmlns:dataValidations/xmlns:dataValidation/xmlns:formula1").size)
+    assert doc.xpath("//xmlns:worksheet/xmlns:dataValidations/xmlns:dataValidation/xmlns:formula1='=5/2'")
+  end
+  
+  def test_multiple_datavalidations_to_xml
+    p = Axlsx::Package.new
+    @ws = p.workbook.add_worksheet :name=>"data_validation"
+    @ws.add_data_validation("A1", { :type => :whole, :operator => :between, :formula1 => '5', :formula2 => '10', 
+        :showErrorMessage => true, :errorTitle => 'Wrong input', :error => 'Only values between 5 and 10', 
+        :errorStyle => :information, :showInputMessage => true, :promptTitle => 'Be carful!', 
+        :prompt => 'Only values between 5 and 10'})
+    @ws.add_data_validation("B1", { :type => :list, :formula1 => 'A1:A5',
+        :showErrorMessage => true, :errorTitle => 'Wrong input', :error => 'Only values from list', 
+        :errorStyle => :stop, :showInputMessage => true, :promptTitle => 'Be carful!', 
+        :prompt => 'Only values from list', :showDropDown => true})
+    
+    doc = Nokogiri::XML.parse(@ws.to_xml_string)
+    
+    #test attributes
+    assert_equal(1, doc.xpath("//xmlns:worksheet/xmlns:dataValidations[@count='2']/xmlns:dataValidation[@sqref='A1']
+      [@promptTitle='Be carful!'][@prompt='Only values between 5 and 10'][@operator='between'][@errorTitle='Wrong input']
+      [@error='Only values between 5 and 10'][@showErrorMessage='true'][@allowBlank='true'][@showInputMessage='true'][@type='whole']
+      [@errorStyle='information']").size)
+    assert doc.xpath("//xmlns:worksheet/xmlns:dataValidations[@count='2']/xmlns:dataValidation[@sqref='A1']
+      [@promptTitle='Be carful!'][@prompt='Only values between 5 and 10'][@operator='between'][@errorTitle='Wrong input']
+      [@error='Only values between 5 and 10'][@showErrorMessage='true'][@allowBlank='true'][@showInputMessage='true']
+      [@type='whole'][@errorStyle='information']")
+    
+    #test attributes
+    assert_equal(1, doc.xpath("//xmlns:worksheet/xmlns:dataValidations[@count='2']/xmlns:dataValidation[@sqref='B1']
+      [@promptTitle='Be carful!'][@prompt='Only values from list'][@errorTitle='Wrong input'][@error='Only values from list']
+      [@showErrorMessage='true'][@allowBlank='true'][@showInputMessage='true'][@showDropDown='true'][@type='list']
+      [@errorStyle='stop']").size)
+    assert doc.xpath("//xmlns:worksheet/xmlns:dataValidations[@count='2']/xmlns:dataValidation[@sqref='B1']
+      [@promptTitle='Be carful!'][@prompt='Only values from list'][@errorTitle='Wrong input'][@error='Only values from list']
+      [@showErrorMessage='true'][@allowBlank='true'][@showInputMessage='true'][@showDropDown='true'][@type='list'][@errorStyle='stop']")
+  end
+end
\ No newline at end of file