0
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.