what's gem can operate Excel on linux?
The Ruby Spreadsheet gem will work for you https://rubygems.org/gems/spreadsheet
Reading and writing Excel files using Ruby on a server without Excel installed
I agree with Gonzih, and I use roo fairly regularly. It allows me to read, write, and write using a template file.
The project is fairly well documented on their site.
I always use something like:
input = Excel.new(path)
output = Array.new
input.default_sheet = input.sheets[sheet]
start.upto(input.last_row) do |row|
output << input.row(row)
end
p output
=> a nested array representing the spreadsheat.
p output[0]
=> [row1_column_a, row1_column_b...]
to read a spreadsheet. note that the roo gem requires you to use Excelx.new
instead of Excel.new
if your file is a .xlsx.
to write you can:
book = Spreadsheet::Workbook.new
write_sheet = book.create_worksheet
row_num = 0
input.each do |row|
write_sheet.row(row_num).replace row
row_num +=1
end
book.write "/path/to/save/to.xls"
where input is an array structured just like output was
Write in an existing Excel .xls file which contains macros
I found a solution :
I use the POI library of Apache which is written in java with the rjb gem (Ruby Java Bridge, which allows to use java libraries with ruby). POI allows to keep macros and formulas of existing xls file and to modify it.
For those who need, here's how to set up rjb to use POI :
# JVM loading
apache_poi_path = File.dirname(__FILE__)+'/poi-3.8/poi-3.8-20120326.jar'
Rjb::load("#{apache_poi_path}", ['-Xmx512M'])
# Java classes import
@file_class = Rjb::import('java.io.FileOutputStream')
@workbook_class = Rjb::import('org.apache.poi.hssf.usermodel.HSSFWorkbook')
@poifs_class = Rjb::import('org.apache.poi.poifs.filesystem.POIFSFileSystem')
Rjb::import('org.apache.poi.hssf.usermodel.HSSFCreationHelper')
Rjb::import('org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator')
@cell_reference_class = Rjb::import('org.apache.poi.hssf.util.CellReference')
@cell_class = Rjb::import('org.apache.poi.hssf.usermodel.HSSFCell')
# You can import all java classes that you need
# Java classes utilisation :
@file_input_class = Rjb::import('java.io.FileInputStream')
@file_input = @file_input_class.new(model_file_path)
@fs = @poifs_class.new(@file_input)
@book = @workbook_class.new(@fs)
@worksheet = @book.getSheet('worksheet')
# ...
# You can use your objects like in Java but with a ruby syntax
ruby excel reader, spreadsheet gem
Did you already take a look to roo?
-> http://rubygems.org/gems/roo
Parsing XLS Spreadsheet in Rails using Roo Gem
Looking at the source for Excel.new, it seems that it wants a file name, not a File object or handler. In other words, it needs string representation of the full path, including filename, to the the file you want to parse. Also, it checks the extension of the file. So if the tempfile doesn't end with ".xls" you'll need to rename the file first.
Generating Excel documents with Ruby
How about opening a spreadsheet (Spreadsheet::Excel) with the formulae already populated. Entering data into the referenced cells will cause them to display the result.
Related Topics
What Do I Need to Do to Get Hash.From_Xml() to Work
Ruby on Rails: Two References with Different Name to the Same Model
Why Are My Thread Variables Intermittent in Rails
Using Each_With_Index with Map
Use Same Controller for Multiple Routes
How to Return Everything After Last Slash(/) in a Ruby String
Using %I and %I Symbol Array Literal
Coping with "String Contains Null Byte" Sent from Users
Nokogiri Recursively Get All Children
Generate a 'Link_To' to the Controller Action 'Edit', Dynamically
Using Ruby 2.0 on Amazon Opsworks
Rails 5, "Nil Is Not a Valid Asset Source"
Rake: Logging Any Task Executing
Thin Doesn't Respond to Sigint or Sigterm
Rails Routing: Giving Default Values for Path Helpers
Which Ruby Rest API Client for Neo4J