Now ruby 2.0 is ready for production. I have tested that only 0.7.7 support ruby 2.0, all the later versions > 0.7.7 doesn't support Ruby 2.0.

OK, to make a long story short

This gem (or dependent gems) starting straight from version "0.8.8" (found by trial-and-error) interferes with I18n module.

The way I wound it:
I'm using transliteration rules defined in my "en.yml" file:
en:
i18n:
transliterate:
rule:
а: "a" (this is for russian characters)

And, if I try doing I18n.transliterate("Russian text goes here"), for the output I get bunch of "?" signs, meaning that the transliteration rule is not specified and Transliterator module doesn't know how to do his job.
If I uninstall spreadsheet gem (or install version 0.8.7) then everything is working good.

My dev. environment: Mac OS X 10.7.5, Rails 3.2.12, Ruby 1.9.3 (p448)

Please, pay close attention to this problem (and this is a serious problem for me, because I use transliteration for my links)

Description

For test purpose I used various gem versions (including 0.7.7 too).
Problem is when Numeric value is quite large.
For Excel opening I used 2007 version

Failing scenario with large Number value

require 'spreadsheet'
book = Spreadsheet::Workbook.new
sheet1 = book.create_worksheet
sheet1.row(1).insert 1, BigDecimal.new(100000000)
book.write '/path/to/output/excel-file.xls'

Successful scenario with smaller Number value

require 'spreadsheet'
book = Spreadsheet::Workbook.new
sheet1 = book.create_worksheet
sheet1.row(1).insert 1, BigDecimal.new(1000000)
book.write '/path/to/output/excel-file.xls'

Spreadsheet version: 0.7.6

Not sure if I'm doing this correctly, but based on what I found on the net, it should work. Basically, I'm trying to set the font size of a cell to be 50:

require 'spreadsheet'

book = Spreadsheet.open 'test.xls'
sheet = book.worksheet(0)
row = sheet.row(0)
format = Spreadsheet::Format.new :size => 50
row.set_format(0, format)
book.write 'result.xls'

Row 0,0 already has text. The result.xls looks exactly like the test.xls. Source files can be found here.

Is this a bug, or am I doing it wrong?

ruby : 1.8.7
spreadsheet: 0.8.5
platform: windows 7
installer: bitnami-redmine-2.0.3-1-windows-installer-r01.exe

I have installed xls_export plugin for redmine, but every time I try exporting an Excel sheet of Issue list, I get the following error:

Iconv::InvalidEncoding (invalid encoding ("UTF-16LE//TRANSLIT//IGNORE", "UTF-8")):
spreadsheet (0.8.5) lib/spreadsheet/encodings.rb:38:in initialize' spreadsheet (0.8.5) lib/spreadsheet/encodings.rb:38:innew'
spreadsheet (0.8.5) lib/spreadsheet/encodings.rb:38:in internal' spreadsheet (0.8.5) lib/spreadsheet/excel/writer/biff8.rb:43:in_unicode_string'
spreadsheet (0.8.5) lib/spreadsheet/excel/writer/biff8.rb:36:in unicode_string' spreadsheet (0.8.5) lib/spreadsheet/excel/writer/workbook.rb:353:inwrite_font'
spreadsheet (0.8.5) lib/spreadsheet/excel/writer/workbook.rb:362:in write_fonts' spreadsheet (0.8.5) lib/spreadsheet/excel/writer/workbook.rb:359:ineach'
spreadsheet (0.8.5) lib/spreadsheet/excel/writer/workbook.rb:359:in write_fonts' spreadsheet (0.8.5) lib/spreadsheet/excel/writer/workbook.rb:429:inwrite_from_scratch'
spreadsheet (0.8.5) lib/spreadsheet/excel/writer/workbook.rb:644:in write_workbook' spreadsheet (0.8.5) lib/spreadsheet/writer.rb:12:inwrite'
spreadsheet (0.8.5) lib/spreadsheet/workbook.rb:124:in write' plugins/redmine_xls_export/lib/xls_export.rb:275:inissues_to_xls2'
plugins/redmine_xls_export/app/controllers/xls_export_controller.rb:116:in export_to_string' plugins/redmine_xls_export/app/controllers/xls_export_controller.rb:50:inexport_current'

We have a problem we keep encountering here where "GENERAL" excel cells filled with string integers will get be read as floats. It is repeatable but I don't have an isolated test case i could upload yet. In practice it will convert every row of a given column to floats (in today's case it's the first column), but it does not convert all GENERAL columns populated with integers to floats.

