xlsx

Pulls ExcelX data from .xlsx files into a hash of arrays keyed by the headers. First row is assumed to be the header row.

The xlsx method is a member of Squib::Deck, but it is also available outside of the Deck DSL with Squib.xlsx(). This allows a construction like:

data = Squib.xlsx file: 'data.xlsx'
Squib::Deck.new(cards: data['name'].size) do
end

Options

file

default: 'deck.xlsx'

the xlsx-formatted file to open. Opens relative to the current directory.

sheet

default: 0

The zero-based index of the sheet from which to read.

strip

default: true

When true, strips leading and trailing whitespace on values and headers

explode

default: 'qty'

Quantity explosion will be applied to the column this name. For example, rows in the csv with a 'qty' of 3 will be duplicated 3 times.

Warning

Data import methods such as xlsx and csv will not consult your layout file or follow the Squib Thinks in Arrays feature.

Individual Pre-processing

The xlsx method also takes in a block that will be executed for each cell in your data. This is useful for processing individual cells, like putting a dollar sign in front of dollars, or converting from a float to an integer. The value of the block will be what is assigned to that cell. For example:

resource_data = Squib.xlsx(file: 'sample.xlsx') do |header, value|
  case header
  when 'Cost'
    "$#{value}k" # e.g. "3" becomes "$3k"
  else
    value # always return the original value if you didn't do anything to it
  end
end

Examples

To get the sample Excel files, go to its source

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
require 'squib'

Squib::Deck.new(cards: 3) do
  background color: :white

  # Reads the first sheet by default (sheet 0)
  # Outputs a hash of arrays with the header names as keys
  data = xlsx file: 'sample.xlsx'

  text str: data['Name'], x: 250, y: 55, font: 'Arial 18'
  text str: data['Level'], x: 65, y: 65, font: 'Arial 24'
  text str: data['Description'], x: 65, y: 600, font: 'Arial 12'

  save format: :png, prefix: 'sample_excel_' # save to individual pngs
end

# xlsx is also a Squib-module-level function, so this also works:
data      = Squib.xlsx file: 'explode_quantities.xlsx' # 2 rows...
num_cards = data['Name'].size                          #          ...but 4 cards!

Squib::Deck.new(cards: num_cards) do
  background color: :white
  rect # card border
  text str: data['Name'], font: 'Arial 18'
  save_sheet prefix: 'sample_xlsx_qty_', columns: 4
end


# Here's another example, a bit more realistic. Here's what's going on:
#   * We call xlsx from Squib directly - BEFORE Squib::Deck creation. This
#     allows us to infer the number of cards based on the size of the "Name"
#     field
#   * We make use of quantity explosion. Fields named "Qty" or "Quantity"
#     (any capitalization), or any other in the "qty_header" get expanded by the
#     number given
#   * We also make sure that trailing and leading whitespace is stripped
#     from each value. This is the default behavior in Squib, but the options
#     are here just to make sure.

resource_data = Squib.xlsx(file: 'sample.xlsx', explode: 'Qty', sheet: 2, strip: true) do |header, value|
  case header
  when 'Cost'
    "$#{value}k" # e.g. "3" becomes "$3k"
  else
    value # always return the original value if you didn't do anything to it
  end
end

Squib::Deck.new(cards: resource_data['Name'].size) do
  background color: :white
  rect width: :deck, height: :deck
  text str: resource_data['Name'], align: :center, width: :deck, hint: 'red'
  text str: resource_data['Cost'], align: :right, width: :deck, hint: 'red'
  save_sheet prefix: 'sample_excel_resources_', columns: 3
end