I am still learning Ruby and object oriented programming.
I wrote an Excel Export CSV Parser script, which does the following:
- reads an XLSX file
- creates a temp file
- re-orders columns
- generates a new CSV file
The input file can be any xlsx which converts to a csv, you only have to specify some column positions which results to one of two fix formats.
Interface / Usage
options = {
column_positions: {
date: 5,
queue_name: 3,
handling_time: [6, 7]
},
target_format: :injixo,
# more options (optional)
keep_tempfile: false,
import_path: 'import'
export_path: 'export'
}
Excel2CsvConvert.new(options).run
require 'roo'
require 'pathname'
require 'date'
require 'fileutils'
require_relative 'xlsx_converter'
require_relative 'parser'
require_relative 'exporter'
class Excel2CsvConvert
attr_reader :column_positions
def initialize(target_format: :injixo, keep_tempfile: false,
column_positions: {
date: 5, queue_name: 3, handling_time: [6, 7]
}, import_path: 'import', export_path: 'export')
@target_format = target_format
@column_positions = column_positions
@keep_tempfile = keep_tempfile
@import_path = Pathname.new(import_path).join('*.xlsx')
@export_path = export_path
end
def run
Dir.glob(@import_path).each do |filename|
puts "Reading #{filename} ..."
temp_file = create_temp_file_for(filename)
export_csv_file(read_and_convert(temp_file), filename)
File.delete(temp_file) unless @keep_tempfile
end
end
private
def read_and_convert(temp_file)
Parser.new(
target_format: @target_format,
file: RooClient.new(temp_file: temp_file).read_temp_file,
column_positions: @column_positions
).parse
end
# TODO: Exporter still handles file creation on its own
def export_csv_file(lines, filename)
Exporter.new(lines: lines, export_path: @export_path,
filename: "#{filename}.export.#{Date.today}.csv").create_export
end
def create_temp_file_for(filename)
XlsxConverter.new(
filename: filename, temp_file: "#{filename}.temp"
).generate_temp_file
end
def parse(cell)
DateTime.parse(cell)
end
end
The full source code (currently without tests) is available on Github
My questions:
- What would you improve?
- Has the
Excel2CsvConvert
class to many responsibilities? - For me passing the column_positions into Parser AND Excel2CsvConvert feels wrong, any suggestions?
- Is there any advantage to not work with a temp file and to store a data structure in memory, beside the performance?
- I haven't used TDD for this as it is hard for me how to start testing, any suggestions?
I still need to understand OOP better, so any advice might be helpful. If you find some good code in the script, let me know about it as well.