把 Excel 文件導(dǎo)入關(guān)系數(shù)據(jù)庫是數(shù)據(jù)分析業(yè)務(wù)中經(jīng)常要做的事情，但許多 Excel 文件的格式并不規(guī)整，需要事先將其中的數(shù)據(jù)結(jié)構(gòu)化后再用 SQL 語句寫入數(shù)據(jù)庫。JAVA程序猿經(jīng)常選擇使用POI或者HSSFWorkbook等第三方類庫來實現(xiàn)，通常都要硬編碼，如果碰到格式復(fù)雜的表格，解析工作量還會成倍增加，Java沒有表格對象，總要利用集合加實體類去實現(xiàn)，導(dǎo)致代碼冗長、不通用。集算器的 SPL 是專業(yè)處理結(jié)構(gòu)化數(shù)據(jù)的語言，它能夠輕松讀取 excel 數(shù)據(jù)，然后結(jié)構(gòu)化成“序表”后導(dǎo)入數(shù)據(jù)庫。使用 SPL 語言后，以往需要編寫數(shù)千行代碼才能完成的 Excel 數(shù)據(jù)結(jié)構(gòu)化入庫工作，現(xiàn)在只需要不到 10 行代碼就可以勝任，簡單情況下甚至只需要 2、3 行代碼。
而關(guān)于導(dǎo)出，有時我們需要用程序來自動生成 Excel 文件，但 Excel 本身帶的 VBA 并不好用，集算器作為數(shù)據(jù)處理工具實現(xiàn)這個需求就會方便很多。
本文中用到的函數(shù)請參看集算器文檔《函數(shù)參考》。
下面我們就來了解一下集算器是如何對表格數(shù)據(jù)進(jìn)行導(dǎo)入或?qū)С龅模?/p>
導(dǎo)入
1、普通行式
表格樣式：
集算器腳本：
腳本說明：
A1：打開“學(xué)生成績表.xlsx”文件并導(dǎo)入成序表，選項@t表示文件第一行是列標(biāo)題；
A2：連接demo數(shù)據(jù)庫；
A3：將A1中的序表存入到demo數(shù)據(jù)庫的xscj表中，由于表中的列名和序表中的字段名一樣，所以只需指定數(shù)據(jù)表名即可。
導(dǎo)入效果：
2、多行表頭行式
表格樣式：
集算器腳本：
腳本說明：
A1：打開文件并導(dǎo)入數(shù)據(jù)成序表，參數(shù)“1,5”表示讀第一個 sheet，從第 5 行開始讀，一直讀到文件結(jié)尾；
A2：將 A1 中讀到的序表列名依次改為“序號、項目編碼、項目名稱、計量單位、數(shù)量、單價、合價”，即要存入的數(shù)據(jù)表的列名。
導(dǎo)入效果：
3、自由格式
表格樣式：
集算器腳本：
腳本說明：
A1：創(chuàng)建列名為“雇員 ID, 姓名, 性別, 職位, 生日, 電話, 地址, 郵編”的空序表
A2：?打開 Excel 數(shù)據(jù)文件
A3：定義雇員信息所在單元格列號序列
B3：定義雇員信息所在單元格行號序列
A4：用 for 循環(huán)讀取每個雇員信息
B4：A3.(~/B3(#))先算出當(dāng)前雇員單元格編號序列, 再讀出這些單元格值組成雇員信息序列。第一次循環(huán)時為 [C1,C2,F2,C3,C4,D5,C7,C8]，第二次循環(huán)時為[C10,C11,F11,C12,C13,D14,C16,C17]……每次行號加 9。$[A2.xlscell(] 與 "A2.xlscell(" 相同，都是表示一個字符串，它的好處是在 IDE 中編寫程序時，如果 A2 單元格的編號發(fā)生了變化，$[A2.xlscell(]中的 A2 會自動變化，比如在 A2 前插入了一行，這個表達(dá)式就會變成 $[A3.xlscell(]，而用引號的話，就不會自動變了。
B5：判斷雇員 ID 值是否為空，為空則退出循環(huán)，結(jié)束運(yùn)行
B6：將一條雇員信息存入 A1 序表尾
B7：讓雇員信息的行號序列都加上 9，讀取下一條雇員信息
導(dǎo)入效果：
4、交叉表
表格樣式：
集算器腳本：
腳本說明：
A1：打開文件并導(dǎo)入數(shù)據(jù)成序表，參數(shù)“1,2”表示讀第一個 sheet，從第 2 行開始讀，一直讀到文件結(jié)尾。選項 @t 表示開始行是列標(biāo)題。
A2：由于第二行第一個單元格是圖片，讀的數(shù)據(jù)為 null，第一列沒有列標(biāo)題，所以將第一列列名改為運(yùn)貨商。
A3：以運(yùn)貨商為分組，對序表數(shù)據(jù)進(jìn)行行列轉(zhuǎn)換，選項 @r 表示將列數(shù)據(jù)轉(zhuǎn)換為行數(shù)據(jù)，轉(zhuǎn)換后新的列名分別為“貨主地區(qū)”、“訂單數(shù)量”。
導(dǎo)入效果：
5、主子表
表格樣式：
集算器腳本：
腳本說明：
A1：創(chuàng)建列名為“身份證號, 姓名, 性別, 出生日期, 民族, 手機(jī)號, 部門, 家庭地址, 婚姻狀況, 入職時間”的空序表，用于保存主表員工信息；
A2：創(chuàng)建列名為“身份證號, 姓名, 關(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 序表的家庭成員列改名為身份證號；
C8：為 B8 序表的身份證號列賦值為員工信息中的身份證號；
B9：將 B8 中的員工家庭成員信息保存到序表 A2。
導(dǎo)入效果：
序表A1如下圖：
序表A2如下圖：
上面這些情況基本羅列了常見的 Excel 數(shù)據(jù)格式，如果遇到更復(fù)雜的文件，也可以靈活使用例子中的技巧予以應(yīng)對。
導(dǎo)出
基礎(chǔ)篇
1、單純導(dǎo)出數(shù)據(jù)
（1）導(dǎo)出新文件
集算器腳本：
腳本說明：
A1：讀入文本格式的某企業(yè)訂單表，用來模擬可能通過計算得到的數(shù)據(jù)；
A2：將 A1 的數(shù)據(jù)導(dǎo)出到 orders.xlsx 文件中 (如果文件不存在，程序運(yùn)行時會自動創(chuàng)建)。例子中導(dǎo)出函數(shù) xlsexport 參數(shù)中沒有指定 x 和 F，因此將導(dǎo)出 A1 中的所有字段，同時保持字段名不變。由于沒有指定參數(shù) s，所以會導(dǎo)出到 sheet1 中。而函數(shù)使用了選項 @t，因此會將字段名導(dǎo)出到第一行。
導(dǎo)出效果：
（2）追加數(shù)據(jù)
集算器腳本：
腳本說明：
A1：讀入文本格式的某日訂單數(shù)據(jù)表；
A2：導(dǎo)出時不要加函數(shù)選項 @t，因為文件中已有標(biāo)題，只需導(dǎo)出數(shù)據(jù)。由于文件已存在，因此會自動追加在原來數(shù)據(jù)的后面。
（3）導(dǎo)出到不同 sheet
集算器腳本：
腳本說明：
A1：讀入文本格式的某企業(yè)訂單表；
A2：對序表 A1 進(jìn)行過濾，只選出公司名稱為山泰企業(yè)的數(shù)據(jù)記錄；
A3：將新序表 A2 導(dǎo)出到 orders.xlsx 中，只導(dǎo)出訂單 ID、公司名稱、訂購日期、訂單金額四個字段，并將訂購日期改名為日期，訂單金額改名為金額，數(shù)據(jù)導(dǎo)出到一個名為山泰企業(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)出時，要添加 @s 這個函數(shù)選項，這樣在導(dǎo)出時就會以流式導(dǎo)出，產(chǎn)生的 excel 結(jié)果文件也不會占用在內(nèi)存中。
導(dǎo)出效果：
注：
本例中導(dǎo)出了 130727 條數(shù)據(jù)記錄。事實上我們可以導(dǎo)出上億條記錄也不在話下，不過 excel 文件的一個 sheet 最多只能存放 1048576 行數(shù)據(jù)，所以當(dāng)導(dǎo)出數(shù)據(jù)超過百萬行時，會在 excel 中新增一個 sheet 來保存。
3、指定顯示屬性
除了直接導(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)出樣式：
注：導(dǎo)出時會使用原文件中定義的各種樣式屬性；大數(shù)據(jù)流式導(dǎo)出時不支持。
4、固定行列填數(shù)據(jù)
集算器里還提供了讀寫 excel 文件中指定的某單元格或某區(qū)塊單元格的方法，這個功能在用 excel 作數(shù)據(jù)填報時非常有用。比如某基金公司總公司向分公司下發(fā)了一張 excel 表格，要求分公司填入它的相關(guān)數(shù)據(jù)后回傳給總公司，下發(fā)的 excel 文件如下：
集算器腳本：
腳本說明：
前 5 行是依次要填的數(shù)據(jù)；樣表中前 6 個要填的單元格都是獨立的，所以只能每次填一個格，第 6 行是可以連續(xù)填寫的單元格，此時就把要填的數(shù)據(jù)拼成以 \t 分隔的字符串，可以同行中按順序填入。數(shù)據(jù)全部填寫完以后，再把 C6 打開的 excel 對象寫回到 hb.xlsx 文件中。
導(dǎo)出樣式：
高級篇
1、動態(tài)條件的顯示屬性
導(dǎo)出需求：
數(shù)據(jù)行的背景色以兩種顏色隔行交替顯現(xiàn)，訂單金額大于 2000 的用紅色顯示，低于 500 的用綠色顯示。
報表設(shè)計：
新建報表數(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ù)不同金額顯示不同的字體顏色。
集算器腳本：
腳本說明：
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 文件。
導(dǎo)出效果：
2、分組帶明細(xì)及統(tǒng)計
報表設(shè)計：
建立數(shù)據(jù)集 ds1，在 A3 格按貨主地區(qū)進(jìn)行分組，B3 格按公司名稱進(jìn)行分組，C3、D3、E3 顯示訂單明細(xì)。E4 格統(tǒng)計各公司的訂單金額總和，E5 格統(tǒng)計各地區(qū)的訂單金額總和。
集算器腳本：
腳本說明：
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 文件。
導(dǎo)出樣式：
3、交叉統(tǒng)計表
報表設(shè)計：
建立數(shù)據(jù)集 ds1，B2 格按訂購日期的年份分組，A3 格按貨主地區(qū)分組，B3 格統(tǒng)計各分組的訂單金額總和。
集算器腳本：
腳本說明：
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 文件。
導(dǎo)出樣式：
總結(jié)
集算器提供了非常靈活的在 excel 文件中定位和讀取數(shù)據(jù)的功能，既可以成片讀取網(wǎng)格數(shù)據(jù)，也可以精確定位單元格進(jìn)行讀取。再結(jié)合特有的“序表”對象，以往需要編寫數(shù)千行代碼才能完成的 Excel 數(shù)據(jù)結(jié)構(gòu)化入庫工作，現(xiàn)在只需要不到 10 行，甚至兩三行代碼就可以勝任。
而關(guān)于導(dǎo)出，在潤乾報表豐富的設(shè)計能力基礎(chǔ)上，通過集算器將計算得到的數(shù)據(jù)傳遞給潤乾報表，然后再導(dǎo)出為 Excel，我們就能夠?qū)?shù)據(jù)以更加豐富直觀的方式提供給業(yè)務(wù)人員閱讀使用，而處理過程也會因為自動化而變得更加快捷。