This is a real problem for us because we're reading rows in from the spreadsheet and using the improperly converted column to match the data in the spreadsheet up with our database and the excel row with 123.0 does not match the "123" entry in the database (not surprising).

Is there some option we can toggle to make it stop trying to be smart about how it parses the data. We'd be perfectly happy if we could get it to just treat everything as a string and let us deal with converting it to the right type. Alternately, is there a way to tell it how to interpret the format on specific columns, or cells?

I'm trying to read cells with several links inside of them (splitted by spaces).
Only the first link is retrieved though. Any other one are ignored.

The links seems to be retrieved from the cell though, as they all appear when inspecting the object.

Please, add tags for all new releases

Steps to Reproduce:

1. Create new spreadsheet with formulas. Example:

row[7] = "=IF(F#{i + 9}=\"\";\"\";LEN(F#{i + 9}))"

1. Open spreadsheet in Microsoft Office Excel 2010.

Actual Results: Formulas don't calculated. It only calculated after I open cell for edit with double click and press Enter.

e.g.:

buggy_string = "blah blah / blah"
sheet = book.create_worksheet :name => "#{buggy_string}"

then we have a sheet named sheet 1 instead of blah blah / blah

Hi! We long ago use your spreadsheet lib but now faced a problem on generated xls file. I can't open this in MS Excel 2010 (v14.0.6023.1000 32bit), it speak about incorect data. But all pretty opens in LibreOffice Calc (3.5.4.2 build: 350m1)
And not raised any exception or warning while file generated =(
I tryed it at 0.6.5.7 and 0.7.3 (after update)

We tested it on LibreOffice at Ubuntu 12.04 (linux 3.2.0-29-generic) and MS Office at Windows 7
ruby 1.9.3p194 (2012-04-20 revision 35410) [x86_64-linux] Ubuntu 12.04

import_template.rb
products_export_xls_98213795.1.xls

Looks like lib/spreadsheet/excel/rgb.rb isn't in the 0.8.0 gem somehow, which causes this error.

This error is thrown when including the gem using bundler via a Gemfile using a simple:

gem 'spreadsheet'

Some companies will only use gems with a certain license.
The canonical and easy way to check is via the gemspec
via e.g.

spec.license = 'MIT'
# or
spec.licenses = ['MIT', 'GPL-2']

There is even a License Finder to help companies ensure all gems they use
meet their licensing needs. This tool depends on license information being available in the gemspec.
Including a license in your gemspec is a good practice, in any case.

How did I find you?

I'm using a script to collect stats on gems, originally looking for download data, but decided to collect licenses too,
and make issues for missing ones as a public service :)
https://gist.github.com/bf4/5952053#file-license_issue-rb-L13 So far it's going pretty well

I'm parsing a spreadsheet where a cell has a custom format: # ##0,000 "MWH".

Code from lib/spreadsheet/format.rb pasted into irb:

irb(main):028:0>       @regexes = {
irb(main):029:1*         :date         => Regexp.new("[YMD]", 'UTF-8'),
irb(main):030:1*         :date_or_time => Regexp.new("[hmsYMD]", 'UTF-8'),
irb(main):031:1*         :datetime     => Regexp.new("([YMD].*[HS])|([HS].*[YMD])", 'UTF-8'),
irb(main):032:1*         :time         => Regexp.new("[hms]", 'UTF-8')
irb(main):033:1>       }
=> {:date=>/[YMD]/i, :date_or_time=>/[hmsYMD]/i, :datetime=>/([YMD].*[HS])|([HS].*[YMD])/i, :time=>/[hms]/i}

irb(main):034:0> @number_format="#,##0.000\\ \"MWH\""
=> "#,##0.000\\ \"MWH\""

irb(main):035:0> !!@regexes[:datetime].match(@number_format.to_s)
=> true

Normally (afaik) when reading a cell that contains a link with

sheet[row_index, col_index]

Spreadsheet should return cell's text (the displayed value), and to retrieve the link one should

sheet[row_index, col_index].url

