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 acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.