STEPS TO READ AND WRITE DATA IN EXCEL SHEETS FROM A RAILS APP.

Many a times,we run in a situation where we have to integerate our rails app and and excel worksheet.

Here is a simple step by step guide to do the same.

First step is to install a gem that I will be using.

The gem is called “Spreadsheet”.

The readme for the same can be found here

To install the gem use :

gem install spreadsheet.

(note : in rails 3 use gem ‘spreadsheet’ in the gem file and then do bundle install)

Once the gem is installed, one can open the controller action where the data is to be written to the ex cel file.

In my case the action was create.

The code for the same is :

def create       

 require ‘spreadsheet’                    

# include libraries

 @sample = Sample.new(params[:sample])

      #sample is my model name

 

    respond_to do |format|

 if @sample.save

book = Spreadsheet::Workbook.new  

 # create a new excel file

sheet1 = book.create_worksheet      

#initialize a new sheet

 sheet1[1,1]=params[:sample][:name]

#insert data in required sheet([row][column]) its starts from [0][0]

  book.write ‘C:/TC/test.xls’              # commit the write

 

(Note : written in blue are the comments and in black is the code)

Similarly to read data from excel files we use the same gem as:

def index

require ‘spreadsheet’                                   

#include the libraries.

  book = Spreadsheet.open ‘C:/TC/test.xls’

#open the required file

  sheet1=book.worksheet 0

#open the required sheet

  @name=sheet1[1,1]

#read the data