We read and alter a source XLSX containing a merged cell (horizontally across 5 cells) which can be read fine but fails when trying to write a copy of the file. Our current solution is to remove the merged cells.

TypeError: Symbol as array index
    from /usr/lib/ruby/gems/1.8/gems/rubyXL-1.2.7/lib/rubyXL/writer/worksheet_writer.rb:176:in `[]'
    from /usr/lib/ruby/gems/1.8/gems/rubyXL-1.2.7/lib/rubyXL/writer/worksheet_writer.rb:176:in `write'
    from /usr/lib/ruby/gems/1.8/gems/rubyXL-1.2.7/lib/rubyXL/writer/worksheet_writer.rb:175:in `each'
    from /usr/lib/ruby/gems/1.8/gems/rubyXL-1.2.7/lib/rubyXL/writer/worksheet_writer.rb:175:in `write'
    from /usr/lib/ruby/gems/1.8/gems/nokogiri-1.5.0/lib/nokogiri/xml/builder.rb:373:in `call'
    from /usr/lib/ruby/gems/1.8/gems/nokogiri-1.5.0/lib/nokogiri/xml/builder.rb:373:in `insert'
    from /usr/lib/ruby/gems/1.8/gems/nokogiri-1.5.0/lib/nokogiri/xml/builder.rb:357:in `method_missing'
    from /usr/lib/ruby/gems/1.8/gems/rubyXL-1.2.7/lib/rubyXL/writer/worksheet_writer.rb:174:in `write'
    from /usr/lib/ruby/gems/1.8/gems/nokogiri-1.5.0/lib/nokogiri/xml/builder.rb:373:in `call'
    from /usr/lib/ruby/gems/1.8/gems/nokogiri-1.5.0/lib/nokogiri/xml/builder.rb:373:in `insert'
    from /usr/lib/ruby/gems/1.8/gems/nokogiri-1.5.0/lib/nokogiri/xml/builder.rb:357:in `method_missing'
    from /usr/lib/ruby/gems/1.8/gems/rubyXL-1.2.7/lib/rubyXL/writer/worksheet_writer.rb:19:in `write'
    from /usr/lib/ruby/gems/1.8/gems/nokogiri-1.5.0/lib/nokogiri/xml/builder.rb:293:in `initialize'
    from /usr/lib/ruby/gems/1.8/gems/rubyXL-1.2.7/lib/rubyXL/writer/worksheet_writer.rb:18:in `new'
    from /usr/lib/ruby/gems/1.8/gems/rubyXL-1.2.7/lib/rubyXL/writer/worksheet_writer.rb:18:in `write'
    from /usr/lib/ruby/gems/1.8/gems/rubyXL-1.2.7/lib/rubyXL/workbook.rb:190:in `write'
    from /usr/lib/ruby/gems/1.8/gems/rubyzip-0.9.8/lib/zip/zip_streamable_stream.rb:12:in `get_output_stream'
    from /usr/lib/ruby/gems/1.8/gems/rubyzip-0.9.8/lib/zip/zip_file.rb:141:in `get_output_stream'
    from /usr/lib/ruby/gems/1.8/gems/rubyXL-1.2.7/lib/rubyXL/workbook.rb:190:in `write'
    from /usr/lib/ruby/gems/1.8/gems/nokogiri-1.5.0/lib/nokogiri/xml/builder.rb:274:in `each_with_index'
    from /usr/lib/ruby/gems/1.8/gems/rubyXL-1.2.7/lib/rubyXL/workbook.rb:188:in `each'
    from /usr/lib/ruby/gems/1.8/gems/rubyXL-1.2.7/lib/rubyXL/workbook.rb:188:in `each_with_index'
    from /usr/lib/ruby/gems/1.8/gems/rubyXL-1.2.7/lib/rubyXL/workbook.rb:188:in `write'
    from /usr/lib/ruby/gems/1.8/gems/rubyzip-0.9.8/lib/zip/zip_file.rb:90:in `open'
    from /usr/lib/ruby/gems/1.8/gems/rubyXL-1.2.7/lib/rubyXL/workbook.rb:107:in `write'

Eg. A cell like "Ben & Jerry's" gets parsed to:

Ben & Jerry's

I'd like to iterate above the rows of a worksheet with each or with each_index.

workbook[1].each_with_index do |x,xindex|
x.each_with_index do |y, yindex|
print(xindex, yindex, x[y].value)
puts
end
puts
end

This functionality is typically available in ruby csv-library and ruby-spreadsheet-library.
Typically this is implemeted by including Enumerable and inplementing the each method.

While parsing xlsx file, the dates are returned as Fixnum format.

The excel files for which the property "Use 1904 date system" is set to true from file options.
To get the original-date i have added,

Date.new(1904) + fixnum_int

But same code not works if the above property is not set. it gives the date 4 years greater than actual date.

Can you suggest me how to work around this issue or would i be able to distinguish the file 1900/1904 format, please guide. Thanks

on or about line 60, the TODO can be resolved by passing the view hash, resulting in each entry in the view hash being written as an attribute.

We found we had to add windowProtection="false" otherwise each sheet would be 'grouped' on windows and Mac which prevents manually adding filters unless the grouping is first removed.

              xml.sheetView(view) {

Here is screenshot of the spreadsheet:
http://dl.dropbox.com/u/1257390/rubyxl_parsingerror.png

Open the worksheet

# open worksheet
    sheet = book.worksheets[worksheet_index]

    sheet.sheet_data.map do |row| 
      ActiveRecord::Base.logger.info "Row: #{row}"
      row.map  do |c| 
        ActiveRecord::Base.logger.info "  C: #{c}"
        if c.is_a?(RubyXL::Cell) 
          ActiveRecord::Base.logger.info "    Value: #{c.value}"
        else 
          ActiveRecord::Base.logger.info "    Value: nil"
        end
      end
    end

Error Message

Row: [(0,0): ID, datatype = s, style_index = 6, (0,1): Bullet, datatype = s, style_index = 7, (0,2): Requirement, datatype = s, style_index = 8, (0,3): Appraisal, datatype = s, style_index = 9, (0,4): Comment, datatype = s, style_index = 8]
  C: #<RubyXL::Cell:0x007fab0c449440>
    Value: ID
  C: #<RubyXL::Cell:0x007fab0c448ba8>
    Value: Bullet
  C: #<RubyXL::Cell:0x007fab0c448040>
    Value: Requirement
  C: #<RubyXL::Cell:0x007fab0c447668>
    Value: Appraisal
  C: #<RubyXL::Cell:0x007fab0c446dd0>
    Value: Comment
Row: [(1,0): 4534, datatype = , style_index = 3, (1,1): , datatype = , style_index = 10, (1,2): DDDD, datatype = s, style_index = 11, (1,3): , datatype = , style_index = 12, (1,4): , datatype = , style_index = 13]
  C: #<RubyXL::Cell:0x007fab0c7f6688>
Completed 500 Internal Server Error in 1525ms

TypeError (can't convert Symbol into Integer):

C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.2.4/lib/rubyXL/parser.rb:229:in block in fill_worksheet': undefined methodblank?' for "33.75":String (NoMethodError)
from C:/Ruby192/lib/ruby/gems/1.9.1/gems/nokogiri-1.5.0-x86-mingw32/lib/nokogiri/xml/node_set.rb:239:in block in each' from C:/Ruby192/lib/ruby/gems/1.9.1/gems/nokogiri-1.5.0-x86-mingw32/lib/nokogiri/xml/node_set.rb:238:inupto'
from C:/Ruby192/lib/ruby/gems/1.9.1/gems/nokogiri-1.5.0-x86-mingw32/lib/nokogiri/xml/node_set.rb:238:in each' from C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.2.4/lib/rubyXL/parser.rb:218:infill_worksheet'
from C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.2.4/lib/rubyXL/parser.rb:87:in block in parse' from C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.2.4/lib/rubyXL/parser.rb:86:ineach_index'
from C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.2.4/lib/rubyXL/parser.rb:86:in parse' from C:/Dokumente und Einstellungen/josemi1/Eigene Dateien/NetBeansProjects/RubyXL/lib/main.rb:19:in

In office 2011 mac (10.7.2) with Office (14.1.3) the file reports as corrupt after being generated a repair allows you to open the file.

this works fine:

workbook = RubyXL::Parser.parse("public/xxx.xlsx",true)
workbook.write("public/xxx.xlsx")

but this breaks:

workbook = RubyXL::Parser.parse("public/xxx.xlsx",false)
workbook.write("public/xxx.xlsx")

write creates this error:
can't convert Symbol into Integer

using sample xlsx from here:
http://archive.msdn.microsoft.com/mss2008sdksamples/Release/ProjectReleases.aspx?ReleaseId=523

I realize this is probably more of a bug with open office than with this gem, but I noticed that if I create an excel file from open office and make one of the cells a number, everything crashes and burns. If I don't use numbers, or if I format the cells to be text first, everything is peachy.

I get a "Symbol as array index" error when I try to call .value on the offending cell, or when I use extract_data.

Not looking in to it too much since I don't need to support open office, but I guess if other people run into my same issue this is documented somewhere. Thank you for rubyXL!

Cannot get the parser to work when data_only is set to false:
workbook = RubyXL::Parser.parse("public/Sampleformslist.xlsx",false)

Get error:
undefined method `content' for nil:NilClass

