Rexcel plugin

Posted by Julien France on Dec 23, 2008

Rexcel is a rails plugin made to create custom and stylish excel files from your applications.

It is inspired by Brian P. Hogan's excel plugin that provides a quick an easy way to export your object collections to an excel workbook.

It worked fine but it didn't have enough functionality for my taste. I was using it in a project when I asked myself: “Ok, but what if I want to merge or collapse some excel cells? What if I want to put some text in color, add a border, change the font-size...?”. This excel plugin just exports your data to an excel sheet in a very basic format. I then decided to make a more advanced plugin that will support most of your requirements in terms of formatting.

Rexcel creates XML files formatted for Excel. It use the Builder::XmlMarkup class for that. You can find the documentation of Builder::XmlMarkup here: http://api.rubyonrails.org/classes/Builder/XmlMarkup.html

Rexcel provides a set of methods that permit you to customize your cells' appearance, customize the column's width, add worksheets to a workbook, add lines to worksheets...

Lines added to a worksheet can be taken from an Array or a Hash, but also from an ActiveRecord object. Each cell can have its own formatting style.

Installation

Download the rexcel source code and extract it to a 'Rexcel' directory.

Copy the “Rexcel” folder and its contents to the “vendor/plugins” of your Rails application and restart your web server. The plugin will automatically be loaded and ready for you to use.

Documentation:

The Workbook class

To create a new workbook, you need to create an instance of Rexcel::Workbook

workbook = Rexcel::Workbook.new

- set_default:

By default the sheet's cells have a standard format (12px, align left, no borders...). You can use the workbook.set_default method to add your own formatting preferences. These will be applied to all of the workbook's worksheets. You'll need to pass a Hash containing all the parameters you want to set:

workbook.set_default :size => 12, :color => “#33EEAA”, :border => {:left => true, :bottom => true, style => “double”}

See the list of allowed parameters at the end of this document.

- add_worksheet:

Use the workbook.add_worksheet method to create a new instance of Worksheet:

worksheet = workbook.add_worksheet(“my_worksheet”)

You have to pass the sheet's name as argument of this method.

- build

When your workbook is ready for export, you can use the “build” method to build the complete XML file for excel. Before rendering it in a rails controller, you'll have to set a few headers. Here is an example:

headers['Content-Type'] = "application/vnd.ms-excel"
headers['Content-Disposition'] = 'attachment; filename="excel-export.xlsx"'
headers['Cache-Control'] = 'max-age=0'
headers['pragma']="public”
render :text => workbook.build

The Worksheet class:

- set_default:

This method is similar to the workbook.set_default method but this time this will set the default parameters for the current worksheet only. Each of your worksheets can then have their own default parameters.

worksheet.set_default :color => "#EEFF22", :horizontal => "center"

See the list of allowed parameters at the end of this document.

- set_col_width:

With this method you can set the width of your sheet's columns. You have to pass it a Hash. The keys will refer to the position of the cols you want to set the width and the value will be the width itself. A special key :default will be used to set the width of all the cols that are not included in your hash's keys.

worksheet.set_col_width :default => 40, 1 => 50, 3 => 50, 9 => 60, 13 => 25

- add_line:

This method is used to add a line (a row) to your worksheet. It can take four arguments. ->The first one is the line itself. It can be given alone. A line can be an Array of value, a Hash or an ActiveRecord object. If you use an ActiveRecord object, Rexcel will convert it to a Hash. The keys will be the names of the object's attributes and the values will be the attributes' values.

worksheet.add_line([“Cell1”, “Cell2”, “Cell3”])
worksheet.add_line({:title1 => “Cell1”, :title2 => “Cell2”, :title3 => “Cell3”})
worksheet.add_line(MyActiveRecordObject.find(:first))

->The second argument you can give to add_line is a Hash of parameters. If you don't set this argument, the cells of your line will take the default formatting that was set with worksheet.set_default (or it will take the default formatting set with workbook.set_default if you haven't set default parameters for the worksheet).

