Using StringIO with Roo and XLSX fails.

[stevendaniels]

I discovered this issue in current versions of Roo while looking into #264.

When a StringIO is used, Roo fails to open the file properly. This failure stems from the fact that Roo::Excelx::Extractor (and its child classes) do not properly handle streams.

Based on the CHANGELOG.md, this behavior has been broken since at least Roo 2.2.0.

[electron0zero]

Don't know how relevant it is to this issue but ran into a similar issue using roo, will mention it anyways

I was using open function to get data from a URL(which uses OpenURI under the hood), and openuri has dual personality. see: https://stackoverflow.com/questions/10496874/why-does-openuri-treat-files-under-10kb-in-size-as-stringio

I fixed it by forcing openuri to always return Tempfile, see: http://winstonyw.com/2013/10/02/openuris_open_tempfile_and_stringio/

[paneq]

typical uscase - in testing responses:

  get :show, params: {id: 2, format: :xlsx}
  file = StringIO.new(response.body)
  Roo::Excelx.new(file)

[parthibeyond]

@stevendaniels @paneq Thanks

[momolog]

@paneq What is a good workaround for this exact usecase?

[momolog]

To answer my own question for a workaround (using Tempfile):

get :show, params: {id: 2, format: :xlsx}    

file = Tempfile.new(['excel', '.xlsx'])
file.write response.body
file.close

Roo::Excelx.new(file.path)

[ShisiJu]

@momolog Hello, I try to use the code . but it doesn't work. Do you have some advices?

file = Tempfile.new(['excel', '.xlsx'])
file.write response.body
file.close

Roo::Excelx.new(file.path)

2.6.3 :021 > Roo::Excelx.new(file.path) Traceback (most recent call last): 2: from (irb):21 1: from (irb):21:in `new' Zip::Error (File /tmp/excel20200806-1156-13rjiv0.xlsx has zero size. Did you mean to pass the create flag?)

It warns xss has zero size, Did you mean to pass the create flag? How to resolve the problem?

my gem file

gem 'roo', '~> 2.7.0'
gem 'roo-xls', '~> 1.2.0'
gem 'spreadsheet', '~> 1.2.4'

[jcstringer]

@momolog Hello, I try to use the code . but it doesn't work. Do you have some advices?

file = Tempfile.new(['excel', '.xlsx'])
file.write response.body
file.close

Roo::Excelx.new(file.path)

2.6.3 :021 > Roo::Excelx.new(file.path) Traceback (most recent call last): 2: from (irb):21 1: from (irb):21:in `new' Zip::Error (File /tmp/excel20200806-1156-13rjiv0.xlsx has zero size. Did you mean to pass the create flag?)

It warns xss has zero size, Did you mean to pass the create flag? How to resolve the problem?

my gem file

gem 'roo', '~> 2.7.0'
gem 'roo-xls', '~> 1.2.0'
gem 'spreadsheet', '~> 1.2.4'

I think you need to pass your file to Roo as well as callfile.rewind before you close it. With a tempfile, I believe it disappears when you close it. Here is some sample code that I would expect should work

file = Tempfile.new(['excel', '.xlsx'])
file.write response.body
file.rewind
excel = Roo::Excelx.new(file.path)
# Do what you want with the excel file

# Close and unlink the file
file.close
file.unlink

[Crammaman]

I was able to address this issue by using Roo::Spreadsheet(string_io, extension: :xlsx)

My scenario is a bit different though, I'm using a gem for downloading an S3 objects from Amazon that come through as StringIO. However this solution will like help others who end up here.