前段時(shí)間，受命寫(xiě)出一個(gè)把客戶(hù)提交的的電子數(shù)據(jù)資料(大部份是excel,也可以是word,access等)轉(zhuǎn)成標(biāo)準(zhǔn)的SQL語(yǔ)句。以初始化數(shù)據(jù)庫(kù)，保護(hù)客戶(hù)現(xiàn)有的資料，減輕客戶(hù)的負(fù)擔(dān)。原來(lái)也有過(guò)這樣的轉(zhuǎn)化工作，本想拿來(lái)就用，具體看了之后才發(fā)現(xiàn)不可能，無(wú)奈之際只有自己實(shí)現(xiàn)一個(gè)這樣的導(dǎo)入設(shè)計(jì)，目標(biāo)是減輕以后的導(dǎo)入工作。
針對(duì)客戶(hù)提交資料的不規(guī)范性(這里我指的規(guī)范是從程序員的角度來(lái)看),那么我們要做的一個(gè)工作就是在原來(lái)資料的基礎(chǔ)上增加開(kāi)發(fā)的一些規(guī)范。比如說(shuō)客戶(hù)提交了一張個(gè)人的信息表(如下：第二行是我后來(lái)加上去),如果單看用戶(hù)提交的表那么要轉(zhuǎn)成SQL語(yǔ)句，就會(huì)出現(xiàn)字段對(duì)應(yīng)的依懶，還有引用的依懶。而一旦出現(xiàn)了這樣的依懶就沒(méi)有通用性可言(這句話(huà)有點(diǎn)過(guò)了,但至少是通用性明顯減弱),為此我們可以對(duì)這張表進(jìn)行預(yù)處理加上一行指示與SQL中字段的對(duì)應(yīng)，這樣我們就擺脫了case "性別": return "Gender";break;這樣的依懶了。或許有人會(huì)對(duì)此持以不同的態(tài)度，說(shuō)我將程序中的這些CASE移至外層來(lái)處理，這個(gè)我接受，這一步是必不可少的，就看放在那里為優(yōu)了。設(shè)計(jì)就是充滿(mǎn)權(quán)衡的，如果沒(méi)有權(quán)衡那么這個(gè)設(shè)計(jì)肯定忽略了一個(gè)弱點(diǎn)。這樣放到外層，可以減少程序編譯，降低程序復(fù)雜性等。解決了字段的對(duì)應(yīng)關(guān)系，還有值的寫(xiě)法關(guān)系也要解決的，如字符串的和整型的值的insert語(yǔ)句時(shí)就不同，為些我加上了{(lán)int}來(lái)表示整型。default就是string了，當(dāng)然還可以有別的類(lèi)型。第三個(gè)要解決的問(wèn)題是引用，在我的數(shù)據(jù)庫(kù)設(shè)計(jì)中是基礎(chǔ)資料項(xiàng)及類(lèi)共用的是三張表，所以這里就會(huì)出現(xiàn)
Party:EntryItem(Party){int}，表時(shí)在這個(gè)Entryitem表中的項(xiàng)必須是party這種類(lèi)別。一般的不放在一張表的就寫(xiě)成如下的形式Congress:Congress{int}就好了，這樣就完成在外圍解決引用這個(gè)問(wèn)題。在上面我完成外部數(shù)據(jù)的無(wú)二義表示，對(duì)其它格式的文件都可以采用上似類(lèi)似做法來(lái)完成數(shù)據(jù)適應(yīng)過(guò)程。
| 姓名 |
性別 |
出生日期 |
民族 |
藉貫 |
黨派 |
學(xué)歷 |
邊界 |
是否歸僑 |
選區(qū) |
工作單位及職務(wù) |
職位 |
工作地址 |
電話(huà) |
工作地址郵編 |
家庭地址 |
家庭電話(huà) |
家庭郵編 |
聯(lián)系方式 |
可否交換 |
手機(jī) |
可否公布手機(jī) |
|
秘書(shū)姓名 |
秘書(shū)電話(huà) |
專(zhuān)業(yè)小組 |
是否選擇 |
代表屆期 |
是否常委 |
代表證號(hào) |
證號(hào)簡(jiǎn)碼 |
技能特長(zhǎng) |
任職類(lèi)別 |
描述 |
相片 |
| Name |
Gender:EntryItem(Gender){int} |
Birthday |
Nationality:EntryItem(Nationality){int} |
Nativeplace |
Party:EntryItem(Party){int} |
Education:EntryItem(Education){int} |
Boundary:EntryItem(Boundary){int} |
IsROC{int} |
ElectoralDistrict:EntryItem(ElectoralDistrict){int} |
Organization |
Position |
Address |
OfficeTelephone |
OfficePostCode |
HomeAddress |
HomeTelephone |
HomePostCode |
ContactType:EntryItem(ContactType){int} |
CanSwitch{int} |
Mobile |
CanPublicMobile{int} |
Email |
SecretaryName |
SecretaryTelephone |
SpecialGroup |
IsSelected{int} |
Congress:Congress{int} |
IsPermanent |
CertificateNo |
CertificateSimpleNo |
Technology |
ElectionType:EntryItem(ElectionType) |
Description |
Photo |
| AAA |
男 |
|
漢 |
|
共產(chǎn)黨 |
未知 |
未知 |
0 |
天沙 |
AAA公司 |
董事、總經(jīng)理 |
|
|
|
|
|
|
|
0 |
|
0 |
|
|
|
|
0 |
|
0 |
|
1 |
|
現(xiàn)任 |
|
|
由于客戶(hù)提供的電子數(shù)據(jù)多樣性，我就決定先生成XML然后由XML去生成SQL，這樣就可以獨(dú)立出XML生成SQL這一塊。到時(shí)用XML生成別的什么形式都是可以的。代碼如下：這里讀取excel我用了Syncfusion。生成后的XML應(yīng)是這樣的形式
<?xml version="1.0"?>
<table name="...">
<record id="1">
<property name="name" value="AAA"/>
......
</record>
.....
</table>
1
using System;
2using System.Collections.Generic;
3using System.Text;
4using System.Xml;
5using System.IO;
6using Syncfusion.XlsIO;
7
8namespace EntitiesTest.Xml
9
{
10
static class XmlConstraint
11
{
12 public const string TABLE = "table";
13 public const string RECORD = "record";
14 public const string PROPERTY = "property";
15 public const string NAME = "name";
16 public const string REFRENCE = "Refrence";
17 public const string RESTRICT = "Restrict";
18 public const string VALUE = "value";
19 public const string ID = "Id";
20 public const string TYPE = "Type";
21
22 //類(lèi)型
23 public const string INT = "int";
24 public const string STRING = "string";
25
}
26
27 class HeaderInformation
28 {
29 private string _HeaderName;
30 public string HeaderName
31 {
32 get { return _HeaderName; }
33 }
34
35 private string _ColumnRefrence;
36 public string ColumnRefrence
37 {
38 get { return _ColumnRefrence; }
39 }
40
41 private string _Restrict;
42 public string Restrict
43 {
44 get { return _Restrict; }
45 }
46
47 private string _DataType;
48 public string DataType
49 {
50 get { return _DataType; }
51 }
52
53 public HeaderInformation(string name)
54 :this(name,string.Empty)
55 {
56 }
57
58 public HeaderInformation(string name, string refrence)
59 : this(name,refrence,string.Empty)
60 {
61 }
62
63 public HeaderInformation(string name, string refrence, string restrict)
64 {
65 }
66 public HeaderInformation(string name, string refrence, string restrict,string strType)
67 {
68 _HeaderName = name;
69 _ColumnRefrence = refrence;
70 _Restrict = restrict;
71 _DataType = strType;
72 }
73 }
74
75 struct ColumnInformation
76 {
77 private string _Value;
78 private HeaderInformation _HeaderInformation;
79
80 public ColumnInformation(HeaderInformation headerInformation, string strValue)
81 {
82 _HeaderInformation = headerInformation;
83 _Value = strValue;
84 }
85
86 public void Convert(XmlWriter writer)
87 {
88 writer.WriteStartElement(XmlConstraint.PROPERTY);
89 writer.WriteAttributeString(XmlConstraint.NAME, _HeaderInformation.HeaderName);
90 writer.WriteAttributeString(XmlConstraint.VALUE, _Value);
91 writer.WriteAttributeString(XmlConstraint.TYPE, _HeaderInformation.DataType);
92 if (!string.IsNullOrEmpty(_HeaderInformation.ColumnRefrence))
93 {
94 writer.WriteAttributeString(XmlConstraint.REFRENCE, _HeaderInformation.ColumnRefrence);
95 if (!string.IsNullOrEmpty(_HeaderInformation.Restrict))
96 {
97 writer.WriteAttributeString(XmlConstraint.RESTRICT, _HeaderInformation.Restrict);
98 }
99 }
100 writer.WriteEndElement();
101 }
102 }
103
104 class RowInformation
105 {
106 private int _Id;
107 private List<ColumnInformation> columnList = new List<ColumnInformation>();
108
109 public RowInformation(int Id)
110 {
111 _Id = Id;
112 }
113
114 public void AddColumn(ColumnInformation column)
115 {
116 columnList.Add(column);
117 }
118
119 public void Convert(XmlWriter writer)
120 {
121 writer.WriteStartElement(XmlConstraint.RECORD);
122 writer.WriteAttributeString(XmlConstraint.ID, _Id.ToString());
123 foreach (ColumnInformation column in columnList)
124 {
125 column.Convert(writer);
126 }
127 writer.WriteFullEndElement();
128 }
129 }
130
131 public class XlsTableXml
132 {
133 private IWorksheet _WorkSheet;
134 protected IWorksheet WorkSheet
135 {
136 get { return _WorkSheet; }
137 }
138
139 private int _RowTitle = 0;
140 public int RowTitle
141 {
142 get { return _RowTitle; }
143 set { _RowTitle = value; }
144 }
145
146 private int _Columns;
147 public int Columns
148 {
149 get { return _Columns; }
150 set { _Columns = value; }
151 }
152
153 private string _FilePath;
154 public string FilePath
155 {
156 get { return _FilePath; }
157 set { _FilePath = value; }
158 }
159
160 public XlsTableXml(IWorksheet sheet)
161 {
162 _WorkSheet = sheet;
163 }
164
165 private Dictionary<int, HeaderInformation> _HeaderDict = new Dictionary<int, HeaderInformation>();
166 public void ConvertToXml()
167 {
168 if (Columns == 0)
169 {
170 _Columns = 100;
171 }
172 if (string.IsNullOrEmpty(_FilePath))
173 {
174 throw new InvalidOperationException("沒(méi)有為路徑賦值");
175 }
176 string strFullPathName = _FilePath + "\\" + WorkSheet.Name + ".xml";
177 CreateXmlFile(strFullPathName);
178 //建立寫(xiě)對(duì)象
179 XmlWriterSettings setting = new XmlWriterSettings();
180 setting.Indent = true;
181 setting.OmitXmlDeclaration = false;
182 setting.NewLineOnAttributes = false;
183 XmlWriter writer = XmlWriter.Create(strFullPathName,setting);
184 CollectHeaderInformation();
185 int iCurrent=_RowTitle+1;
186 //寫(xiě)動(dòng)作
187 writer.WriteStartElement(XmlConstraint.TABLE);
188 writer.WriteAttributeString(XmlConstraint.NAME, WorkSheet.Name);
189 for (int iRow = iCurrent; iRow < WorkSheet.Rows.Length; ++iRow)
190 {
191 IRange row = WorkSheet.Rows[iRow];
192 RowInformation rowInfo = new RowInformation(iRow);
193 for (int iColumn = 0; iColumn < _HeaderDict.Count; ++iColumn)
194 {
195 string strValue = row.Cells[iColumn].Value.Trim();
196 if (!string.IsNullOrEmpty(strValue))
197 {
198 ColumnInformation column = new ColumnInformation(_HeaderDict[iColumn], strValue);
199 rowInfo.AddColumn(column);
200 }
201 }
202 rowInfo.Convert(writer);
203 }
204 writer.WriteFullEndElement();
205 writer.Flush();
206 }
207
208 private void CollectHeaderInformation()
209 {
210 for (int iColumn = 1; iColumn < Columns; ++iColumn)
211 {
212 string strFullValue = WorkSheet.GetText(RowTitle+1, iColumn);
213 if (string.IsNullOrEmpty(strFullValue))
214 {
215 return;
216 }
217 HeaderInformation headerInformation = null;
218 string strRefrence = string.Empty;
219 string strRestrict = string.Empty;
220 string strName = string.Empty;
221 string strType = Xml.XmlConstraint.STRING;
222 int iLeft = strFullValue.IndexOf('{');
223 int iRight = strFullValue.IndexOf('}');
224 if (iLeft > 0 && iLeft < iRight)
225 {
226 string temp = strFullValue.Substring(iLeft + 1, iRight - iLeft - 1);
227 if (temp == XmlConstraint.INT)
228 {
229 strType = XmlConstraint.INT;
230 }
231 strFullValue = strFullValue.Substring(0, iLeft);
232 }
233 int colon = strFullValue.IndexOf(':');
234 if (colon < 0)
235 {
236 strName=strFullValue.Trim();
237 }
238 else
239 {
240 strName = strFullValue.Substring(0, colon).Trim();
241 int leftBracket = strFullValue.IndexOf('(');
242 if (leftBracket < 0)
243 {
244 strRefrence = strFullValue.Substring(colon + 1);
245 }
246 else
247 {
248 int rightBracket = strFullValue.IndexOf(')');
249 strRefrence = strFullValue.Substring(colon + 1, leftBracket - colon-1).Trim();
250 strRestrict = strFullValue.Substring(leftBracket + 1, rightBracket - leftBracket-1).Trim();
251 }
252 }
253 _HeaderDict.Add(iColumn-1,new HeaderInformation(strName,strRefrence,strRestrict,strType));
254 }
255 }
256
257 private void CreateXmlFile(string strFullPathName)
258 {
259 if (!File.Exists(strFullPathName))
260 {
261 using (File.Open(strFullPathName, FileMode.Create))
262 {
263 }
264 }
265 else
266 {
267 File.SetAttributes(strFullPathName, FileAttributes.Archive);
268 }
269 }
270 }
271
}
272
1using System.Xml;
2using System.IO;
3using Syncfusion.XlsIO;
4
5namespace EntitiesTest.Xml
6{
7 public class Launch
8 {
9 private IWorkbook _Workbook;
10 public IWorkbook Workbook
11 {
12 get { return _Workbook; }
13 }
14
15 private List<string> _convertList = new List<string>();
16
17 public Launch(IWorkbook book)
18 {
19 _Workbook = book;
20 }
21
22 public void AddConvertName(string name)
23 {
24 if (!_convertList.Contains(name))
25 {
26 _convertList.Add(name);
27 }
28 }
29
30 public void Start(string strDictionary)
31 {
32 foreach (string name in _convertList)
33 {
34 IWorksheet sheet = _Workbook.Worksheets[name];
35 if (sheet == null)
36 {
37 throw new ArgumentOutOfRangeException(string.Format("{0}表沒(méi)有找到，出錯(cuò)", name));
38 }
39 XlsTableXml xlsTable = new XlsTableXml(sheet);
40 xlsTable.RowTitle = 1;
41 xlsTable.FilePath = strDictionary;
42 xlsTable.ConvertToXml();
43 }
44 }
45 }
46}
47