Ruby中是否有任何将CSV文件转换为Excel的插件.我做了很少的谷歌,但我发现只是将Excel文件转换为CSV.我知道一些宝石,我可以稍微调整并用于将Excel转换为CSV但我需要知道是否有人之前已经这样做过.
解决方法
根据
this post,spreadsheet宝石是可能的.看起来这是一个非常受欢迎的宝石.看看这个.例：
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)
根据this post,write_xlsx是可能的.
我用JRuby的Apache POI library来导出xls文件.这是一个简单的例子.
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电子表格的方法是
> sheet.createFreezePane(0,1,1)
> wb.setRepeatingRowsAndColumns(0,-1,1)
> sheet.setColumnWidth(i,100 * 256)
> sheet.autoSizeColumn(i),但要注意,如果你在无头模式下运行,你必须调用java.lang.System.setProperty(“java.awt.headless”,“true”)
如果安装了Excel,也可以在Windows上使用Win32ole
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格式化的一些有用方法是
> xl.Rows(1).Font.Bold = true
> ws.Cells.EntireColumn.AutoFit
另一个选择是直接写入微软的XML Spreadsheet格式,正如Railscasts.com上的Ryan Bates做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>