最近更新: 2007-04-12

以 REXML 將 CSV 文件轉換成 MS Excel 2k/XP XML 文件

Tags: ruby xml spreadsheet

本文示範以 Ruby 標準庫 REXML 實踐 MS Excel 2k/XP XML 文件之匯出工作。首先讀取一個 CSV 文件 test.csv,再以 REXML 建立一份 XML 文件實例,接著按 MS Excel 2k/XP 之 XML 文件結構,將自 CSV 讀取之資料存入 XML 文件實例,最後儲存為 test.xml 。該文件可以 MS Excel 2k/XP 讀取。

關於 MS Excel 2k/XP XML 文件之相關訊息,請見《A note of creating XML document by SimpleXML》。

require 'CSV'
require 'iconv'
require 'rexml/document'
include REXML

sheets = [[]]

Iconv.open('utf-8', 'big5') do |cd|
    CSV.open('test.csv', 'r') do |row|
        uRow = []
        row.each do |cell|
            uRow << cd.iconv(cell)
        end
        sheets[0] << uRow
    end
end

doc = REXML::Document.new(
'<?xml version="1.0" encoding="utf-8"?>
<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"></Workbook>')

indexOfSheet = 0
sheets.each do |sheet|
    #<Worksheet ss:Name="Sheet1">
    #<Table>
    worksheetNode = Element.new 'Worksheet'
    worksheetNode.attributes['ss:Name'] = "sheet#{indexOfSheet+=1}"

    tableNode = Element.new 'Table'
    sheet.each do |row|
        #<Row>
        rowNode = Element.new 'Row'
        row.each do |col|
            #<Cell><Data ss:Type="Number">1</Data></Cell>
            dataNode = Element.new 'Data'
            dataNode.attributes['ss:Type'] = 'String'
            dataNode.text = col
            cellNode = Element.new 'Cell'
            cellNode.add_element dataNode
            rowNode.elements << cellNode
        end
        tableNode.elements << rowNode
    end
    worksheetNode.elements << tableNode
    doc.root.add_element worksheetNode
end

File.open('test.xml', 'w') {|file|
    doc.write file
}
樂多舊網址: http://blog.roodo.com/rocksaying/archives/2995613.html