You're now able to customize the formatting styles for the cells of a particular line.

worksheet.add_line([“Cell1”, “Cell2”, “Cell3”], {:horizontal => “right”, :color => “#EEFCAA”, :size => 14})

->The third argument is particularly useful when you give an ActiveRecord object or a Hash in the first argument. It is a sorting array. Try adding an ActiveRecord object to your worksheet and you will see that all the values are randomly added to the sheet. You may also want to add only particular values of the ActiveRecord object to your sheet. The sorting array will then permit you to specified which value you want to add and in which order. The value of the sorting array should correspond to keys of the first argument hash. (They can be a string or a symbol, which can be used interchangeably.)

worksheet.add_line({:title1 => “Cell1”, :title3 => “Cell3”, :title2 => “Cell2”}, {}, [:title1, "title2"])

->When you use an ActiveRecord object, you'll sometimes want to add the titles (attribute's names) of you object to your worksheet. And you'll maybe want them to have a particular format style. You can then use the last argument of add_line.

This argument is a hash. The first key of this hash should be :title and its value should be a boolean. If you set it to true, Rexcel will add a line with the title of your Activerecord object to the sheet. If you have given a sorting array, the titles will be the values of this array. If you want your titles to have a particular style, you can add the :params key to the hash. The value should be a hash containing the formatting styles you want (See the list of allowed parameters at the end of this document).

worksheet.add_line({:title1 => “Cell1”, :title3 => “Cell3”, :title2 => “Cell2”}, {}, [], {:title => true, :params => {:bold => true, :underline => double}})
#=> This will add a line with: title1, title3, title2

worksheet.add_line({:title1 => “Cell1”, :title3 => “Cell3”, :title2 => “Cell2”}, {}, [:title1, :title2], {:title => true, :params => {:bold => true, :underline => double}})
#=> This will add a line with: title1, title2

Giving a sorting array to the method add_line may look obvious if you give an Array of value in the first argument but it can make sense if you set the :title parameter to true. Indeed, in that case the sorting array's values will be added to the sheet as title.

worksheet.add_line([“Cell1”, “Cell2”, “Cell3”], {}, [:title1, :title2, :title3], {:title => true, :params {:bold => true, :underline => double}})
#=> This will add a line with: title1, title2, title3

- add_lines:

The add_lines method is the plural version of add_line. It takes the same parameters as add_line except the first one that should be an array of lines (array of arrays, array of hashes or array of ActiveRecord objects). In this case, when we set the :title parameter to true, Rexcel will take the value in the sorting array if it has one or it will take the keys of the first hash in the first argument.

worksheet.add_lines([[“Cell11”, “Cell12”][“Cell21”, “Cell22”]])
#=> This will add:
#| Cell11 | Cell12 |
#| Cell21 | Cell22 |

- skip:

The skip method can be used to skip lines in your worksheet. Actually, this simply adds 1 or more empty lines with default parameters. You can give the number of lines you want to skip in argument.

worksheet.skip(3)

This method can sometimes be useful when you use rowmerge. Let's see an example:

worksheet.add_line([“Cell1”, “Cell2”], {:rowspan => 2})
worksheet.add_line([“Cell3”, “Cell4”]
#See what this code does. It may not be what you expected to be. “cell3” and “cell4” are added at the third and forth position of line 2.
#If you want “cell3” and “cell4” to be under “cell1” and “cell2” you'll have to skip a line
worksheet.skip
#Then you've got what you want.

- add_empty:

This method is an improved version of “skip”. add_empty allows you to add an empty line with merged cells. The first parameter you can give it is the number of cells you want merged. The second parameter can be a Hash containing the formatting styles you want for this empty line. (See the list of allowed parameters at the end of this document).

worksheet.add_empty(10, {:back_color => “#DDDDDD”})

Values format:

We have seen that we can set the default formatting parameters for the whole workbook, for a particular worksheet, or for a particular line in a worksheet.

Now let's see how to add formatting parameters to a particular cell. We know that a line can be an Array, a Hash or an ActiveRecord object. We also know that the values are the Array's elements, the Hash's value or the ActiveRecord attribute's values. But what is the format of the value itself?

Actually, the value can be an Array, a Hash or a String (or any object that responds to the method :to_s) If you don't want to customize your cell's formatting parameters, you can just use the String format.

worksheet.add_line [“Cell1”, “Cell2”, “Cell3”]

If you want to give your cells a particular look, you can use the Array format. The first element in the array should be the value itself. The second element should be a Hash containing the formatting styles you want for this cell. (See the list of allowed parameters at the end of this document).

worksheet.add_line [[“Cell1”, {:color => “#332211”}], [“Cell2”, {:color => “#112233”}]]
#If you want to add an empty cell with a particular formatting style, you can just give the Hash containing the formatting styles.
#You can also pass a value in the the Hash with the key :value.
worksheet.add_line [{:value => “Cell1”, :color => “#332211”}, {:color => “#112233”}]

With all that stuff you're now ready to make fully customized excel files from your Rails application.

Examples:

Example 1:

Imagine you have a Customer model and that you want to export the first_name, last_name, nick, email and password of all your customers to an excel file.

Here is the simplest way to export your model to excel:

class ExcelController < ApplicationController
def excel_export
  workbook = Rexcel::Workbook.new
  worksheet = workbook.add_worksheet("Customers")
  worksheet.add_lines(Customer.find(:all))
  headers['Content-Type'] = "application/vnd.ms-excel"
  headers['Content-Disposition'] = 'attachment; filename="excel-export.xlsx"'
  headers['Cache-Control'] = 'max-age=0'
  headers['pragma']="public"
  render :text => workbook.build
end
end

This code will give you the followings result:

We can see that all the fields are randomly added to the excel file and that there are some fields we don't need. There is no title so we don't know what we see.

Let's remove the unnecessary fields, add titles and order it:

class ExcelController < ApplicationController
def excel_export
  workbook = Rexcel::Workbook.new
  worksheet = workbook.add_worksheet("Customers")
  worksheet.add_lines(Customer.find(:all), {}, [:first_name, :last_name, :nick, :email, :password], {})
  headers['Content-Type'] = "application/vnd.ms-excel"
  headers['Content-Disposition'] = 'attachment; filename="excel-export.xlsx"'
  headers['Cache-Control'] = 'max-age=0'
  headers['pragma']="public"
  render :text => workbook.build
end
end

Here is the new result:

This file looks ordered well but it is not pretty at all. You can't distinguish the record's values from the titles, the email can't be read because of the cell's width...

Let's create a prettier excel export:

class ExcelController < ApplicationController
  def excel_export
    workbook = Rexcel::Workbook.new
    worksheet = workbook.add_worksheet("Customers")
    worksheet.set_col_width({:default => 50, "1" => 70, "2" => 60, "4" => 130})
    main_title_params = {:colspan => 5, :rowspan => 2, :size => 16, :underline => "double", :italic => true, :color => "#990066"}
    worksheet.add_line([["Customer list:", main_title_params]])
    worksheet.skip
    worksheet.add_empty(5)
    title_params = {:border => {:style => "double", :top => true, :bottom => true, :left => true, :right => true, :color => "#660000"}, :color => "#FF9900"}
    worksheet.add_lines(Customer.find(:all), {:border => {:left => true, :right => true, :top => true, :bottom => true}}, [:first_name, :last_name, :nick, :email, :password], {:title => true, :params => title_params})
    headers['Content-Type'] = "application/vnd.ms-excel"
    headers['Content-Disposition'] = 'attachment; filename="excel-export.xlsx"'
    headers['Cache-Control'] = 'max-age=0'
    headers['pragma']="public"
    render :text => workbook.build
  end
end

Here is the result. This time, it looks better:

Example 2:

class ExcelController < ApplicationController
  def excel_export
    workbook = Rexcel::Workbook.new
    workbook.set_default :horizontal => "center", :border => {:left => true, :right => true, :top => true, :bottom => true}
    worksheet = workbook.add_worksheet("Customers")
    worksheet.set_col_width({:default => 50, "1" => 70, "2" => 80, "4" => 130})
    main_title_params = {:vertical => "top", :colspan => 6, :rowspan => 3, :size => 16, :underline => "double", :italic => true, :color => "#990066"}
    worksheet.add_line([["Customer list:", main_title_params]])
    worksheet.skip
    worksheet.skip
    title_params = {:border => {:style => "double", :top => true, :bottom => true, :left => true, :right => true, :color => "#660000"}, :color => "#FF9900"}
    worksheet.add_line [
    ["Title1", {:colspan => 2, :rowspan => 2}],
    ["Title2", {:colspan => 4}]
    ], title_params
    worksheet.add_line [
    ["Title2-1", {:rowspan => 2, :color => "", :vertical => "bottom"}],
    ["Title2-2", {:colspan => 3}]
    ], title_params
    worksheet.add_line ["Title1a", "Title1b", "Title2-2a", "Title2-2b", "Title2-2c"], title_params.merge({:color => ""})
    Customer.find(:all).each do |customer|
      worksheet.add_line [
      customer.first_name,
      customer.last_name.upcase,
      [customer.nick, {:italic => true}],
      [customer.email, {:color => "#231ABB"}],
      customer.password,
      {:back_color => "#EEEEEE"}
      ]
    end
    headers['Content-Type'] = "application/vnd.ms-excel"
    headers['Content-Disposition'] = 'attachment; filename="excel-export.xlsx"'
    headers['Cache-Control'] = 'max-age=0'
    headers['pragma']="public"
    render :text => workbook.build
  end
end

Here is the result you should have:

List of allowed parameters for the setting's hashes:

Here is a list of all the parameters with their values you can use in hashes when you custom cell's styles.

:colspan => Use to merge two or more cells horizontally. The value should be the number of cells you want to merge.
The default value is 1 (no merge)

:rowspan => Use to merge two or more cells vertically. The value should be the number of cells you want to merge.
The default value is 1 (no merge)

:horizontal => The horizontal alignment of the text in the cell. Value can be: “left”, “right”, “justified” or “center”.
The default value is: “left”

:vertical => The vertical alignment of the text in the cell. Value can be: “center”, “top” or “bottom”
The default value is: “center”

:bold => The value must be boolean. Set it to true if you want your text in bold.
The default value is false.

:italic => The value must be boolean. Set it to true if you want your text in italic.
The default value is false.

:underline => Use it to underline your text. The value can be: “single” or “double”
The default value is nil

:size => Use to set the text's size. The value must be include in the range: (2..99)
The default value is 12

:font => Use to set the font style of your text. The value should be the name of a supported font style (If your excel software don't support it, the default font style will be set to: “Arial”.
The default value is “Arial”

:back_color => The background color of the cell. The value should be a valid hexadecimal color code (ex: “#EE12AF”)
The default value id: “#000000”

:color => The color of your text. The value should be a valid hexadecimal color code (ex: “#EE12AF”)
The default value id: “#000000”

:border => Use to add borders to your cells. The value must be a hash containing the followings:

   :left => The value must be boolean. Set it to true to add a left border to your cell.
   The default value is false.

   :right => The value must be boolean. Set it to true to add a right border to your cell.
   The default value is false.

   :top => The value must be boolean. Set it to true to add a top border to your cell.
   The default value is false.

   :bottom => The value must be boolean. Set it to true to add a bottom border to your cell.
   The default value is false.

   :style => The style of the border. The value ca be: “continuous”, “double”, “dotted” or “dashed”
   The default value is “continuous”

   :color => The color of your border. The value should be a valid hexadecimal color code (ex: “#EE12AF”)
   The default value id: “#000000”

   :weight => The weight of your border. The value should be included in the range: (0..10)
   The default value is 1