I'm attempting to copy all the contents of a CSV file over to an excel workbook using the AXLSX gem. On a second sheet, I only want 2 of the columns copied over. Below is an example.
I tried the '.map' method but that didn't work.
require 'csv'require 'Axlsx'p = Axlsx::Package.newwb = p.workbookanimals = CSV.read('test.csv', headers:true)column = ['Animals', 'Name']headers = Array.newheaders << "Animal"headers << "Name"headers << "Age"headers << "State"wb.add_worksheet(:name => 'Copy') do |sheet|animals.each do |row|headers.map { |col| sheet.add_row [row[col]] }endendwb.add_worksheet(:name => 'Names') do |sheet|animals.each do |row|column.map { |col| sheet.add_row [row[col]] }endendp.serialize 'Animals.xlsx'CSV - But also desired output on XLSXOutput from my code
Best Answer
At first read file using IO::readlines, then split every line (separate by the cells using Array#map and String#split).
It will produce nested array. Something like
[["Animal", "Name", "Age", "State"], ["Dog", "Rufus", "7", "CA"], ["Bird", "Magnus", "3", "FL"]]Each subarray here is the row of your table, each item of subarray is the cell of your table.
If you need just "Name" column on second sheet you need make nested array
[["Name"], ["Rufus"], ["Magnus"]]That's all you need to generate XLSX file.
For example you have animals.csv with data serapated by , and want to generate animals.xlsx:
require 'axlsx'copy = File.readlines('animals.csv', chomp: true).map { |s| s.split(',') }names = copy.map { |l| [l[1]] }Axlsx::Package.new do |p|p.workbook.add_worksheet(name: 'Animals') { |sheet| copy.each { |line| sheet.add_row(line) }}p.workbook.add_worksheet(name: 'Names') { |sheet| names.each { |line| sheet.add_row(line) }}p.serialize('animals.xlsx')end