But on certain .xls files, Spreadsheet returns links instead of cell values, when reading them without the .url method. An example of one such file can be downloaded here, problem cells: [12, 3], [25, 3] (there's more).

Current workaround for this (to get the text, not the link) is

sheet.row(row_index).at(col_index)

All in all, I believe this is unpredictable behaviour.

Another problem with the mentioned file is that Spreadsheet returns a broken link which contains garbage symbols, like this:

sheet[12, 3] # returns 'http://gbros.ru/catalog/100/\u0000塹?ᴻ䡿ⲯ嶂藄挧\u0000\u0000\uABA5'
sheet[25, 3] # returns 'http://gbros.ru/catalog/100/\u0000塹?ᴻ䡿ⲯ嶂藄挧\u0000\u0000\uABA5'

One of the workarounds for this is to filter out non-printing characters with regex:

sheet[12, 3].split(/[^[:print:]]/).first

Another workaround is to open and re-save the file in a newer version of Excel, but this can be difficult to do on a production system.

if column type is Link, can not use row[idx] = value to change the value for that row. platform windows7, use latest version of this gem.

I'm getting the following error when I try and parse an XLS spreadsheet using version 0.9.6.

/Users/sam/.rbenv/versions/2.0.0-p247/lib/ruby/gems/2.0.0/gems/spreadsheet-0.9.6/lib/spreadsheet/excel/reader/biff8.rb:157:in `read_string_header': undefined method `>>' for nil:NilClass (NoMethodError)
    from /Users/sam/.rbenv/versions/2.0.0-p247/lib/ruby/gems/2.0.0/gems/spreadsheet-0.9.6/lib/spreadsheet/excel/reader/biff8.rb:128:in `read_string'
    from /Users/sam/.rbenv/versions/2.0.0-p247/lib/ruby/gems/2.0.0/gems/spreadsheet-0.9.6/lib/spreadsheet/excel/reader.rb:997:in `read_style'
    from /Users/sam/.rbenv/versions/2.0.0-p247/lib/ruby/gems/2.0.0/gems/spreadsheet-0.9.6/lib/spreadsheet/excel/reader.rb:834:in `read_workbook'
    from /Users/sam/.rbenv/versions/2.0.0-p247/lib/ruby/gems/2.0.0/gems/spreadsheet-0.9.6/lib/spreadsheet/excel/reader.rb:135:in `read'
    from /Users/sam/.rbenv/versions/2.0.0-p247/lib/ruby/gems/2.0.0/gems/spreadsheet-0.9.6/lib/spreadsheet/excel/workbook.rb:32:in `open'
    from /Users/sam/.rbenv/versions/2.0.0-p247/lib/ruby/gems/2.0.0/gems/spreadsheet-0.9.6/lib/spreadsheet.rb:63:in `open'
    from /Users/sam/.rbenv/versions/2.0.0-p247/lib/ruby/gems/2.0.0/gems/spreadsheet-0.9.6/lib/spreadsheet.rb:69:in `open'
    from parse.rb:4:in `<main>'

Have you ever seen an error like this before? Opening the spreadsheet in Excel and re-saving it fixes the problem, but I don't want to do this for every single spreadsheet I want to parse.

I created a test case where "-1912.167" is read as "-1912.1670000000001" from an excel file. I think it results from some floating point conversions but can't find the code which creates floats from an excel file. Maybe someone else knows the place.

Here is the test case:
SKoschnicke/spreadsheet-1@f309aeb

When I try to open an empty file, I get NoMethodError: undefined method `read' for false:FalseClass

That's because of this code:

%w{Book Workbook BOOK WORKBOOK book workbook}.any? do |name|
  @book = @ole.file.open(name) rescue false
end
@data = @book.read

I think that in this case some more specific exception should be raised, so that it's easier to catch it.

i am trying to print excel file into landscape. using:
sheet.pagesetup[:orientation] = :landscape

but i am getting the following error:
undefined method `pagesetup' for #Spreadsheet::Worksheet:0x007f5fb240fe70

please help me doing that

Steps to reproduce:

Create empty spreadsheet in LibreOffice for Mac, use Text tool in Drawing toolbar (View - Toolbars - Drawing) to draw a rectangle and write some text in it. Save as textshape.xls or download from http://mog.se/textshape.xls

Run this code to try to access first row of spreadsheet:

require 'spreadsheet'
Spreadsheet.open('button.xls').worksheet(0).row(0)

We are presented with this exception:

/gems/1.9.1/gems/spreadsheet-0.9.6/lib/spreadsheet/excel/reader.rb:913:in `read_worksheet': undefined method `text=' for nil:NilClass (NoMethodError)
  from /gems/1.9.1/gems/spreadsheet-0.9.6/lib/spreadsheet/excel/worksheet.rb:53:in `ensure_rows_read'
  from /gems/1.9.1/gems/spreadsheet-0.9.6/lib/spreadsheet/excel/worksheet.rb:57:in `row'
  from -e:1:in `<main>'

I am trying to create a dropdown cell in an xls worksheet. Normally in Excel using the GUI you would do the following: http://spreadsheets.about.com/od/datamanagementinexcel/qt/20071113_drpdwn.htm

Is there a way via this gem that I can set cell validation so that I can create a dropdown? This link has some Excel VBA or .net code that illustrates the point:
http://www.mrexcel.com/forum/excel-questions/258323-programmatically-creating-drop-down-menus.html

Cells(Row, i).Select ' To select cell by numbers
'Range("J14").Select ' To select cell by cell name
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$B$4:$B$6" 'The cells which have the values wanted in the drop down menu
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Hi!

Great library. The examples helped me make a few fun excel for testing purposes (my dream is to use ruby to auto-generated my excel files one day... for now I still must manually do most things in libreoffice, which is tedious compared to programmatic solutions)

In the main readme, at section "== Examples"

You have this:

"Have a look at the GUIDE[link://files/GUIDE_txt.html]."

But the link to the GUIDE does not work. The link on the next sentence works though. Can this tiny documentation flaw be improved? People can quickly click on GUIDE to continue reading, right now I need to manually click on GUIDE on the github file listing instead.

This might already exist, but it's not obvious to me (as of version 0.7.7).

I'd like to be able to get/set the background color of a cell or row. I noticed there is some level of support for background colors in the Spreadsheet::Format class, but I'm not sure if it's used or set in any way.

(For what it's worth, I did clone the repository and poke around, and I realized the XLS format is not pleasant terrain for newbies.)

NoMethodError 'shared_string' raised when save new Workbook which have a worksheet by adding (#add_worksheet) worksheet from other file

file_1 = Spreadsheet.open './excel_templates/merchant_invoice.xls'
file_2 = Spreadsheet::Workbook.new
file_2.add_worksheet file_1.worksheet(0)
file_2.write "merchant_invoice-copy.xls"

NoMethodError: undefined method `shared_string' for #<Spreadsheet::Workbook:0x007fdb32520548>
    from /Users/barock/.rvm/gems/ruby-1.9.3-p448@maple/gems/spreadsheet-0.8.2/lib/spreadsheet/excel/worksheet.rb:71:in `shared_string'
    from /Users/barock/.rvm/gems/ruby-1.9.3-p448@maple/gems/spreadsheet-0.8.2/lib/spreadsheet/excel/reader.rb:603:in `read_labelsst'
    from /Users/barock/.rvm/gems/ruby-1.9.3-p448@maple/gems/spreadsheet-0.8.2/lib/spreadsheet/excel/reader.rb:686:in `block in read_row'
    from /Users/barock/.rvm/gems/ruby-1.9.3-p448@maple/gems/spreadsheet-0.8.2/lib/spreadsheet/excel/reader.rb:651:in `fetch'
    from /Users/barock/.rvm/gems/ruby-1.9.3-p448@maple/gems/spreadsheet-0.8.2/lib/spreadsheet/excel/reader.rb:651:in `read_row'
    from /Users/barock/.rvm/gems/ruby-1.9.3-p448@maple/gems/spreadsheet-0.8.2/lib/spreadsheet/excel/worksheet.rb:47:in `row'
    from /Users/barock/.rvm/gems/ruby-1.9.3-p448@maple/gems/spreadsheet-0.8.2/lib/spreadsheet/worksheet.rb:133:in `block in each'
    from /Users/barock/.rvm/gems/ruby-1.9.3-p448@maple/gems/spreadsheet-0.8.2/lib/spreadsheet/worksheet.rb:132:in `upto'
    from /Users/barock/.rvm/gems/ruby-1.9.3-p448@maple/gems/spreadsheet-0.8.2/lib/spreadsheet/worksheet.rb:132:in `each'
    from /Users/barock/.rvm/gems/ruby-1.9.3-p448@maple/gems/spreadsheet-0.8.2/lib/spreadsheet/excel/worksheet.rb:35:in `each'
    from /Users/barock/.rvm/gems/ruby-1.9.3-p448@maple/gems/spreadsheet-0.8.2/lib/spreadsheet/worksheet.rb:173:in `format_dates!'
    from /Users/barock/.rvm/gems/ruby-1.9.3-p448@maple/gems/spreadsheet-0.8.2/lib/spreadsheet/excel/writer/workbook.rb:110:in `block in sanitize_worksheets'
    from /Users/barock/.rvm/gems/ruby-1.9.3-p448@maple/gems/spreadsheet-0.8.2/lib/spreadsheet/excel/writer/workbook.rb:108:in `each'
    from /Users/barock/.rvm/gems/ruby-1.9.3-p448@maple/gems/spreadsheet-0.8.2/lib/spreadsheet/excel/writer/workbook.rb:108:in `sanitize_worksheets'
    from /Users/barock/.rvm/gems/ruby-1.9.3-p448@maple/gems/spreadsheet-0.8.2/lib/spreadsheet/excel/writer/workbook.rb:393:in `write_from_scratch'
    from /Users/barock/.rvm/gems/ruby-1.9.3-p448@maple/gems/spreadsheet-0.8.2/lib/spreadsheet/excel/writer/workbook.rb:644:in `write_workbook'
    from /Users/barock/.rvm/gems/ruby-1.9.3-p448@maple/gems/spreadsheet-0.8.2/lib/spreadsheet/writer.rb:15:in `block in write'
    from /Users/barock/.rvm/gems/ruby-1.9.3-p448@maple/gems/spreadsheet-0.8.2/lib/spreadsheet/writer.rb:14:in `open'
    from /Users/barock/.rvm/gems/ruby-1.9.3-p448@maple/gems/spreadsheet-0.8.2/lib/spreadsheet/writer.rb:14:in `write'
    from /Users/barock/.rvm/gems/ruby-1.9.3-p448@maple/gems/spreadsheet-0.8.2/lib/spreadsheet/workbook.rb:124:in `write'
    from (irb):56
    from /Users/barock/.rvm/gems/ruby-1.9.3-p448@maple/gems/railties-3.2.11/lib/rails/commands/console.rb:47:in `start'
    from /Users/barock/.rvm/gems/ruby-1.9.3-p448@maple/gems/railties-3.2.11/lib/rails/commands/console.rb:8:in `start'
    from /Users/barock/.rvm/gems/ruby-1.9.3-p448@maple/gems/railties-3.2.11/lib/rails/commands.rb:41:in `<top (required)>'
    from script/rails:6:in `require'
    from script/rails:6:in `<main>'1.9.3p448 :057 >

Currently, if I call row.to_a, the data returned is not correctly decoded to handle Date/DateTime, but it should be.

is it possible to update the formula or write a formula to specify column ?

Hi,

Using spreadsheet gem v0.9.6 I have been working on a spreadsheet where we populate some source data that is then picked up by formulas to dispay results and to generate a piechart.

We have successfully released a feature using the spreadsheet gem, but had several issues generating an input that opens without warnings/errors/missing data in Excel for Mac 2011 after being processed through the spreadsheet gem.

I've been using LibreOffice to generate .xls files, and merely opening and saving the file in Excel and processing it through spreadsheet gem breaks the file opening cleanly back into Excel.

I've created a gist for the script to reproduce it here: https://gist.github.com/morganchristiansson/8707434

2nd issue

Also a 2nd issue, without the following code, affecting the same spreadsheet file.

book.worksheets.each do |sheet|
  sheet.each(0) { |row| row.replace row }
end

The file never opens at all! I found this workaround online and have been using it since I started the feature.

I'm emailing spreadsheet files to zdavatz at gmail dot com

Hello!

Got a little question: how do you run tests in dev env?
Runing rake with hoe installed had no effect, reandming all test files to *_test.rb solved those things.

May be we could do it?

xls files generated by spreadsheet on ruby 2.0.0 are broken.

For example this code generates valid xls on 1.9.3, but not on 2.0.0

require 'spreadsheet'
book = Spreadsheet::Workbook.new
sheet = book.create_worksheet
sheet[0,0] = 'text'
book.write 'file.xls'

https://dl.dropbox.com/u/937160/ruby/file193.xls
https://dl.dropbox.com/u/937160/ruby/file200.xls

ruby 2.0.0p0 (2013-02-24 revision 39474) [i686-linux]
rubygems 2.0.0
spreadsheet 0.8.1

Calling Spreadsheet.open works fine in a simple ruby script, but blows up my machine and (seemingly) never finishes when i try to use it in my rails app. Also blows up in a newly minted rails app that just has gem 'spreadsheet' added

Any guesses as to what might cause this? Core extensions maybe? Is there anything I can do to help debug this problem?

Thinking to just create a separate service to handle xls files, but would be easier to integrate it and access all my existing models and helpers, etc...

Error:

excel/reader.rb:126:in `block in postread_worksheet': undefined method `text' for nil:NilClass (NoMethodError)

File: http://oss.sheetjs.com/test_files/comments_stress_test.xls

CSV content: http://oss.sheetjs.com/reflector.html?file=test_files/comments_stress_test.xls&fmt=csv

Actual script: https://github.com/SheetJS/test_files/blob/master/tests/roo.rb

require 'roo'
require 'spreadsheet'

p ARGV[0]
f = Roo::Spreadsheet.open(ARGV[0])
f.each_with_pagename do |name, sheet|
    p sheet;
end

Hello, i got this exception when tried to open xls file, ruby 1.9.3

Thanks

ArgumentError: comparison of Fixnum with nil failed
        from /home/olkeene/.rvm/gems/ruby-1.9.3-p0@cvchk/gems/spreadsheet-0.7.5/lib/spreadsheet/excel/worksheet.rb:89:in `each'
        from /home/olkeene/.rvm/gems/ruby-1.9.3-p0@cvchk/gems/spreadsheet-0.7.5/lib/spreadsheet/excel/worksheet.rb:89:in `min'
        from /home/olkeene/.rvm/gems/ruby-1.9.3-p0@cvchk/gems/spreadsheet-0.7.5/lib/spreadsheet/excel/worksheet.rb:89:in `recalculate_dimensions'
        from /home/olkeene/.rvm/gems/ruby-1.9.3-p0@cvchk/gems/spreadsheet-0.7.5/lib/spreadsheet/worksheet.rb:122:in `dimensions'
        from /home/olkeene/.rvm/gems/ruby-1.9.3-p0@cvchk/gems/spreadsheet-0.7.5/lib/spreadsheet/worksheet.rb:240:in `row_count'
        from /home/olkeene/.rvm/gems/ruby-1.9.3-p0@cvchk/gems/spreadsheet-0.7.5/lib/spreadsheet/worksheet.rb:213:in `inspect'
        from /home/olkeene/.rvm/gems/ruby-1.9.3-p0@cvchk/gems/activesupport-3.2.8/lib/active_support/core_ext/array/conversions.rb:55:in `inspect'
        from /home/olkeene/.rvm/gems/ruby-1.9.3-p0@cvchk/gems/activesupport-3.2.8/lib/active_support/core_ext/array/conversions.rb:55:in `to_formatted_s'
        from /home/olkeene/.rvm/gems/ruby-1.9.3-p0@cvchk/gems/spreadsheet-0.7.5/lib/spreadsheet/workbook.rb:86:in `%'
        from /home/olkeene/.rvm/gems/ruby-1.9.3-p0@cvchk/gems/spreadsheet-0.7.5/lib/spreadsheet/workbook.rb:86:in `block in inspect'
        from /home/olkeene/.rvm/gems/ruby-1.9.3-p0@cvchk/gems/spreadsheet-0.7.5/lib/spreadsheet/workbook.rb:85:in `collect'
        from /home/olkeene/.rvm/gems/ruby-1.9.3-p0@cvchk/gems/spreadsheet-0.7.5/lib/spreadsheet/workbook.rb:85:in `inspect'

Hi, getting this after upgrade from 0.6.9 to 0.9.6:

sheet = Spreadsheet.open(self.src).worksheet(0)
sheet.rows
*** NoMethodError Exception: undefined method `pack' for nil:NilClass
sheet.rows
[#<Spreadsheet::Excel::Row:0x000000070ebb70 ...

First time when I call sheet.rows it always fail with the same error. I could email you the xls file I am working with.

Best,
Alex

Generate excel from template work very well, but cannot change the worksheet style, for example the border style, font style and etc.

Thanks for your work on spreadsheet, it really very cool.

Hello! I have a problem on opening xlsx file.

Steps to reproduce:

Get this sample xlsx file to current folder:

$ wget https://github.com/pkrumins/gnu-coreutils-cheat-sheet/raw/master/gnu-coreutils-cheat-sheet.xlsx

Open your interactive ruby shell or pry, paste this snippet of code:

require 'spreadsheet'
Spreadsheet.open './gnu-coreutils-cheat-sheet.xlsx'

yields:

Ole::Storage::FormatError: OLE2 signature is invalid
from /Users/Juanito/.rvm/gems/ruby-2.0.0-p353/gems/ruby-ole-1.2.11.7/lib/ole/storage/base.rb:378:in `validate!'

I am running on Mac OS X Mavericks 10.9.1 and using Ruby 2.0.0-p353.

Any ideas? Thanks!

Hi,

Please provide support for "ODS" format which is used in Libreoffice and Openoffice.

Thanks.

Hello, I'm getting this error when trying to save a spreadsheet build with some external data on which I don't have the control of the character encoding.

I've noticed there is a Spreadsheet.client_encoding accessor to override the charset when reading the data, but there is none for modifying the defaults when saving.

I've made a fork with a quick and dirty fix that adds a couple of accessor for overriding the defaults, but maybe that's not the best option. Maybe changing lines 38 and 44 in lib/encodings as follows would be a good fix:

-iconv = @@iconvs[key] ||= Iconv.new('UTF-16LE', client)
+iconv = @@iconvs[key] ||= Iconv.new('UTF-16LE//TRANSLIT//IGNORE', client)

-iconv = @@iconvs[key] ||= Iconv.new('UTF-8', client)
+iconv = @@iconvs[key] ||= Iconv.new('UTF-8//TRANSLIT//IGNORE', client)

what's your opinion?

I'm using multischema app with postgres.
before try spreadsheet for my application, I tried to git clone app https://github.com/jalagrange/excel_test_app everything goes well.

https://github.com/jalagrange/excel_test_app/blob/master/app/controllers/people_controller.rb#L125

  def parse_save_from_excel
    test_file = params[:excel_file]
    file = FileUploader.new
    file.store!(test_file)
    book = Spreadsheet.open "#{file.store_path}"
    sheet1 = book.worksheet 0
    @people = []
    sheet1.each 1 do |row|
      p = Person.new
      p.first_name = row[0]
      p.last_name = row[1]
      p.age = row[2]
      @people << p
    end
    file.remove!
  end

And the problem appears when all of them are applied in my application.

def import
    @account = Account.find(current_adminsekolah.id)
    with_in_schemas :only => @account.schema do
    test_file = params[:excel_file]
    file = ExcelUploader.new
    file.store!(test_file)
    book = Spreadsheet.open "#{file.store_path}"
    sheet1 = book.worksheet 0
    @pelajar = []
    sheet1.each 1 do |row|
      p = Pelajar.new
      p.nama = row[0]
      p.nis = row[1]
      p.tglahir = row[2]
      p.jk = row[3]
      @pelajar << p
      p.save
     end
        redirect_to adminsekolah_pelajars_path
   end
end

admin and account under default public schema, but pelajar under account schema.
When i tried import, data is not imported.

Started POST "/admin/pelajars/import" for 127.0.0.1 at 2013-03-28 19:08:4
0 +0700
Processing by Admin::PelajarsController#import as HTML
  Parameters: {"utf8"=>"Γ£ô", "authenticity_token"=>"bN8hAFJr9EF+/H1bX8U1qmVztf+
jfJdxBIJbtRq0b7Q=", "excel_file"=>#<ActionDispatch::Http::UploadedFile:0x541d238
 @original_filename="template_pelajar.xls", @content_type="application/vnd.ms-ex
cel", @headers="Content-Disposition: form-data; name=\"excel_file\"; filename=\"
template_pelajar.xls\"\r\nContent-Type: application/vnd.ms-excel\r\n", @tempfile
=#<File:C:/Users/zilgis/AppData/Local/Temp/RackMultipart20130328-2996-1tzwgsk>>,
 "commit"=>"Import"}
  Adminsekolah Load (2.0ms)  SELECT "public"."admins".* FROM "public"."ad
mins" WHERE "public"."admins"."id" = 3 LIMIT 1
  Account Load (2.0ms)  SELECT "public"."accounts".* FROM "public"."accounts" WH
ERE "public"."accounts"."id" = $1 LIMIT 1  [["id", 3]]
   (2.0ms)  BEGIN
   (1.0ms)  ROLLBACK
Redirected to http://lvh.me:3000/admin/pelajars
Completed 302 Found in 591ms (ActiveRecord: 47.0ms)
--- Select Schema: account
--- Restore Schema to public

When I'm trying to load spreadsheet gem there's an above error.
I've searched issues history and found similiar problem where no relevant line was in Manifest.txt. I've made fix on forked repo but it's not working as expected.

https://github.com/kacperix/spreadsheet/commit/0e744d2dedd5efbf12be7995881510e64520bf24

After that in my Gemfile I've set:

gem 'spreadsheet', git: '[email protected]:kacperix/spreadsheet.git'

Doesn't work.

I get some incorrect data when trying to access the data in a row like a would an array.

If I am trying to access the data with a range of 0..-1 I am returned with an empty array. This is because the Spreadsheet::Excel::Row#[]:33 method does not respect the fact that my range is inclusive.

idx = idx...(idx+len)

So in my case, it's trying to get a range from 0 to 0 because the range is not inclusive

I'm attempting to install spreadsheet. I've got rvm installed and after entering gem install spreadsheet, I get the following error:

ERROR:  While executing gem ... (Gem::Exception)
    Cannot load gem at [spreadsheet gem] in /Users/nathan/temp

I'll clone it for now.

Hi,

First of all, thanks for creating this gem!

I'm trying to use the gem to load account data using excel. I foresee having 100+ users in a single excel file with 15+ attributes for each user. When I open a sample file(Spreadsheet.open('sample.xls')), this takes considerable time (>1 minute in some cases on a MacBook Pro).

I suggest adding some sort of logging to the console so the user knows that Spreadsheet is still parsing and not in an infinite loop.

In the long term, I suggest looking into how performance could be improved while opening larger spreadsheets.

I originally thought this was an error and looked at a similar sounding issue: #8. For that reason, I'll share the first line of a stack trace after using ctrl-c:

### irb ###
IRB::Abort: abort then interrupt!
    from /Users/nathan/.rvm/gems/ruby-1.9.3-p0@foo/gems/spreadsheet-0.7.7/lib/spreadsheet/workbook.rb:94:in `call'

### rails console ###
IRB::Abort: abort then interrupt!
    from /Users/nathan/.rvm/gems/ruby-1.9.3-p0@foo/gems/activesupport-3.2.11/lib/active_support/core_ext/array/conversions.rb:55:in `call'

Cheers,
Nathan

Is there a way to set annotation for a specific cell?

Would be great if the time objects could retain the milliseconds or microseconds when they are converted to ruby Time objects

When trying to read a write protected file (checked in into a version control system) I received the following error message (on Windows).

C:/Data/Ruby193/lib/ruby/gems/1.9.1/gems/spreadsheet-0.8.1/lib/spreadsheet.rb:69:in initialize': Permission denied - C:/Data/my_file.xls (Errno::EACCES) from C:/Data/Ruby193/lib/ruby/gems/1.9.1/gems/spreadsheet-0.8.1/lib/spreadsheet.rb:69:in open'
from C:/Data/Ruby193/lib/ruby/gems/1.9.1/gems/spreadsheet-0.8.1/lib/spreadsheet.rb:69:in open'`

The only resolution I have is the remove the write protection.

Target: Being able to read files, while they remain write protected.

In my code I have this method:

  def self.xls(infile, outfile, var_hash)
    book = Spreadsheet.open infile
    sheet = book.worksheet 0

    sheet.each_with_index do |row, i|
      row.each_with_index do |cell, j|
        sheet[i, j] = cell.gsub(/\[[A-Z0-9_]+\]/) { |var| var_hash[ var.gsub!(/[\[\]]/, '').downcase.to_sym ] } if cell
      end
    end

    book.write outfile
    return outfile
  end

To use this method I must populate hash with variables and place this variables (hash keys) in square brackets into xls template.

var_hash[:name] = 'Bob'

and in xls [NAME] will be substituted by Bob.

So with this code spreadsheet gem acts like odf-report gem, and it is much easier for me to fill template with variables, than to count rows and cells, because documents that I use have complex structure with many merged cells. It is also easy to delegate task of template modification to somebody else.

Maybe it is nice feature for spreadsheet gem to have.

Hi there.

I have a situation, where I need to lock the file that the spreadsheet is written to e.g. something like this:

File.open("master.xls", "w") do |file|
  file.flock(File::LOCK_EX)
  book.write(file)
  # push file to s3
  file.flock(File::LOCK_UN)
end

I'm doing this to avoid collisions while writing to the temporary master.xls file. When the file has been pushed to s3, i can unlock it again, and the next item in the queue can have a swing at the same temporary file.

Is this possible?

The Chinese word 进 can cause Format Error. I tested on some other Chinese words and there is no problem. Please help to check.

require "spreadsheet"

Spreadsheet.client_encoding = 'UTF-8'
doc = Spreadsheet::Workbook.new
sheet = doc.create_worksheet
row = sheet.row(0)
row.push "进"
doc.write "test.xls"