Module: SheetHelper

Included in:
EmployeePayrollTransactions::ImportService, Employees::ImportService
Defined in:
app/helpers/sheet_helper.rb

Instance Method Summary collapse

Instance Method Details

#each_row(file, sheet_name = nil) ⇒ Object 



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
 
# File 'app/helpers/sheet_helper.rb', line 2

def each_row(file, sheet_name = nil)
  # Determine file extension
  ext = File.extname(file).downcase

  if ext == '.csv'
    CSV.foreach(file, headers: true, header_converters: :symbol, encoding: 'ISO-8859-1').with_index(1) do |row, index|
      yield row.to_h, index
    end
  elsif ['.xlsx', '.sheet'].include?(ext)
    # Parse XLSX using Roo
    if ext == '.sheet'
      # Create temporary file for .sheet format
      temp_file = Tempfile.new(['temp_sheet', '.xlsx'], binmode: true)
      temp_file.binmode
      temp_file.write(File.read(file))
      temp_file.rewind
      xlsx = Roo::Excelx.new(temp_file.path)
    else
      xlsx = Roo::Excelx.new(file)
    end

    sheet = xlsx.sheet(sheet_name || 0)

    # Get headers and convert to symbols
    headers = sheet.row(1).map { |h| h.to_s.downcase.to_sym }

    # Iterate over data rows (skip header row)
    (2..sheet.last_row).each_with_index do |i, idx|
      row = sheet.row(i)
      # Create hash with headers as keys
      row_hash = headers.zip(row).to_h
      yield row_hash, idx + 1
    end

    # Clean up temporary file if it was created
    temp_file&.close
    temp_file&.unlink
  else
    raise "Unsupported file format: #{ext}"
  end
end

#hashes_array_from_file(file:, sheet_name: nil, base64_csv: false) ⇒ Object 



44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
 
# File 'app/helpers/sheet_helper.rb', line 44

def hashes_array_from_file(file:, sheet_name: nil, base64_csv: false)
  # Determine file extension
  ext = File.extname(file).downcase

  if ext == '.csv'
    # Parse CSV
    csv_content = base64_csv ? Base64.decode64(File.read(file)) : File.read(file)
    CSV.parse(csv_content, headers: true, header_converters: :symbol).map(&:to_h)
  elsif ['.xlsx', '.sheet'].include?(ext)
    # Parse XLSX using Roo
    if ext == '.sheet'
      # Create temporary file for .sheet format
      temp_file = Tempfile.new(['temp_sheet', '.xlsx'], binmode: true)
      temp_file.binmode
      temp_file.write(File.read(file))
      temp_file.rewind
      xlsx = Roo::Excelx.new(temp_file.path)
    else
      xlsx = Roo::Excelx.new(file)
    end

    sheet = xlsx.sheet(sheet_name || 0)

    # Get headers and convert to symbols
    headers = sheet.row(1).map { |h| h.to_s.downcase.to_sym }

    # Iterate over data rows (skip header row)
    result = (2..sheet.last_row).map do |i|
      row = sheet.row(i)
      # Create hash with headers as keys
      headers.zip(row).to_h
    end

    # Clean up temporary file if it was created
    temp_file&.close
    temp_file&.unlink

    result
  else
    raise "Unsupported file format: #{ext}"
  end
end

#process_import_results(rows, file_rows, processed_rows_count, error_message = 'Importation rollbacked', columns_to_nullify = []) ⇒ Object 



87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
 
# File 'app/helpers/sheet_helper.rb', line 87

def process_import_results(
  rows,
  file_rows,
  processed_rows_count,
  error_message = 'Importation rollbacked',
  columns_to_nullify = []
)
  processed_rows = rows.dup
  remaining_rows = file_rows[processed_rows_count..] || []

  # Add remaining rows with failure status
  remaining_rows.each do |row|
    processed_rows << {
      is_created: false,
      record_id: nil,
      creation_errors: error_message
    }.merge(row.to_h)
  end

  # If any rows were processed but failed (i.e., rollback occurred), update all to failed
  if processed_rows.any? { |r| r[:creation_errors].present? }
    processed_rows.map do |r|
      r.merge(
        is_created: false,
        record_id: nil,
        creation_errors: r[:creation_errors] || error_message
      )
    end

    # Nullify specified columns
    processed_rows.each do |row|
      columns_to_nullify.each do |column|
        row[column] = nil if row.key?(column)
      end
    end
  else
    processed_rows
  end
end