Creating xlsx file using roo gem

Question

I was using spreadsheet gem for reading and writing data to xls file. It was kind of easy. Now I need to work on xlsx file and I guess spreadsheet doesn't support xlsx files.

I found roo gem supports xlsx.

For creating of xls file using spreadsheet, It was something like

book = Spreadsheet::Workbook.new
sheet = book.create_worksheet
sheet.row(0).concat %w[col1 col2 col3 col4]

For reading

book1 = Spreadsheet.open('Data/CA.xls')
read_sheet = book1.worksheet('sheet')

How to create and read using roo.

Answer 1

Actually, roo supports both, but the projects are now split.

To have xlsx support you need to:

require 'roo'

To have xls support you need to require both:

require 'roo'
require 'roo-xls'

You can't create a spreadsheet excel file with roo it specializes on reading such file.

For reading then you need to:

require 'roo'

xlsx = Roo::Spreadsheet.open('./read.xlsx')

# get basic information about spreadsheet
xlsx.info
# get sheets
xlsx.sheets
# => ['Wow', 'greet1', 'greet2'] 
# get first row in the sheet Wow
xlsx.sheet('Wow').row(1)

For more information visit the roo's github pages

To create an excel file

To write an Excel file I would recommend Axlsx gem. I did not use it for some time now, but it used to be quite good.

There is whole buch of examples right at github - example.rb.

They are better than I would come up here so I'll post one of them:

Edit - missing output file

require 'axlsx'
p = Axlsx::Package.new
wb = p.workbook

## A Simple Workbook
if examples.include? :basic
  wb.add_worksheet(:name => "Basic Worksheet") do |sheet|
    sheet.add_row ["First Column", "Second", "Third"]
    sheet.add_row [1, 2, 3]
    sheet.add_row ['     preserving whitespace']
  end
end

p.serialize("example.xlsx")

Answer 2

Exporting data with multiple XLSX spreadsheets single sort on Rails

ods.sheets

out_put = ['Info', 'Sheet 2', 'Sheet 3']

ods.each do |sheet|


   ods.default_sheet = sheet

     ods.each_with_index do |name, sheet|

       p sheet.row(1)
     end 
end