Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Axlsx gem Integration in Rails

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 485
    Comment on it

    AXLSX gem is one the most recommended gem for the creating excel file in rails application.

    To integrate AXLSX gem, we have following steps.

    first we need to insert gem in our gemfile
     

    gem 'axlsx_rails'

    and run

    bundle install

    Now we need to create data which we want to export in Excel file

    First we need to call the Axlsx package

    excel_package = Axlsx::Package.new

    Now, we will create a worksheet.

    excel_package.workbook.add_worksheet(name: "sample worksheet") do |worksheet|
        worksheet.add_row["Name", "Age", "Occupation"]
        worksheet.add_row["Bruce", "25", "Architect"]
        worksheet.add_row["Willis", "19", "Graduate"]
        worksheet.add_row["Mel", "21", "Ruby on Rails Developer"]
        worksheet.add_row["Alex", "23", "Banker"]
    end

    Now, we need to insert it in a xlsx file

    file = File.new("/home/document/sample.xlsx",  "w+")
    excel_package.serialize file
    send_file file
    file.close()

    In the above code, first we have created a new file.
    then, insert the package into the file.

    This is how our sheet looks like

    The above excel file is for static data.

    Axlsx give us the privilege of creating excel sheet with dynamic data enhanced with colors, bars, pie-chart and many more.

    def index
    	@users = User.all
    	respond_to do |format| 
    		format.xlsx do 
    			excel_package = Axlsx::Package.new
    			style = excel_package.workbook.styles
    			highlight_cell = style.add_style bg_color: "45808c",fg_color: "FFFFFF",alignment: { horizontal: :center }, :b => true
    			heading_cell = style.add_style bg_color: cyan, fg_color: "45808c",:b => true
    			article_cell = style.add_style fg_color: "45808c",:b => true
    			@users.each do |user|
    				excel_package.workbook.add_worksheet(name: user.first_name.capitalize) do |worksheet|
    					worksheet.add_row["","Articles",""], :style=>highlight_cell
    					worksheet.add_row["Title","Body","Tags"], :style=>heading_cell
    					user.articles.each do |article|
    						tags = []
    						article.tags.each do |tag|
    							tags.push(tag.tag_name)
    						end
    						worksheet.add_row [article.title, article.body, tags.join(',')], :style=>article_cell
    					end
    				end
    			end
    
    			excel_package.workbook.add_worksheet(name: "sample Pie chart") do |sheet|
    				sheet.add_row ["Total count",""], :style=> heading_color
    			    sheet.add_row ["Total Articles", Article.count], :style=>highlight_cell
    			    sheet.add_row ["Total Comments", Comment.count], :style=>highlight_cell
    			    sheet.add_row ["Total Users", @users.count], :style=>highlight_cell
    				sheet.add_chart(Axlsx::Pie3DChart, :start_at => [0,5], :end_at => [10, 20], :title => "Pie Chart") do |chart|
    			      chart.add_series :data => sheet["B2:B4"], :labels => sheet["A2:A4"],  :colors => ['6cccc9', 'ed6f26', '8fdb9d']
    			    end
    			end
    			path = "#{Rails.root}/public"
    			file = File.new("#{path}/sample.xlsx",  "w+")
    			excel_package.serialize file
    			send_file file
    			file.close()
    		end
        end
    end
    

    Sample look of new Excel file

     

    and with Charts


 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: