6

I want to insert datas an existing Excel (.xls) file with Ruby under Linux. This file has already data, it has some format properties and it contains macros.

I tried to insert data into the file with the spreadsheet gem but when I save modifications, the format and all the macros of the file are lost.

Here's an example of a simple modification where I meet this problem : 

book = Spreadsheet.open('myOriginalFile.xls')
sheet = book.worksheet 0
sheet.write('C12','hello')
book.write('myModifiedFile.xls')

I tried lots of things, did research on forums and the web but I didn't find a solution... Does anyone has an idea?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Jerem
  • 191
  • 1
  • 9
  • I don't know about xls but i had success with odt a while back. Odt files are basically archives with, amongst other things, editable XML. – jsj Nov 08 '12 at 14:40
  • @trideceth12 : I saw it was possible with odt (with xlsx too apparently) but I absolutely have to work with an xls file... – Jerem Nov 08 '12 at 14:48

4 Answers4

4

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
Jerem
  • 191
  • 1
  • 9
  • Why did you not use C++,Php,Erlang,Python or Basic modules? :D – bmalets Sep 17 '13 at 18:36
  • 1
    I needed to write into existing xls files too. Thanks to this answer I used Apache POI and made a gem to save time if you need this as I did. https://github.com/hakunin/poi_spreadsheet – hakunin Oct 12 '14 at 08:36
2

You need to write the modified file to a new file name. Check out this

If you have more than one sheet, you need to rewrite other sheets

XLS with several sheets but only modify one of the sheets (and don't touch the other data), there is no way, that spreadsheet "remembers" what is in the other sheets. You will have to write the unmodified sheets as well or otherwise unexpected things will happen.

Ergo: Write the modified sheet and also write the complete unmodified sheets again, when modifying an XLS with spreadsheet with several sheets.

Emmanuel N
  • 7,350
  • 2
  • 26
  • 36
  • I already do like this and it doesn't work (here the original file name is "myOriginalFile.xls" and the new file name is "myModifiedFile.xls") – Jerem Nov 08 '12 at 14:56
  • if you have more than one sheet you need to rewrite other sheets also or if you don't need them just delete them. – Emmanuel N Nov 08 '12 at 15:01
  • Thanks for your help. I did it and it resolved the problem of the format but macros are lost anyway... – Jerem Nov 08 '12 at 15:09
1

Might want to check out Axlsx not sure if it will be able to edit a plain .xls, but I did some work with it a few weeks ago, it worked wonders for the xlsx I was working with.

Eugene
  • 4,829
  • 1
  • 24
  • 49
1

You just need to open existing file, write your changes into file and save it with another name. For example on server you have template.xls file.

Simple working example ( need to have template.xls near your .rb file ):


#edit_xls.rb
require 'rubygems'
require 'spreadsheet'

book = Spreadsheet.open 'template.xls'
sheet = book.worksheet 0
sheet[0,0] = 'qweqeqw'
book.write 'edited.xls'
bmalets
  • 3,207
  • 7
  • 35
  • 64