How to convert CSV to Excel?
For those seeing this currently, the syntax changed a bit in these eight years. The following worked perfectly for me, based on the previous answer (recopied here for the sake of your copy-and-paste habits):
def convert_csv_to_xlsx
book = Spreadsheet::Workbook.new
sheet1 = book.create_worksheet
header_format = Spreadsheet::Format.new(
weight: :bold,
horizontal_align: :center,
bottom: :medium,
locked: true
)
sheet1.row(0).default_format = header_format
CSV.open(input_path, 'r') do |csv|
csv.each_with_index do |row, i|
sheet1.row(i).replace(row)
end
end
book.write(output_path)
end
I.E.: FasterCSV is now just CSV and true for :bottom is deprecated
If you don't found any gem for convert CSV to EXCEL then you can try to find two gems separately
- Read/Write CSV(For reading CSV file) e.g. FasterCSV
- Read/Write EXCEL(For write EXCEL file) e.g. SpreadSheet
According to this post, the spreadsheet gem is a possibility. It looks like this is a very popular gem. Check it out. Example:
book = Spreadsheet::Workbook.new
sheet1 = book.create_worksheet
header_format = Spreadsheet::Format.new(
:weight => :bold,
:horizontal_align => :center,
:bottom => true,
:locked => true
)
sheet1.row(0).default_format = header_format
FasterCSV.open(input_path, 'r') do |csv|
csv.each_with_index do |row, i|
sheet1.row(i).replace(row)
end
end
book.write(output_path)
According to this post, write_xlsx is a possibility.
I've used the Apache POI library with JRuby to export xls files. Here's a quick example.
require 'java'
require 'poi.jar'
# require 'poi-ooxml.jar'
require 'rubygems'
require 'fastercsv'
java_import org.apache.poi.hssf.usermodel.HSSFWorkbook;
wb = HSSFWorkbook.new # OR XSSFWorkbook, for xlsx
sheet = wb.create_sheet('Sheet 1')
FasterCSV.open(ARGV.first) do |csv|
csv.each_with_index do |csv_row, line_no|
row = sheet.createRow(line_no)
csv_row.each_with_index do |csv_value, col_no|
cell = row.createCell(col_no)
cell.setCellValue(csv_value) unless csv_value.nil? # can't pass nil.
end
end
end
f = java.io.FileOutputStream.new("workbook.xls")
wb.write(f)
f.close
Some useful methods for formatting POI spreadsheets are
sheet.createFreezePane(0,1,0,1)
wb.setRepeatingRowsAndColumns(0, -1, -1, 0, 1)
sheet.setColumnWidth(i, 100 *256)
sheet.autoSizeColumn(i)
, but beware, if you're running in headless mode, you have to calljava.lang.System.setProperty("java.awt.headless", "true")
You can also use Win32ole on Windows, if you have Excel installed
require 'win32ole'
require 'rubygems'
require 'fastercsv'
xl = WIN32OLE.new('Excel.Application')
xl.Visible = 0
wb = xl.Workbooks.Add
ws = wb.Worksheets(1)
FasterCSV.open(ARGV.first) do |csv|
csv.each_with_index do |csv_row, line_no|
csv_row.each_with_index do |value, col|
ws.Cells(line_no + 1, col + 1).Value = value
end
end
end
wb.SaveAs("workbook.xls", 56) # 56 = xlExcel8 aka Excel 97-2003. i.e. xls
wb.SaveAs("workbook.xlsx", 51) # 51 = xlOpenXMLWorkbook
wb.SaveAs("workbook.xlsb", 50) # 50 = xlExcel12
wb.Close(2) #xlDoNotSaveChanges
xl.Quit
Some useful methods for formatting with Excel are
xl.Rows(1).Font.Bold = true
ws.Cells.EntireColumn.AutoFit
Yet another option is to write directly to Microsoft's XML Spreadsheet format, as Ryan Bates at Railscasts.com does at the end of his Exporting CSV and Excel episode.
<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<Worksheet ss:Name="Sheet1">
<Table>
<Row>
<Cell><Data ss:Type="String">ID</Data></Cell>
<Cell><Data ss:Type="String">Name</Data></Cell>
<Cell><Data ss:Type="String">Release Date</Data></Cell>
<Cell><Data ss:Type="String">Price</Data></Cell>
</Row>
<% @products.each do |product| %>
<Row>
<Cell><Data ss:Type="Number"><%= product.id %></Data></Cell>
<Cell><Data ss:Type="String"><%= product.name %></Data></Cell>
<Cell><Data ss:Type="String"><%= product.released_on %></Data></Cell>
<Cell><Data ss:Type="Number"><%= product.price %></Data></Cell>
</Row>
<% end %>
</Table>
</Worksheet>
</Workbook>
This gem looks promising, too.