Skip to content

Instantly share code, notes, and snippets.

Avatar

Randy Morgan (@morgan_randy) randym

View GitHub Profile
@randym
randym / comment-hack.rb
Created Oct 11, 2013
axlsx hacking comment position
View comment-hack.rb
# Hacking comment position!
# Use at your own risk :)
# add_comment is the preferred way to add comments to your worksheet. However, if you really need to position a
# a specific comment, here is one way to do it.
require 'axlsx'
p = Axlsx::Package.new
wb = p.workbook
wb.add_worksheet(:name => 'comments') do |sheet|
sheet.add_row ['Can we build it?', 'Yes we can!']
sheet.add_comment(:ref => 'A1', :author => 'Bob', :text => 'Yes We Can!').tap do |comment|
@randym
randym / outline_level.rb
Created Dec 14, 2012
Collapsed outline level
View outline_level.rb
#```ruby
wb.add_worksheet(name: 'outline_level') do |sheet|
sheet.add_row [1, 2, 3, 4, Time.now, 149455.15]
sheet.add_row [1, 2, 5, 6, Time.now,14100.19]
sheet.add_row [9500002267, 1212, 1212, Time.now,14100.19]
sheet.add_row [], :collapsed => 1
sheet.rows[0..2].each do |row|
row.outline_level = 1
# This will collapse the outline level rows
@randym
randym / boxed_border.rb
Created Oct 30, 2012
Surrounding Box Border in Axlsx
View boxed_border.rb
require 'axlsx'
p = Axlsx::Package.new
p.workbook do |wb|
# Stuff like this is why I LOVE RUBY
# If you dont know about hash default values
# LEARN IT! LIVE IT! LOVE IT!
defaults = { :style => :thick, :color => "000000" }
borders = Hash.new do |hash, key|
hash[key] = wb.styles.add_style :border => defaults.merge( { :edges => key.to_s.split('_').map(&:to_sym) } )
@randym
randym / kana_lookup.rb
Created Oct 21, 2012
crap code for finding index of kanna phonetics
View kana_lookup.rb
# encoding: UTF-8
#
require 'nkf'
class PhoneticMap
def data
@data ||= build_data
end
def index_of(string)
@randym
randym / custom_defined_name.rb
Created Oct 18, 2012
Defining Custom Names for cells and ranges with axlsx
View custom_defined_name.rb
require 'axlsx'
p = Axlsx::Package.new
wb = p.workbook
wb.add_worksheet(:name => 'defined name') do |sheet|
sheet.add_row [1, 2, 17, '=FOOBAR']
wb.add_defined_name("'defined name'!$C1", :local_sheet_id => sheet.index, :name => 'FOOBAR')
end
p.serialize 'custom_defined_name'
@randym
randym / transpose.rb
Created Oct 12, 2012
Array#transpose that accepts a block for populating missing elements
View transpose.rb
class Array
# A more meaningful message when you have an insufficient number of columns
# in a row for transposition.
MSG_TRANSPOSE_INDEX_ERROR = "Row %s has only %s element(s), but transposition
requires %s. Please specify a block to populate missing items or adjust
your array to contain an equal number of elements in each row array."
# Overrides the default transpose method to allow a block that can be used to return
# elements required to populate the transposition.
@randym
randym / nandeyanen.rb
Created Sep 16, 2012
control character regex matching in ruby 1.8.7 vs 1.9.x
View nandeyanen.rb
if RUBY_VERSION == "1.8.7"
nasty_control_char_matcher = Regexp.new("[\x01\x02\x03\x04\x05\x06\x07\x08\x1F\v\xE2]")
else
nasty_control_char_matcher = Regexp.new("[\x01\x02\x03\x04\x05\x06\x07\x08\x1F\v\u2028]")
end
@randym
randym / date_styles.rb
Created Sep 14, 2012
Axlsx date formatting with custom styles
View date_styles.rb
$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
#
@randym
randym / axlsx.rb
Created Aug 30, 2012
Axlsx: Line Chart without axis labels
View axlsx.rb
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
randym / no_cat_labels.rb
Created Aug 30, 2012
hide tick lables in chart
View no_cat_labels.rb
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
You can’t perform that action at this time.