roo-rb / roo Goto Github PK

CPU 100% under ruby 2.0.0, 1.9.2 works ok!

Checked the code,
set_value is private now, but the official doc still told that use set_value.

set method still doesn't work either.

so how to write at present?

Probably should just be skipped.

s = Roo::CSV.new "sample.csv"
s.to_hash # should return { :header1 => "value for first row, column ", :header2 => "val for first row second column "}

Hi,

In order to get rid off whitespace problem with headers I tried to use clean: true option. It worked. Well, particularly...

There is an implementation problem with clean option because it removes ALL unicode characters in the sheet... But all I wanted it to remove white spaces at the headers.

I don't think that this gem is only for english language? :)

Would be cool if there's an option for ONLY cleaning up headers.

Thank you

To reproduce:

1. Create a spreadsheet on Google Docs
2. Put 4:00:02 in the first cell
3. Use Format menu, Number, Time
4. Use File menu, Download as, Microsoft Excel (.xlsx)
5. Load file into Excel and see that it's properly recognized as Time formatted
6. Load file into Roo and see that it's only a Float

Hey guys-

So, I have a spreadsheet a column of unformatted phone numbers (we have international users, so a one-size-fits-all format is impractical) — most of these are simply numbers, but even when the xlsx (or xls) column type is set to "Text", these still come in as Floats and end up imported with a ".0" at the end.

This is obviously incorrect, but I really thought the column type would be respected.

Am I doing anything obviously wrong here, or is there an issue?

Roo doesn't seem to be compatible with the newest rubyzip 1.0, as until I lock it to the version 0.9.9 it raises cannot load such file -- zip/zipfilesystem. So roo should be fixed to be compatible with rubyzip 1.0 or at least roo gemspec should specify the proper rubyzip version limitation.

Roo::Spreadsheet.open does not accept Tempfile, because it specifically checks for File, which is not ideal, since there are many classes that can implement File interface.

This patch introduces duck typing.

I think there's something very simple that I don't see going on here that's wrong. Heres My jquery code that's uploading the file:

import: function (e) {
    e.preventDefault();

    var formData = new FormData();
    jQuery.each($('#import_excel_file')[0].files, function(i, file) {
      formData.append('import_file', file, 'xls');
    });
    formData.append('fuel_type_id', $('#import_fuel_type').val());

    this.shipOff(formData);
  },

  shipOff: function (formData) {
    $.ajax({
      type: 'POST',
      url: App.Options.rootUrl + "/stations/stations/excel_import",
      data: formData,
      cache: false,
      contentType: false,
      processData: false,
      success: function (data) {
        console.log('successful upload', data);
      }
    });
  }

Then in the controller, where I call import = Excel.new(xls_file.tempfile.to_path.to_s), I get an error like TypeError (/var/folders/rd/58f3hjw10lv09q_8hsl0l7zn1mn1rf/T/RackMultipart20130909-36782-r1bv5n is not an Excel file)

What am I missing here?

Already reported this to nokogiri team but not sure whether it is really a bug or an incompatibility:

Updating my bundle to nokogiri 1.5.6 breaks roo ods spreadsheet parsing.

Steps to reproduce: install nokogiri 1.5.6 and roo 1.10.1 gems and exec the following ruby code:

require 'rubygems'
require 'roo'
doc = Openoffice.new("some.ods")
doc.sheets.each do |s|
  puts s
end

No sheets are found as it cannot parse the sheets correctly.

downgrade nokogiri to 1.5.5. and re-run and it correctly displays all sheet names.

Not sure whether this is a bug in nokogiri or a change in the API that causes the roo gem to break.

Hi, this is great, I've gotten a prototype app up and running in a few hours, and it is working great. Just one thing - I've got fields that look like numbers, but aren't: for example, employee_id; depending on the company, employee_id may be numeric or alpha-numeric, but I won't know in advance, so want to store them all as strings. However, in sample data that has all numeric ids, roo wants to interpret them as floats, and so I end with with an id.0 in my database, leading to all sorts of trouble. This is related to similar issue #53, except that in my case, I can't even guarantee that the field will be explicitly marked text - it is up to the client to manage the spreadsheets, and that means total unreliability.
Is there any way, that anyone can think of, to force ALL fields to be interpreted as text? For my purposes, that would be fine - there's nothing in my data that is not text, so a global option on open would be ideal.
Any ideas?
Thanks,
Ian

