Module: DataShift::ExcelBase

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

Instance Attribute Details

#excel ⇒ Object

Returns the value of attribute excel.



9
10
11
 
# File 'lib/datashift/excel_base.rb', line 9

def excel
  @excel
end

#sheet ⇒ Object

Returns the value of attribute sheet.



9
10
11
 
# File 'lib/datashift/excel_base.rb', line 9

def sheet
  @sheet
end

Class Method Details

.max_columns ⇒ Object 



5
6
7
 
# File 'lib/datashift/excel_base.rb', line 5

def self.max_columns
  1024
end

Instance Method Details

#ar_to_xls(records, options = {}) ⇒ Object

Pass a set of AR records



96
97
98
99
100
101
102
103
104
105
106
107
 
# File 'lib/datashift/excel_base.rb', line 96

def ar_to_xls(records, options = {})
  return if !records.first.is_a?(ActiveRecord::Base) || records.empty?

  # assume headers present
  row_index = (options[:start_row]) ? (options[:start_row]) : 1

  records.each do |record|
    ar_to_xls_row(row_index, 0, record)

    row_index += 1
  end
end

#ar_to_xls_cell(row, column, record, connection_column) ⇒ Object 



122
123
124
125
126
127
128
129
130
131
132
 
# File 'lib/datashift/excel_base.rb', line 122

def ar_to_xls_cell(row, column, record, connection_column)

  datum = record.send(connection_column.name)

  self[row, column] = datum
rescue => e
  logger.error("Failed to export #{datum} from #{connection_column.inspect} to column #{column}")
  logger.error( e.message )
  logger.error(e.backtrace)

end

#ar_to_xls_row(row, start_column, record) ⇒ 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



111
112
113
114
115
116
117
118
119
120
 
# File 'lib/datashift/excel_base.rb', line 111

def ar_to_xls_row(row, start_column, record)
  return unless record.is_a?(ActiveRecord::Base)

  column = start_column
  record.class.columns.each do |connection_column|
    ar_to_xls_cell(row, column, record, connection_column)
    column += 1
  end
  column
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



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

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 



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

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 



91
92
93
 
# File 'lib/datashift/excel_base.rb', line 91

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



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

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