roo-rb / roo-xls Goto Github PK

Our dates are in a format that is getting incorrectly parsed by the default parsing strategies.

Our date format is y-mmm, e.g. "16-Aug" for month of August, no day, year 2016
Roo/Roo-xls give us a Date object for 2017-08-16 for month of August, day 16, year 2017

sheet.cell(1,1) => #<Date: 2017-08-16 ((2457982j,0s,0n),+0s,2299161j)>

In Roo for .xlsx files, we've been using the #formatted_value method as a workaround. It reformats the date object which essentially gives us the underlying string.

sheet.formatted_cell(1,1) => "16-AUG"

The #formatted_value method is a bit of a hacky workaround but is serving us well enough. But there seems to be no way to get the underlying string representation of a Date, Time, or DateTime field for .xls files in roo-xls.

Since upgrading from the old roo project to roo-xls I now get these Ê characters in front of one of my number cells. Is there a good way to strip it out during import? I can just gsub the character afterwards but would like to nip it on the way in. Thanks!

@chopraanmol1 - While converting to CSV, Integer values like 2 are being changed to 2.0

This issue was addressed as part of the below PR by beetlegius. Can you consider merging this PR to master if it is fine?
https://github.com/roo-rb/roo-xls/pull/52/files

The author of the spreadsheet gem seems to have a fairly bizarre interpretation of the GPLv3, and has been unwilling to clarify that interpretation. His interpretation looks to me like he has confused the GPLv3 with the GNU AGPL.

You may wish to note in the README that while the license is presented as being GPLv3 that the author seems to interpret it more like the GNU AGPL and that they should plan accordingly as they will (apparently) need a commercial license if they wish to run a public-facing service using this code.

There have been problems reported when opening Excel files like this one from Freedom House.

Code that causes the memory growth (roo-1.13.2):

require 'roo'
require 'uri'  # Needed to prevent a NameError on URI

# Where the xls is a renamed copy of the mentioned sheet ...
sheet = Roo::Spreadsheet.open('freedomhouse-press.xls')

See:
roo-rb/roo#179
roo-rb/roo#168

This is blocked by fixes to other issues such as #42 and #43, but support for fonts and their colors is what I was originally in search of! :)

I would like to be able to do this via the #font method, for example:

roo = Roo::Excel2003XML.new('test/files/font_colors.xml')
roo.default_sheet = roo.sheets.first

roo.font(1, 'A').name
#=> "Courier New"

roo.font(1, 'A').size
#=> "24"

roo.font(1, 'A').color
#=> "#000000"

This results in this message being printed:

[DEPRECATION] extend Roo::Tempdir and use its .make_tempdir instead

This method is used here and here.

This block has references to fields that aren't using the ${namespace}: prefix. This breaks the style reading completely, but should be a relatively easy fix.

I've finished the first pass at an xls parser. It works for our needs for now, but is missing a lot of the functionality of spreadsheet for sure. Do you have an idea of what the minimum feature set it'd need to support to replace spreadsheet?

We also don't have a good sample size of excel spreadsheets to feel comfortable that it fully parses all xls docs. Does anyone have some decent spreadsheets to write tests against to get more coverage?

The gem is here: https://github.com/p2bi/read_xls

When I generate a file with a column containing ISBN numbers and format this columns/cells to be of type "text", after reading with roo-xls, the data comes as Float instead of String.

{:isbn=>9781609731939.0, :quantity=>10.0}

spreadsheet = open_spreadsheet(file)

# This line prints out "Sheet0" which is the correct sheet name
puts spreadsheet.default_sheet

# Prints nil
puts spreadsheet 

# Throws an error that `upto` is not defined on nilClass
# I assume the code fails because of this line 
# https://github.com/roo-rb/roo/blob/master/lib/roo/base.rb#L197
headers = spreadsheet.row(1)

I am using Rails 4.0.13 with Ruby 2.1.0

Although the existing codebase appears to intend to support detecting font styling (bold/italic/underline) via the #font method, it appears broken with any file I can generate on Mac OS X Excel when saving in the 2002/3/4 XML format.

The failure appears to be, among other things, due to failure to namespace the attributes that are used for XPath lookups.

See #42 for example files.

When I use roo-xls, it causes some errors.

path = "..../abc.xls"
xls = Roo::Excel.new(path)

extend Roo::Tempdir and use its .make_tempdir instead
NameError: uninitialized constant Roo::Base::TEMP_PREFIX

My roo version is 2.5.0.

Hi, there : )

Nice repo.
I've found out that master branch already fixed the ruby warnings such as:
https://github.com/roo-rb/roo-xls/blob/v1.2.0/lib/roo/xls/excel.rb#L51

but latest gem which is 1.2.0 doesn't have this commit yet !
Is there any plan about when to release the next version ?

like in roo-rb/roo#139 I need to read values from an xls as strings

on issuecomment-49214362 there's a way to do it:

cell = s.cell(row,col)
cell = s.excelx_value(row,col) if s.excelx_type(row,col) == [:numeric_or_formula, "General"]

can I do the same in roo-xls?

thanks!

Following code raises Permission denied @ unlink_internal - resources/tmp.xls (Errno::EACCES):

require 'roo-xls'

file_path = 'resources/tmp.xls'
Roo::Excel.new(file_path)

File.delete(file_path)

Can you please describe a correct way to close file after reading in case my code isn't correct?

roo-xls v1.1.0

See: roo-rb/roo#132
Need test cases to determine if this is an issue.

How can i save a modified xls?

This is a method that base Roo::Base supports, but this gem does not.

Steps to reproduce

require 'roo'
csv = Roo::CSV.new('test.csv')
puts csv.last_row

Issue

Works with Ruby 2.7.2.

Error with Ruby 3.0.0 Preview 1

no implicit conversion of Hash into String

System configuration

Roo::Xls version : 2.8.3

Ruby version: 3.1.2p20

can you merge the fix ?
#roo-rb/roo#551
22d9930

Hi! How can i read colour of the cell's background?

Thanks for the gem. I'm trying to open a Microsoft Excel 97-2004 Worksheet with:

book = Roo::Spreadsheet.open 'AusTender-Contract-Notice-Export_20150613_183324.xls', extension: :xls

and getting the error Ole::Storage::FormatError: OLE2 signature is invalid.

I'm not confident I'm using this correctly, a simple example of opening a file would make a very useful addition to the readme. Anything would be better than nothing ;)

Thanks again

Allowing document parsing via streams (which is allowed in roo) has been merged in as of June 2015, but cannot be used because the version number is still 1.0.0. Can you bump it so we can take advantage of this feature?

See https://github.com/roo-rb/roo#expand_merged_ranges

Probably would be best to just mention this in the README?

I have data that I'm streaming in from a remote service and I'd like to be able to hand the IO object to Roo::Spreadsheet.open, but this does not work for Roo::Excel files because the file_type_check method blows up when handed a non-String object. The Roo::Excelx class's initialize method skips calling file_type_check when it is given a stream.

roo-xls throws a strange Ole::Storage::FormatError when attempting to open a non-xls file.

require 'roo'
require 'roo-xls'
Roo::Excel.new('simple_spreadsheet.xlsx', file_warning: :ignore)
#Ole::Storage::FormatError: OLE2 signature is invalid

roo-rb/roo#67 (comment)

It appears that the existing "Excel 2003 XML" cell parsing algorithm doesn't end up with the correct row and column numbers when merged cells (across or down) are present. Ditto for "Span" rows, which Excel appears to use to identify missing or sparse rows.

See attached files for demonstration. (My intent with these files
was to test-drive support of for fonts and colors, but happened to also discover that the cell addressing was broken on the way).

font_colors.xml.zip

Roo::Spreadsheet.open(fp)
Errno::EACCES (Permission denied @ rb_sysopen - /%file%.xls)

If you chmod o+w the file, then you can open it. Why write permissions for this?

System configuration

Roo::Xls version: 1.2.0

Ruby version: 2.6.3p62 (2019-04-16 revision 67580) [x86_64-linux]

Issue

AFAIK, both old XLS and new XLSX formats supports encrypted workbooks with a password.
Should be possible to #open a file specifying this password.
Also, saving the file preserving the encryption or removing it.

Currently only the first of the merged cells is returning a value.
The rest cells are nil.

Example:
Suppose cells A1, A2, A3 are merged with value "ROO IS GREAT".
Reading of A1 returns the value. A2 and A3 return nil.

Related: roo-rb/roo#171 [according to roo changelog, this has been implemented]

ruby 2.3.1p112 (2016-04-26 revision 54768) [x86_64-linux]

*** LOCAL GEMS ***
roo (2.5.1, 2.4.0)
roo-xls (1.0.0)

best regards,
Charitakis Yannis