It would be great if Roo::Spreadsheet.open accepts extensions not only in 'xls' format, but also in '.xls' format, as it would make it possible to use the results of File.extname method without removing its dot.

Hi there,

roo is released under the MIT license as the project states. That's why we choose it for our apps. It was a joy to use and we dropped spreadsheet in its favor.

Today I found out that roo depends upon spreadsheet when parsing XLS files, which is licensed under GPL v.3. See here. So roo's license was very misleading in our case.

Please either correct the license to the most restrictive, you depend on or at least state the other gems license models. It would help choose wisely and prevent license violations.

Thanks (for your effort - OSS is hard, I know).

I have a excel with a record of percentage data, while parsing it, the percentage data has become float..what am I missing?

Hi,
And thanks for a great gem.

So if Roo::SomeSheetFormat.new('xls') somehow appears in pry, ruby shoots the processor at 99% and nothing happens.

Pry (irb on steroids) uses inspect to show the guts of an object. To avoid this nuisance you may add an inspect method on each Roo::SomeSheetFormat (or their super class if they have one), give it 2 words of info and it'll work fine.

doing this:

spreadsheet = Roo::Spreadsheet.open('test.csv')
spreadsheet.parse(headers: true)

raises this:

NoMethodError: undefined method `upto' for nil:NilClass
    ~/project/vendor/bundle/gems/roo-1.11.2/lib/roo/generic_spreadsheet.rb:476:in `each'
    ~/project/vendor/bundle/gems/roo-1.11.2/lib/roo/generic_spreadsheet.rb:485:in `parse'
    ~/project/lib/client_list.rb:19:in `from'
    ~/project/spec/lib/client_list_spec.rb:43:in `block (4 levels) in describe_each_format_of'

Looks like Roo::GenericSpreadsheet never gets initialized, which causes @header_line to be nil. Also, with the :headers options set, the code path taken never sets @header_line.

Maybe all the subclasses just need to call super in their initialize.

I'm sorry I don't know if this is an issues/bug or just mistake I done, but I'm kind of sure that I have try everything to analyze this case, so, I'm sorry if I wrote on the wrong place.

The case, I have 683 records that I want to imported to my application. It's about 3 or 4 times (in not sequential) I try to imported those record, but always make my local server like dead lock. I done trying imported 25 records, 100 records, 500 records, 600 records, even 682 records all done well, but from 683 records it turn like that.

I done trying to swap the data, for example I have data (1, 2, 3, 4, 5, 6, 7 , 8, 9, 10), I done trying with data (1, 2, 3) then (1, 2, 3, 4, 5, 6, 7) or (4, 5, 6, 7, 8, 9, 10) or (3, 6, 7, 10) etc. But it all resulting the same result. Total rows <= 682 all perfect but from 683 that always make my (local) server (kind of) dead lock.

My Laptop specification:
Processor: AMD A8-3500M APU with Radeon(tm) HD Graphics × 4
Memory: 5.3 GiB
OS: Ubuntu 12.04 (64 bit)

Ruby version: Ruby 1.9.3p194
Rails version: Rails 3.2.8
RVM version: RVM 1.16.8 (stable)
Roo version: Roo (1.11.1)

Is there anyone ever met the same problem like this?

Thank you and sorry for bad grammar.

Ruby 2.0.0 is not supported mainly due to Iconv being removed from Ruby's API.
I think this affects only the old Excel format parser.

Would it be possible to add support for reading change tracking information from Excel files?

What I mean is, if change tracking was turned on in Excel, it should be possible to read the change history of the file, including metadata like which changed have been reviews and which haven't.

I'm using Excel 2010 & 2013, so I'm mostly thinking of XLSX files here. According to the XML schema (available from ECMA), there is an element named revisions of type CT_Revisions that appears to hold the relevant data.

Specifically, it would be great if we could have a revisions method that works in a similar way to the existing comment and comments methods, i.e. s.revisions returns all the revisions, and s.revision(row, col) returns any revisions for the specified cell.

Hi all,

I think it will be nice if there's a way to login to Google Drive using access_token instead of username and password

Ref. https://github.com/Empact/roo/blob/master/lib/roo/google.rb#L15

It is possible using google_drive gem https://github.com/gimite/google-drive-ruby/blob/master/lib/google_drive.rb#L78

I can work on it if you guys think that it won't introduce problems to roo

Let me know what do you guys think about it.

I have repeating issue with loading of XLS files that were generated by some application (I do not have technical details) where exactly first 255 rows are red.
However when I open and save this file in the Excel, all lines all loaded properly.
This must be some race condition or unexpected error in the input file, but since I want to implement automated imports I'd like to resolve the issue.

I can not share the sample excel file (contains company data) but I can try to investigate and maybe provide a fix if some will point me in a good direction.
I will investigate anyway, but any help is appreciated :-)

Regards

Mac 10.8.2 with rvm installed 2.0.0:

Using roo (1.11.0) from git://github.com/Empact/roo.git (at master)
roo at /Users/allenwyma/.rvm/gems/ruby-2.0.0-p0/bundler/gems/roo-de1039928e99 did not have a valid gemspec.
This prevents bundler from installing bins or native extensions, but that may not affect its functionality.
The validation message from Rubygems was:
  ["test/log/roo.log"] are not files
from /Users/allenwyma/.rvm/gems/ruby-2.0.0-p0/bundler/gems/roo-de1039928e99/roo.gemspec

Hi,

In CSV we have the facility to open an IO stream or a file

# raw data could also be an uploaded file 
# which remains in memory
raw_data = File.open(file).read 
csv_data = CSV.new(raw_data)

or

CSV.open(file)
# etc etc

Is it possible to just give roo an IO stream if I can specify its type and parse?
I want to open excel based files without hitting the file system is this possible

Many thanks in advance.

Kind regards,
Carl

hello, I am loading up a reasonable size xlsx file 2M, but Roo is taking so long to load it, whereas using python xlrd, it seems instantenous, what might take it so long for Roo to load it? is this a general issue or it might be my file ?

thanks

Can we use "require 'active_record' " instead of 'activerecord'

I upgraded to roo 1.13.0, and my tests that parsed spreadsheets started to blow up.

The problem comes down to this line:

@cell[sheet][key] = Spreadsheet::Link.new(@hyperlink[sheet][key], @cell[sheet][key]) if hyperlink?(y,x+i)

(https://github.com/Empact/roo/blob/4e4e305a22695cb715f13f18048465f4f7109d39/lib/roo/excelx.rb#L379)

Spreadsheet::Link extends String. The second argument to the Spreadsheet::Link constructor needs to be a String and blows up if it's a Float:

irb> Spreadsheet::Link.new("http://example.com", "foobar")
=> "foobar"
irb> Spreadsheet::Link.new("http://example.com", 3.4)
TypeError: can't convert Float into String

The ultimate solution is probably to have the spreadsheet gem fix the problem upstream. However, I have little use for having a Spreadsheet::Link returned to me, so I'd be happy with a flag I could pass in to the Roo::Excelx constructor that makes link parsing optional. If I need a hyperlink, I can always use the #hyperlink method.

This error is thrown when attempting to read a particular xlsx file:

Zip end of central directory signature not found (Zip::ZipError)

A gist fix has been posted here (I haven't tried it yet):

https://gist.github.com/roblingle/1333908

Note the fix was written for xlsm files but a comment confirms that it also works for xlsx files.

Hi, sorry maybe its just me, but the pink background image on this site http://roo.rubyforge.org/ makes the text really hard to read, especially when you scroll the page it makes it really hard to scan the page with my eyes. It looks much better with just a white background or maybe the other background color you have set in the stylesheet background-color: #E1D1F1;.

When i try to read files from test/files directory like so:

require 'roo'
puts Roo::Spreadsheet.open('Bibelbund.xls').inspect
puts Roo::Spreadsheet.open('Bibelbund.ods').inspect

I get at error at trird line. xls file is read normally, reading and inspecting ods file gives this error:

/var/lib/gems/1.9.1/gems/roo-1.10.2/lib/roo/generic_spreadsheet.rb:703:in `validate_sheet!': Error: sheet 'nil' not valid (ArgumentError)
    from /var/lib/gems/1.9.1/gems/roo-1.10.2/lib/roo/openoffice.rb:327:in `read_cells'
    from /var/lib/gems/1.9.1/gems/roo-1.10.2/lib/roo/openoffice.rb:203:in `to_s'
    from manip.rb:14:in `inspect'
    from manip.rb:14:in `<main>'