Using this sample xlsx:
http://archive.msdn.microsoft.com/mss2008sdksamples/Release/ProjectReleases.aspx?ReleaseId=523

Otherwise, when data only=true everything seems to work great:)

I took a document made with MS Excel, that was saved as .xlsx

I opened in open office, then saved (as .xlsx).

I started getting this error:

1. Error:
   test_get_file_header(TestSpreadsheet2Cloud):
   TypeError: can't convert Symbol into Integer
   /home/hoguej/.rvm/gems/ruby-1.9.2-p180/gems/rubyXL-1.1.12/lib/rubyXL/cell.rb:32:in []' /home/hoguej/.rvm/gems/ruby-1.9.2-p180/gems/rubyXL-1.1.12/lib/rubyXL/cell.rb:32:inblock in is_date?'
   /home/hoguej/.rvm/gems/ruby-1.9.2-p180/gems/rubyXL-1.1.12/lib/rubyXL/cell.rb:32:in select' /home/hoguej/.rvm/gems/ruby-1.9.2-p180/gems/rubyXL-1.1.12/lib/rubyXL/cell.rb:32:inis_date?'
   /home/hoguej/.rvm/gems/ruby-1.9.2-p180/gems/rubyXL-1.1.12/lib/rubyXL/cell.rb:21:in value' /home/hoguej/.rvm/gems/ruby-1.9.2-p180/gems/rubyXL-1.1.12/lib/rubyXL/worksheet.rb:31:inblock (2 levels) in extr
   act_data'
   /home/hoguej/.rvm/gems/ruby-1.9.2-p180/gems/rubyXL-1.1.12/lib/rubyXL/worksheet.rb:31:in map' /home/hoguej/.rvm/gems/ruby-1.9.2-p180/gems/rubyXL-1.1.12/lib/rubyXL/worksheet.rb:31:inblock in extract_data'
   /home/hoguej/.rvm/gems/ruby-1.9.2-p180/gems/rubyXL-1.1.12/lib/rubyXL/worksheet.rb:31:in map' /home/hoguej/.rvm/gems/ruby-1.9.2-p180/gems/rubyXL-1.1.12/lib/rubyXL/worksheet.rb:31:inextract_data'
   /home/hoguej/dev/Spreadsheet2Cloud/lib/spreadsheet_2_cloud.rb:25:in headers' test/unit/test_spreadsheet_2_cloud.rb:46:intest_get_file_header'

If I skip the is_date? function, everything works fine.

Writing an XLSX file:

