Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to convert CSV to Excel?

Tags:

ruby

csv

excel

gem

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.

like image 623
Bhushan Lodha Avatar asked Apr 12 '12 06:04

Bhushan Lodha


People also ask

How to convert CSV file to excel columns?

To convert CSV file to Excel columns, actually, you can handle it through three different methods. In this article, I will talk about these three methods for you. Open CSV file in Excel . Insert CSV file in Excel. Open CSV file in Excel. The most usually used method must be opening CSV file directly through Excel. 1.

How do I open a CSV file in Excel 2007?

Open CSV file in Excel The most usually used method must be opening CSV file directly through Excel. 1. Click File > Open > Browse to select a CSV file from a folder, remember to choose All Files in the drop-down list next to File name box.

How to select a CSV file from a folder in Excel?

1. Click File > Open > Browse to select a CSV file from a folder, remember to choose All Files in the drop-down list next to File name box. See screenshot:

How do I associate a CSV file with Excel?

To associate a .csv file with Excel, right-click on the file in the Windows Explorer and then select Properties. 2. To the right of Opens with, select the Change button. 3. Select Excel and then click OK. 4.


2 Answers

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

like image 91
Luis F M Alves Avatar answered Sep 18 '22 12:09

Luis F M Alves


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.

like image 37
John Douthat Avatar answered Sep 17 '22 12:09

John Douthat