Designing Spreadsheets using Axlsx in Rails

As a Rails developer we often need to create custom reports in some format – pdf, ppt, xlsx etc. Here I will explain how to generate spreadsheets in Rails with custom design and style. To generate  spreadsheets we will be using a tool. Tool…? No! In Rails there are no ‘tools’; there are ‘gems’ and the one we shall use is Axlsx.

What is Axlsx? Axlsx is a Ruby gem which generates an open office spreadsheets with custom design and styles, custom and automated width and height of columns and rows, images (with links), merged cells, formulas, data types and formats, validations, hyperlinks and charts (Pie, Bar, line charts 2D and 3D). So lets start generating excel worksheets with Axlsx.

First thing first create rails application and add

 gem 'axlsx'

to your Gemfile. Then install the bundle

 bundle install

Lets create a Student model to represent data in a worksheet with fields like fname, lname, marks, percentage, grade and remark.

rails generate model student fname:string lname:string marks:integer percentage:float grade:string remark:string

And now the corresponding controller.

 rails generate controller students

To start with, lets create basic Excel (.xlsx) report

First instantiate Axlsx::Package class and get workbook object to work on and like

package = Axlsx::Package.new
workbook = package.workboook
@students = Student.all
workbook.add_worksheet(name: "Basic work sheet") do |sheet|
  sheet.add_row ["First Name", "Last Name", "Marks", "Percentage", "Grade", "Remark"]
  @students.each do |st|
    sheet.add_row [st.fname, st.lname, st.marks, st.percentage, st.grade, st.remark]
  end
end
pacakege.serialize
send_file("#{Rails.root}/tmp/basic.xlsx", filename: "Basic.xlsx", type: "application/vnd.ms-excel")

Here just now created basic xlsx with name ‘Basic work sheet’.

Now let me take you through the styles – How can we add custom styles to worksheet? Axlsx has a rich set of functionality to add styles to a worksheet. Lets define a basic style.

workbook.styles do |s|
  heading = s.add_style alignment: {horizontal: :center}, b: true, sz: 18, bg_color: "0066CC", fg_color: "FF"
end

Here we have just created a style for a header in a worksheet with the alignment as bold, size, background and foreground colors. Now lets apply it like this.

sheet.add_row ["First Name", "Last Name", "Marks", "Percentage", "Grade", "Remark"], style: heading

More styling

We can add column and row specific styles like this.

center = s.add_style alignment: {horizontal: :center}, fg_color: "0000FF"
header = s.add_style alignment: {horizontal: :center}, b: true, sz: 10, bg_color: "C0C0C0"
sheet.row_style 0, header, col_offset: 1
sheet.col_style 4, center, row_offset: 1

Well, here we have added a header style to row 0 with column offset 1. This means that this style will get applied to row 0 skipping column 1. Here it’s important to note that row and column indexing starts with 0. Similarly, for column 4 the center style will get applied.

We can also make an array of styles and apply it

 style1 = s.add_style alignment: {horizontal: :center}, fg_color: "00FF00"
 style2 = s.add_style alignment: {horizontal: :left}, fg_color: "FF0000"
 style_array = [style1, style2] # creating an array of two styles namely style1 and style2
 sheet.add_row ["Pass", "Fail"], style: style_array # applying an array of styles

Column widths and Row heights

We can specify height and width for columns and rows respectively like

sheet.add_row ['First Name', 'Last Name'], style: height: 25
sheet.column_widths 20, 20, nil, nil, nil, nil

Here among the six columns, the first and second will have a column width of 20 and the remaining will have the default width because nil has been specified.

Text Wrapping and Merging cells

We can wrap text for column and rows, merge cells like

data = s.add_style alignment: {wrap_text: true}
sheet.add_row ["This will wrap text in cell"], style: data
sheet.merge_cells("B1:D1")# will merge cell B1 an D1

Here Important to Note that When merging the cells
If you are merging cell to form single cell, then you have to be careful else such spread sheet gives error while opening in MS Excel 2003 and 2007.

I had statement as follows in my program

sheet.merge_cells("E1:F1"); sheet.merge_cells("E1:G1"); sheet.merge_cells("E1:H1");

This statement was throwing error as “Excel found unreadable content in abc.xlsx. Do you want to recover the content of this workbook? If you trust the source of this workbook,click Yes”

If you click Yes, excel sheet will get open without any problem. So to merge multiple columns statement should be


sheet.merge_cells("E1:H1")  #To merge columns from E1 to H1

Inserting Hyperlinks and Images in Worksheet

Inserting Hyperlinks

#hyperlink within worksheet
workbook.add_worksheet(:name =>'Hyperlinks') do |sheet|
  sheet.add_row ['Axlsx Exploring']
  sheet.add_hyperlink :location =>'http://axlsx-exploring.herokuapp.com/', :ref => sheet.rows.first.cells.first
  sheet.add_hyperlink :location => 'Next Sheet'!A1 , :ref => 'A2' # link to next worksheet
  sheet.add_row ['Go to next sheet']
end
workbook.add_worksheet(:name => 'Next Sheet') do |sheet|
 sheet.add_row ['hello!']
end

Here add_hyperlink takes :location and :ref parameters

Inserting Image

sheet.add_row ["", "Yehh !! Results", "", "", "", ""], style: heading, height: 30
img = File.expand_path(Rails.root+'app/assets/images/result.png')
sheet.add_image(:image_src => img,:hyperlink=> "http://axlsx-exploring.herokuapp.com/", :end_at => true) do |image|
  image.width=400
  image.height=300
  image.hyperlink.tooltip = "Labeled Link"
  image.start_at 1, 1
  image.end_at 10, 10
