Write in an existing Excel .xls file which contains macros

Question

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?

Answer 1

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

Answer 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.