兩種非勻質(zhì)Excel表轉(zhuǎn)換成關(guān)系數(shù)據(jù)庫表的方法

方木云 趙長鮮 張祝夢(mèng)

摘? 要：依據(jù)表中數(shù)據(jù)的特點(diǎn)，Excel表可以分為勻質(zhì)和非勻質(zhì)兩種類型，而關(guān)系數(shù)據(jù)庫表只有勻質(zhì)一種類型，所以非勻質(zhì)的Excel表數(shù)據(jù)不能直接導(dǎo)入到關(guān)系數(shù)據(jù)庫表中，需要進(jìn)行表結(jié)構(gòu)的勻質(zhì)化轉(zhuǎn)換。為了解決這一問題，提出了兩種非勻質(zhì)EXCEL表轉(zhuǎn)換成關(guān)系數(shù)據(jù)庫表的方法，實(shí)現(xiàn)了非勻質(zhì)Excel表結(jié)構(gòu)向關(guān)系數(shù)據(jù)庫表結(jié)構(gòu)的轉(zhuǎn)換，并用C#編程實(shí)現(xiàn)了Excel表數(shù)據(jù)向關(guān)系數(shù)據(jù)庫表的自動(dòng)導(dǎo)入。

關(guān)鍵詞：勻質(zhì)Excel;非勻質(zhì)Excel;關(guān)系數(shù)據(jù)庫

中圖分類號(hào)：TP311? ? ?文獻(xiàn)標(biāo)識(shí)碼：A

Abstract： According to the characteristics of the data in the table， the Excel table can be divided into homogeneous and inhomogeneous types， while the relational database table has only one type， so the inhomogeneous Excel table data cannot be directly imported into the relational database table， and it is required to implement the homogenization transformation of the table structure. In order to solve this problem， the paper proposes two methods of transforming inhomogeneous Excel table into relational database table. The study achieve the transformation from inhomogeneous Excel table structure to relational database table structure， and the automatic import of Excel table data into relational database table in C# programming language.

Keywords： homogeneous excel; inhomogeneous excel; relational database

1? ?引言（Introduction）

Excel廣泛應(yīng)用在日常辦公的數(shù)據(jù)處理中。Excel的一個(gè)突出特點(diǎn)是采用表格方式管理數(shù)據(jù)，所有的數(shù)據(jù)和信息都以工作表的二維表格形式管理，單元格中數(shù)據(jù)間的相互關(guān)系一目了然。很多信息系統(tǒng)早期都是使用Excel來進(jìn)行管理，不少單位的財(cái)務(wù)系統(tǒng)甚至到現(xiàn)在還在使用Excel。隨著可視化編程語言和關(guān)系數(shù)據(jù)庫的出現(xiàn)，很多應(yīng)用系統(tǒng)開始向C/S （Client/Server）和B/S （Browser/Server）模式的信息管理系統(tǒng)遷移，Excel系統(tǒng)逐漸被替代。盡管Excel表跟關(guān)系數(shù)據(jù)庫表一樣采用二維表管理數(shù)據(jù)，可是很多Excel表數(shù)據(jù)不能行列對(duì)應(yīng)地轉(zhuǎn)換成關(guān)系數(shù)據(jù)庫表數(shù)據(jù)。如何將單位內(nèi)部已經(jīng)使用多年的各種Excel表結(jié)構(gòu)快速有效地轉(zhuǎn)化成關(guān)系數(shù)據(jù)庫的表結(jié)構(gòu)并實(shí)現(xiàn)數(shù)據(jù)自動(dòng)導(dǎo)入是一個(gè)重要的應(yīng)用問題[1-9]。

