randym’s gists

randym / gist:1883637

Created February 22, 2012 09:41

	# In a controller

	document = db.Text(template.render('tpl/estimate.xls',template_values),encoding="utf_8")
	self.response.headers.add_header('Content-disposition', 'attachment; filename="'+seq+'.xls"')
	self.response.headers.add_header('Content-type', 'application/vnd.ms-excel; filename="'+seq+'.xls"')
	self.response.headers.add_header('name', seq+'.xls')
	self.response.out.write(document)

randym / axlsx.example.rb

Created April 12, 2012 23:42

axlsx Generating Excel documents with Ruby

	require 'axlsx'

	Axlsx::Package.new do \|p\|
	p.workbook do \|wb\|
	styles = wb.styles
	header = styles.add_style :bg_color => "DD", :sz => 16, :b => true, :alignment => {:horizontal => :center}
	tbl_header = styles.add_style :b => true, :alignment => { :horizontal => :center }
	ind_header = styles.add_style :bg_color => "FFDFDEDF", :b => true, :alignment => {:indent => 1}
	col_header = styles.add_style :bg_color => "FFDFDEDF", :b => true, :alignment => { :horizontal => :center }
	label = styles.add_style :alignment => { :indent => 1 }

randym / benchmark.rb

Created April 18, 2012 04:39

axlsx benchmarks

	#!/usr/bin/env ruby -s
	# -- coding: utf-8 --
	$:.unshift "#{File.dirname(__FILE__)}/../lib"
	require 'axlsx'
	require 'csv'

	require 'benchmark'
	row = []
	input = (32..126).to_a.pack('U*').chars.to_a
	20.times { row << input.shuffle.join}

randym / getting_barred.rb

Created April 24, 2012 23:16

Axlxs: Writing Excel With Ruby - Conditional Formatting

	require 'axlsx'
	p = Axlsx::Package.new
	p.workbook do \|wb\|
	# define your regular styles
	styles = wb.styles
	title = styles.add_style :sz => 15, :b => true, :u => true
	default = styles.add_style :border => Axlsx::STYLE_THIN_BORDER
	header = styles.add_style :bg_color => '00', :fg_color => 'FF', :b => true
	money = styles.add_style :format_code => '#,###,##0', :border => Axlsx::STYLE_THIN_BORDER
	percent = styles.add_style :num_fmt => Axlsx::NUM_FMT_PERCENT, :border => Axlsx::STYLE_THIN_BORDER

randym / extractive.rb

Created May 3, 2012 12:21

log based axis an colored data

randym / example_table_autofilter.rb

Created July 25, 2012 23:21

Example: Setting up auto filters / Table with axlsx

	#Using Tables:
	#This will add in the sorting filters for you as well as apply default table styling in excel.
	#LibraOffice does the filters, but not the styling (as does Google docs if I recall correctly.)


	#```ruby
	wb.add_worksheet(:name => "Table") do \|sheet\|
	sheet.add_row ["Build Matrix"]
	sheet.add_row ["Build", "Duration", "Finished", "Rvm"]
	sheet.add_row ["19.1", "1 min 32 sec", "about 10 hours ago", "1.8.7"]

randym / repeating_header.rb

Created July 31, 2012 04:09

Excel with Ruby: Printing Header Row for every page with axlsx

	#```ruby
	require 'axlsx'
	p = Axlsx::Package.new
	wb = p.workbook

	wb.add_worksheet(:name => "repeated header") do \|sheet\|
	sheet.add_row %w(These Column Header Will Render On Every Printed Sheet)
	200.times { sheet.add_row %w(1 2 3 4 5 6 7 8) }
	# This is the magic
	wb.add_defined_name("'repeated header'!$1:$1", :local_sheet_id => sheet.index, :name => '_xlnm.Print_Titles')

randym / no_cat_labels.rb

Created August 30, 2012 04:08

hide tick lables in chart

	p = Axlsx::Package.new
	p.workbook.add_worksheet(:name => "Bar Chart") do \|sheet\|
	sheet.add_row ["A Simple Bar Chart"]
	sheet.add_row ["First", "Second", "Third"]
	sheet.add_row [1, 2, 3]
	sheet.add_chart(Axlsx::Bar3DChart, :start_at => "A4", :end_at => "F17") do \|chart\|
	chart.add_series :data => sheet["A3:C3"], :labels => sheet["A2:C2"], :title => sheet["A1"]
	chart.valAxis.label_rotation = -45
	chart.catAxis.label_rotation = 45
	chart.d_lbls.d_lbl_pos = :outEnd

randym / axlsx.rb

Created August 30, 2012 23:50

Axlsx: Line Chart without axis labels


	require 'axlsx'
	p = Axlsx::Package.new
	p.workbook.add_worksheet(:name => "Line Chart") do \|sheet\|
	sheet.add_row ['1', '2', '3', '4']
	sheet.add_row [1, 2, 3, '=sum(A2:C2)']
	sheet.add_chart(Axlsx::Line3DChart, :start_at => [0,2], :end_at => [5, 15], :title => "Chart") do \|chart\|
	chart.add_series :data => sheet["A2:D2"], :labels => sheet["A1:D1"]

	chart.serAxis.tick_lbl_pos = :none

randym / date_styles.rb

Created September 14, 2012 02:12

Axlsx date formatting with custom styles

	$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib"
	require 'axlsx'
	require 'date'

	p = Axlsx::Package.new
	wb = p.workbook
	wb.styles do \|style\|

	# Date/Time Styles
	#

Randy Morgan randym