CODEWITHSUNDEEP
rubyexcelcsvspreadsheet
Scotty Brown
Scotty Brown

Reputation: 1

Trouble writing to a workbook using ruby spreadsheet gem from CSV file

I am currently new to Ruby and am having a hard time writing to an excel file.

I want to parse through a CSV file, extract data where the 'food' column in the csv file = butter and put the rows where 'food' column = butter into a new excel workbook. I can write the data that contains butter in the 'food' column just fine into a CSV file but am having trouble writing it to a workbook (excel format).

require 'rubygems'
require 'csv'
require 'spreadsheet'

csv_fname = 'commissions.csv'

options = { headers: :first_row }

food_type = { 'food' => 'butter'}

food_type_match = nil

CSV.open(csv_fname, 'r', options) do |csv|
  food_type_match = csv.find_all do |row|
    Hash[row].select { |k,v| food_type[k] } == food_type
  end
end

#writing the 'butter' data to a CSV file
#CSV.open('butter.csv', 'w') do |csv_object|
# food_type_match.each do |row_array|
#   csv_object << row_array
# end
#end

book = Spreadsheet::Workbook.new
sheet1 = book.create_worksheet

food_type_match.each do |csv|
  csv.each_with_index do |row, i|
    sheet1.row(i).replace(row)
  end
end

The spreadsheet generates but comes out blank. I have searched through numerous topics on ruby spreadsheet but I cannot get it to work. Any help would be greatly appreciated.

Upvotes: 0

Views: 2280

Answers (2)

ohho
ohho

Reputation: 51921

Try write_xlsx gem. Here is my simple csvtoxlsx.rb script to combine *.csv in a folder to a single.xlsx:

require "csv"
require "write_xlsx"

def csvtoxls(csv, xlsx)

    count = 0

    workbook = WriteXLSX.new(xlsx)

    Dir[csv].sort.each do | file |
        puts file
        name = File.basename(file, ".csv")
        worksheet = workbook.add_worksheet(name)
        i = 0
        CSV.foreach(file) do | row |
            worksheet.write_row(i, 0, row)
            i = i + 1
            count = count + 1
        end
    end

    workbook.close

    count
end

abort("Syntax: ruby -W0 csvtoxlsx.rb 'folder/*.csv' single.xlsx") if ARGV.length < 2

time_begin = Time.now
count = csvtoxls(ARGV[0], ARGV[1])
time_spent = Time.now - time_begin

puts "csvtoxlsx process #{ARGV[0]} with #{count} rows in #{time_spent.round(2)} seconds"

Upvotes: 0

engineersmnky
engineersmnky

Reputation: 29318

Updated Completely What if you try this:

book = Spreadsheet::Workbook.new
sheet1 = book.create_worksheet

food_type_match.each do |csv|
  csv.each_with_index do |row, i|
    sheet1.insert_row(i,row)
  end
end
book.write('/path_to_output_location/book.xls')

Also where does this output to? I cannot see a give path for this so I would think that is the issue but you say it generates? I added the write line because the code states this for #write

Write this Workbook to a File, IO Stream or Writer Object. The latter will make more sense once there are more than just an Excel-Writer available.

Like I said I am completely unfamiliar with this gem and the documentation is terrible with axslx it would be something like this 

package = Axlsx::Package.new
book = package.workbook
book.add_worksheet do |sheet|
  food_type_match.each do |csv|
    sheet.add_row csv
  end
end
package.serialize('/path_to_output_location/book.xlsx')

Upvotes: 2

Related Questions