如何将CSV转换为Excel？ [英] How to convert CSV to Excel?

问题描述

Ruby中有任何插件可将CSV文件转换为Excel。我做了一点谷歌，但我发现是将Excel文件转换为CSV。我知道几个宝石，我可以调整一点，并使用将Excel转换为CSV，但我需要知道是否有任何人做过。

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.

推荐答案

根据这篇文章，电子表格 gem是一种可能性。看起来这是一个非常受欢迎的宝石。一探究竟。示例：

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)

根据此帖， write_xlsx 是一种可能性。

我使用了 Apache POI库与JRuby导出xls文件。这是一个快速示例。

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

格式化POI电子表格的一些有用方法是

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） ，如果你在无头模式下运行，你必须调用 java.lang.System.setProperty（java.awt.headless，true）

• 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")

如果您安装了Excel，也可以在Windows上使用Win32ole

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

一些有用的格式化方法Excel

Some useful methods for formatting with Excel are

• xl.Rows（1）.Font.Bold = true


• ws.Cells.EntireColumn.AutoFit

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

还有一种选择是直接写入Microsoft的 XML电子表格格式，因为Railscasts.com上的Ryan Bates在结束时

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>

这个宝石看起来很有前途。

这篇关于如何将CSV转换为Excel？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！