Generate XLS documents through the use of ”.rxls” views.

Dependencies

• JDK 1.4 or greater
• YAJB 0.8.1 or greater
• Jakarta POI (No need for install. Supplied as part of this package)

INSTALLATION INSTRUCTIONS

• First, YAJB (yet another java bridge) needs to be installed
  • You can download YAJB from here
    Extract the tar to a temp directory and run setup.rb to install YAJB.

• Next, install the Rails XLS plugin itself into your Rails project: ruby script/plugin install svn://rubyforge.org//var/svn/railsxls/trunk/

Usage

Example Objective: You want to generate a downloadable Excel workbook called mydoc.xls

• Create a controller named main and a view named make_my_excel_doc (i.e. ruby script/generate controller main make_my_excel_doc)

• There is now a file within the app/views/main directory called make_my_excel_doc.rhtml. Rename this file to make_my_excel_doc.rxls. View names must use a .rxls as the extension.

• Open the file main_controller.rb in the app/controllers directory.

• Change the function called make_my_excel_doc to read exactly as follows:
  

    
    def make_my_excel_doc
      @rails_worksheet_name = 'mydoc.xls'
      render :action => "make_my_excel_doc", :layout => false
    end
  

• Open the file renamed in Step 2 (make_my_excel_doc.rxls) and delete any text found within the file.

• This rxls file is where you specify what information will end up in your Excel document. The rxls files have access to a built-in object called workbook to manipulate the workbook that will be generated.

• You should now have an empty file called make_my_excel_doc.rxls. Add the following code to this ‘empty’ file(this is where you specify what data goes into the workbook and where it goes in):
  

  sheet = workbook.createSheet("new sheet") row = sheet.createRow(0) 
  row.createCell(0).setCellValue(1) 
  row.createCell(1).setCellValue(1.2) 
  row.createCell(2).setCellValue("This is a Testing Row") 
  row.createCell(3).setCellValue("All Hail DHH!")
  

• Fire up webrick (or whatever server you have) and navigate to http://localhost:3000/main/make_my_excel_doc and you should be prompted to download your Excel workbook.

END of EXAMPLE

1. For large Worksheet documents
2. To overcome YAJB’s out of process call overhead, a custom Java Helper is bundled with this plugin. This gives pretty much a very fast method to write cells.

1. Use CellBatch to batch the cell inserts/updates
2. Use RowGroupBatch to batch the row grouping calls

1. When only single sheet is present, the cell as well as row group changes are applied at once aumatically as a single Batch call using a custom java Helper function

1. When multiple sheets are present, after doing the sheet specific cell as well as group operations call CellBatch.write_to(sheet_object) for cell updates. Call RowGroupBatch.group_rows(sheet_object) to apply rowGroupings.

1. See cell_batch.rb code for options on setting formula cells.

CellBatch.add(5,1,”Row 6 col 1”)
CellBatch.add(5,2,”Row 6 col 2”)
CellBatch.add(7,1,”Row 8 col 1”)
CellBatch.add(8,2,”Row 9 col 2”)
RowGroupBatch.add(7,8)
CellBatch.add(9,1,”Two rows above is grouped”)

Additional POI Usage Information

See Jakarta POI Project Home Page below for a quick guide on POI Usage

http://jakarta.apache.org/poi/hssf/index.html

Jakarta POI API Doc link is given below:

http://jakarta.apache.org/poi/apidocs/index.html?org/apache/poi/hssf/usermodel/package-summary.html

Homepage:

http://rubyforge.org/projects/railsxls/

Install with:

ruby script/plugin install svn://rubyforge.org//var/svn/railsxls/trunk/

See also Plugins