關(guān)系數(shù)據(jù)庫表只提供數(shù)據(jù)標(biāo)準(zhǔn)化管理，不提供自由編輯，其列數(shù)據(jù)類型約束強(qiáng)，一列只能全部填寫數(shù)字或者全部填寫文字;而Excel對(duì)列數(shù)據(jù)類型不強(qiáng)制約束，一列可以填寫數(shù)字、文字或?yàn)榭铡Ｋ杂脩艚?jīng)常隨心所欲地使用Excel表，這種用戶友好性導(dǎo)致表格數(shù)據(jù)多種多樣。

依據(jù)所存數(shù)據(jù)的特點(diǎn)，Excel表可以分為勻質(zhì)和非勻質(zhì)兩種類型。勻質(zhì)的Excel表結(jié)構(gòu)是指列的數(shù)據(jù)類型一致和列的數(shù)據(jù)行相等，如學(xué)生表、課程表、選課表等;非勻質(zhì)的Excel表結(jié)構(gòu)是指列的數(shù)據(jù)類型不一致或列的數(shù)據(jù)行不相等。勻質(zhì)的Excel表結(jié)構(gòu)可以行列對(duì)應(yīng)地直接轉(zhuǎn)換成關(guān)系數(shù)據(jù)庫表結(jié)構(gòu);非勻質(zhì)的Excel表結(jié)構(gòu)不能直接轉(zhuǎn)換成關(guān)系數(shù)據(jù)庫表結(jié)構(gòu)，所以需要進(jìn)行勻質(zhì)化轉(zhuǎn)換。

在實(shí)際開發(fā)中，由于缺乏經(jīng)驗(yàn)，很多開發(fā)者不區(qū)分勻質(zhì)和非勻質(zhì)Excel，直接將所有Excel表行列對(duì)應(yīng)地直接映射到關(guān)系數(shù)據(jù)庫表。結(jié)果出現(xiàn)不少表格的數(shù)據(jù)不便于檢索和無法擴(kuò)展，用戶數(shù)據(jù)需求一旦增加，軟件就無法使用，導(dǎo)致開發(fā)出來的系統(tǒng)是“僵尸”系統(tǒng)。所以需要提出一種非勻質(zhì)Excel表轉(zhuǎn)換成關(guān)系數(shù)據(jù)庫表的方法。文獻(xiàn)[1]—文獻(xiàn)[7]開發(fā)了將Excel數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫的工具，沒有區(qū)分不同的Excel，文獻(xiàn)[8]—文獻(xiàn)[9]考慮了Excel的不規(guī)則性，從而開發(fā)了一個(gè)導(dǎo)入工具。本文將Excel的不規(guī)則性區(qū)分為行非勻質(zhì)和列非勻質(zhì)的兩種Excel。

2? 非勻質(zhì)Excel表的定義（Definition of non-homogeneous Excel tables）

關(guān)系數(shù)據(jù)庫表是強(qiáng)約束的，有如下特征：（1）一個(gè)表描述的是一個(gè)實(shí)體或者實(shí)體之間的一個(gè)聯(lián)系;（2）一個(gè)列描述一個(gè)屬性，也稱為字段，是存儲(chǔ)和檢索數(shù)據(jù)的關(guān)鍵;（3）每個(gè)列的數(shù)據(jù)類型是唯一的，如：只能是數(shù)值或者是文本，不能同時(shí)存數(shù)值和文本;（4）表的行數(shù)是相等的，也就是每個(gè)字段有相同的行值，允許有缺省值。Excel表是弱約束的，有如下特征：（1）一個(gè)表可以記錄任意一個(gè)實(shí)體和聯(lián)系;（2）一個(gè)單元格是存儲(chǔ)和檢索數(shù)據(jù)的關(guān)鍵;（3）一個(gè)列可以記錄不同類型的數(shù)據(jù);（4）表的行可以長短不一致。

Excel表通過單元格進(jìn)行數(shù)據(jù)的相對(duì)和絕對(duì)引用，通過VLOOK來查找和調(diào)用數(shù)據(jù)。關(guān)系數(shù)據(jù)庫表通過列使用SELECT結(jié)合WHERE條件來查找和調(diào)用數(shù)據(jù)。

