溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
有時我們需要用程序來自動生成 Excel 文件，但 Excel 本身帶的 VBA 并不好用，而集算器作為數(shù)據(jù)處理工具實現(xiàn)這個需求就會方便很多。本文將介紹如何用集算器生成 Excel 文件，而集算器本身強大的數(shù)據(jù)計算能力不是本文重點，因此文中只是簡單地用文本作為數(shù)據(jù)源舉例，實際應(yīng)用中可能會從各種各樣的數(shù)據(jù)源中取數(shù)，再經(jīng)過一系列運算得到需要導(dǎo)出的數(shù)據(jù)。
本文中用到的函數(shù)請參看集算器文檔《函數(shù)參考》。
這個例子中，A1 單元格讀入文本格式的某企業(yè)訂單表，用來模擬可能通過計算得到的數(shù)據(jù)。A2 中的表達(dá)式是將 A1 的數(shù)據(jù)導(dǎo)出到 orders.xlsx 文件中 (如果文件不存在，程序運行時會自動創(chuàng)建)。例子中導(dǎo)出函數(shù) xlsexport 參數(shù)中沒有指定 x 和 F，因此將導(dǎo)出 A1 中的所有字段，同時保持字段名不變。由于沒有指定參數(shù) s，所以會導(dǎo)出到 sheet1 中。而函數(shù)使用了選項 @t，因此會將字段名導(dǎo)出到第一行。
| A | |
|---|---|
| 1 | =file(“orders.txt”:”UTF-8″).import@t() |
| 2 | =file(“orders.xlsx”).xlsexport@t(A1) |
下圖中就是導(dǎo)出的 excel 文件：
假如某企業(yè)需要將每天的新訂單追加到已經(jīng)存在且有往日數(shù)據(jù)的 orders.xlsx 文件中，那么應(yīng)該怎么做呢？與上例類似，在 A1 中是要追加的某日訂單數(shù)據(jù)，數(shù)據(jù)結(jié)構(gòu)保持相同，在 A2 中導(dǎo)出時不要加函數(shù)選項 @t，因為文件中已有標(biāo)題，只需導(dǎo)出數(shù)據(jù)。由于文件已存在，因此會自動追加在原來數(shù)據(jù)的后面。
| A | |
|---|---|
| 1 | =file(“aday.txt”:”UTF-8″).import@t() |
| 2 | =file(“orders.xlsx”).xlsexport(A1) |
假如想對訂單中針對山泰企業(yè)的訂單作特殊關(guān)注，要將其數(shù)據(jù)的某些字段導(dǎo)出到專門的一個 sheet 中，又該如何進(jìn)行呢？
可以在 A2 單元格中，對序表 A1 進(jìn)行過濾，只選出公司名稱為山泰企業(yè)的數(shù)據(jù)記錄，在 A3 中將新序表 A2 導(dǎo)出到 orders.xlsx 中，只導(dǎo)出訂單 ID、公司名稱、訂購日期、訂單金額四個字段，并將訂購日期改名為日期，訂單金額改名為金額，數(shù)據(jù)導(dǎo)出到一個名為山泰企業(yè)的新 sheet 中。對應(yīng)的腳本修改如下：
| A | |
|---|---|
| 1 | =file(“orders.txt”:”UTF-8″).import@t() |
| 2 | =A1.select(公司名稱 ==”山泰企業(yè)”) |
| 3 | =file(“orders.xlsx”).xlsexport@t(A2, 訂單 ID, 公司名稱, 訂購日期: 日期, 訂單金額: 金額;”山泰企業(yè)”) |
下圖是導(dǎo)出結(jié)果：
如果數(shù)據(jù)量很大時又該怎么辦？
集算器提供了游標(biāo)來處理數(shù)據(jù)量很大的情況，游標(biāo)在讀取數(shù)據(jù)時從前向后遍歷一次，逐條從數(shù)據(jù)源讀取數(shù)據(jù)，并不是一次將所有數(shù)據(jù)讀入內(nèi)存，因此不會受到內(nèi)存不足的限制。而且，集算器游標(biāo)不僅可以應(yīng)用于數(shù)據(jù)庫，還可以應(yīng)用于數(shù)據(jù)文件或者內(nèi)存排列。
本例中 A1 打開了文件游標(biāo)，A2 中將游標(biāo)所指的大數(shù)據(jù)導(dǎo)出到 big.xlsx 文件中。在用游標(biāo)導(dǎo)出時，要添加 @s 這個函數(shù)選項，這樣在導(dǎo)出時就會以流式導(dǎo)出，產(chǎn)生的 excel 結(jié)果文件也不會占用在內(nèi)存中。
| A | |
|---|---|
| 1 | =file(“big.txt”:”UTF-8″).cursor@t() |
| 2 | =file(“big.xlsx”).xlsexport@st(A1) |
下圖是本例的導(dǎo)出結(jié)果，本例中導(dǎo)出了 130727 條數(shù)據(jù)記錄。事實上我們可以導(dǎo)出上億條記錄也不在話下，不過 excel 文件的一個 sheet 最多只能存放 1048576 行數(shù)據(jù)，所以當(dāng)導(dǎo)出數(shù)據(jù)超過百萬行時，會在 excel 中新增一個 sheet 來保存。
除了直接導(dǎo)出數(shù)據(jù)，有時我們還希望生成的 excel 文件能夠顯示得比較美觀，比如可以指定字體、顏色、背景色、對齊方式、顯示格式等。這時，只要我們預(yù)先建好這個 excel 文件（模板），定義好我們需要的這些顯示屬性，然后再用集算器向這個文件中導(dǎo)出數(shù)據(jù)，定義好的顯示屬性就會隨之呈現(xiàn)。
如下圖所示，我們在 orders.xlsx 文件 sheet1 的第一行寫上表格名稱，在第二行寫上字段列名，并對表名和各列定義一些樣式屬性，第 1、3、4 列中間對齊，第 2 列左對齊，第 5 列右對齊，第 4 列顯示格式為“yyyy 年 mm 月 dd 日”，第 5 列顯示格式為“#,###.00”。
集算器導(dǎo)出程序與本文第 1 例相同，導(dǎo)出結(jié)果如下圖所示。在導(dǎo)出到已有文件時，會將文件的最后一個非空行當(dāng)作表頭，用導(dǎo)出的表頭覆蓋此行。導(dǎo)出時會使用原文件中定義的各種樣式屬性 (×××式導(dǎo)出時不支持)。
集算器里還提供了讀寫 excel 文件中指定的某單元格或某區(qū)塊單元格的方法，這個功能在用 excel 作數(shù)據(jù)填報時非常有用。舉個例子，比如某基金公司總公司向分公司下發(fā)了一張 excel 表格，要求分公司填入它的相關(guān)數(shù)據(jù)后回傳給總公司，下發(fā)的 excel 文件如下：
對于這樣的填報表，由于每個季度都需向總公司填報，如果寫一個集算器程序首先算出要填的單元格的數(shù)據(jù)，然后調(diào)用函數(shù)自動填入對應(yīng)的格子，將會是非常省事又方便的辦法。
這個例子的腳本 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 文件內(nèi)容：
導(dǎo)出需求有時會非常復(fù)雜，比如導(dǎo)出時需要隔行顯示不同的背景色、單元格顏色與數(shù)據(jù)值動態(tài)相關(guān)、用導(dǎo)出數(shù)據(jù)畫統(tǒng)計圖、對導(dǎo)出數(shù)據(jù)進(jìn)行分組統(tǒng)計、多維交叉表等等。集算器如何實現(xiàn)這些復(fù)雜的導(dǎo)出需求呢？
別急！集算器里還有高級兵器庫——外部庫，其中的潤乾報表 5 就可以實現(xiàn)導(dǎo)出 excel 的復(fù)雜功能 (也可以導(dǎo)出 pdf 和 word)。如何部署外部庫功能請參看《外部庫使用指南》（下載地址 外部庫指南 ），集算器中以 report_ 開頭的系列函數(shù)就是實現(xiàn)此功能的。
下面來看一個導(dǎo)出訂單表的例子，導(dǎo)出需求：數(shù)據(jù)行的背景色以兩種顏色隔行交替顯現(xiàn)，訂單金額大于 2000 的用紅色顯示，低于 500 的用綠色顯示。
實現(xiàn)步驟：
1、打開潤乾報表 5 設(shè)計器，新建報表模板“訂單表.rpx”，截圖如下。
新建報表數(shù)據(jù)集 ds1，這個數(shù)據(jù)集只用于從集算器接收導(dǎo)出的序表數(shù)據(jù)，所以只需指定數(shù)據(jù)集名稱。報表的第一行是表名稱，第二行是要導(dǎo)出的列名稱，第三行是數(shù)據(jù)記錄行，數(shù)據(jù)記錄的具體寫法可以參閱潤乾報表的相關(guān)教程。
選中第三行的所有單元格，在背景色表達(dá)式中填入：if(row()%2==0,-853778,-1)，用來指定交替顯示的兩種背景色。
選擇第三行最后一個單元格，指定顯示格式為 #.00，在前景色表達(dá)式中填入：if(value()>2000,-65536,if(value()<500,-16711936,-16777216))，指定根據(jù)不同金額顯示不同的字體顏色。
2、打開集算器設(shè)計器，新建 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 中讀入要導(dǎo)出的序表數(shù)據(jù)；
A2 中進(jìn)行報表環(huán)境的配置，主要是配置報表主目錄以及授權(quán)文件；
A3 中打開我們剛才設(shè)計的報表模板；
A4 中將 A1 中的序表作為數(shù)據(jù)集 ds1 對傳遞給報表對象 A3 進(jìn)行計算；
A5 中將計算后的報表對象 A3 導(dǎo)出成 excel 文件。
3、運行上面的 dfx 文件，得到導(dǎo)出的 excel 文件如下：
Excel 中根據(jù)數(shù)據(jù)的層次進(jìn)行分組和明細(xì)的顯示也是一個非常常見的需求，這里我們就針對這個需求，看看集算器是怎么處理的：
1、打開潤乾報表 5 設(shè)計器，新建報表模板“訂單統(tǒng)計表.rpx”，截圖如下。
同上例類似，建立數(shù)據(jù)集 ds1，在 A3 格按貨主地區(qū)進(jìn)行分組，B3 格按公司名稱進(jìn)行分組，C3、D3、E3 顯示訂單明細(xì)。E4 格統(tǒng)計各公司的訂單金額總和，E5 格統(tǒng)計各地區(qū)的訂單金額總和。
2、打開集算器設(shè)計器，新建 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 文件，得到導(dǎo)出的 excel 如下圖：
同樣，交叉統(tǒng)計表也是十分常見的表格之一，集算器加潤乾報表，也可以完美實現(xiàn)將數(shù)據(jù)導(dǎo)出到 Excel 中的交叉統(tǒng)計表中：
1、打開潤乾報表 5 設(shè)計器，新建報表模板“訂單交叉表.rpx”，截圖如下。
同上例類似，建立數(shù)據(jù)集 ds1，B2 格按訂購日期的年份分組，A3 格按貨主地區(qū)分組，B3 格統(tǒng)計各分組的訂單金額總和。
2、打開集算器設(shè)計器，新建 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 文件，得到導(dǎo)出的 excel 如下圖：
可以看到，在潤乾報表豐富的設(shè)計能力基礎(chǔ)上，通過集算器將計算得到的數(shù)據(jù)傳遞給潤乾報表，然后再導(dǎo)出為 Excel，我們就能夠?qū)?shù)據(jù)以更加豐富直觀的方式提供給業(yè)務(wù)人員閱讀使用，而處理過程也會因為自動化而變得更加快捷。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼
