最近更新: 2007-04-17

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

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

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

<?php
require 'SpreadsheetReader/CSV/SpreadsheetReader_CSV.php';

$csvReader = new SpreadsheetReader_CSV;
$sheets = $csvReader->read('test.csv');

$doc = new SimpleXMLElement(
'<?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 = 1;
foreach ($sheets as $sheet) {
    //<Worksheet ss:Name="Sheet1">
    //<Table>
    $worksheetNode = $doc->addChild('Worksheet');
    //$worksheetNode->addAttribute('ss:Name', 'sheet1');//BUG?
    $worksheetNode['ss:Name'] = 'sheet' . ($indexOfSheet++);

    $worksheetNode->Table = '';//add a child with value '' by setter
    //$tableNode = $worksheetNode->addChild('Table');/add a child by addChild()

    foreach ($sheet as $row) {
        //<Row>
        $rowNode = $worksheetNode->Table->addChild('Row');
        
        foreach ($row as $col) {
            //<Cell><Data ss:Type="Number">1</Data></Cell>
            $cellNode = $rowNode->addChild('Cell');
            $cellNode->Data = iconv('big5', 'utf-8', $col);
            $cellNode->Data['ss:Type'] = 'String';
        }
    }
}

$doc->asXML('test1.xml');

?>
本文是 php-SpreadsheetReader 專案之一部工作。
樂多舊網址: http://blog.roodo.com/rocksaying/archives/3021213.html