How to convert CSV to Excel?

Question

Is there a plugin in Ruby that converts a CSV file to Excel. I did a bit of Google, but all I found was converting the Excel file to CSV. I know a few stones that I can tweak a bit and use to convert Excel to CSV, but I need to know if I did this before.

Answer 1

According to this post , spreadsheet is an opportunity. It seems to be a very popular stone. Check this. 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 feature.

I used Apache POI library with JRuby to export xls files. Here is a brief 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 tables:

• sheet.createFreezePane(0,1,0,1)
• wb.setRepeatingRowsAndColumns(0, -1, -1, 0, 1)
• sheet.setColumnWidth(i, 100 *256)
• sheet.autoSizeColumn(i) , but be careful if you are working in headless mode, you need to call java.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. ie 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 in Excel:

• xl.Rows(1).Font.Bold = true
• ws.Cells.EntireColumn.AutoFit

Another option is to write directly in the Microsoft XML Spreadsheet format , as Ryan Bates on Railscasts.com makes at the end of his CSV and Excel export episodes .

 <?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 stone looks promising, too .

Answer 2

If you have not found a stone to convert CSV to EXCEL, you can try to find two gems separately

• Read / write CSV (for reading CSV file), for example. Fastercsv
• Read / write EXCEL (for writing EXCEL file), for example. Spreadsheet