Apologies, I'm still quite new to RoR, and maybe I'm missing something obvious to everyone else, but how do I close a Roo file once open? I've got spreadsheet = Roo::Spreadsheet.open(file_name) just fine, but I want to do either spreadsheet.close, or preferably, Roo::Spreadsheet.open(self.file_name) do |spreadsheet| ... end.
Is there a way to do this, or is it something missing? I ask because I'm running into problems deleting files after parsing them, and I'm pretty sure it's because they still are locked open by Roo.

HI @Empact ,

I am trying to get your fork of roo installed, however the rubygems.org version doesn't include this fork code, even though it lists you as an author.

As no one seems to be "officially" maintaining this gem, can you / will you be releasing this as a Gem? Or should I just clone and build my own gem?

Cheers,
cam

I feel a bit uncomfortable with the Documentation. If you're open for a proposal, give me some time and I'll try to do something. Maybe be some text reformatting will do.

Disclaimer: this issue is not intended as a personal insult. The gem is great and save's me tons of tears with other painful gems.

I get the above error when I'm trying to open a temporary uploaded file. Trace gives out

/gems/roo-1.12.2/lib/roo/base.rb:524:infile_type_check'`

Looking at that line, it checks for FILE EXTENSION to confirm FILE TYPE.

Isn't there a better way to check? All temporary filenames have no extensions. I have to manually rename it, and that's just bad.

