復(fù)雜 Excel 表格導(dǎo)入導(dǎo)出的最簡(jiǎn)方法

把 Excel 文件導(dǎo)入關(guān)系數(shù)據(jù)庫(kù)是數(shù)據(jù)分析業(yè)務(wù)中經(jīng)常要做的事情，但許多 Excel 文件的格式并不規(guī)整，需要事先將其中的數(shù)據(jù)結(jié)構(gòu)化后再用 SQL 語(yǔ)句寫入數(shù)據(jù)庫(kù)。JAVA程序猿經(jīng)常選擇使用POI或者HSSFWorkbook等第三方類庫(kù)來實(shí)現(xiàn)，通常都要硬編碼，如果碰到格式復(fù)雜的表格，解析工作量還會(huì)成倍增加，Java沒有表格對(duì)象，總要利用集合加實(shí)體類去實(shí)現(xiàn)，導(dǎo)致代碼冗長(zhǎng)、不通用。集算器的 SPL 是專業(yè)處理結(jié)構(gòu)化數(shù)據(jù)的語(yǔ)言，它能夠輕松讀取 excel 數(shù)據(jù)，然后結(jié)構(gòu)化成“序表”后導(dǎo)入數(shù)據(jù)庫(kù)。使用 SPL 語(yǔ)言后，以往需要編寫數(shù)千行代碼才能完成的 Excel 數(shù)據(jù)結(jié)構(gòu)化入庫(kù)工作，現(xiàn)在只需要不到 10 行代碼就可以勝任，簡(jiǎn)單情況下甚至只需要 2、3 行代碼。

而關(guān)于導(dǎo)出，有時(shí)我們需要用程序來自動(dòng)生成 Excel 文件，但 Excel 本身帶的 VBA 并不好用，集算器作為數(shù)據(jù)處理工具實(shí)現(xiàn)這個(gè)需求就會(huì)方便很多。

本文中用到的函數(shù)請(qǐng)參看集算器文檔《函數(shù)參考》。

下面我們就來了解一下集算器是如何對(duì)表格數(shù)據(jù)進(jìn)行導(dǎo)入或?qū)С龅模?/p>

導(dǎo)入

1、普通行式

表格樣式：

集算器腳本：

腳本說明：

A1：打開“學(xué)生成績(jī)表.xlsx”文件并導(dǎo)入成序表，選項(xiàng)@t表示文件第一行是列標(biāo)題；

A2：連接demo數(shù)據(jù)庫(kù)；

A3：將A1中的序表存入到demo數(shù)據(jù)庫(kù)的xscj表中，由于表中的列名和序表中的字段名一樣，所以只需指定數(shù)據(jù)表名即可。

導(dǎo)入效果：

2、多行表頭行式

表格樣式：

集算器腳本：

腳本說明：

A1：打開文件并導(dǎo)入數(shù)據(jù)成序表，參數(shù)“1,5”表示讀第一個(gè) sheet，從第 5 行開始讀，一直讀到文件結(jié)尾；

A2：將 A1 中讀到的序表列名依次改為“序號(hào)、項(xiàng)目編碼、項(xiàng)目名稱、計(jì)量單位、數(shù)量、單價(jià)、合價(jià)”，即要存入的數(shù)據(jù)表的列名。

導(dǎo)入效果：

3、自由格式

表格樣式：

集算器腳本：

腳本說明：

A1：創(chuàng)建列名為“雇員 ID, 姓名, 性別, 職位, 生日, 電話, 地址, 郵編”的空序表

A2： 打開 Excel 數(shù)據(jù)文件

A3：定義雇員信息所在單元格列號(hào)序列

B3：定義雇員信息所在單元格行號(hào)序列

A4：用 for 循環(huán)讀取每個(gè)雇員信息