完全不同于關(guān)系數(shù)據(jù)庫一個(gè)表只能記錄一個(gè)實(shí)體或聯(lián)系的數(shù)據(jù)，Excel表弱約束帶來的寬松和靈活性使得其得到廣泛應(yīng)用，可以同時(shí)用來記錄結(jié)構(gòu)化和非結(jié)構(gòu)化的數(shù)據(jù)，可以記錄多個(gè)實(shí)體和聯(lián)系，導(dǎo)致表格可以分為勻質(zhì)和非勻質(zhì)兩種類型。

定義：當(dāng)Excel表存儲(chǔ)結(jié)構(gòu)化數(shù)據(jù)時(shí)，完全按照關(guān)系數(shù)據(jù)庫表模式來記錄數(shù)據(jù)的Excel表稱為勻質(zhì)Excel表;凡是不按照關(guān)系數(shù)據(jù)庫表模式來記錄數(shù)據(jù)的Excel表稱為非勻質(zhì)Excel表。其中，數(shù)據(jù)行不相等的Excel表稱為行非勻質(zhì)Excel表;數(shù)據(jù)列類型不一致的Excel表稱為列非勻質(zhì)Excel表。

結(jié)論：當(dāng)Excel表存儲(chǔ)結(jié)構(gòu)化數(shù)據(jù)時(shí)，所有非勻質(zhì)Excel表都可以歸入行非勻質(zhì)Excel表或者列非勻質(zhì)Excel表，不存在行列同時(shí)不勻質(zhì)的Excel表。

證明：一個(gè)正確表達(dá)結(jié)構(gòu)化信息的Excel表是二維表，利用某一列或某一行來存儲(chǔ)某個(gè)屬性，其不勻質(zhì)只能來自列或行，不能同時(shí)來自列和行，否則無法正確表達(dá)信息。

下面舉出幾個(gè)實(shí)例（如表1—表3所示），分別是勻質(zhì)Excel表、行非勻質(zhì)Excel表、列非勻質(zhì)Excel表。

表1是勻質(zhì)Excel表，其表結(jié)構(gòu)和數(shù)據(jù)可以行列對(duì)應(yīng)地直接導(dǎo)入到關(guān)系數(shù)據(jù)庫表當(dāng)中;表2是行非勻質(zhì)Excel表，行的長度不一致;表3是列非勻質(zhì)Excel表，列的屬性不一致，列B既存了姓名這種文本型數(shù)據(jù)又存了年齡這種數(shù)字型數(shù)據(jù)。表2和表3的數(shù)據(jù)結(jié)構(gòu)和值都不能行列對(duì)應(yīng)地直接導(dǎo)入到關(guān)系數(shù)據(jù)庫表當(dāng)中，需要進(jìn)行勻質(zhì)化轉(zhuǎn)換。

下面討論如何將行非勻質(zhì)和列非勻質(zhì)Excel表的數(shù)據(jù)結(jié)構(gòu)轉(zhuǎn)換為關(guān)系數(shù)據(jù)庫表結(jié)構(gòu)，并如何編程將對(duì)應(yīng)數(shù)據(jù)導(dǎo)入到關(guān)系數(shù)據(jù)庫表中。

3 非勻質(zhì)Excel表向數(shù)據(jù)庫表的轉(zhuǎn)換方法（Transformation method of non-uniform excel table to database table）

3.1? ?行非勻質(zhì)的Excel轉(zhuǎn)換方法

針對(duì)表2這種行非勻質(zhì)的Excel表，首先進(jìn)行形式化描述：將Excel表中數(shù)據(jù)劃分為列字段的名稱和列字段的值兩個(gè)部分。Excel表中第一行數(shù)據(jù)為列字段的名稱，分別標(biāo)識(shí)為F1，F(xiàn)2，...，F(xiàn)n，其中n>1;Excel表中剩下的數(shù)據(jù)為列字段的值，分別標(biāo)識(shí)為：