UPDATE: read the usage info here, which is more up to date than what the main site has. Apparently, I can use open and just pass the file. That's good. But this file type checking should still be improved, imo. I think if this issue stays here open / closed, will help the ones like myself.

The VERSION constant is not being updated accordingly ...

https://github.com/Empact/roo/blob/master/lib/roo.rb#L3

As of version 1.11.2, it's still at 1.10.3 on source code.

Argh, at first I thought first_row was broken, but it turns out that my call to info was resetting default_sheet. That's sloppy.

Code to demonstrate:

def readit_1(s)  # s is either a xls or xlsx
  s.default_sheet = s.sheets.first
  puts s.info # looks good
  puts "first_row: "+ s.first_row.inspect # prints nil, incorrect
end

def readit_2(s)  # s is either a xls or xlsx
  s.default_sheet = s.sheets.first
  puts s.info # looks good
  s.default_sheet = s.sheets.first # put it back!
  puts "first_row: "+ s.first_row.inspect # prints 1, correct
end

There are currently 2 failing specs related to datetime in an xlsx spreadsheet. It appears to be a round error when converting to a string. This may be related to issue #21

Is there any way to give encoding: argument to Csv.new / Excel.new method?

Hey,

I was trying to use Roo to read an XLS file in jRuby and ended up with the following error:

Java::JavaLang::OutOfMemoryError: Java heap space
        from org.jruby.util.ByteList.ensure(ByteList.java:341)
        from org.jruby.RubyString.modify(RubyString.java:948)
        from org.jruby.RubyString.cat(RubyString.java:1384)
        from org.jruby.RubyString.cat19(RubyString.java:1349)
        from org.jruby.RubyString.cat19(RubyString.java:1342)
        from org.jruby.RubyArray.inspectAry(RubyArray.java:1490)
        from org.jruby.RubyArray.inspect(RubyArray.java:1514)
        from org.jruby.RubyArray.to_s(RubyArray.java:646)
        from org.jruby.RubyArray$INVOKER$i$0$0$to_s.call(RubyArray$INVOKER$i$0$0$to_s.gen)
        from org.jruby.RubyClass.finvoke(RubyClass.java:674)
        from org.jruby.runtime.Helpers.invoke(Helpers.java:474)
        from org.jruby.RubyBasicObject.asString(RubyBasicObject.java:667)
        from org.jruby.util.Sprintf.rubySprintfToBuffer(Sprintf.java:534)
        from org.jruby.util.Sprintf.sprintf1_9(Sprintf.java:227)
        from org.jruby.RubyString.opFormatCommon(RubyString.java:1218)
        from org.jruby.RubyString.op_format(RubyString.java:1193)
        from org.jruby.RubyString$INVOKER$i$1$0$op_format.call(RubyString$INVOKER$i$1$0$op_format.gen)
        from org.jruby.runtime.callsite.CachingCallSite.call(CachingCallSite.java:168)
        from org.jruby.ast.CallOneArgNode.interpret(CallOneArgNode.java:57)
        from org.jruby.ast.NewlineNode.interpret(NewlineNode.java:105)
        from org.jruby.evaluator.ASTInterpreter.INTERPRET_BLOCK(ASTInterpreter.java:112)
        from org.jruby.runtime.Interpreted19Block.evalBlockBody(Interpreted19Block.java:206)
        from org.jruby.runtime.Interpreted19Block.yield(Interpreted19Block.java:157)
        from org.jruby.runtime.Block.yield(Block.java:142)
        from org.jruby.RubyArray.collect(RubyArray.java:2404)
        from org.jruby.RubyArray.collect19(RubyArray.java:2412)
        from org.jruby.RubyArray$INVOKER$i$0$0$collect19.call(RubyArray$INVOKER$i$0$0$collect19.gen)
        from org.jruby.runtime.callsite.CachingCallSite.cacheAndCall(CachingCallSite.java:316)
        from org.jruby.runtime.callsite.CachingCallSite.callBlock(CachingCallSite.java:145)
        from org.jruby.runtime.callsite.CachingCallSite.callIter(CachingCallSite.java:154)
        from org.jruby.ast.CallNoArgBlockNode.interpret(CallNoArgBlockNode.java:64)
        from org.jruby.ast.CallOneArgNode.interpret(CallOneArgNode.java:57)

If you guys have any idea on how to solve this, this would be really helpful.

Cheers.

I have a xls spreadsheet where there is a column of dates, one of the dates is stored as a string '20012014. I have no control over the spreadsheets as they are uploaded by users.

The following error is raised when trying to open the spreadsheet