B4：A3.(~/B3(#))先算出當(dāng)前雇員單元格編號(hào)序列, 再讀出這些單元格值組成雇員信息序列。第一次循環(huán)時(shí)為 [C1,C2,F2,C3,C4,D5,C7,C8]，第二次循環(huán)時(shí)為[C10,C11,F11,C12,C13,D14,C16,C17]……每次行號(hào)加 9。$[A2.xlscell(] 與 "A2.xlscell(" 相同，都是表示一個(gè)字符串，它的好處是在 IDE 中編寫程序時(shí)，如果 A2 單元格的編號(hào)發(fā)生了變化，$[A2.xlscell(]中的 A2 會(huì)自動(dòng)變化，比如在 A2 前插入了一行，這個(gè)表達(dá)式就會(huì)變成 $[A3.xlscell(]，而用引號(hào)的話，就不會(huì)自動(dòng)變了。

B5：判斷雇員 ID 值是否為空，為空則退出循環(huán)，結(jié)束運(yùn)行

B6：將一條雇員信息存入 A1 序表尾

B7：讓雇員信息的行號(hào)序列都加上 9，讀取下一條雇員信息

導(dǎo)入效果：

4、交叉表

表格樣式：

集算器腳本：

腳本說明：

A1：打開文件并導(dǎo)入數(shù)據(jù)成序表，參數(shù)“1,2”表示讀第一個(gè) sheet，從第 2 行開始讀，一直讀到文件結(jié)尾。選項(xiàng) @t 表示開始行是列標(biāo)題。

A2：由于第二行第一個(gè)單元格是圖片，讀的數(shù)據(jù)為 null，第一列沒有列標(biāo)題，所以將第一列列名改為運(yùn)貨商。

A3：以運(yùn)貨商為分組，對(duì)序表數(shù)據(jù)進(jìn)行行列轉(zhuǎn)換，選項(xiàng) @r 表示將列數(shù)據(jù)轉(zhuǎn)換為行數(shù)據(jù)，轉(zhuǎn)換后新的列名分別為“貨主地區(qū)”、“訂單數(shù)量”。

導(dǎo)入效果：

5、主子表

表格樣式：

集算器腳本：

腳本說明：

A1：創(chuàng)建列名為“×××號(hào), 姓名, 性別, 出生日期, 民族, 手機(jī)號(hào), 部門, 家庭地址, 婚姻狀況, 入職時(shí)間”的空序表，用于保存主表員工信息；

A2：創(chuàng)建列名為“×××號(hào), 姓名, 關(guān)系, 工作單位, 聯(lián)系電話”的空序表，用于保存子表員工家庭成員信息；

A3：定義主表員工信息所在單元格序列；

A4：打開 Excel 數(shù)據(jù)文件；

A5：循環(huán)讀取 Excel 文件各 sheet 數(shù)據(jù)；

B6：讀取員工信息序列；

C6：將 B6 讀取的員工信息保存到序表 A1；

B7：從第 6 行開始讀取員工家庭成員信息，只讀指定的“家庭成員, 姓名, 關(guān)系, 工作單位, 聯(lián)系電話”5 列；

B8：將 B7 序表的家庭成員列改名為×××號(hào)；

C8：為 B8 序表的×××號(hào)列賦值為員工信息中的×××號(hào)；

B9：將 B8 中的員工家庭成員信息保存到序表 A2。

導(dǎo)入效果：

序表A1如下圖：

序表A2如下圖：

上面這些情況基本羅列了常見的 Excel 數(shù)據(jù)格式，如果遇到更復(fù)雜的文件，也可以靈活使用例子中的技巧予以應(yīng)對(duì)。

導(dǎo)出

基礎(chǔ)篇

1、單純導(dǎo)出數(shù)據(jù)

（1）導(dǎo)出新文件

集算器腳本：

腳本說明：

A1：讀入文本格式的某企業(yè)訂單表，用來模擬可能通過計(jì)算得到的數(shù)據(jù)；

A2：將 A1 的數(shù)據(jù)導(dǎo)出到 orders.xlsx 文件中 (如果文件不存在，程序運(yùn)行時(shí)會(huì)自動(dòng)創(chuàng)建)。例子中導(dǎo)出函數(shù) xlsexport 參數(shù)中沒有指定 x 和 F，因此將導(dǎo)出 A1 中的所有字段，同時(shí)保持字段名不變。由于沒有指定參數(shù) s，所以會(huì)導(dǎo)出到 sheet1 中。而函數(shù)使用了選項(xiàng) @t，因此會(huì)將字段名導(dǎo)出到第一行。

導(dǎo)出效果：

（2）追加數(shù)據(jù)

集算器腳本：

腳本說明：

A1：讀入文本格式的某日訂單數(shù)據(jù)表；

A2：導(dǎo)出時(shí)不要加函數(shù)選項(xiàng) @t，因?yàn)槲募幸延袠?biāo)題，只需導(dǎo)出數(shù)據(jù)。由于文件已存在，因此會(huì)自動(dòng)追加在原來數(shù)據(jù)的后面。

（3）導(dǎo)出到不同 sheet

集算器腳本：

腳本說明：

A1：讀入文本格式的某企業(yè)訂單表；

A2：對(duì)序表 A1 進(jìn)行過濾，只選出公司名稱為山泰企業(yè)的數(shù)據(jù)記錄；

A3：將新序表 A2 導(dǎo)出到 orders.xlsx 中，只導(dǎo)出訂單 ID、公司名稱、訂購(gòu)日期、訂單金額四個(gè)字段，并將訂購(gòu)日期改名為日期，訂單金額改名為金額，數(shù)據(jù)導(dǎo)出到一個(gè)名為山泰企業(yè)的新 sheet 中。