經(jīng)過這樣的轉(zhuǎn)換后，數(shù)據(jù)存兩列，整個(gè)變勻質(zhì)了。以表2為例，第一行的【建設(shè)投資成本，┄┄，股權(quán)出資單位】成為類別字段的值，【建設(shè)工程費(fèi)用，┄┄，工程預(yù)備費(fèi)用】隨著類別【建設(shè)投資成本】循環(huán)存，這樣數(shù)據(jù)就可以擴(kuò)充了。

3.2? ?列非勻質(zhì)的Excel轉(zhuǎn)換方法

針對(duì)表3這種列非勻質(zhì)的Excel表，首先進(jìn)行形式化描述：將Excel表劃分為列字段的名稱和列字段的值兩個(gè)部分，Excel表中第一列數(shù)據(jù)為列字段的名稱，分別標(biāo)識(shí)為F1，F(xiàn)2，...，F(xiàn)n，其中n>1;Excel表中剩余數(shù)據(jù)為列字段的值，分別標(biāo)識(shí)為：

經(jīng)過這樣的轉(zhuǎn)換后，整個(gè)表變勻質(zhì)了，這樣數(shù)據(jù)就可以擴(kuò)充了。通過SELECT結(jié)合WHERE條件來查找數(shù)據(jù)。列非勻質(zhì)Excel表比行非勻質(zhì)Excel表容易轉(zhuǎn)換，轉(zhuǎn)換前也容易理解，轉(zhuǎn)換時(shí)不需要另外建立字段名。行非勻質(zhì)Excel表“欺騙性”比較強(qiáng)，沒有經(jīng)驗(yàn)的開發(fā)者，可能不進(jìn)行轉(zhuǎn)換就直接行列對(duì)應(yīng)地轉(zhuǎn)換成數(shù)據(jù)庫表，這樣的系統(tǒng)沒有擴(kuò)展性，使用不方便。

4? 編程實(shí)現(xiàn)自動(dòng)轉(zhuǎn)換結(jié)構(gòu)和導(dǎo)入數(shù)據(jù)（Programming to achieve automatic transformation structure and import data）

提出了行非勻質(zhì)Excel表和列非勻質(zhì)Excel表向關(guān)系數(shù)據(jù)庫表轉(zhuǎn)換的方法之后，可以編程自動(dòng)實(shí)現(xiàn)結(jié)構(gòu)的轉(zhuǎn)換和數(shù)據(jù)的導(dǎo)入。下面分別給出兩個(gè)結(jié)構(gòu)轉(zhuǎn)換和數(shù)據(jù)導(dǎo)入算法：

（1）行非勻質(zhì)Excel表的轉(zhuǎn)換算法

Step1：利用Create table命令在數(shù)據(jù)庫中建立具有兩個(gè)字段TypeField和ContentField的表T1;

Step2：讀取Excel表，以F1至Fn作為外循環(huán)，以VF11至VF1x等作為內(nèi)循環(huán)，將值循環(huán)填入到TypeField和ContentField兩個(gè)字段下面;

Step3：循環(huán)終止。

（2）列非勻質(zhì)Excel表的轉(zhuǎn)換算法

Step1：讀取Excel表，以F1至Fn作為循環(huán)拼接字符串，利用Create table命令在數(shù)據(jù)庫中建立具有n個(gè)字段F1，...，F(xiàn)n的表T1;

Step2：讀取Excel表，以F1，...，F(xiàn)n作為讀取條件，循環(huán)將對(duì)應(yīng)的值填入到其字段下面;

Step3：循環(huán)終止。

在PPP財(cái)務(wù)評(píng)價(jià)軟件中，利用C#編程，將兩種非勻質(zhì)的Excel表成功的轉(zhuǎn)換到關(guān)系數(shù)據(jù)庫當(dāng)中。下面以行非勻質(zhì)的Excel表為例，針對(duì)表2的Excel表轉(zhuǎn)換結(jié)果如下：關(guān)系數(shù)據(jù)庫表結(jié)構(gòu)如圖1所示，關(guān)系數(shù)據(jù)庫表記錄如圖2所示。

