Writing to excel files in ruby using roo gem

Question

I am parsing Excel and Excelx file using Roo gem. But I am not sure how to write in those files. set_value(row, column, text) method is not working.

Code

@oo = Excelx.new('tes.xlsx')
@oo.default_sheet = @oo.sheets.first

def return_column
  keywords = ["website", "url"]
  keywords.each do |keyword|
  1.upto(@oo.last_column) do |n|
  data = @oo.cell(1, n)
  return n if data.downcase=~/#{keyword}/i
end
end
end

def return_rows
  n = return_n
  2.upto(@oo.last_row) do |row|
  data = @oo.cell(row, n)
  stack << data 
 end
end

def appender
  @oo.set_value(1,11, "hey")
end

 appender

The Error Message I'm getting is

 /.rvm/gems/ruby-1.8.7-p352/gems/roo-1.10.1/lib/roo/generic_spreadsheet.rb:441:in `method_missing': private method `set_value' called for #<Excelx:0x101221f08> (NoMethodError)
from /Users/bhushan/.rvm/gems/ruby-1.8.7-p352/gems/roo-1.10.1/lib/roo/excelx.rb:168:in `method_missing'
from parser.rb:32:in `appender'
from parser.rb:35

Could you show us your full code? Are you applying this method to an existing object? — JMax, Dec 13 '11 at 20:24
you can use the set methods some answers suggest but you will only be able to save the result as a CSV file, nots as a XLSX file, for that you need to use an alternative like the rubyXL gem — peter, Sep 04 '19 at 11:48

score 4 · Answer 1 · answered May 26 '17 at 01:14

No answers here actually answer the question of how to do this with Roo, so I'll add the solution that I just tested in our app.

Roo recently added functionality for editing cells: https://github.com/roo-rb/roo/blob/master/lib/roo/csv.rb#L42

You can use it like such:

sheet.set_value(1, 5, 'TEST', nil) # to set the 1st row, 5th column to the string 'TEST'

Notes:

The last argument nil is not used in the function but has no default so it's required.
This is only added in version 2.7.0.

score 3 · Answer 2 · answered Jan 31 '12 at 04:16

Try 'set' method instead of 'set_value' method in Excelx or OpenOffice object. For more refer API http://rubydoc.info/gems/roo/1.10.1/frames and I think roo gem specializes in reading excel contents than writing. For instance using set method will not save back to the spreadsheet file. It saves on the buffer I think. Try some other gems for writing

Aaron Henderson · Answer 3 · 2013-02-15T23:19:53.830

You can set the value of a column by pushing a string into it.

sheet.row(0).push 'some value'

The code below writes to a spreadsheet

require 'spreadsheet'

class Util::Table < ActiveRecord::Migration

  def self.create_import_template
    # create an xls workbook template for data importing based on models in activerecord
    @format = Spreadsheet::Format.new(:weight => :bold)
    @template_folder = File.join(Dir.home, 'Dropbox', 'horizon', 'data', 'templates')
    @template_file = File.join(@template_folder, "data_import_template_#{Time.now.round(3).to_s.chomp(' -0700').gsub(':','-').gsub(' ','_').chop.chop.chop}.xls")
    @book = Spreadsheet::Workbook.new
    ActiveRecord::Base.send(:subclasses).each {|model| add_worksheet_to_template(model)}
    @book.write @template_file
  end

  def self.add_worksheet_to_template(model)
    # create a tab for each model that you wish to import data into
    write_sheet = @book.create_worksheet :name => model
    write_sheet.row(0).set_format(0, @format)
    model.columns.each_with_index do |c,i|  
      column = ""
      column << "*" unless c.null # indicate required field 
      column << c.name
      write_sheet.row(0).set_format(i+1, @format)
      write_sheet.row(0).push column
    end
  end
end

Can you guide me how to edit an XLS using spreadsheet gem and apply formatting on top of that without creating a new XLS. — Kishore, Mar 05 '15 at 01:34
Kishore, I don't think you can write back to the same xls that you are opening. I suggest creating a new file, even if it's just a copy of the original. — Aaron Henderson, Mar 07 '15 at 17:05
Oh thanks I did approached the suggestion which you gave already which involves lot of coding. — Kishore, Mar 07 '15 at 18:09

score 1 · Answer 4 · answered Dec 22 '18 at 10:54

1

you can use set method

sheet.set(row, col, value)

answered Dec 22 '18 at 10:54

Mark Nguyen

11
2

Writing to excel files in ruby using roo gem

4 Answers4