CODEWITHSUNDEEP
rubyexcelcsvrubygems
Bhushan Lodha
Bhushan Lodha

Reputation: 6862

How to convert CSV to Excel?

Is there any plugin in Ruby that converts CSV file onto Excel. I did little Google but all I found was converting Excel file into CSV. I know few gems which I can tweak a little and use to convert Excel to CSV but I need to know if anyone has done that before.

Upvotes: 11

Views: 15750

Answers (4)

Luis F M Alves
Luis F M Alves

Reputation: 21

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

Upvotes: 2

Aminah Nuraini
Aminah Nuraini

Reputation: 19156

The easy way is:

  1. Open the CSV using your favorite text editor like Sublime Text. Notepad is okay
  2. Replace all , (comma) to a tab \t
  3. Save as it with extension .xls
  4. Open the file using Excel and TADA! Here you go!

Upvotes: -2

Hardik Patel
Hardik Patel

Reputation: 838

If you don't found any gem for convert CSV to EXCEL then you can try to find two gems separately

  1. Read/Write CSV(For reading CSV file) e.g. FasterCSV
  2. Read/Write EXCEL(For write EXCEL file) e.g. SpreadSheet

Upvotes: 2

John Douthat
John Douthat

Reputation: 41179

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 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. 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.

Upvotes: 11

Related Questions