自動導出 Excel 的利器

　　有時我們需要用程序來自動生成 Excel 文件，但 Excel 本身帶的 VBA 并不好用，而集算器作為數據處理工具實現這個需求就會方便很多。本文將介紹如何用集算器生成 Excel 文件，而集算器本身強大的數據計算能力不是本文重點，因此文中只是簡單地用文本作為數據源舉例，實際應用中可能會從各種各樣的數據源中取數，再經過一系列運算得到需要導出的數據。
　　本文中用到的函數請參看集算器文檔《函數參考》。

基礎篇

1. 單純導出數據

1.1 導出新文件

　　這個例子中，A1 單元格讀入文本格式的某企業訂單表，用來模擬可能通過計算得到的數據。A2 中的表達式是將 A1 的數據導出到 orders.xlsx 文件中 (如果文件不存在，程序運行時會自動創建)。例子中導出函數 xlsexport 參數中沒有指定 x 和 F，因此將導出 A1 中的所有字段，同時保持字段名不變。由于沒有指定參數 s，所以會導出到 sheet1 中。而函數使用了選項 @t，因此會將字段名導出到第一行。

　　下圖中就是導出的 excel 文件：

1.2 追加數據

　　假如某企業需要將每天的新訂單追加到已經存在且有往日數據的 orders.xlsx 文件中，那么應該怎么做呢？與上例類似，在 A1 中是要追加的某日訂單數據，數據結構保持相同，在 A2 中導出時不要加函數選項 @t，因為文件中已有標題，只需導出數據。由于文件已存在，因此會自動追加在原來數據的后面。

1.3 導出到不同 sheet

　　假如想對訂單中針對山泰企業的訂單作特殊關注，要將其數據的某些字段導出到專門的一個 sheet 中，又該如何進行呢？

　　可以在 A2 單元格中，對序表 A1 進行過濾，只選出公司名稱為山泰企業的數據記錄，在 A3 中將新序表 A2 導出到 orders.xlsx 中，只導出訂單 ID、公司名稱、訂購日期、訂單金額四個字段，并將訂購日期改名為日期，訂單金額改名為金額，數據導出到一個名為山泰企業的新 sheet 中。對應的腳本修改如下：

　　下圖是導出結果：

2. 導出大量數據

　　如果數據量很大時又該怎么辦？

　　集算器提供了游標來處理數據量很大的情況，游標在讀取數據時從前向后遍歷一次，逐條從數據源讀取數據，并不是一次將所有數據讀入內存，因此不會受到內存不足的限制。而且，集算器游標不僅可以應用于數據庫，還可以應用于數據文件或者內存排列。

　　本例中 A1 打開了文件游標，A2 中將游標所指的大數據導出到 big.xlsx 文件中。在用游標導出時，要添加 @s 這個函數選項，這樣在導出時就會以流式導出，產生的 excel 結果文件也不會占用在內存中。

　　下圖是本例的導出結果，本例中導出了 130727 條數據記錄。事實上我們可以導出上億條記錄也不在話下，不過 excel 文件的一個 sheet 最多只能存放 1048576 行數據，所以當導出數據超過百萬行時，會在 excel 中新增一個 sheet 來保存。

3. 指定顯示屬性

　　除了直接導出數據，有時我們還希望生成的 excel 文件能夠顯示得比較美觀，比如可以指定字體、顏色、背景色、對齊方式、顯示格式等。這時，只要我們預先建好這個 excel 文件（模板），定義好我們需要的這些顯示屬性，然后再用集算器向這個文件中導出數據，定義好的顯示屬性就會隨之呈現。

　　如下圖所示，我們在 orders.xlsx 文件 sheet1 的第一行寫上表格名稱，在第二行寫上字段列名，并對表名和各列定義一些樣式屬性，第 1、3、4 列中間對齊，第 2 列左對齊，第 5 列右對齊，第 4 列顯示格式為“yyyy 年 mm 月 dd 日”，第 5 列顯示格式為“#,###.00”。

　　集算器導出程序與本文第 1 例相同，導出結果如下圖所示。在導出到已有文件時，會將文件的最后一個非空行當作表頭，用導出的表頭覆蓋此行。導出時會使用原文件中定義的各種樣式屬性 (×××式導出時不支持)。

4. 固定行列填數據

　　集算器里還提供了讀寫 excel 文件中指定的某單元格或某區塊單元格的方法，這個功能在用 excel 作數據填報時非常有用。舉個例子，比如某基金公司總公司向分公司下發了一張 excel 表格，要求分公司填入它的相關數據后回傳給總公司，下發的 excel 文件如下：

