Class: ActiveSanction::Parsers::Spreadsheet

Inherits:
Object
  • Object
show all
Extended by:
T::Sig
Defined in:
lib/active_sanction/parsers/spreadsheet.rb,
lib/active_sanction/parsers/spreadsheet/row.rb,
lib/active_sanction/parsers/spreadsheet/reader.rb,
lib/active_sanction/parsers/spreadsheet/archive.rb,
lib/active_sanction/parsers/spreadsheet/workbook.rb

Overview

Reads an Office Open XML workbook -- an .xlsx file -- into rows an adapter can map onto Entities.

A table is a description of the file, built once and reused for every sync; a Reader is one pass over one payload.

LIST = ActiveSanction::Parsers::Spreadsheet.new(sheet: "Consolidated List")

LIST.read(bytes).each { |row| row[:name_of_individual_or_entity] }

With no dependency, which was the point

Australia publishes its Consolidated List as a spreadsheet and as nothing else -- no CSV, no XML, no JSON -- so reading it is the price of screening against Australian sanctions at all. The alternative was a spreadsheet gem, which would have been this library's first third-party dependency taken on one publisher's behalf, in a gem whose stated rule is that a compliance library should not be the reason a deployment installs something.

It turned out not to cost much. An .xlsx is a ZIP of XML parts; zlib is in the standard library and this gem already reads XML, so what was actually missing was a ZIP header unpacker (Archive) and the two lookups that make a cell mean something (Workbook). Everything below that is the XML toolkit the other five adapters use.

What it reads, and what it does not

One sheet of cell values, as strings. Dates are rendered ISO 8601 at the precision the cell's own format displays -- see Workbook -- so that PartialDate::Parser reads them without an adapter writing a format.

Formulas are not evaluated: a formula cell is read as the value last cached in it, which is what a publisher's export contains and what the file displays. Merged cells, comments, charts, styling and every other thing a spreadsheet can hold are ignored, because none of them is data on a sanctions list. Only .xlsx is read, not the older binary .xls -- they share a file extension in conversation and nothing at all in format.

Columns, and why declaring them is optional here

A published spreadsheet has a header row, unlike OFAC's CSVs, so the first row of the sheet is always the header and never a record. By default its cells are what the columns are named. Declaring columns: instead renames them by position, which pins the sheet's shape for a publisher who has form for re-labelling things -- the header is still consumed, because it is still a header.

Defined Under Namespace

Classes: Row

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(columns: nil, null: nil, sheet: nil) ⇒ void

Parameters:

  • columns (T.untyped) (defaults to: nil)
  • null (T.untyped) (defaults to: nil)
  • sheet (T.untyped) (defaults to: nil) 


91
92
93
94
95
96
97
 
# File 'lib/active_sanction/parsers/spreadsheet.rb', line 91

def initialize(columns: nil, null: nil, sheet: nil)
  @columns = T.let(columns!(columns), T.nilable(T::Array[Symbol]))
  @nulls = T.let(nulls!(null), T::Array[String])
  @sheet = T.let(sheet, T.untyped)
  @encoding = T.let(DEFAULT_ENCODING, Encoding)
  freeze
end

Instance Attribute Details

#columns ⇒ Array<Symbol>? (readonly)

nil where the sheet names its own columns -- see #headers?.

Returns:

  • (Array<Symbol>, nil) 


76
77
78
 
# File 'lib/active_sanction/parsers/spreadsheet.rb', line 76

def columns
  @columns
end

#encoding ⇒ Encoding (readonly)

Always UTF-8, and not a caller's choice: the parts of a workbook are XML documents that declare their own encoding, and every writer emits UTF-8.

Returns:

  • (Encoding) 


88
89
90
 
# File 'lib/active_sanction/parsers/spreadsheet.rb', line 88

def encoding
  @encoding
end

#nulls ⇒ Array<String> (readonly)

Returns:

  • (Array<String>) 


83
84
85
 
# File 'lib/active_sanction/parsers/spreadsheet.rb', line 83

def nulls
  @nulls
end

#sheet ⇒ T.untyped (readonly)

The sheet to read: a name, a zero-based index, or nil for the first one.

Returns:

  • (T.untyped) 


80
81
82
 
# File 'lib/active_sanction/parsers/spreadsheet.rb', line 80

def sheet
  @sheet
end

Instance Method Details

#coerce(names, cells) ⇒ Hash{Symbol => String, nil}

Zips a row's cells against the column names by position. A column the row left empty is nil, and a cell past the last named column is dropped -- the Reader has already warned about the second.

Parameters:

  • names (Array<Symbol>)
  • cells (Hash{Integer => String})

Returns:

  • (Hash{Symbol => String, nil}) 


135
136
137
 
# File 'lib/active_sanction/parsers/spreadsheet.rb', line 135

def coerce(names, cells)
  names.each_with_index.to_h { |name, index| [name, cells[index]] }.freeze
end

#headers? ⇒ Boolean

Whether the sheet names its own columns.

Returns:

  • (Boolean) 


116
 
# File 'lib/active_sanction/parsers/spreadsheet.rb', line 116

def headers? = columns.nil?

#inspect ⇒ String

Returns:

  • (String) 


140
141
142
143
144
 
# File 'lib/active_sanction/parsers/spreadsheet.rb', line 140

def inspect
  declared = columns
  shape = declared.nil? ? "headers from the sheet" : "#{declared.size} columns"
  "#<#{self.class} #{sheet_name}, #{shape}#{" null=#{nulls.first.inspect}" if nulls.any?}>"
end

#read(payload) ⇒ Reader

A pass over one payload. Takes the bytes as a String, which is what Sources::Base hands #parse.

Parameters:

  • payload (T.untyped)

Returns:

  • (Reader) 


102
 
# File 'lib/active_sanction/parsers/spreadsheet.rb', line 102

def read(payload) = Reader.new(table: self, payload: payload)

#sheet_name ⇒ String

What to call the sheet being read, for a message: the name or index the caller asked for, or what "the first one" means when they asked for nothing.

Returns:

  • (String) 


122
123
124
125
126
 
# File 'lib/active_sanction/parsers/spreadsheet.rb', line 122

def sheet_name
  return "the first sheet" if sheet.nil?

  sheet.is_a?(Integer) ? "sheet #{sheet}" : sheet.to_s.inspect
end

#unescape(text) ⇒ String?

A cell's text with Excel's escapes resolved. Applied to every string a sheet holds, shared or inline, because a name carrying a literal _x000D_ is a name nothing will match.

Parameters:

  • text (String, nil)

Returns:

  • (String, nil) 


108
109
110
111
112
 
# File 'lib/active_sanction/parsers/spreadsheet.rb', line 108

def unescape(text)
  return text if text.nil? || !text.include?("_x")

  text.gsub(ESCAPE) { ::Regexp.last_match(1) || [::Regexp.last_match(2).to_s.hex].pack("U") }
end