導(dǎo)出效果：

2、導(dǎo)出大量數(shù)據(jù)

集算器腳本：

腳本說明：

A1：讀入文本格式某數(shù)據(jù)量較大的表；

A2：將游標(biāo)所指的大數(shù)據(jù)導(dǎo)出到 big.xlsx 文件中。在用游標(biāo)導(dǎo)出時(shí)，要添加 @s 這個(gè)函數(shù)選項(xiàng)，這樣在導(dǎo)出時(shí)就會(huì)以流式導(dǎo)出，產(chǎn)生的 excel 結(jié)果文件也不會(huì)占用在內(nèi)存中。

導(dǎo)出效果：

注：

本例中導(dǎo)出了 130727 條數(shù)據(jù)記錄。事實(shí)上我們可以導(dǎo)出上億條記錄也不在話下，不過 excel 文件的一個(gè) sheet 最多只能存放 1048576 行數(shù)據(jù)，所以當(dāng)導(dǎo)出數(shù)據(jù)超過百萬(wàn)行時(shí)，會(huì)在 excel 中新增一個(gè) sheet 來保存。

3、指定顯示屬性

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

表格樣式：

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

集算器腳本：

導(dǎo)出樣式：

注：導(dǎo)出時(shí)會(huì)使用原文件中定義的各種樣式屬性；×××式導(dǎo)出時(shí)不支持。

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

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

集算器腳本：

腳本說明：

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

導(dǎo)出樣式：

高級(jí)篇

1、動(dòng)態(tài)條件的顯示屬性

導(dǎo)出需求：

數(shù)據(jù)行的背景色以兩種顏色隔行交替顯現(xiàn)，訂單金額大于 2000 的用紅色顯示，低于 500 的用綠色顯示。

報(bào)表設(shè)計(jì)：

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

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

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

集算器腳本：

腳本說明：

A1：讀入要導(dǎo)出的序表數(shù)據(jù)；

A2：進(jìn)行報(bào)表環(huán)境的配置，主要是配置報(bào)表主目錄以及授權(quán)文件；

A3：打開我們剛才設(shè)計(jì)的報(bào)表模板；

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

A5：將計(jì)算后的報(bào)表對(duì)象 A3 導(dǎo)出成 excel 文件。

導(dǎo)出效果：

2、分組帶明細(xì)及統(tǒng)計(jì)

報(bào)表設(shè)計(jì)：

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

集算器腳本：

腳本說明：

A1：讀入要導(dǎo)出的序表數(shù)據(jù)；

A2：進(jìn)行報(bào)表環(huán)境的配置，主要是配置報(bào)表主目錄以及授權(quán)文件；

A3：打開我們剛才設(shè)計(jì)的報(bào)表模板；

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

A5：將計(jì)算后的報(bào)表對(duì)象 A3 導(dǎo)出成 excel 文件。

導(dǎo)出樣式：

3、交叉統(tǒng)計(jì)表

報(bào)表設(shè)計(jì)：

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

集算器腳本：

腳本說明：

A1：讀入要導(dǎo)出的序表數(shù)據(jù)；

A2：進(jìn)行報(bào)表環(huán)境的配置，主要是配置報(bào)表主目錄以及授權(quán)文件；

A3：打開我們剛才設(shè)計(jì)的報(bào)表模板；

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

A5：將計(jì)算后的報(bào)表對(duì)象 A3 導(dǎo)出成 excel 文件。

導(dǎo)出樣式：

總結(jié)

集算器提供了非常靈活的在 excel 文件中定位和讀取數(shù)據(jù)的功能，既可以成片讀取網(wǎng)格數(shù)據(jù)，也可以精確定位單元格進(jìn)行讀取。再結(jié)合特有的“序表”對(duì)象，以往需要編寫數(shù)千行代碼才能完成的 Excel 數(shù)據(jù)結(jié)構(gòu)化入庫(kù)工作，現(xiàn)在只需要不到 10 行，甚至兩三行代碼就可以勝任。

而關(guān)于導(dǎo)出，在潤(rùn)乾報(bào)表豐富的設(shè)計(jì)能力基礎(chǔ)上，通過集算器將計(jì)算得到的數(shù)據(jù)傳遞給潤(rùn)乾報(bào)表，然后再導(dǎo)出為 Excel，我們就能夠?qū)?shù)據(jù)以更加豐富直觀的方式提供給業(yè)務(wù)人員閱讀使用，而處理過程也會(huì)因?yàn)樽詣?dòng)化而變得更加快捷。