workbook = RubyXL::Workbook.new()
worksheet = RubyXL::Worksheet.new(workbook,'Blah'')
workbook.worksheets[0] = worksheet #overwrite initial 'Sheet1' worksheet

Make the first row 'Blah'

workbook.worksheets[0].add_cell(0,0,'Blah')
workbook.worksheets[0].change_row_bold(0,true) # text in Bold
workbook.worksheets[0].change_row_fill(0, 'BFBFBF') #sets color to grey
workbook.worksheets[0].change_row_font_color(0, '5A5A5A') #sets title in grey

Add some content from row 1 here....

Make row 1 bold

workbook.worksheets[0].change_row_bold(1,true)
workbook.worksheets[0].change_row_fill(1, '5A5A5A') #sets color to grey
workbook.worksheets[0].change_row_font_color(1, 'FFFFFF') #sets text white

Write to file

workbook.write(xlsx_filename)

Read same file

workbook = RubyXL::Parser.parse(xlsx_filename)

Error:
undefined method []' for nil:NilClass /usr/local/lib/ruby/gems/1.9.1/gems/rubyXL-1.2.5/lib/rubyXL/parser.rb:142:inblock in fill_styles' /usr/local/lib/ruby/gems/1.9.1/gems/rubyXL-1.2.5/lib/rubyXL/parser.rb:139:in each' /usr/local/lib/ruby/gems/1.9.1/gems/rubyXL-1.2.5/lib/rubyXL/parser.rb:139:infill_styles' /usr/local/lib/ruby/gems/1.9.1/gems/rubyXL-1.2.5/lib/rubyXL/parser.rb:72

When i enter 6/1/2012 excel auto formates it to a date and apply the value as 6/1/12 and then when i parse the spread sheet i get 41061 as a Fixnum returned

Hi. I tried to use this gem along with ruby-units (https://github.com/olbrich/ruby-units) in a Rails project, and saving an Excel document with RubyXL failed on line 140 in (rubyXL gem path)/lib/rubyXL/writer/styles_writer.rb (https://github.com/gilt/rubyXL/blob/master/lib/rubyXL/writer/styles_writer.rb#L140). I haven't investigated the issue too deeply, but it would be nice if those two gems could coexist peacefully. It seems that the core of the issue is that ruby-units defines a helper method named 'u', which is a name that rubyXL uses for its own purposes.

This is the exception I got:

ArgumentError: Invalid Unit Format
/usr/lib/ruby/gems/2.0.0/gems/ruby-units-1.4.4/lib/ruby_units/unit.rb:318:in `initialize'
/usr/lib/ruby/gems/2.0.0/gems/ruby-units-1.4.4/lib/ruby_units/unit.rb:352:in `initialize'
/usr/lib/ruby/gems/2.0.0/gems/ruby-units-1.4.4/lib/ruby_units/array.rb:7:in `new'
/usr/lib/ruby/gems/2.0.0/gems/ruby-units-1.4.4/lib/ruby_units/array.rb:7:in `to_unit'
/usr/lib/ruby/gems/2.0.0/gems/ruby-units-1.4.4/lib/ruby_units/object.rb:9:in `Unit'
/usr/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/writer/styles_writer.rb:140:in `block (5 levels) in write'
/usr/lib/ruby/gems/2.0.0/gems/nokogiri-1.6.0/lib/nokogiri/xml/builder.rb:391:in `call'
/usr/lib/ruby/gems/2.0.0/gems/nokogiri-1.6.0/lib/nokogiri/xml/builder.rb:391:in `insert'
/usr/lib/ruby/gems/2.0.0/gems/nokogiri-1.6.0/lib/nokogiri/xml/builder.rb:375:in `method_missing'
/usr/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/writer/styles_writer.rb:131:in `block (4 levels) in write'
/usr/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/writer/styles_writer.rb:127:in `upto'
/usr/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/writer/styles_writer.rb:127:in `block (3 levels) in write'
/usr/lib/ruby/gems/2.0.0/gems/nokogiri-1.6.0/lib/nokogiri/xml/builder.rb:391:in `call'
/usr/lib/ruby/gems/2.0.0/gems/nokogiri-1.6.0/lib/nokogiri/xml/builder.rb:391:in `insert'
/usr/lib/ruby/gems/2.0.0/gems/nokogiri-1.6.0/lib/nokogiri/xml/builder.rb:375:in `method_missing'
/usr/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/writer/styles_writer.rb:126:in `block (2 levels) in write'
/usr/lib/ruby/gems/2.0.0/gems/nokogiri-1.6.0/lib/nokogiri/xml/builder.rb:391:in `call'
/usr/lib/ruby/gems/2.0.0/gems/nokogiri-1.6.0/lib/nokogiri/xml/builder.rb:391:in `insert'
/usr/lib/ruby/gems/2.0.0/gems/nokogiri-1.6.0/lib/nokogiri/xml/builder.rb:375:in `method_missing'
/usr/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/writer/styles_writer.rb:22:in `block in write'
/usr/lib/ruby/gems/2.0.0/gems/nokogiri-1.6.0/lib/nokogiri/xml/builder.rb:293:in `initialize'
/usr/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/writer/styles_writer.rb:21:in `new'
/usr/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/writer/styles_writer.rb:21:in `write'
/usr/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/workbook.rb:145:in `block (2 levels) in write'
/usr/lib/ruby/gems/2.0.0/gems/rubyzip-0.9.9/lib/zip/zip_streamable_stream.rb:12:in `get_output_stream'
/usr/lib/ruby/gems/2.0.0/gems/rubyzip-0.9.9/lib/zip/zip_file.rb:159:in `get_output_stream'
/usr/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/workbook.rb:145:in `block in write'
/usr/lib/ruby/gems/2.0.0/gems/rubyzip-0.9.9/lib/zip/zip_file.rb:90:in `open'
/usr/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/workbook.rb:122:in `write'
(controller code, etc.)

By the way, I created an issue at ruby-units and seems that rubyXL also can't coexist with 'mathn', which is a dependency of ruby-units. The exception I get when I require 'mathn', but not 'ruby-units', looks like this:

NoMethodError: undefined method `chr' for (1691/26):Rational
/usr/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/cell.rb:390:in `block in convert_to_cell'
/usr/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/cell.rb:379:in `upto'
/usr/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/cell.rb:379:in `convert_to_cell'
/usr/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/writer/worksheet_writer.rb:33:in `block (2 levels) in write'
/usr/lib/ruby/gems/2.0.0/gems/nokogiri-1.6.0/lib/nokogiri/xml/builder.rb:391:in `call'
/usr/lib/ruby/gems/2.0.0/gems/nokogiri-1.6.0/lib/nokogiri/xml/builder.rb:391:in `insert'
/usr/lib/ruby/gems/2.0.0/gems/nokogiri-1.6.0/lib/nokogiri/xml/builder.rb:375:in `method_missing'
/usr/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/writer/worksheet_writer.rb:19:in `block in write'
/usr/lib/ruby/gems/2.0.0/gems/nokogiri-1.6.0/lib/nokogiri/xml/builder.rb:293:in `initialize'
/usr/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/writer/worksheet_writer.rb:18:in `new'
/usr/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/writer/worksheet_writer.rb:18:in `write'
/usr/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/workbook.rb:205:in `block (3 levels) in write'
/usr/lib/ruby/gems/2.0.0/gems/rubyzip-0.9.9/lib/zip/zip_streamable_stream.rb:12:in `get_output_stream'
/usr/lib/ruby/gems/2.0.0/gems/rubyzip-0.9.9/lib/zip/zip_file.rb:159:in `get_output_stream'
/usr/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/workbook.rb:205:in `block (2 levels) in write'
/usr/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/workbook.rb:203:in `each'
/usr/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/workbook.rb:203:in `each_with_index'
/usr/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/workbook.rb:203:in `block in write'
/usr/lib/ruby/gems/2.0.0/gems/rubyzip-0.9.9/lib/zip/zip_file.rb:90:in `open'
/usr/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/workbook.rb:122:in `write'
(controller code, etc.)

I have saved a sheet with the name "D&D-Catagories" using Excel.

While the expected sheet.sheet_name is "D&D-Catagories", but it is "D&D-Catagories" .

For an external api I would expect something like:

def sheet_name()
@sheet_name_internal.gsub("&lt;","<").gsub("&gt;",">").gsub("&quot;", """).gsub("&apos;","'").gsub("&amp;","&")
end

def sheet_name=(value)
@sheet_name_internal = value.gsub("&", "&amp;").gsub("<", "&lt;").gsub(">", "&gt;").gsub(""", "&quot;").gsub(","'", "&apos;")
end

I've tried to open a file with macros:

workbook = RubyXL::Parser.parse('/Users/me/myfile.xlsm')

I got this error:

/Users/me/.rvm/gems/ruby-1.9.2-p290/gems/rubyXL-1.1.10/lib/rubyXL/parser.rb:325:in `read': Is a directory - ./2011_10_26_14_17_38_0400/xl/drawings/_rels (Errno::EISDIR)
from /Users/prathe/.rvm/gems/ruby-1.9.2-p290/gems/rubyXL-1.1.10/lib/rubyXL/parser.rb:325:in `block in decompress'
from /Users/prathe/.rvm/gems/ruby-1.9.2-p290/gems/rubyXL-1.1.10/lib/rubyXL/parser.rb:324:in `each'
from /Users/prathe/.rvm/gems/ruby-1.9.2-p290/gems/rubyXL-1.1.10/lib/rubyXL/parser.rb:324:in `each_with_index'
from /Users/prathe/.rvm/gems/ruby-1.9.2-p290/gems/rubyXL-1.1.10/lib/rubyXL/parser.rb:324:in `decompress'
from /Users/prathe/.rvm/gems/ruby-1.9.2-p290/gems/rubyXL-1.1.10/lib/rubyXL/parser.rb:38:in `parse'
from xls.rb:11:in `<main>'

I'm using version 1.1.10 of the gem.

› ruby --version
ruby 1.9.2p290 (2011-07-09 revision 32553) [x86_64-darwin11.0.1]

http://stackoverflow.com/questions/16767009/reading-worksheet-name-when-using-rubyxl-to-load-file-created-by-axlsx

I am facing some problems reading excel files created with the Axlsx module via RubyXL. The sample code is this

require 'axlsx'
require 'RubyXL'

#Create the Excel doc with Axlsx with two sheets named My Sheet1 and My Sheet 2
Axlsx::Package.new do |p|
  p.workbook.add_worksheet(:name => "My Sheet1") do |sheet|
    sheet.add_row ["Simple Pie Chart"]
    %w(first second third).each { |label| sheet.add_row [label, rand(24)+1] }
  end
  p.workbook.add_worksheet(:name => "My Sheet 2") do |sheet|
    sheet.add_row ["Simple Pie Chart"]
    %w(first second third).each { |label| sheet.add_row [label, rand(24)+1] }
  end
  p.serialize('simple.xlsx')
end

#Parse the above created Excel via RubyXL and print the names of the two sheets. This comes up blank
workbook = RubyXL::Parser.parse("simple.xlsx")
workbook.worksheets.each do |worksheet|
  puts "Worksheet is #{worksheet.sheet_name}"
end

The problem is that the worksheet names come up blank. If I after having created with axlsx open up the created Excel with MS Excel and Save it, then the RubyXL parser is able to read the sheet names correctly. It seems like MS Excel is fixing the document somehow.

Am I missing something when creating the Excel via Axlsx?

I am using axlsx version 1.3.6 and rubyXL 1.2.10.

I also tried adding p.use_shared_strings = true before serializing with axlsx.

And then instead of this in parser.rb of rubyXL.

#sheet_names, dimensions
def Parser.create_matrix(wb,i, files)
  sheet_names = files['app'].css('TitlesOfParts vt|vector vt|lpstr').children
  sheet = Worksheet.new(wb,sheet_names[i].to_s,[])

I replaced it with

#sheet_names, dimensions
def Parser.create_matrix(wb,i, files)
  sheet_names = []
  files['workbook'].css('sheet').each do |sheet|
    sheet_names.push sheet.attribute('name').to_s
  end
  sheet = Worksheet.new(wb,sheet_names[i].to_s,[])

Is that the right fix?

I'm using REE and Rails 2.3.x, had to install the andand gem to prevent errors when parsing spreadsheets (the "andand" idiom is used by RubyXL::Cell#is_date?)

Works in 1.9 and 2.0 but not 1.8.7

╰─$ gem install rubyXL
Fetching: rubyXL-1.2.10.gem (100%)
Successfully installed rubyXL-1.2.10
Parsing documentation for rubyXL-1.2.10
Installing ri documentation for rubyXL-1.2.10
1 gem installed
╰─$ irb

>> require 'rubyXL'
=> []
>> workbook = RubyXL::Workbook.new
=> #<RubyXL::Workbook:0x108a7c9a8 @defined_names=nil, @calc_chain=nil, @application="Microsoft Macintosh Excel", @printer_settings=nil, @cell_xfs={:xf=>{:attributes=>{:borderId=>0, :xfId=>0, :numFmtId=>0, :fillId=>0, :fontId=>0}}, :attributes=>{:count=>1}}, @external_links=nil, @size=0, @num_fmts_hash=nil, @shared_strings_XML=nil, @shared_strings=nil, @company="", @worksheet_rels=nil, @borders={"0"=>{:count=>1, :border=>{:top=>{}, :left=>{}, :right=>{}, :diagonal=>{}, :bottom=>{}}}}, @modified_at="2013-07-23T11:45:32Z", @date1904=false, @num_fmts=nil, @colors=nil, @cell_styles={:cellStyle=>{:attributes=>{:xfId=>0, :builtinId=>0, :name=>"Normal"}}, :attributes=>{:count=>1}}, @creator=nil, @drawings=nil, @fills={"1"=>{:fill=>{:patternFill=>{:attributes=>{:patternType=>"gray125"}}}, :count=>0}, "0"=>{:fill=>{:patternFill=>{:attributes=>{:patternType=>"none"}}}, :count=>1}}, @modifier=nil, @column_lookup_hash={}, @num_strings=0, @appversion="12.0000", @created_at="2013-07-23T11:45:32Z", @macros=nil, @cell_style_xfs={:xf=>{:attributes=>{:borderId=>0, :numFmtId=>0, :fillId=>0, :fontId=>0}}, :attributes=>{:count=>1}}, @filepath=nil, @worksheets=[#<RubyXL::Worksheet:0x108a7c7f0 @sheet_view={:attributes=>{:zoomScaleNormal=>100, :tabSelected=>1, :workbookViewId=>0, :view=>"normalLayout", :zoomScale=>100}}, @sheet_name="Sheet1", @row_styles={}, @cols=[], @legacy_drawing=nil, @merged_cells=[], @extLst=nil, @workbook=#<RubyXL::Workbook:0x108a7c9a8 ...>, @sheet_data=[[nil]]>], @style_corrector=nil, @fonts={"1"=>{:count=>0, :font=>{:sz=>{:attributes=>{:val=>8}}, :name=>{:attributes=>{:val=>"Verdana"}}}}, "0"=>{:count=>1, :font=>{:sz=>{:attributes=>{:val=>10}}, :name=>{:attributes=>{:val=>"Verdana"}}}}}>
>> workbook.worksheets << RubyXL::Worksheet.new(workbook, 'Sheet2')
=> [#<RubyXL::Worksheet:0x108a7c7f0 @sheet_view={:attributes=>{:zoomScaleNormal=>100, :tabSelected=>1, :workbookViewId=>0, :view=>"normalLayout", :zoomScale=>100}}, @sheet_name="Sheet1", @row_styles={}, @cols=[], @legacy_drawing=nil, @merged_cells=[], @extLst=nil, @workbook=#<RubyXL::Workbook:0x108a7c9a8 @defined_names=nil, @calc_chain=nil, @application="Microsoft Macintosh Excel", @printer_settings=nil, @cell_xfs={:xf=>{:attributes=>{:borderId=>0, :xfId=>0, :numFmtId=>0, :fillId=>0, :fontId=>0}}, :attributes=>{:count=>1}}, @external_links=nil, @size=0, @num_fmts_hash=nil, @shared_strings_XML=nil, @shared_strings=nil, @company="", @worksheet_rels=nil, @borders={"0"=>{:count=>1, :border=>{:top=>{}, :left=>{}, :right=>{}, :diagonal=>{}, :bottom=>{}}}}, @modified_at="2013-07-23T11:45:32Z", @date1904=false, @num_fmts=nil, @colors=nil, @cell_styles={:cellStyle=>{:attributes=>{:xfId=>0, :builtinId=>0, :name=>"Normal"}}, :attributes=>{:count=>1}}, @creator=nil, @drawings=nil, @fills={"1"=>{:fill=>{:patternFill=>{:attributes=>{:patternType=>"gray125"}}}, :count=>0}, "0"=>{:fill=>{:patternFill=>{:attributes=>{:patternType=>"none"}}}, :count=>1}}, @modifier=nil, @column_lookup_hash={}, @num_strings=0, @appversion="12.0000", @created_at="2013-07-23T11:45:32Z", @macros=nil, @cell_style_xfs={:xf=>{:attributes=>{:borderId=>0, :numFmtId=>0, :fillId=>0, :fontId=>0}}, :attributes=>{:count=>1}}, @filepath=nil, @worksheets=[...], @style_corrector=nil, @fonts={"1"=>{:count=>0, :font=>{:sz=>{:attributes=>{:val=>8}}, :name=>{:attributes=>{:val=>"Verdana"}}}}, "0"=>{:count=>1, :font=>{:sz=>{:attributes=>{:val=>10}}, :name=>{:attributes=>{:val=>"Verdana"}}}}}>, @sheet_data=[[nil]]>, #<RubyXL::Worksheet:0x108a66810 @sheet_view={:attributes=>{:zoomScaleNormal=>100, :tabSelected=>1, :workbookViewId=>0, :view=>"normalLayout", :zoomScale=>100}}, @sheet_name="Sheet2", @row_styles={}, @cols=[], @legacy_drawing=nil, @merged_cells=[], @extLst=nil, @workbook=#<RubyXL::Workbook:0x108a7c9a8 @defined_names=nil, @calc_chain=nil, @application="Microsoft Macintosh Excel", @printer_settings=nil, @cell_xfs={:xf=>{:attributes=>{:borderId=>0, :xfId=>0, :numFmtId=>0, :fillId=>0, :fontId=>0}}, :attributes=>{:count=>1}}, @external_links=nil, @size=0, @num_fmts_hash=nil, @shared_strings_XML=nil, @shared_strings=nil, @company="", @worksheet_rels=nil, @borders={"0"=>{:count=>1, :border=>{:top=>{}, :left=>{}, :right=>{}, :diagonal=>{}, :bottom=>{}}}}, @modified_at="2013-07-23T11:45:32Z", @date1904=false, @num_fmts=nil, @colors=nil, @cell_styles={:cellStyle=>{:attributes=>{:xfId=>0, :builtinId=>0, :name=>"Normal"}}, :attributes=>{:count=>1}}, @creator=nil, @drawings=nil, @fills={"1"=>{:fill=>{:patternFill=>{:attributes=>{:patternType=>"gray125"}}}, :count=>0}, "0"=>{:fill=>{:patternFill=>{:attributes=>{:patternType=>"none"}}}, :count=>1}}, @modifier=nil, @column_lookup_hash={}, @num_strings=0, @appversion="12.0000", @created_at="2013-07-23T11:45:32Z", @macros=nil, @cell_style_xfs={:xf=>{:attributes=>{:borderId=>0, :numFmtId=>0, :fillId=>0, :fontId=>0}}, :attributes=>{:count=>1}}, @filepath=nil, @worksheets=[...], @style_corrector=nil, @fonts={"1"=>{:count=>0, :font=>{:sz=>{:attributes=>{:val=>8}}, :name=>{:attributes=>{:val=>"Verdana"}}}}, "0"=>{:count=>1, :font=>{:sz=>{:attributes=>{:val=>10}}, :name=>{:attributes=>{:val=>"Verdana"}}}}}>, @sheet_data=[[nil]]>]
>> workbook.write '/Users/studm/Desktop/text.xlsx'
=> "/Users/studm/Desktop/text.xlsx"

Hello,

I am having an issue with parsing a xlsx file (4999 rows 24 cols). I believe it is a parsing issues on line 243 rubyXL/parser.rb.
If the number is lower than E-005 it throws an error.

For example on my file on row 2350 col N I had a value 0.00009 that thrown the error: invalid value for Integer(): "9E-005". So I had to change it to 0.00119 and the error was "resolved".

I am calling the parser from this simple line: workbook = RubyXL::Parser.parse("#{file.store_path}").

Please feel free to shout at me if I am doing something stupid I haven't noticed :)

Thanks for the great work!! Rock on!

Something trivial like that throws an exception:

task :error => :environment do
workbook = RubyXL::Workbook.new
workbook.write("error.xlsx")
workbook = RubyXL::Parser.parse("error.xlsx")
workbook.write("error.xlsx")
end

That's the output:

"\n<workbook xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships\" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main\"><fileVersion rupBuild="4505" lastEdited="4" appName="xl" lowestEdited="4"/><workbookPr showInkAnnotation="0" date1904="0" autoCompressPictures="0"/><workbookView tabRatio="500" xWindow="-20" windowWidth="21600" yWindow="-20" windowHeight="13340"/><sheet name="Sheet1" sheetId="1" r:id="rId1"/><calcPr concurrentCalc="0" calcId="130407"/><ext xmlns:mx="http://schemas.microsoft.com/office/mac/excel/2008/main\" uri="http://schemas.microsoft.com/office/mac/excel/2008/main\"><mx:ArchID Flags="2"/>\n"

<mx:ArchID Flags="2"/>
"\n<workbook xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships\" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main\"><fileVersion rupBuild="4505" lastEdited="4" appName="xl" lowestEdited="4"/><workbookPr showInkAnnotation="0" date1904="0" autoCompressPictures="0"/><workbookView tabRatio="500" xWindow="-20" windowWidth="21600" yWindow="-20" windowHeight="13340"/><sheet name="Sheet1" sheetId="1" r:id="rId1"/><calcPr concurrentCalc="0" calcId="130407"/><ext xmlns:mx="http://schemas.microsoft.com/office/mac/excel/2008/main\" uri="http://schemas.microsoft.com/office/mac/excel/2008/main\"><mx:ArchID Flags="2"/>\n"

<mx:ArchID Flags="2"/>
rake aborted!
Symbol as array index
/Library/Ruby/Gems/1.8/gems/rubyXL-1.1.10/lib/rubyXL/writer/styles_writer.rb:132:in []' /Library/Ruby/Gems/1.8/gems/rubyXL-1.1.10/lib/rubyXL/writer/styles_writer.rb:132:inwrite'
/Library/Ruby/Gems/1.8/gems/nokogiri-1.5.0/lib/nokogiri/xml/builder.rb:373:in call' /Library/Ruby/Gems/1.8/gems/nokogiri-1.5.0/lib/nokogiri/xml/builder.rb:373:ininsert'
/Library/Ruby/Gems/1.8/gems/nokogiri-1.5.0/lib/nokogiri/xml/builder.rb:357:in method_missing' /Library/Ruby/Gems/1.8/gems/rubyXL-1.1.10/lib/rubyXL/writer/styles_writer.rb:131:inwrite'
/Library/Ruby/Gems/1.8/gems/rubyXL-1.1.10/lib/rubyXL/writer/styles_writer.rb:127:in upto' /Library/Ruby/Gems/1.8/gems/rubyXL-1.1.10/lib/rubyXL/writer/styles_writer.rb:127:inwrite'
/Library/Ruby/Gems/1.8/gems/nokogiri-1.5.0/lib/nokogiri/xml/builder.rb:373:in call' /Library/Ruby/Gems/1.8/gems/nokogiri-1.5.0/lib/nokogiri/xml/builder.rb:373:ininsert'
/Library/Ruby/Gems/1.8/gems/nokogiri-1.5.0/lib/nokogiri/xml/builder.rb:357:in method_missing' /Library/Ruby/Gems/1.8/gems/rubyXL-1.1.10/lib/rubyXL/writer/styles_writer.rb:126:inwrite'
/Library/Ruby/Gems/1.8/gems/nokogiri-1.5.0/lib/nokogiri/xml/builder.rb:373:in call' /Library/Ruby/Gems/1.8/gems/nokogiri-1.5.0/lib/nokogiri/xml/builder.rb:373:ininsert'
/Library/Ruby/Gems/1.8/gems/nokogiri-1.5.0/lib/nokogiri/xml/builder.rb:357:in method_missing' /Library/Ruby/Gems/1.8/gems/rubyXL-1.1.10/lib/rubyXL/writer/styles_writer.rb:22:inwrite'
/Library/Ruby/Gems/1.8/gems/nokogiri-1.5.0/lib/nokogiri/xml/builder.rb:293:in initialize' /Library/Ruby/Gems/1.8/gems/rubyXL-1.1.10/lib/rubyXL/writer/styles_writer.rb:21:innew'
/Library/Ruby/Gems/1.8/gems/rubyXL-1.1.10/lib/rubyXL/writer/styles_writer.rb:21:in write' /Library/Ruby/Gems/1.8/gems/rubyXL-1.1.10/lib/rubyXL/workbook.rb:119:inwrite'
/Library/Ruby/Gems/1.8/gems/rubyzip-0.9.4/lib/zip/zip.rb:1634:in get_output_stream' /Library/Ruby/Gems/1.8/gems/rubyzip-0.9.4/lib/zip/zip.rb:1456:inget_output_stream'
/Library/Ruby/Gems/1.8/gems/rubyXL-1.1.10/lib/rubyXL/workbook.rb:119:in write' /Library/Ruby/Gems/1.8/gems/rubyzip-0.9.4/lib/zip/zip.rb:1413:inopen'
/Library/Ruby/Gems/1.8/gems/rubyXL-1.1.10/lib/rubyXL/workbook.rb:96:in `write'

When rubyXL crashes, no one removes dir 2013_09_25_10_32_22_0400. This should be fixed... Can I help?

No such file or directory - C:\data/2013_09_25_10_32_22_0400/docProps/app.xml
    C:/Ruby200_32/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/parser.rb:331:in `initialize'
    C:/Ruby200_32/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/parser.rb:331:in `open'
    C:/Ruby200_32/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/parser.rb:331:in `decompress'
    C:/Ruby200_32/lib/ruby/gems/2.0.0/gems/rubyXL-1.2.10/lib/rubyXL/parser.rb:47:in `parse'
    C:/root/Projects/StatTree/StatTree2.rb:294:in `parse_xls'
    C:/root/Projects/StatTree/StatTree2.rb:445:in `parse_file'
    C:/root/Projects/StatTree/StatTree2.rb:499:in `block in <top (required)>'
    C:/root/Projects/StatTree/StatTree2.rb:495:in `each'
    C:/root/Projects/StatTree/StatTree2.rb:495:in `<top (required)>'
    -e:1:in `load'
    -e:1:in `<main>'

Apparently the revised RubyXL::Cell#is_date? function expects an array of number formats, but my users are encountering spreadsheets that only have a single number format, which throws an exception.

BTW, I'm not enough of an Excel expert to know if those spreadsheets should be considered corrupt; FWIW we haven't encountered any other issues with them.

Revising the #is_date? method as follows appears to fix the issue (though perhaps a better approach would be to ensure that the number formats data is always generated as an array).

    def is_date?
      if !@value.is_a?(String)
        if @workbook.num_fmts
          num_fmt_id = xf_id()[:numFmtId]
          tmp_num_fmt = @workbook.num_fmts[:numFmt].select { |f| f[:attributes][:numFmtId] == num_fmt_id }[0]
          num_fmt = (tmp_num_fmt &&tmp_num_fmt[:attributes] && tmp_num_fmt[:attributes][:formatCode]) ? tmp_num_fmt[:attributes][:formatCode] : nil
          if num_fmt && workbook.date_num_fmt?(num_fmt)
            return true
          end
        end
      end
      return false
    end

Sorry I'm not submitting this as a pull request and corresponding test, I'm only getting up to speed with rspec and am not able to create a proper failing test case with it as of yet. Please let me know if you'd like a copy of the problematic spreadsheet and I'll send it along.

Thanks again for all your work on this terrific gem!

Although it is possible to vertical alignment, it is not possible to set whether the text should wrap

Hi,

I've downloaded latest Excel viewer to check if generated file is recognized. It is not. Excel Viewer gives error that the file is incorrect.

File is created using RubyXL::Workbook.new.write 'text.xlsx'

Openoffice and ZohoSheet recognizes file properly.

We load an existing XSLX with multiple named worksheets.
We are unable to read the sheet_name in code and these names are not retained when the workbook is saved. I have verified that manually copying the sheets fragment from the source back into the xl/workbook.xml file corrects the issue, but this doesn't occur from within RubyXL.

Dear all,
The only annoying limitation to convert html to excel is the multiple worksheets tabs.
Would it be possible that rubyxl is able to convert html to excel format enabling the multiple sheets ?
Have a good day,
Antoine

I received the following error while trying to parse a .xlsx file on RHEL6, using Ruby 1.9.3. I did not get this error when I ran the same script on the same file on OSX 10.7:

1.9.3
~hoffman/.rvm/gems/ruby-1.9.3-p125/gems/rubyXL-1.2.7/lib/rubyXL/parser.rb:357:in initialize': No such file or directory - ../data/2012_04_10_16_22_50_0400/xl/drawings/_rels (Errno::ENOENT) from /home/dlib/hoffman/.rvm/gems/ruby-1.9.3-p125/gems/rubyXL-1.2.7/lib/rubyXL/parser.rb:357:innew'
from /home/dlib/hoffman/.rvm/gems/ruby-1.9.3-p125/gems/rubyXL-1.2.7/lib/rubyXL/parser.rb:357:in decompress' from /home/dlib/hoffman/.rvm/gems/ruby-1.9.3-p125/gems/rubyXL-1.2.7/lib/rubyXL/parser.rb:49:inparse'

I was able to solve this locally by adding the following line above line 357 in parser.rb:

Dir.mkdir(drawings_path)

workbook = RubyXL::Parser.parse("execl_workbook.xlsx")
insetad of:
sheet = workbook[0]

use:
sheet = workbook.name("test1")

to open the worksheet in the workbook with the sheet named "test1".

Hi, I've used RubyXL to generate a simple excel doc.

This file cannot be previewed using quick look, but when you open it using Excel and saving it again, it becomes previewable.

I think this might be there's some issue in the underlying xml files. I've tried to git them and view the differences before and after save, but couldn't drill down the issue because Excel changes the ordering of the XML by a significant amount.

Not being able to preview them on mac is a small issue, but the bigger issue might be that the generated excel files are not "valid" and breaks on some parsers.

Just wanna check if this is a common issue other people are facing as well.

Cleanup created temporary directories when they are no longer needed.

I suggest the following approach:

workbook = RubyXL::Parser.parse("path/to/Excel/file.xlsx")
do work here ...

workbook.close() # cleanup the directory generated by rubyXL.

Preferably, introduce a path where the temporary directories are created. Other people get confused, when they find them among the Excel sheets.

I have this error in Rails 3.0.9 (both Ruby 1.8.7 and 1.9.2):
Errno::EACCES in IndexController Permission denied - /.zip

My code:
workbook = RubyXL::Workbook.new workbook.worksheets << RubyXL::Worksheet.new("Test") workbook.worksheets[0].add_cell(0,0,'test') file = "#{Rails.root}/tmp/test.xls" workbook.write(file) send_file file

Gemfile:
gem 'rubyXL', :require => 'rubyXL' gem 'rubyzip', :require => 'zip/zip' gem 'nokogiri'

The temporary directory name created by Parse.decompress only changes once per second, causing problems when parsing multiple files that can be processed more quickly than that. One quick fix is to change line 314 of parser.rb from

  dir_path = File.join(File.dirname(dir_path), make_safe_name(Time.now.to_s))

to

  dir_path = File.join(File.dirname(dir_path), make_safe_name(File.basename(dir_path) + Time.now.to_s))

Without nokogiri or zip installed any attempt to require rubyXL will return a not found error.

gem install rubyXL

ignores checking for this requirement

gem install nokogiri zip

solves the dependency issues but takes a while to track down if not familiar with ruby -- would update the gemspec file but I'm part of that group that isn't so familiar with ruby

Not sure whether it's really an undefined method or the documentation just needs updating, but the documentation explicitly mentions about the existence of this method.

I added rubyXL to my Gemfile in a project that uses bundler. When I load my gems environment, I get the following error:

[...]/rubyXL-1.2.4/lib/rubyXL/writer/content_types_writer.rb:6:in `require': cannot load such file -- nokogiri (LoadError)

Seems like rubyXL depends on nokogiri, but doesn't actually have it as a dependency in the gemspec.

Using ruby 1.9.3, rubygems 1.8.10.

I tried to open an *.xlsm file as follows:

 encoding: utf-8
require 'rubyXL'

file = "/home/serge/Development/Ruby/drafts/lib/test.xlsm"
workbook = RubyXL::Parser.parse(file)
puts workbook[1].sheet_data[3][9]

And got the below error:

/home/serge/.rvm/gems/ruby-1.9.3-p0/gems/rubyXL-1.2.5/lib/rubyXL/parser.rb:352:in `read': Is a directory - /home/serge/Development/Ruby/drafts/lib/2012_03_19_22_07_52_0100/xl/drawings/_rels (Errno::EISDIR)
    from /home/serge/.rvm/gems/ruby-1.9.3-p0/gems/rubyXL-1.2.5/lib/rubyXL/parser.rb:352:in `block in decompress'
    from /home/serge/.rvm/gems/ruby-1.9.3-p0/gems/rubyXL-1.2.5/lib/rubyXL/parser.rb:351:in `each'
    from /home/serge/.rvm/gems/ruby-1.9.3-p0/gems/rubyXL-1.2.5/lib/rubyXL/parser.rb:351:in `each_with_index'
    from /home/serge/.rvm/gems/ruby-1.9.3-p0/gems/rubyXL-1.2.5/lib/rubyXL/parser.rb:351:in `decompress'
    from /home/serge/.rvm/gems/ruby-1.9.3-p0/gems/rubyXL-1.2.5/lib/rubyXL/parser.rb:45:in `parse'
    from roo_read_file.rb:5:in `<main>'

I'm on Ubuntu 10.04, Ruby 1.9.3. Any idea? Thanks you.

Is there a way to create a newline within a cell? I've tried \n, \n, \r\n,

Nothing so far...It either removes the newline and puts it all on one line, or outputs the characters instead.

Apologies if this is an Excel issue, not a rubyXL issue.

Hi guys,

I've got those xls files generated through a 3rd party webservice,
they don't have any app.xml core.xml styles.xml in their structure, only those :
inflating: xl/workbook.xml
extracting: _rels/.rels
inflating: xl/worksheets/sheet.xml
inflating: xl/_rels/workbook.xml.rels
inflating: [Content_Types].xml
Excel opens it fine but RubyXL won't :(

here's the file : https://dl.dropbox.com/u/6774414/Move_2012_11_12_00_00_00_Scuba%20diving.xlsx

Any idea?

We have a source XLSX which can be read but crashes when trying to write (even without alteration)
The cell formatting in some cells is '0' ie cell > format cells > numbers > format code contains the number zero.
While this is likely a bug in our spreadsheet, the code should not crash either.

Our suggested fix was to change the line to reference fmt_num.to_s

NoMethodError: private method `split' called for 0:Fixnum
    from /usr/lib/ruby/gems/1.8/gems/rubyXL-1.2.7/lib/rubyXL/workbook.rb:240:in `is_date_format?'
    from /usr/lib/ruby/gems/1.8/gems/rubyXL-1.2.7/lib/rubyXL/workbook.rb:227:in `date_num_fmt?'
    from /usr/lib/ruby/gems/1.8/gems/rubyXL-1.2.7/lib/rubyXL/cell.rb:35:in `is_date?'
    from /usr/lib/ruby/gems/1.8/gems/rubyXL-1.2.7/lib/rubyXL/cell.rb:21:in `value'
    from /usr/lib/ruby/gems/1.8/gems/rubyXL-1.2.7/lib/rubyXL/writer/worksheet_writer.rb:155:in `write'
    from /usr/lib/ruby/gems/1.8/gems/nokogiri-1.5.0/lib/nokogiri/xml/builder.rb:373:in `call'
    from /usr/lib/ruby/gems/1.8/gems/nokogiri-1.5.0/lib/nokogiri/xml/builder.rb:373:in `insert'
    from /usr/lib/ruby/gems/1.8/gems/nokogiri-1.5.0/lib/nokogiri/xml/builder.rb:357:in `method_missing'
    from /usr/lib/ruby/gems/1.8/gems/rubyXL-1.2.7/lib/rubyXL/writer/worksheet_writer.rb:140:in `write'
    from /usr/lib/ruby/gems/1.8/gems/nokogiri-1.5.0/lib/nokogiri/xml/builder.rb:274:in `each_with_index'
    from /usr/lib/ruby/gems/1.8/gems/rubyXL-1.2.7/lib/rubyXL/writer/worksheet_writer.rb:131:in `each'
    from /usr/lib/ruby/gems/1.8/gems/rubyXL-1.2.7/lib/rubyXL/writer/worksheet_writer.rb:131:in `each_with_index'
    from /usr/lib/ruby/gems/1.8/gems/rubyXL-1.2.7/lib/rubyXL/writer/worksheet_writer.rb:131:in `write'
    from /usr/lib/ruby/gems/1.8/gems/nokogiri-1.5.0/lib/nokogiri/xml/builder.rb:373:in `call'
    from /usr/lib/ruby/gems/1.8/gems/nokogiri-1.5.0/lib/nokogiri/xml/builder.rb:373:in `insert'
    from /usr/lib/ruby/gems/1.8/gems/nokogiri-1.5.0/lib/nokogiri/xml/builder.rb:357:in `method_missing'

ruby 1.9.2p290 (2011-07-09) [i386-mingw32]
C:/Ruby192/lib/ruby/site_ruby/1.9.1/rubygems/source_index.rb:123: warning: C:/Dokumente und Einstellungen/josemi1/.gem/ruby/1.9.1:C:/Ruby192/lib/ruby/gems/1.9.1/specifications: Invalid argument
C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/writer/styles_writer.rb:127: warning: shadowing outer local variable - i
C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/writer/styles_writer.rb:172: warning: shadowing outer local variable - i
C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/writer/styles_writer.rb:205: warning: shadowing outer local variable - i
C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/writer/shared_strings_writer.rb:28: warning: mismatched indentations at 'end' with 'class' at 10
C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/writer/worksheet_writer.rb:83: warning: shadowing outer local variable - col
C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/writer/worksheet_writer.rb:104: warning: shadowing outer local variable - row
C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/writer/worksheet_writer.rb:104: warning: shadowing outer local variable - i
C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/worksheet.rb:358: warning: shadowing outer local variable - i
C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/worksheet.rb:1221: warning: shadowing outer local variable - i
C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/worksheet.rb:1362: warning: shadowing outer local variable - i
C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/worksheet.rb:1436: warning: shadowing outer local variable - i
C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/worksheet.rb:1469: warning: shadowing outer local variable - index
C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/parser.rb:326:in read': Permission denied - Y:/Data/TestRubyXL/2011_12_20_11_35_47_0100/xl/drawings/_rels (Errno::EACCES) from C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/parser.rb:326:inblock in decompress'
from C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/parser.rb:325:in each' from C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/parser.rb:325:ineach_with_index'
from C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/parser.rb:325:in decompress' from C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/parser.rb:39:inparse'
from C:/Dokumente und Einstellungen/josemi1/Eigene Dateien/NetBeansProjects/RubyXL/lib/main.rb:3:in `

The reason for the error seems to be that the parser tries to read the diretory .../xl/drawings/_rels and to open it as a file in:
'''
dir.each_with_index do |draw,i|
files['drawings'][i+1] = File.read(File.join(drawings_path,draw))
end
'''
As a workaround, I found the following solution to skip any directories with next in parser.rb line 325 to skip any directories:
'''
if File.directory?(File.join(dir_path,'xl','drawings'))
files['drawings'] = {}
drawings_path = File.join(dir_path,'xl','drawings')
dir = Dir.new(drawings_path).entries.reject {|f| [".", "..", ".DS_Store"].include? f}
dir.each_with_index do |draw,i|
puts(drawings_path,draw) # Added by josemic
next unless File.file?(File.join(drawings_path,draw)) # Added by josemic
files['drawings'][i+1] = File.read(File.join(drawings_path,draw))
end
end
''''
When run with the 'puts'-line command above the following output is given:

ruby 1.9.2p290 (2011-07-09) [i386-mingw32]
C:/Ruby192/lib/ruby/site_ruby/1.9.1/rubygems/source_index.rb:123: warning: C:/Dokumente und Einstellungen/josemi1/.gem/ruby/1.9.1:C:/Ruby192/lib/ruby/gems/1.9.1/specifications: Invalid argument
C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/writer/styles_writer.rb:127: warning: shadowing outer local variable - i
C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/writer/styles_writer.rb:172: warning: shadowing outer local variable - i
C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/writer/styles_writer.rb:205: warning: shadowing outer local variable - i
C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/writer/shared_strings_writer.rb:28: warning: mismatched indentations at 'end' with 'class' at 10
C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/writer/worksheet_writer.rb:83: warning: shadowing outer local variable - col
C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/writer/worksheet_writer.rb:104: warning: shadowing outer local variable - row
C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/writer/worksheet_writer.rb:104: warning: shadowing outer local variable - i
C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/worksheet.rb:358: warning: shadowing outer local variable - i
C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/worksheet.rb:1221: warning: shadowing outer local variable - i
C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/worksheet.rb:1362: warning: shadowing outer local variable - i
C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/worksheet.rb:1436: warning: shadowing outer local variable - i
C:/Ruby192/lib/ruby/gems/1.9.1/gems/rubyXL-1.1.12/lib/rubyXL/worksheet.rb:1469: warning: shadowing outer local variable - index
Y:/Data/TestRubyXL/2011_12_20_12_15_07_0100/xl/drawings
drawing1.xml
Y:/Data/TestRubyXL/2011_12_20_12_15_07_0100/xl/drawings
drawing2.xml
Y:/Data/TestRubyXL/2011_12_20_12_15_07_0100/xl/drawings
drawing3.xml
Y:/Data/TestRubyXL/2011_12_20_12_15_07_0100/xl/drawings
drawing4.xml
Y:/Data/TestRubyXL/2011_12_20_12_15_07_0100/xl/drawings
drawing5.xml
Y:/Data/TestRubyXL/2011_12_20_12_15_07_0100/xl/drawings
vmlDrawing1.vml
Y:/Data/TestRubyXL/2011_12_20_12_15_07_0100/xl/drawings
vmlDrawing2.vml
Y:/Data/TestRubyXL/2011_12_20_12_15_07_0100/xl/drawings
vmlDrawing3.vml
Y:/Data/TestRubyXL/2011_12_20_12_15_07_0100/xl/drawings
_rels

I'n not sure whether this is the right solution, but it seems to work.It seems to me that the code had never been tested with drawings and '/drawings/_rels'-directory together.

Hi, I have a problem when I try to write a xlsm-file. I get the following error:

TypeError: Symbol as array index
[] at org/jruby/RubyArray.java:1346
write at D:/jruby-1.6.7.2/lib/ruby/gems/1.8/gems/rubyXL-1.2.10/lib
/rubyXL/writer/worksheet_writer.rb:178
each at org/jruby/RubyHash.java:1181
write at D:/jruby-1.6.7.2/lib/ruby/gems/1.8/gems/rubyXL-1.2.10/lib
/rubyXL/writer/worksheet_writer.rb:177
call at org/jruby/RubyProc.java:270
call at org/jruby/RubyProc.java:220
insert at D:/jruby-1.6.7.2/lib/ruby/gems/1.8/gems/nokogiri-1.5.5-ja
va/lib/nokogiri/xml/builder.rb:379
method_missing at D:/jruby-1.6.7.2/lib/ruby/gems/1.8/gems/nokogiri-1.5.5-ja
va/lib/nokogiri/xml/builder.rb:363
write at D:/jruby-1.6.7.2/lib/ruby/gems/1.8/gems/rubyXL-1.2.10/lib
/rubyXL/writer/worksheet_writer.rb:176
call at org/jruby/RubyProc.java:270
call at org/jruby/RubyProc.java:220
insert at D:/jruby-1.6.7.2/lib/ruby/gems/1.8/gems/nokogiri-1.5.5-ja
va/lib/nokogiri/xml/builder.rb:379
method_missing at D:/jruby-1.6.7.2/lib/ruby/gems/1.8/gems/nokogiri-1.5.5-ja
va/lib/nokogiri/xml/builder.rb:363
write at D:/jruby-1.6.7.2/lib/ruby/gems/1.8/gems/rubyXL-1.2.10/lib
/rubyXL/writer/worksheet_writer.rb:19
initialize at D:/jruby-1.6.7.2/lib/ruby/gems/1.8/gems/nokogiri-1.5.5-ja
va/lib/nokogiri/xml/builder.rb:293
write at D:/jruby-1.6.7.2/lib/ruby/gems/1.8/gems/rubyXL-1.2.10/lib
/rubyXL/writer/worksheet_writer.rb:18
write at D:/jruby-1.6.7.2/lib/ruby/gems/1.8/gems/rubyXL-1.2.10/lib
/rubyXL/workbook.rb:205
get_output_stream at D:/jruby-1.6.7.2/lib/ruby/gems/1.8/gems/rubyzip-0.9.9/lib
/zip/zip_streamable_stream.rb:12
get_output_stream at D:/jruby-1.6.7.2/lib/ruby/gems/1.8/gems/rubyzip-0.9.9/lib
/zip/zip_file.rb:159
write at D:/jruby-1.6.7.2/lib/ruby/gems/1.8/gems/rubyXL-1.2.10/lib
/rubyXL/workbook.rb:205
each at org/jruby/RubyArray.java:1615
each_with_index at org/jruby/RubyEnumerable.java:951
write at D:/jruby-1.6.7.2/lib/ruby/gems/1.8/gems/rubyXL-1.2.10/lib
/rubyXL/workbook.rb:203
open at D:/jruby-1.6.7.2/lib/ruby/gems/1.8/gems/rubyzip-0.9.9/lib
/zip/zip_file.rb:90
write at D:/jruby-1.6.7.2/lib/ruby/gems/1.8/gems/rubyXL-1.2.10/lib
/rubyXL/workbook.rb:122
(root) at excel.rb:14

I believe it should be possible to skip the filename extension check in RubyXL::Parser.parse.
That prevents to use uploaded Temfiles as they are, so something like:

RubyXL::Parser.parse('abcd', :data_only => false, :check_filename_extension => false)

would be great.

I can submit a patch if you like.
Thanks

Hi,

I'm looking to write hundreds of thousands of rows to an Excel spreadsheet in a web app. Keeping it all in memory is no fun; I'd like to write it straight to the output stream. Looking at the API, it doesn't look like that's something I can do -- am I correct?

If so, would this be something that's fairly easy to implement? Or even possible?

Thanks!

I received the following error while trying to parse a .xlsx file using Ruby 1.9.3 on OS X 10.8.2:

/Users/Dylan/.rvm/gems/ruby-1.9.3-p327/gems/rubyXL-1.2.10/lib/rubyXL/parser.rb:404:in `initialize': No such file or directory - ../xl/worksheets/sheet6.xml (Errno::ENOENT)
    from /Users/Dylan/.rvm/gems/ruby-1.9.3-p327/gems/rubyXL-1.2.10/lib/rubyXL/parser.rb:404:in `open'
    from /Users/Dylan/.rvm/gems/ruby-1.9.3-p327/gems/rubyXL-1.2.10/lib/rubyXL/parser.rb:404:in `block in decompress'
    from /Users/Dylan/.rvm/gems/ruby-1.9.3-p327/gems/rubyXL-1.2.10/lib/rubyXL/parser.rb:402:in `upto'
    from /Users/Dylan/.rvm/gems/ruby-1.9.3-p327/gems/rubyXL-1.2.10/lib/rubyXL/parser.rb:402:in `decompress'
    from /Users/Dylan/.rvm/gems/ruby-1.9.3-p327/gems/rubyXL-1.2.10/lib/rubyXL/parser.rb:47:in `parse'
    from xlcrawler.rb:9:in `block in <main>'
    from /Users/Dylan/.rvm/rubies/ruby-1.9.3-p327/lib/ruby/1.9.1/find.rb:41:in `block in find'
    from /Users/Dylan/.rvm/rubies/ruby-1.9.3-p327/lib/ruby/1.9.1/find.rb:40:in `catch'
    from /Users/Dylan/.rvm/rubies/ruby-1.9.3-p327/lib/ruby/1.9.1/find.rb:40:in `find'
    from xlcrawler.rb:7:in `<main>'

Turns out this is a bug in the decompress method. Errors are encountered when excel sheets are deleted and recreated - which affects excel's naming convention. Ultimately, the decompress method makes assumptions about sheet numbering which are untrue. Please see http://stackoverflow.com/questions/14174451/rubyxl-errnoenoent

lib/rubyXL/writer/styles_writer.rb:177

Might I suggest nil checks for:
- fill[:patternFill],
- fill[:patternFill][:attributes], and
- fill[:patternFill][:attributes][:patternType]

I have a normal xls file i want to parse. So, I'm doing this:

RubyXL::Parser.parse "path/to/file.xls", skip_filename_check: true

And I get the following error :

Zip::ZipError: Zip end of central directory signature not found
from /usr/local/lib/ruby/gems/1.9.1/gems/rubyzip-0.9.9/lib/zip/zip_central_directory.rb:97:in get_e_o_c_d' from /usr/local/lib/ruby/gems/1.9.1/gems/rubyzip-0.9.9/lib/zip/zip_central_directory.rb:55:inread_e_o_c_d'
from /usr/local/lib/ruby/gems/1.9.1/gems/rubyzip-0.9.9/lib/zip/zip_central_directory.rb:85:in read_from_str from /usr/local/lib/ruby/gems/1.9.1/gems/rubyzip-0.9.9/lib/zip/zip_file.rb:67:inblock in initialize'
from /usr/local/lib/ruby/gems/1.9.1/gems/rubyzip-0.9.9/lib/zip/zip_file.rb:66:in open' from /usr/local/lib/ruby/gems/1.9.1/gems/rubyzip-0.9.9/lib/zip/zip_file.rb:66:ininitialize'
from /usr/local/lib/ruby/gems/1.9.1/gems/rubyzip-0.9.9/lib/zip/zip_file.rb:87:in new' from /usr/local/lib/ruby/gems/1.9.1/gems/rubyzip-0.9.9/lib/zip/zip_file.rb:87:inopen'
from /usr/local/lib/ruby/gems/1.9.1/gems/rubyXL-1.2.10/lib/rubyXL/zip.rb:10:in unzip' from /usr/local/lib/ruby/gems/1.9.1/gems/rubyXL-1.2.10/lib/rubyXL/parser.rb:326:indecompress'
from /usr/local/lib/ruby/gems/1.9.1/gems/rubyXL-1.2.10/lib/rubyXL/parser.rb:47:in `parse'
from (irb):4
from /usr/local/lib/ruby/gems/1.9.1/gems/railties-3.2.11/lib/rails/comma
from /usr/local/lib/ruby/gems/1.9.1/gems/railties-3.2.11/lib/rails/comma

Do you have an idea what it might be? The file doesn't seem corrupt, at least I can open it in microsoft excel.

Hi

I'm trying to modified an already existing XLSX file adding some numeric data (basically using that file as a template). That file has its formula, styles and charts.

RubyXL seems to respect and kept the styles and the formula but the charts just disappear. Is there any way to avoid that with RubyXL?

regards,
Rafael Muñoz

I am getting following error
LoadError: no such file to load -- zip/zip

• I have installed rubyXL gem from rubygems.org
• I went to irb and fired require command , its gives error