What's Gem Can Operate Excel on Linux

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



Leave a reply



Submit