Geek Answers Handbook

Write to an existing Excel.xls file that contains macros

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

I tried to insert data into a file using a spreadsheet, but when I save the changes, the format and all macros of the file are lost.

Here is 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 many things, did research on forums and on the Internet, but I did not find a solution ... Does anyone have an idea?

+6
source share
4 answers

I found a solution:

I use the Apache POI library, which is written in java using rjb gem (Ruby Java Bridge, which allows you to use java libraries with ruby). POI allows you to save macros and formulas of an existing xls file and modify it.

For those who need this, here's how to configure 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
+4
source

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

If you have multiple sheets, you need to rewrite other sheets

XLS with several sheets, but just modify one of the sheets (and do not touch other data), there is no way for the table to “remember” what is in the other sheets. You will need to write unmodified sheets, as well as unforeseen events.

Ergo: write a modified sheet, and also write a complete unmodified sheet again, when changing XLS with a table with multiple sheets.

+2
source

You might want to check out Axlsx , you are not sure if it can edit a simple .xls, but I worked with it a few weeks ago, it worked wonders for the xlsx that I worked with.

+1
source

You just need to open an existing file, write the changes to the file and save it with a different name. For example, on the server you have a template.xls file.

A simple working example (you need to have template.xls next to 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'
#edit_xls.rb require 'rubygems' require 'spreadsheet' book = Spreadsheet.open 'template.xls' sheet = book.worksheet 0 sheet[0,0] = 'qweqeqw' book.write 'edited.xls'
+1
source

More articles:


All Articles