undefined method `round' for #<String:0x7fdc4aefd7a0> (NoMethodError)
/app/shared/bundle/ruby/1.9.1/gems/spreadsheet-0.8.3/lib/spreadsheet/excel/row.rb:63:in `_datetime'
/app/shared/bundle/ruby/1.9.1/gems/spreadsheet-0.8.3/lib/spreadsheet/excel/row.rb:21:in `datetime'
/app/shared/bundle/ruby/1.9.1/gems/roo-1.11.1/lib/roo/excel.rb:310:in `read_cell_date_or_time'
/app/shared/bundle/ruby/1.9.1/gems/roo-1.11.1/lib/roo/excel.rb:256:in `block (2 levels) in read_cells'
/app/shared/bundle/ruby/1.9.1/gems/roo-1.11.1/lib/roo/excel.rb:250:in `each'
/app/shared/bundle/ruby/1.9.1/gems/roo-1.11.1/lib/roo/excel.rb:250:in `block in read_cells'
/app/shared/bundle/ruby/1.9.1/gems/spreadsheet-0.8.3/lib/spreadsheet/worksheet.rb:133:in `block in each'
/app/shared/bundle/ruby/1.9.1/gems/spreadsheet-0.8.3/lib/spreadsheet/worksheet.rb:132:in `upto'
/app/shared/bundle/ruby/1.9.1/gems/spreadsheet-0.8.3/lib/spreadsheet/worksheet.rb:132:in `each'
/app/shared/bundle/ruby/1.9.1/gems/spreadsheet-0.8.3/lib/spreadsheet/excel/worksheet.rb:35:in `each'
/app/shared/bundle/ruby/1.9.1/gems/roo-1.11.1/lib/roo/excel.rb:249:in `read_cells'
/app/shared/bundle/ruby/1.9.1/gems/roo-1.11.1/lib/roo/generic_spreadsheet.rb:99:in `last_row'

If I open the same spreadsheet using the Spreadsheet gem directly it does not have an error. I need to support xls and xlsx so using the Spreadsheet gem directly is not an option.

I am using Roo 1.11.1 and Ruby 1.9.3.

Is there any reason why Roo::Base is being autoloaded instead of being required? Any of Roo::<something> classes anyway depend on Roo::Base, so if I use Roo in any way, Roo::Base will have to be loaded.

The problem is that without manually requiring Roo::Base I can't set a rescue ::Zip::ZipError, which is quite strange.

Please let me know if this isn't the repository where the latest release was done from. If it is please add tags to the repository:

https://rubygems.org/gems/roo/versions/1.10.2

Mac 10.8.2 with rvm installed 2.0.0:

Using roo (1.11.0) from git://github.com/Empact/roo.git (at master)
roo at /Users/allenwyma/.rvm/gems/ruby-2.0.0-p0/bundler/gems/roo-de1039928e99 did not have a valid gemspec.
This prevents bundler from installing bins or native extensions, but that may not affect its functionality.
The validation message from Rubygems was:
  ["test/log/roo.log"] are not files
from /Users/allenwyma/.rvm/gems/ruby-2.0.0-p0/bundler/gems/roo-de1039928e99/roo.gemspec

Gemfile:

  gem "roo", github: "Empact/roo", require: false
  gem 'google-spreadsheet-ruby', require: false

The README has these lines:

xls.sheet('Info').row_count
xls.sheet(0).row_count

They don't work for me. I don't think this method or attribute exists. I can't find it in the code.

Trying to open a non-zipped .xlsx file on the local machine. Here's the call:

@spreadsheet = Roo::Excelx.new(file.path, :file_warning => :ignore)

And getting this error:

undefined method `<=' for nil:NilClass
rubyzip (1.0.0) lib/zip/central_directory.rb:80:in `read_e_o_c_d'
rubyzip (1.0.0) lib/zip/central_directory.rb:105:in `read_from_stream'
rubyzip (1.0.0) lib/zip/file.rb:76:in `block in initialize'
rubyzip (1.0.0) lib/zip/file.rb:75:in `open'
rubyzip (1.0.0) lib/zip/file.rb:75:in `initialize'
rubyzip (1.0.0) lib/zip/file.rb:95:in `new'
rubyzip (1.0.0) lib/zip/file.rb:95:in `open'
roo (1.12.2) lib/roo/excelx.rb:569:in `extract_content'
roo (1.12.2) lib/roo/excelx.rb:89:in `block in initialize'
roo (1.12.2) lib/roo/base.rb:565:in `block in make_tmpdir'

Looks like the file is missing some comment / header section that maybe is important? Sorry, I don't know Office formats very well :( Tried adding a nil catch to that line in rubyzip, but that creates more issues down the line.

Any suggestions / ideas would be awesome. Thanks!

I get the error below when trying to get an excel file:

Errno::EACCES: Permission denied - oo_1404_4197352129

I think roo is trying to save the excel file to a temp directory. Anyone know where it's trying to write to?

How does one read tab separated values with roo?

I've just installed the Roo Gem and I'm experiencing a collision between the Matrix class and my own ActiveRecord model with the same name.

I think that the correct thing to do is to move Roo classes into a module (e.g. Roo::Matrix) so that it won't collide with application classes, or even other Gems.

I'm happy to submit a pull request for this, but before I do I'd like to discuss it. Does this seem a reasonable approach? It may break existing applications that depend upon Roo.

Is it possible to create a Google spreadsheet instance without the username and password? I set up a test spreadsheet and set the Sharing options to "Anyone with the link". I can't get past authentication.

Sample, public doc: https://docs.google.com/spreadsheet/ccc?key=0AizkTDTejN8RdEpRZzkxWTNvQVY0bXc3Q1c1cW4tM3c#gid=0

require 'rubygems'
require 'roo'

s = Roo::Google.new("0AizkTDTejN8RdEpRZzkxWTNvQVY0bXc3Q1c1cW4tM3c") # creates an Google Spreadsheet instance

s.default_sheet = s.sheets.first

puts s.cell(1,1)

Here's the 403 response:

user not set
password not set
~/.rvm/gems/ruby-1.9.3-p194/gems/google_drive-0.3.5/lib/google_drive/session.rb:93:in `rescue in login': Authentication failed for : Response code 403 for post https://www.google.com/accounts/ClientLogin: Error=BadAuthentication (GoogleDrive::AuthenticationError)
...