5? ?結(jié)論（Conclusion）

在實(shí)際應(yīng)用當(dāng)中，非勻質(zhì)Excel表存在的原因有以下幾點(diǎn)：（1）數(shù)據(jù)的直觀性，表達(dá)方式比較直觀，便于使用者瞬間理解;（2）數(shù)據(jù)量不大，不需要擴(kuò)充的時(shí)候，通過VLOOK來加工查找數(shù)據(jù)方便;（3）數(shù)據(jù)的原始生成者，沒有想到數(shù)據(jù)擴(kuò)充的問題，因?yàn)镋xcel是按照文件來擴(kuò)充數(shù)據(jù)的。

非勻質(zhì)Excel表轉(zhuǎn)向關(guān)系數(shù)據(jù)庫的時(shí)候，不采用類似本文提出的轉(zhuǎn)換方法，如果都是行列對(duì)應(yīng)直接導(dǎo)入，導(dǎo)致數(shù)據(jù)庫不能擴(kuò)充，所作出來的軟件將會(huì)成為“僵尸”系統(tǒng)?？雌饋砗芎?，但無法擴(kuò)充。

將非勻質(zhì)Excel表分為行非勻質(zhì)和列非勻質(zhì)兩種類型，并提出結(jié)構(gòu)轉(zhuǎn)換和數(shù)據(jù)自動(dòng)導(dǎo)入的方法，并利用C#編程實(shí)現(xiàn)，成功應(yīng)用到PPP財(cái)務(wù)評(píng)價(jià)軟件系統(tǒng)的項(xiàng)目開發(fā)當(dāng)中。

參考文獻(xiàn)（References）

[1] 陳小龍，陳綺璟.基于C#.NET實(shí)現(xiàn)Excel數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫技術(shù)[J].計(jì)算機(jī)與網(wǎng)絡(luò)，2019，45（23）：46-47.

[2] 周曉俊.ASP.NET中Excel數(shù)據(jù)處理的技術(shù)實(shí)現(xiàn)[J].信息與電腦（理論版），2018（06）：113-115.

[3] 羅雅麗.關(guān)于大數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫的方法探究[J].電腦編程技巧與維護(hù)，2019（08）：111-113.

[4] 魏景東.將Excel表數(shù)據(jù)導(dǎo)入MS SQL Server數(shù)據(jù)庫表的一種有效方法[J].電腦編程技巧與維護(hù)，2013（07）：53-56.

[5] 羅麗云，段艷萍，簡碧園.ASP.NET中導(dǎo)入Excel數(shù)據(jù)到數(shù)據(jù)庫的應(yīng)用與實(shí)現(xiàn)[J].科技創(chuàng)新與應(yīng)用，2015（29）：89.

[6] 詹重詠.MySQL數(shù)據(jù)庫中數(shù)據(jù)導(dǎo)入與導(dǎo)出探析[J].數(shù)字技術(shù)與應(yīng)用，2017（12）：231;233.

[7] 陳道遠(yuǎn)，孫兆輝.基于XML配置的Excel通用導(dǎo)入組件設(shè)計(jì)與應(yīng)用[J].電腦編程技巧與維護(hù)，2019（08）：99-100.

[8] 武彤，陸昱霖.基于XML映射模板實(shí)現(xiàn)不規(guī)則Excel數(shù)據(jù)的轉(zhuǎn)換[J].計(jì)算機(jī)技術(shù)與發(fā)展，2015，25（07）：209-212.

[9] 張琦.基于.Net技術(shù)實(shí)現(xiàn)Excel數(shù)據(jù)抽取及批量入庫[J].電腦編程技巧與維護(hù)，2018（09）：85-88.