examples/data_validate.rb in write_xlsx-1.09.4 vs examples/data_validate.rb in write_xlsx-1.09.5
- old
+ new
@@ -17,26 +17,25 @@
workbook = WriteXLSX.new('data_validate.xlsx')
worksheet = workbook.add_worksheet
# Add a format for the header cells.
header_format = workbook.add_format(
- :border => 1,
- :bg_color => '#C6EFCE',
- :bold => 1,
- :text_wrap => 1,
- :valign => 'vcenter',
- :indent => 1
+ :border => 1,
+ :bg_color => '#C6EFCE',
+ :bold => 1,
+ :text_wrap => 1,
+ :valign => 'vcenter',
+ :indent => 1
)
# Set up layout of the worksheet.
worksheet.set_column('A:A', 68)
worksheet.set_column('B:B', 15)
worksheet.set_column('D:D', 15)
worksheet.set_row(0, 36)
worksheet.set_selection('B3')
-
# Write the header cells and some data that will be used in the examples.
row = 0
heading1 = 'Some examples of data validation in Excel::Writer::XLSX'
heading2 = 'Enter values in this column'
heading3 = 'Sample Data'
@@ -47,232 +46,246 @@
worksheet.write('D3', ['Integers', 1, 10])
worksheet.write('D4', ['List data', 'open', 'high', 'close'])
worksheet.write('D5', ['Formula', '=AND(F5=50,G5=60)', 50, 60])
-
#
# Example 1. Limiting input to an integer in a fixed range.
#
txt = 'Enter an integer between 1 and 10'
row += 2
worksheet.write(row, 0, txt)
-worksheet.data_validation(row, 1,
- {
- :validate => 'integer',
- :criteria => 'between',
- :minimum => 1,
- :maximum => 10
- })
+worksheet.data_validation(
+ row, 1,
+ {
+ :validate => 'integer',
+ :criteria => 'between',
+ :minimum => 1,
+ :maximum => 10
+ }
+)
-
#
# Example 2. Limiting input to an integer outside a fixed range.
#
txt = 'Enter an integer that is not between 1 and 10 (using cell references)'
row += 2
worksheet.write(row, 0, txt)
-worksheet.data_validation(row, 1,
- {
- :validate => 'integer',
- :criteria => 'not between',
- :minimum => '=E3',
- :maximum => '=F3'
- })
+worksheet.data_validation(
+ row, 1,
+ {
+ :validate => 'integer',
+ :criteria => 'not between',
+ :minimum => '=E3',
+ :maximum => '=F3'
+ }
+)
-
#
# Example 3. Limiting input to an integer greater than a fixed value.
#
txt = 'Enter an integer greater than 0'
row += 2
worksheet.write(row, 0, txt)
-worksheet.data_validation(row, 1,
- {
- :validate => 'integer',
- :criteria => '>',
- :value => 0
- })
+worksheet.data_validation(
+ row, 1,
+ {
+ :validate => 'integer',
+ :criteria => '>',
+ :value => 0
+ }
+)
-
#
# Example 4. Limiting input to an integer less than a fixed value.
#
txt = 'Enter an integer less than 10'
row += 2
worksheet.write(row, 0, txt)
-worksheet.data_validation(row, 1,
- {
- :validate => 'integer',
- :criteria => '<',
- :value => 10
- })
+worksheet.data_validation(
+ row, 1,
+ {
+ :validate => 'integer',
+ :criteria => '<',
+ :value => 10
+ }
+)
-
#
# Example 5. Limiting input to a decimal in a fixed range.
#
txt = 'Enter a decimal between 0.1 and 0.5'
row += 2
worksheet.write(row, 0, txt)
-worksheet.data_validation(row, 1,
- {
- :validate => 'decimal',
- :criteria => 'between',
- :minimum => 0.1,
- :maximum => 0.5
- })
+worksheet.data_validation(
+ row, 1,
+ {
+ :validate => 'decimal',
+ :criteria => 'between',
+ :minimum => 0.1,
+ :maximum => 0.5
+ }
+)
-
#
# Example 6. Limiting input to a value in a dropdown list.
#
txt = 'Select a value from a drop down list'
row += 2
-bp=1
+bp = 1
worksheet.write(row, 0, txt)
-worksheet.data_validation(row, 1,
- {
- :validate => 'list',
- :source => ['open', 'high', 'close']
- })
+worksheet.data_validation(
+ row, 1,
+ {
+ :validate => 'list',
+ :source => %w[open high close]
+ }
+)
-
#
# Example 6. Limiting input to a value in a dropdown list.
#
txt = 'Select a value from a drop down list (using a cell range)'
row += 2
worksheet.write(row, 0, txt)
-worksheet.data_validation(row, 1,
- {
- :validate => 'list',
- :source => '=$E$4:$G$4'
- })
+worksheet.data_validation(
+ row, 1,
+ {
+ :validate => 'list',
+ :source => '=$E$4:$G$4'
+ }
+)
-
#
# Example 7. Limiting input to a date in a fixed range.
#
txt = 'Enter a date between 1/1/2008 and 12/12/2008'
row += 2
worksheet.write(row, 0, txt)
-worksheet.data_validation(row, 1,
- {
- :validate => 'date',
- :criteria => 'between',
- :minimum => '2008-01-01T',
- :maximum => '2008-12-12T'
- })
+worksheet.data_validation(
+ row, 1,
+ {
+ :validate => 'date',
+ :criteria => 'between',
+ :minimum => '2008-01-01T',
+ :maximum => '2008-12-12T'
+ }
+)
-
#
# Example 8. Limiting input to a time in a fixed range.
#
txt = 'Enter a time between 6:00 and 12:00'
row += 2
worksheet.write(row, 0, txt)
-worksheet.data_validation(row, 1,
- {
- :validate => 'time',
- :criteria => 'between',
- :minimum => 'T06:00',
- :maximum => 'T12:00'
- })
+worksheet.data_validation(
+ row, 1,
+ {
+ :validate => 'time',
+ :criteria => 'between',
+ :minimum => 'T06:00',
+ :maximum => 'T12:00'
+ }
+)
-
#
# Example 9. Limiting input to a string greater than a fixed length.
#
txt = 'Enter a string longer than 3 characters'
row += 2
worksheet.write(row, 0, txt)
-worksheet.data_validation(row, 1,
- {
- :validate => 'length',
- :criteria => '>',
- :value => 3
- })
+worksheet.data_validation(
+ row, 1,
+ {
+ :validate => 'length',
+ :criteria => '>',
+ :value => 3
+ }
+)
-
#
# Example 10. Limiting input based on a formula.
#
txt = 'Enter a value if the following is true "=AND(F5=50,G5=60)"'
row += 2
worksheet.write(row, 0, txt)
-worksheet.data_validation(row, 1,
- {
- :validate => 'custom',
- :value => '=AND(F5=50,G5=60)'
- })
+worksheet.data_validation(
+ row, 1,
+ {
+ :validate => 'custom',
+ :value => '=AND(F5=50,G5=60)'
+ }
+)
-
#
# Example 11. Displaying and modify data validation messages.
#
txt = 'Displays a message when you select the cell'
row += 2
worksheet.write(row, 0, txt)
-worksheet.data_validation(row, 1,
- {
- :validate => 'integer',
- :criteria => 'between',
- :minimum => 1,
- :maximum => 100,
- :input_title => 'Enter an integer:',
- :input_message => 'between 1 and 100'
- })
+worksheet.data_validation(
+ row, 1,
+ {
+ :validate => 'integer',
+ :criteria => 'between',
+ :minimum => 1,
+ :maximum => 100,
+ :input_title => 'Enter an integer:',
+ :input_message => 'between 1 and 100'
+ }
+)
-
#
# Example 12. Displaying and modify data validation messages.
#
txt = 'Display a custom error message when integer isn\'t between 1 and 100'
row += 2
worksheet.write(row, 0, txt)
-worksheet.data_validation(row, 1,
- {
- :validate => 'integer',
- :criteria => 'between',
- :minimum => 1,
- :maximum => 100,
- :input_title => 'Enter an integer:',
- :input_message => 'between 1 and 100',
- :error_title => 'Input value is not valid!',
- :error_message => 'It should be an integer between 1 and 100'
- })
+worksheet.data_validation(
+ row, 1,
+ {
+ :validate => 'integer',
+ :criteria => 'between',
+ :minimum => 1,
+ :maximum => 100,
+ :input_title => 'Enter an integer:',
+ :input_message => 'between 1 and 100',
+ :error_title => 'Input value is not valid!',
+ :error_message => 'It should be an integer between 1 and 100'
+ }
+)
-
#
# Example 13. Displaying and modify data validation messages.
#
txt = 'Display a custom information message when integer isn\'t between 1 and 100'
row += 2
worksheet.write(row, 0, txt)
-worksheet.data_validation(row, 1,
- {
- :validate => 'integer',
- :criteria => 'between',
- :minimum => 1,
- :maximum => 100,
- :input_title => 'Enter an integer:',
- :input_message => 'between 1 and 100',
- :error_title => 'Input value is not valid!',
- :error_message => 'It should be an integer between 1 and 100',
- :error_type => 'information'
- })
+worksheet.data_validation(
+ row, 1,
+ {
+ :validate => 'integer',
+ :criteria => 'between',
+ :minimum => 1,
+ :maximum => 100,
+ :input_title => 'Enter an integer:',
+ :input_message => 'between 1 and 100',
+ :error_title => 'Input value is not valid!',
+ :error_message => 'It should be an integer between 1 and 100',
+ :error_type => 'information'
+ }
+)
workbook.close