溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
把 Excel 文件導入關系數據庫是數據分析業務中經常要做的事情，但許多 Excel 文件的格式并不規整，需要事先將其中的數據結構化后再用 SQL 語句寫入數據庫。JAVA程序猿經常選擇使用POI或者HSSFWorkbook等第三方類庫來實現，通常都要硬編碼，如果碰到格式復雜的表格，解析工作量還會成倍增加，Java沒有表格對象，總要利用集合加實體類去實現，導致代碼冗長、不通用。集算器的 SPL 是專業處理結構化數據的語言，它能夠輕松讀取 excel 數據，然后結構化成“序表”后導入數據庫。使用 SPL 語言后，以往需要編寫數千行代碼才能完成的 Excel 數據結構化入庫工作，現在只需要不到 10 行代碼就可以勝任，簡單情況下甚至只需要 2、3 行代碼。
而關于導出，有時我們需要用程序來自動生成 Excel 文件，但 Excel 本身帶的 VBA 并不好用，集算器作為數據處理工具實現這個需求就會方便很多。
本文中用到的函數請參看集算器文檔《函數參考》。
下面我們就來了解一下集算器是如何對表格數據進行導入或導出的：
表格樣式：
集算器腳本：
腳本說明：
A1：打開“學生成績表.xlsx”文件并導入成序表，選項@t表示文件第一行是列標題；
A2：連接demo數據庫；
A3：將A1中的序表存入到demo數據庫的xscj表中，由于表中的列名和序表中的字段名一樣，所以只需指定數據表名即可。
導入效果：
表格樣式：
集算器腳本：
腳本說明：
A1：打開文件并導入數據成序表，參數“1,5”表示讀第一個 sheet，從第 5 行開始讀，一直讀到文件結尾；
A2：將 A1 中讀到的序表列名依次改為“序號、項目編碼、項目名稱、計量單位、數量、單價、合價”，即要存入的數據表的列名。
導入效果：
表格樣式：
集算器腳本：
腳本說明：
A1：創建列名為“雇員 ID, 姓名, 性別, 職位, 生日, 電話, 地址, 郵編”的空序表
A2： 打開 Excel 數據文件
A3：定義雇員信息所在單元格列號序列
B3：定義雇員信息所在單元格行號序列
A4：用 for 循環讀取每個雇員信息
B4：A3.(~/B3(#))先算出當前雇員單元格編號序列, 再讀出這些單元格值組成雇員信息序列。第一次循環時為 [C1,C2,F2,C3,C4,D5,C7,C8]，第二次循環時為[C10,C11,F11,C12,C13,D14,C16,C17]……每次行號加 9。$[A2.xlscell(] 與 "A2.xlscell(" 相同，都是表示一個字符串，它的好處是在 IDE 中編寫程序時，如果 A2 單元格的編號發生了變化，$[A2.xlscell(]中的 A2 會自動變化，比如在 A2 前插入了一行，這個表達式就會變成 $[A3.xlscell(]，而用引號的話，就不會自動變了。
B5：判斷雇員 ID 值是否為空，為空則退出循環，結束運行
B6：將一條雇員信息存入 A1 序表尾
B7：讓雇員信息的行號序列都加上 9，讀取下一條雇員信息
導入效果：
表格樣式：
集算器腳本：
腳本說明：
A1：打開文件并導入數據成序表，參數“1,2”表示讀第一個 sheet，從第 2 行開始讀，一直讀到文件結尾。選項 @t 表示開始行是列標題。
A2：由于第二行第一個單元格是圖片，讀的數據為 null，第一列沒有列標題，所以將第一列列名改為運貨商。
A3：以運貨商為分組，對序表數據進行行列轉換，選項 @r 表示將列數據轉換為行數據，轉換后新的列名分別為“貨主地區”、“訂單數量”。
導入效果：
表格樣式：
集算器腳本：
腳本說明：
A1：創建列名為“×××號, 姓名, 性別, 出生日期, 民族, 手機號, 部門, 家庭地址, 婚姻狀況, 入職時間”的空序表，用于保存主表員工信息；
A2：創建列名為“×××號, 姓名, 關系, 工作單位, 聯系電話”的空序表，用于保存子表員工家庭成員信息；
A3：定義主表員工信息所在單元格序列；
A4：打開 Excel 數據文件；
A5：循環讀取 Excel 文件各 sheet 數據；
B6：讀取員工信息序列；
C6：將 B6 讀取的員工信息保存到序表 A1；
B7：從第 6 行開始讀取員工家庭成員信息，只讀指定的“家庭成員, 姓名, 關系, 工作單位, 聯系電話”5 列；
B8：將 B7 序表的家庭成員列改名為×××號；
C8：為 B8 序表的×××號列賦值為員工信息中的×××號；
B9：將 B8 中的員工家庭成員信息保存到序表 A2。
導入效果：
序表A1如下圖：
序表A2如下圖：
上面這些情況基本羅列了常見的 Excel 數據格式，如果遇到更復雜的文件，也可以靈活使用例子中的技巧予以應對。
集算器腳本：
腳本說明：
A1：讀入文本格式的某企業訂單表，用來模擬可能通過計算得到的數據；
A2：將 A1 的數據導出到 orders.xlsx 文件中 (如果文件不存在，程序運行時會自動創建)。例子中導出函數 xlsexport 參數中沒有指定 x 和 F，因此將導出 A1 中的所有字段，同時保持字段名不變。由于沒有指定參數 s，所以會導出到 sheet1 中。而函數使用了選項 @t，因此會將字段名導出到第一行。
導出效果：
集算器腳本：
腳本說明：
A1：讀入文本格式的某日訂單數據表；
A2：導出時不要加函數選項 @t，因為文件中已有標題，只需導出數據。由于文件已存在，因此會自動追加在原來數據的后面。
集算器腳本：
腳本說明：
A1：讀入文本格式的某企業訂單表；
A2：對序表 A1 進行過濾，只選出公司名稱為山泰企業的數據記錄；
A3：將新序表 A2 導出到 orders.xlsx 中，只導出訂單 ID、公司名稱、訂購日期、訂單金額四個字段，并將訂購日期改名為日期，訂單金額改名為金額，數據導出到一個名為山泰企業的新 sheet 中。
導出效果：
集算器腳本：
腳本說明：
A1：讀入文本格式某數據量較大的表；
A2：將游標所指的大數據導出到 big.xlsx 文件中。在用游標導出時，要添加 @s 這個函數選項，這樣在導出時就會以流式導出，產生的 excel 結果文件也不會占用在內存中。
導出效果：
注：
本例中導出了 130727 條數據記錄。事實上我們可以導出上億條記錄也不在話下，不過 excel 文件的一個 sheet 最多只能存放 1048576 行數據，所以當導出數據超過百萬行時，會在 excel 中新增一個 sheet 來保存。
除了直接導出數據，有時我們還希望生成的 excel 文件能夠顯示得比較美觀，比如可以指定字體、顏色、背景色、對齊方式、顯示格式等。這時，只要我們預先建好這個 excel 文件（模板），定義好我們需要的這些顯示屬性，然后再用集算器向這個文件中導出數據，定義好的顯示屬性就會隨之呈現。
表格樣式：
在 orders.xlsx 文件 sheet1 的第一行寫上表格名稱，在第二行寫上字段列名，并對表名和各列定義一些樣式屬性，第 1、3、4 列中間對齊，第 2 列左對齊，第 5 列右對齊，第 4 列顯示格式為“yyyy 年 mm 月 dd 日”，第 5 列顯示格式為“#,###.00”。
集算器腳本：
導出樣式：
注：導出時會使用原文件中定義的各種樣式屬性；×××式導出時不支持。
集算器里還提供了讀寫 excel 文件中指定的某單元格或某區塊單元格的方法，這個功能在用 excel 作數據填報時非常有用。比如某基金公司總公司向分公司下發了一張 excel 表格，要求分公司填入它的相關數據后回傳給總公司，下發的 excel 文件如下：
集算器腳本：
腳本說明：
前 5 行是依次要填的數據；樣表中前 6 個要填的單元格都是獨立的，所以只能每次填一個格，第 6 行是可以連續填寫的單元格，此時就把要填的數據拼成以 \t 分隔的字符串，可以同行中按順序填入。數據全部填寫完以后，再把 C6 打開的 excel 對象寫回到 hb.xlsx 文件中。
導出樣式：
導出需求：
數據行的背景色以兩種顏色隔行交替顯現，訂單金額大于 2000 的用紅色顯示，低于 500 的用綠色顯示。
報表設計：
新建報表數據集 ds1，這個數據集只用于從集算器接收導出的序表數據，所以只需指定數據集名稱。報表的第一行是表名稱，第二行是要導出的列名稱，第三行是數據記錄行，數據記錄的具體寫法可以參閱潤乾報表的相關教程。
選中第三行的所有單元格，在背景色表達式中填入：if(row()%2==0,-853778,-1)，用來指定交替顯示的兩種背景色。
選擇第三行最后一個單元格，指定顯示格式為 #.00，在前景色表達式中填入：if(value()>2000,-65536,if(value()<500,-16711936,-16777216))，指定根據不同金額顯示不同的字體顏色。
集算器腳本：
腳本說明：
A1：讀入要導出的序表數據；
A2：進行報表環境的配置，主要是配置報表主目錄以及授權文件；
A3：打開我們剛才設計的報表模板；
A4：將 A1 中的序表作為數據集 ds1 對傳遞給報表對象 A3 進行計算；
A5：將計算后的報表對象 A3 導出成 excel 文件。
導出效果：
報表設計：
建立數據集 ds1，在 A3 格按貨主地區進行分組，B3 格按公司名稱進行分組，C3、D3、E3 顯示訂單明細。E4 格統計各公司的訂單金額總和，E5 格統計各地區的訂單金額總和。
集算器腳本：
腳本說明：
A1：讀入要導出的序表數據；
A2：進行報表環境的配置，主要是配置報表主目錄以及授權文件；
A3：打開我們剛才設計的報表模板；
A4：將 A1 中的序表作為數據集 ds1 對傳遞給報表對象 A3 進行計算；
A5：將計算后的報表對象 A3 導出成 excel 文件。
導出樣式：
報表設計：
建立數據集 ds1，B2 格按訂購日期的年份分組，A3 格按貨主地區分組，B3 格統計各分組的訂單金額總和。
集算器腳本：
腳本說明：
A1：讀入要導出的序表數據；
A2：進行報表環境的配置，主要是配置報表主目錄以及授權文件；
A3：打開我們剛才設計的報表模板；
A4：將 A1 中的序表作為數據集 ds1 對傳遞給報表對象 A3 進行計算；
A5：將計算后的報表對象 A3 導出成 excel 文件。
導出樣式：
集算器提供了非常靈活的在 excel 文件中定位和讀取數據的功能，既可以成片讀取網格數據，也可以精確定位單元格進行讀取。再結合特有的“序表”對象，以往需要編寫數千行代碼才能完成的 Excel 數據結構化入庫工作，現在只需要不到 10 行，甚至兩三行代碼就可以勝任。
而關于導出，在潤乾報表豐富的設計能力基礎上，通過集算器將計算得到的數據傳遞給潤乾報表，然后再導出為 Excel，我們就能夠將數據以更加豐富直觀的方式提供給業務人員閱讀使用，而處理過程也會因為自動化而變得更加快捷。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
