Validation list with more than 255 chars

[chrstphdm]

I'm trying to create a data_validation list from an array with 10 elements.

I'm getting this error :

TypeError - can't convert String into Integer:
  writeexcel (1.0.4) lib/writeexcel/formula.rb:302:in `convert_string'
  writeexcel (1.0.4) lib/writeexcel/formula.rb:101:in `parse_tokens'
  writeexcel (1.0.4) lib/writeexcel/formula.rb:52:in `parse_formula'
  writeexcel (1.0.4) lib/writeexcel/data_validations.rb:278:in `pack_dv_formula'
  writeexcel (1.0.4) lib/writeexcel/data_validations.rb:120:in `dv_record'
...

This test throught the error at line 302 of formula.rb :

 exit "String in formula has more than 255 chars\n" if length > 255

Concatenation of my 10 array elements gave a string of more than 255 length chars.

[cxn03651]

Hi, chdem

when the sum of array's elements length is bigger than 255, writeexcel
reports error message and exit.
spreadsheet-writeexcel, which is original perl module, also shows same
behavior.

thank you for your report.

'exit' should be replaced with 'raise' in line 302 in formula.rb. I'll fix
it later.

cxn03651

2014-05-22 2:22 GMT+09:00 chdem [email protected]:

I'm trying to create a data_validation list from an array with 10 elements.

I'm getting this error :

TypeError - can't convert String into Integer: writeexcel (1.0.4) lib/writeexcel/formula.rb:302:in convert_string' writeexcel (1.0.4) lib/writeexcel/formula.rb:101:inparse_tokens' writeexcel (1.0.4) lib/writeexcel/formula.rb:52:in parse_formula' writeexcel (1.0.4) lib/writeexcel/data_validations.rb:278:inpack_dv_formula'
writeexcel (1.0.4) lib/writeexcel/data_validations.rb:120:in `dv_record'...

This test throught the error at line 302 of formula.rb :

exit "String in formula has more than 255 chars\n" if length > 255

Concatenation of my 10 array elements gave a string of more than 255
length chars.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/38
.

[chrstphdm]

Hi cxn03651,

So if your recommendation is to replace 'exit' by 'raise', there is no way to have a sum of array's elements length bigger than 255. In other words, we have a limit concerning the number of elements of a validation list. It's a pity.

Is it a 8 bits xls binary format limitation ?

I'm a ruby developper, but I have no knowledge about xls binary specs format. Is there any way to change this limitation ?

Perhaps, with your directives, I could make some corrections, make some tests and if I reach a solution, I could push it on the git.

Chdem

[cxn03651]

it seems excel has limitation on data validation.
see https://www.google.com/search?q=excel+data+validation+255

you have another way:

1. write allowed value not to array but to another worksheet.
2. specify data_validation parameter such as :value => 'sheet2!A1:A100'

[vincentpaca]

@cxn03651 the above doesn't actually work. Writing then referencing the source from another worksheet produces an empty sheet.

Adding the reference list to the same worksheet has some issues too, it shifts the reference as you move down in rows. For example, the item list is in B1:B3, adding a data_validation at A2 only returns the items listed in B2:B3. Adding a data_validation on A3 only returns the item in B3 and so on.

[cxn03651]

Hi, vincentpaca

Yes, that's bug!
I'll try to fix it, but it'll take long time because I'm busy until next spring.

If you use write_xlsx gem, it works well. :-)

# -*- coding: utf-8 -*-

require 'writeexcel'                            # require 'write_xlsx'

workbook  = WriteExcel.new('issue38.xlsx')      # workbook = WriteXLSX.new('issue38.xlsx')
worksheet = workbook.add_worksheet
worksheet.write('B1', 'a')
worksheet.write('B2', 'b')
worksheet.write('B3', 'c')
worksheet.data_validation('A2',
                      validate: 'list',
                      source:   'B1:B3'
                      )
workbook.close

[vincentpaca]

Yes. I've just tried that with write_xlsx and it works. :)