end

Here to note that start_at and end_at takes the column and row index to which we want to insert image at, also we can give hyperlink to image

Adding charts to worksheet

Line chart

workbook.add_worksheet(:name => "Line Chart") do |sheet|
  sheet.add_row ["First", 1, 5, 7, 9]
  sheet.add_row ["Second", 5, 2, 14, 9]
  sheet.add_chart(Axlsx::LineChart, :title => "Line Chart") do |chart|
    chart.start_at 0, 2
    chart.end_at 10, 15
    chart.add_series :data => sheet["B1:E1"], :title => sheet["A1"], :color =>"0000FF"    chart.add_series :data => sheet["B2:E2"], :title => sheet["A2"], :color => "FF0000"
    chart.catAxis.title = 'Y Axis'
    chart.valAxis.title = 'X Axis'
  end
end

Here add_series takes three parameters :data is data range to represent on line :title in to add on line and :color for line

Adding Bar chart

a = Student.where(grade: "A").count
b = Student.where(grade: "B").count
c = Student.where(grade: "C").count
fail = Student.where(remark: "FAIL").count
workbook.add_worksheet(name: "Bar Chart") do |sheet|
  sheet.add_row ["", "Result Analysis", "", "", "", ""], style: heading, height: 30
  sheet.add_row ["Grade A", "Grade B", "Grade C", "FAIL"]
  sheet.add_row [a, b, c, fail]
  sheet.add_chart(Axlsx::Bar3DChart, :start_at => "A4", :end_at => "H19", :bar_dir => :bar) do |chart|
    chart.add_series :data => sheet["A3:D3"], :labels => sheet["A2:D2"], :title => sheet["B1"], colors: ["00FF00", "0066CC", "F00000", "FF0000"]
  end
  sheet.column_widths 10, 10, nil, nil, nil, nil
end

Here we taking an example of 3D bar chart which is the first parameter to add_chart function and block of code with data range, labels, title, colors.

Adding Pie chart

   woorkbook.add_worksheet(:name => "Pie Chart") do |sheet|
      sheet.add_row ["", "Result Analysis"], style: heading
      sheet.add_row ["Grade", "Percentage"], style: header
      a = Student.where(grade: "A").count
      b = Student.where(grade: "B").count
      c = Student.where(grade: "C").count
      sheet.add_row ["A", a]
      sheet.add_row ["B", b]
      sheet.add_row ["C", c]
      sheet.add_chart(Axlsx::Pie3DChart, :start_at => [0,6], :end_at => [6, 20], :title => "Pie Chart") do |chart|
        chart.add_series :data => sheet["B3:B5"], :labels => sheet["A3:A5"], :colors =>  ['FF0000', '00FF00', '0000FF']
        chart.d_lbls.d_lbl_pos = :bestFit
        chart.d_lbls.show_percent = :true
      end
    end

Nice thing to note about Pie chart is chart.d_lbls.d_lbl_pos used for labels on Pie chart sections and chart.d_lbls.show_percent to use whether in percent format or not.

In this post, I have explained all the commonly used methods while generating spreadsheets, You can find entire rails application hereAxlsx has a very rich set of functions to generate customized spreadsheet and also some very nice documentation and examples.

Do let me know if you require some specific customization in your spreadsheet. I would gladly help out.

Advertisements

13 thoughts on “Designing Spreadsheets using Axlsx in Rails

  1. Hi! This greatly helped me in my project. I’m having a problem looking for a tutorial on how to make rows in a row that is already in a loop.

    I have this code:

    @records.each do |client|
    sheet.add_row [
    “#{Report.get_cinfo(client).id}”,
    “#{Report.get_cinfo(client).full_name}”,
    “#{Report.get_caddress(client).full_address}”
    “#{Report.get_cpolicies(client).count}”,
    “#{Report.get_ccontactinfo(client).count}”
    ],
    style: [border_cell, border_left_cell, border_cell, border_cell, border_cell, border_left_cell, border_cell, border_cell, border_cell, border_cell, border_cell, border_cell]
    end

    Where, address, policies and contact infos may be more than one. Therefor I need a customization where I can loop on those inside a sheet.add_row. Can this be done?

    Really helpful blog up until now! Thanks in advance

  2. Thanks for this tutorial.
    I’m just starting to read your article, and it seems there are some mistake in your first example :

    package = Axlsx::Package.new
    workbook = package.workboook
    @students = Student.all
    workbook.add_worksheet(name: “Basic work sheet”) do |sheet|
    sheet.add_row [“First Name”, “Last Name”, “Marks”, “Percentage”, “Grade”, “Remark”]
    @students.each do |s|
    sheet.add_row [st.fname, st.lname, st.marks, st.percentage, st.grade, st.remark]
    end
    end
    pacakege.serialize

    correction : “@students.each do |st|” instead of “@students.each do |s|”
    correction : “package” instead of “pacakege”

  3. The article is a great read. Did I miss some obvious steps? After cloning from github and running bundle install and “rails server” , browser access to localhost:3000 got an error as follow:

    Moped::Errors::ConnectionFailure in Students#index

    Showing /home//tests/Axlsx_examples/app/views/students/index.html.haml where line #18 raised:

    Could not connect to any secondary or primary nodes for replica set <Moped::Cluster nodes=[]>

Add your thoughts...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.