Module: DataShift::ExcelBase

Includes:
Logging
Included in:
ExcelExporter, ExcelGenerator, ExcelLoader, Spreadsheet::Worksheet
Defined in:
lib/datashift/excel_base.rb

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Methods included from Logging

#logdir, #logdir=, #logger, #verbose

Instance Attribute Details

#excel ⇒ Object

Returns the value of attribute excel.



11
12
13
 
# File 'lib/datashift/excel_base.rb', line 11

def excel
  @excel
end

#sheet ⇒ Object

Returns the value of attribute sheet.



11
12
13
 
# File 'lib/datashift/excel_base.rb', line 11

def sheet
  @sheet
end

Class Method Details

.max_columns ⇒ Object 



7
8
9
 
# File 'lib/datashift/excel_base.rb', line 7

def self.max_columns
  1024
end

Instance Method Details

#ar_to_xls(records, start_row: 1, headers: nil, data_flow_schema: nil) ⇒ Object

Pass a set of AR records



107
108
109
110
111
112
113
114
115
116
 
# File 'lib/datashift/excel_base.rb', line 107

def ar_to_xls(records, start_row: 1, headers: nil, data_flow_schema: nil)
  return if (!exportable?(records.first) || records.empty?)

  # assume header row present
  row_index = start_row
  records.each do |record|
    ar_to_xls_row(row_index, record, headers: headers, data_flow_schema: data_flow_schema)
    row_index += 1
  end
end

#ar_to_xls_cell(row_idx, col_idx, record, ar_method) ⇒ Object

Expect to be able to send ar_method to the record i.e callable method to retrieve actual data to export



140
141
142
143
144
145
146
147
 
# File 'lib/datashift/excel_base.rb', line 140

def ar_to_xls_cell(row_idx, col_idx, record, ar_method)
  datum = record.send(ar_method)
  self[row_idx, col_idx] = datum
rescue => e
  logger.error("Failed to export #{datum} from #{ar_method.inspect} to column #{col_idx}")
  logger.error(e.message)
  logger.error(e.backtrace)
end

#ar_to_xls_row(row, record, start_column: 0, headers: nil, data_flow_schema: nil) ⇒ Object

Save data from an AR record to the current row, based on the record’s columns [c1,c2,c3] Returns the number of the final column written to



120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
 
# File 'lib/datashift/excel_base.rb', line 120

def ar_to_xls_row(row, record, start_column: 0, headers: nil, data_flow_schema: nil)

  column = start_column

  record_methods_to_call = if(data_flow_schema)
                             data_flow_schema.sources
                           elsif headers.present?
                             headers.collect(&:source)
                           else
                             ModelMethods::Catalogue.column_names(record.class)
                           end
  record_methods_to_call.each do |method|
    ar_to_xls_cell(row, column, record, method)
    column += 1
  end
  column
end

#exportable?(record) ⇒ Boolean

TODO: DRY

Returns:

  • (Boolean) 


98
99
100
101
102
103
104
 
# File 'lib/datashift/excel_base.rb', line 98

def exportable?(record)
  return true if record.is_a?(ActiveRecord::Base)

  return true if Module.const_defined?(:Mongoid) && record.is_a?(Mongoid::Document)

  false
end

#open_excel(file_name, options = {}) ⇒ Object

Open excel file and assign to @excel and set @sheet

Options :

:sheet_name : Create a new worksheet assign to @sheet. Default is class.name

:sheet_number : Select the sheet by index - sheet_name takes precedence



45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
 
# File 'lib/datashift/excel_base.rb', line 45

def open_excel( file_name, options = {})

  @excel = DataShift::Excel.new

  @excel.open(file_name)

  if options[:sheet_name]

    @sheet = @excel.create_worksheet( name: options[:sheet_name] )

    unless sheet
      logger.error("Excel failed to create WorkSheet for #{name}")

      raise "Failed to create Excel WorkSheet for #{name}"
    end

  elsif options[:sheet_number]
    @sheet = @excel.worksheet( options[:sheet_number] )
  else
    @sheet = @excel.worksheets.first
  end

  @excel
end

#parse_headers(sheet, header_row_idx = 0) ⇒ Object 



70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
 
# File 'lib/datashift/excel_base.rb', line 70

def parse_headers( sheet, header_row_idx = 0 )

  headers = DataShift::Headers.new(:excel, header_row_idx)

  header_row = sheet.row(header_row_idx)
  unless header_row
    raise MissingHeadersError,
          "No headers found - Check Sheet #{sheet} is complete and Row #{header_row_idx} contains headers"
  end

  # TODO: - make more robust - currently end on first empty column
  # There is no actual max columns in Excel .. you will run out of memory though at some point
  (0..ExcelBase.max_columns).each do |column|
    cell = header_row[column]
    break unless cell
    header = cell.to_s.strip
    break if header.empty?
    headers << header
  end

  headers
end

#sanitize_sheet_name(name) ⇒ Object 



93
94
95
 
# File 'lib/datashift/excel_base.rb', line 93

def sanitize_sheet_name( name )
  name.gsub(/[\[\]:\*\/\\\?]/, '')
end

#start_excel(klass, options = {}) ⇒ Object

Create @excel and set @sheet

Options :

:sheet_name : Create a new worksheet assign to @sheet.
              Default is class.name



20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
# File 'lib/datashift/excel_base.rb', line 20

def start_excel(klass, options = {})

  @excel = DataShift::Excel.new

  name = options[:sheet_name] ? options[:sheet_name] : klass.name

  @sheet = excel.create_worksheet( name: name )

  unless sheet
    logger.error("Excel failed to create WorkSheet called [#{name}]")

    raise "Failed to create Excel WorkSheet called [#{name}]"
  end

  @excel
end