財務 programmer 與使用者多半有這樣的體驗:試算表對某些會計資料特別合適,改一個數字立刻看到連動結果,還能隨意做情境模擬,不必動到正式的會計記錄。
問題出在報表端。收入支出表、試算表(trial balance)、資產負債表這些靜態報表,往往由另一套應用程式產生,卻需要讀取動態試算的結果才能完成。於是常見的做法是跨應用程式複製貼上——既耗時,又容易出錯;改用 CSV 之類的格式搬資料,同樣顯得笨拙。
關鍵在於:Gnumeric 存下的檔案本身就是 XML。既然報表程式(例如 PHP)天生就能讀 XML,那麼直接讀取原始檔,路徑其實比想像中短得多。
最典型的例子莫過於折舊/資本成本減值準備(Capital Cost Allowances,CCA)。一台機器若要在生產線上服役多年,價值會隨時間攤提,壽命屆滿時才更換。會計必須決定每年的折舊百分比:這個比例不要求逐年一致,稅務機關通常只給定一個上限,企業在上限之內自行選擇當期適用的比例。
動態試算表可以一次呈現好幾年的記錄:既有前幾年的折舊歷史,也有未來幾年的推估。搭配 Solver 等統計工具,老闆能算出今年該採用的折舊額。決策定案後,這個數字就必須出現在靜態報表裡——而傳遞方式的效率差異很大。
圖 1 是一份涵蓋 2005 至 2014 年的折舊/CCA 試算表示例。
圖 1. 折舊或 CCA 試算表樣例
示範的條件是這樣:2005 年初購入一台價格 $30,000 的機器,稅務機關允許此類資產每年最高折舊 20%。前兩年企業選擇 10%,到了 2010 年改為 15%。只要在試算表(儲存格 C8)改動數字,後續年度的條目會立刻重算更新。
Gnumeric 檔案是純 XML,用一般的文字編輯器就能大致掌握結構。要注意的是它預設可能以壓縮格式儲存,若想直接檢視內容,請先在 Gnumeric 的設定中把壓縮率設為零再存檔。
瀏覽整體結構有個小技巧:把檔案複製一份並改為 .xml 副檔名,再用支援 XML 的瀏覽器開啟,就能自由摺疊或展開各層元素。
接著只要盯住真正承載資料的部分即可——試算表環境裡大量資訊(樣式、欄寬、格式設定)對單純的資料交換而言是冗餘的。
清單 1 是 Gnumeric 存檔中的一段摘錄。
清單 1. 資料檔案片段
<?xml version="1.0" encoding="UTF-8"?>
<gnm:Workbook xmlns:gnm="https://www.gnumeric.org/v10.dtd"
xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="https://www.gnumeric.org/v9.xsd">
<gnm:Version Epoch="1" Major="9" Minor="13" Full="1.9.13"/>
<gnm:Attributes ...>
...
<gnm:Sheets>
...
<gnm:Sheet ...>
...
<gnm:Name>Sheet1</gnm:Name>
...
<gnm:Cells>
<gnm:Cell Row="0" Col="0" ValueType="60">A depreciation example</gnm:Cell>
...
根元素是 <gnm:Workbook>,前綴 gnm 代表名稱空間。Workbook 底下有許多子元素,其中 Sheets 又包含 Sheet,Sheet 底下才是 Cells 與 Cell。也就是說,要拿到資料,得往下走四層。
上面那段摘錄中,第 0 列第 0 欄的儲存格內容是字串 A depreciation example;這裡的「0 列 0 欄」就對應試算表裡的 A1。另外要留意,工作表名稱 Sheet1 存在 Sheet 的 Name 子元素中。
知道資料在哪裡之後,用 PHP 提取看似輕鬆,但實作時會遇到兩個真正的障礙:
名稱空間讓 XML 能符合預期結構,卻也提高了擷取門檻。若不處理名稱空間,PHP 的 SimpleXML 相關呼叫會失效:呼叫 children() 時必須帶入前綴名稱以及「它是作為前綴使用」的參數;而帶前綴的元素在讀取屬性時,寫法也略有不同。
試算表的儲存格可能是固定值,也可能是計算式。如果報表引擎試圖解析計算式,就等於要用和試算表一樣的方式重算一遍。因此更省事的做法是:只引用存放固定值的儲存格。
清單 2 是對應的示例函式。
清單 2. 提取資料
function read_cca_data() {
$ccafile = "sscca.gnumeric";
if (!file_exists($ccafile)) {
die("Problem: CCA file $ccafile not found");
} else {
$xml = simplexml_load_file($ccafile);
foreach ($xml->children('gnm', TRUE) as $child1) {
if ($child1->getName() == "Sheets") {
foreach ($child1->children('gnm', TRUE) as $child2) {
foreach ($child2->children('gnm', TRUE) as $child3) {
if ($child3->getName() == "Name") {
if ($child3 != "Sheet1") break 2;
}
if ($child3->getName() == "Cells") {
foreach ($child3->children('gnm', TRUE) as $child4) {
// child4 就是一個儲存格
$row = (int) $child4->attributes()->Row;
$col = (int) $child4->attributes()->Col;
$val = "$child4"; // 加引號後會轉為字串或數值型別
$val = (is_numeric($val)) ? round($val, 3) : $val;
$ccaarr[$row][$col] = $val;
}
}
}
}
}
}
}
return $ccaarr;
}
這段程式的流程是:先定義 Gnumeric 檔名,接著檢查檔案是否存在,不存在就結束執行;存在則載入為 SimpleXML 物件,然後往下遞進四層($child1 到 $child4)尋找儲存格。每一層都會驗證元素名稱或工作表名稱是否正確。每次呼叫 children() 都要指定名稱空間,並表明它被當作前綴使用。
到了儲存格這一層,程式從 Row 與 Col 屬性取出列號與欄號並轉為整數備用;若值為數字,則四捨五入到小數第三位。最後所有值(字串或數值)都存入二維陣列,整份工作表讀完後回傳給呼叫方,呼叫方再用已知的索引取出需要的數字。
回到折舊範例。圖 2 是圖 1 的調整版本。
圖 2. 另一個折舊或 CCA 試算表樣例
新出現的 F3:H12 區域,是 B3:D12 經過「選擇性貼上」而來的一份複本。原始區域含有需要計算的值,而新區域存的是固定值,因此可以直接讀取。這個複製貼上動作在試算表裡很容易自動化;當然,如果原始資料已在陣列中,您也可以自行重算需要的值。
另外提醒兩點:第 2 列的數字 5、6、7 只是欄號提示(欄號從 0 起算,A 欄是第 0 欄);E 欄雖然是空白欄,在 XML 裡仍會留下沒有值的儲存格記錄。
從報表應用程式呼叫清單 2 的函式後,會得到一個多維陣列,例如 $ccaarr[2][0]=2005、$ccaarr[2][6]=0.1。
Gnumeric 與 PHP 的組合,剛好各取所長:試算表負責吸納多變的資訊並以 XML 保存,PHP 報表程式則能直接讀取 XML,不必經過任何會損失精度的中段格式。省下的不只是複製貼上的時間,還有一整類「數字對不上」的排查成本。