自動導出Excel的利器

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

創(chuàng)新互聯(lián)從2013年創(chuàng)立，先為孟州等服務建站，孟州等地企業(yè)，進行企業(yè)商務咨詢服務。為孟州企業(yè)網站制作PC+手機+微官網三網同步一站式服務解決您的所有建站問題。

基礎篇

1. 單純導出數(shù)據(jù)

1.1 導出新文件

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

	A
1	=file(“orders.txt”:”UTF-8″).import@t()
2	=file(“orders.xlsx”).xlsexport@t(A1)

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

1.2 追加數(shù)據(jù)

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

	A
1	=file(“aday.txt”:”UTF-8″).import@t()
2	=file(“orders.xlsx”).xlsexport(A1)

1.3 導出到不同 sheet

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

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

	A
1	=file(“orders.txt”:”UTF-8″).import@t()
2	=A1.select(公司名稱 ==”山泰企業(yè)”)
3	=file(“orders.xlsx”).xlsexport@t(A2, 訂單 ID, 公司名稱, 訂購日期: 日期, 訂單金額: 金額;”山泰企業(yè)”)

下圖是導出結果：

2. 導出大量數(shù)據(jù)

如果數(shù)據(jù)量很大時又該怎么辦？

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

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

	A
1	=file(“big.txt”:”UTF-8″).cursor@t()
2	=file(“big.xlsx”).xlsexport@st(A1)

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

3. 指定顯示屬性

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

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

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

4. 固定行列填數(shù)據(jù)

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

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

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

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

	A	B	C	D	E	F	G
1	猛?；?/td>	2017	三	58.2	364	300
2	8.5	50	200	100	400	200
3	182.6	76.3	43.7	28.5	16.4
4	120	1.07	30	0.27	90	0.8
5	154		6				4
6	=file(“hb.xlsx”)		=A6.xlsopen()
7	=C6.xlscell(“B2”,1;A1)		=C6.xlscell(“J2”,1;B1)		=C6.xlscell(“L2”,1;C1)
8	=C6.xlscell(“B3”,1;D1)		=C6.xlscell(“G3”,1;E1)		=C6.xlscell(“K3”,1;F1)
9	=C6.xlscell(“B6”,1;[A2:F2].concat(“\t”))			=C6.xlscell(“H6”,1;[A3:E3].concat(“\t”))
10	=C6.xlscell(“B9”,1;[A4:F4].concat(“\t”))			=C6.xlscell(“B11”,1;[A5:G5].concat(“\t”))
11	=A6.xlswrite(C6)

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

高級篇

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

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

1. 動態(tài)條件的顯示屬性

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

實現(xiàn)步驟：

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

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

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

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

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

	A
1	=file(“orders.txt”:”UTF-8″).import@t()
2	>report_config(“E:\\work\\raqsoftReport\\raqsoftConfig.xml”)
3	=report_open(“訂單表.rpx”)
4	=report_run(A3;A1:”ds1″)
5	=report_exportXls@x(A3,”rpt.xlsx”)

A1 中讀入要導出的序表數(shù)據(jù)；

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

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

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

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

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

2. 分組帶明細及統(tǒng)計

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

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

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

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

	A
1	=file(“orders.txt”:”UTF-8″).import@t()
2	>report_config(“E:\\work\\raqsoftReport\\raqsoftConfig.xml”)
3	=report_open(“訂單統(tǒng)計表.rpx”)
4	=report_run(A3;A1:”ds1″)
5	=report_exportXls@x(A3,”rpt.xlsx”)

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

3. 交叉統(tǒng)計表

同樣，交叉統(tǒng)計表也是十分常見的表格之一，集算器加潤乾報表，也可以完美實現(xiàn)將數(shù)據(jù)導出到 Excel 中的交叉統(tǒng)計表中：

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

同上例類似，建立數(shù)據(jù)集 ds1，B2 格按訂購日期的年份分組，A3 格按貨主地區(qū)分組，B3 格統(tǒng)計各分組的訂單金額總和。

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

	A
1	=file(“orders.txt”:”UTF-8″).import@t()
2	>report_config(“E:\\work\\raqsoftReport\\raqsoftConfig.xml”)
3	=report_open(“訂單交叉表.rpx”)
4	=report_run(A3;A1:”ds1″)
5	=report_exportXls@x(A3,”rpt.xlsx”)

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

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

本文題目：自動導出Excel的利器
轉載源于：http://muchs.cn/article8/isphip.html

成都網站建設公司_創(chuàng)新互聯(lián)，為您提供微信公眾號、建站公司、網站策劃、企業(yè)網站制作、網站收錄、網站導航

廣告

聲明：本網站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)