　　對于這樣的填報表，由于每個季度都需向總公司填報，如果寫一個集算器程序首先算出要填的單元格的數據，然后調用函數自動填入對應的格子，將會是非常省事又方便的辦法。

　　這個例子的腳本 dfx 文件如下圖所示，假定要填的數據都算出來了，前 5 行是依次要填的數據。

　　樣表中前 6 個要填的單元格都是獨立的，所以只能每次填一個格，第 6 行是可以連續填寫的單元格，此時就把要填的數據拼成以 \t 分隔的字符串，可以同行中按順序填入。數據全部填寫完以后，再把 C6 打開的 excel 對象寫回到 hb.xlsx 文件中。

　　下圖即是完成填報后的 excel 文件內容：

高級篇

　　導出需求有時會非常復雜，比如導出時需要隔行顯示不同的背景色、單元格顏色與數據值動態相關、用導出數據畫統計圖、對導出數據進行分組統計、多維交叉表等等。集算器如何實現這些復雜的導出需求呢？

　　別急！集算器里還有高級兵器庫——外部庫，其中的潤乾報表 5 就可以實現導出 excel 的復雜功能 (也可以導出 pdf 和 word)。如何部署外部庫功能請參看《外部庫使用指南》（下載地址 外部庫指南 ），集算器中以 report_ 開頭的系列函數就是實現此功能的。

1. 動態條件的顯示屬性

　　下面來看一個導出訂單表的例子，導出需求：數據行的背景色以兩種顏色隔行交替顯現，訂單金額大于 2000 的用紅色顯示，低于 500 的用綠色顯示。

　　實現步驟：

　　1、打開潤乾報表 5 設計器，新建報表模板“訂單表.rpx”，截圖如下。

　　新建報表數據集 ds1，這個數據集只用于從集算器接收導出的序表數據，所以只需指定數據集名稱。報表的第一行是表名稱，第二行是要導出的列名稱，第三行是數據記錄行，數據記錄的具體寫法可以參閱潤乾報表的相關教程。

　　選中第三行的所有單元格，在背景色表達式中填入：if(row()%2==0,-853778,-1)，用來指定交替顯示的兩種背景色。

　　選擇第三行最后一個單元格，指定顯示格式為 #.00，在前景色表達式中填入：if(value()>2000,-65536,if(value()<500,-16711936,-16777216))，指定根據不同金額顯示不同的字體顏色。

　　2、打開集算器設計器，新建 dfx 文件如下：

　　A1 中讀入要導出的序表數據；

　　A2 中進行報表環境的配置，主要是配置報表主目錄以及授權文件；

　　A3 中打開我們剛才設計的報表模板；

　　A4 中將 A1 中的序表作為數據集 ds1 對傳遞給報表對象 A3 進行計算；

　　A5 中將計算后的報表對象 A3 導出成 excel 文件。

3、運行上面的 dfx 文件，得到導出的 excel 文件如下：

2. 分組帶明細及統計

　　Excel 中根據數據的層次進行分組和明細的顯示也是一個非常常見的需求，這里我們就針對這個需求，看看集算器是怎么處理的：

　　1、打開潤乾報表 5 設計器，新建報表模板“訂單統計表.rpx”，截圖如下。

　　同上例類似，建立數據集 ds1，在 A3 格按貨主地區進行分組，B3 格按公司名稱進行分組，C3、D3、E3 顯示訂單明細。E4 格統計各公司的訂單金額總和，E5 格統計各地區的訂單金額總和。

　　2、打開集算器設計器，新建 dfx 文件如下：

　　3、運行這個 dfx 文件，得到導出的 excel 如下圖：

3. 交叉統計表

　　同樣，交叉統計表也是十分常見的表格之一，集算器加潤乾報表，也可以完美實現將數據導出到 Excel 中的交叉統計表中：

　　1、打開潤乾報表 5 設計器，新建報表模板“訂單交叉表.rpx”，截圖如下。

　　同上例類似，建立數據集 ds1，B2 格按訂購日期的年份分組，A3 格按貨主地區分組，B3 格統計各分組的訂單金額總和。

　　2、打開集算器設計器，新建 dfx 文件如下：

　　3、運行這個 dfx 文件，得到導出的 excel 如下圖：

　　可以看到，在潤乾報表豐富的設計能力基礎上，通過集算器將計算得到的數據傳遞給潤乾報表，然后再導出為 Excel，我們就能夠將數據以更加豐富直觀的方式提供給業務人員閱讀使用，而處理過程也會因為自動化而變得更加快捷。