2011-07-27 15:43:50| 分類(lèi)：
編程專(zhuān)欄 | 標(biāo)簽：
excel 連接字符串 參數(shù) 含義 |字號(hào)大中小
程序中讀取Excel數(shù)據(jù)一般有兩種連接方式：ODBC和OLEDB；
ODBC連接字符串：
Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDir=c:\mypath;
解釋下 DriverId=790： 790是Microsoft Excel 97這個(gè)驅(qū)動(dòng)程序的整型標(biāo)志符；
其他 驅(qū)動(dòng)程序的整型標(biāo)識(shí)符：
25 (Microsoft Access)
21 (dBASE III)
277 (dBASE IV)
533 (dBASE 5.0)
534 (Microsoft Excel 3.0)
278 (Microsoft Excel 4.0)
22 (Microsoft Excel 5.0/7.0)
790 (Microsoft Excel 97)
26 (Paradox 3.x)
282 (Paradox 4.x)
538 (Paradox 5.x)
27 (Text)
OLEDB連接字符串：
Excel 97-2003都是用下面這個(gè)
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
Excel2007不再使用Jet，而是使用ACE，如下：
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyExcel.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";
重點(diǎn)提示！
雙引號(hào) " 需要在你的編程語(yǔ)言中進(jìn)行字符轉(zhuǎn)義處理
c#, c++ \"
VB6, VBScript ""
xml (web.config 等) "
或者使用 '
HDR=Yes，代表第一行是標(biāo)題，不做為數(shù)據(jù)使用 ，如果用HDR=NO，則表示第一行不是標(biāo)題，做為數(shù)據(jù)來(lái)使用。系統(tǒng)默認(rèn)的是YES
"IMEX=1;" 通知驅(qū)動(dòng)程序以文本格式讀取混合型的列（如數(shù)字、日期、字符串等）。需要注意的是，這個(gè)選項(xiàng)可能會(huì)導(dǎo)致Excel文件的寫(xiě)入拒絕
SQL 語(yǔ)法 "SELECT [列名1], [列名12] FROM [Sheet1$]". 即Excel工作表名后加上"$"，在"[ ]"內(nèi)加入列名
檢查本地注冊(cè)表鍵值 Excel97-2003：[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel]
Excel2007：[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel]
找到其中的 "TypeGuessRows"（REG_DWORD數(shù)據(jù)類(lèi)型）鍵。這個(gè)數(shù)值表示，決定ISAM驅(qū)動(dòng)程序從前n條數(shù)據(jù)采樣確定數(shù)據(jù)類(lèi)型，默認(rèn)為8，將這個(gè)值改成0，可以對(duì)所有行進(jìn)行采樣，不過(guò)性能開(kāi)銷(xiāo)很大。另請(qǐng)注意，有些時(shí)候，設(shè)置選項(xiàng)IMEX=1可能會(huì)導(dǎo)致IMEX驅(qū)動(dòng)程序只進(jìn)行8行采樣。用IMEX=0代替可以確定注冊(cè)表值TypeGuessRows=0起效
如果Excel設(shè)置了密碼保護(hù)，將不能打文件開(kāi)讀取數(shù)據(jù)，哪怕是在連接字符串中配置了正確的密碼，你會(huì)收到這樣的錯(cuò)誤信息 "Could not decrypt file"
說(shuō)一下 Extended Properties這幾個(gè)Key是指什么：
Excel8.0指Excel的驅(qū)動(dòng)版本，從Excel97到2003都用這個(gè)，Excel2007中開(kāi)始用Excel12.0；
HDR 有兩個(gè)可選的值：Yes或者No，默認(rèn)是Yes； 指是否需要列標(biāo)題，Yes表示第一行當(dāng)作列標(biāo)題來(lái)處理，No則把第一行當(dāng)作數(shù)據(jù)內(nèi)容來(lái)處理；一般我們用到的導(dǎo)入導(dǎo)出的Excel都是有標(biāo)題欄的，那就可以不寫(xiě)這個(gè)參數(shù)或者設(shè)為Yes。
IMEX有三個(gè)可選的值：0，1和2，默認(rèn)是0； 微軟是這樣解釋的：
0 is Export mode -------導(dǎo)出模式（從Excel文件讀取數(shù)據(jù)時(shí)使用）
1 is Import mode -------導(dǎo)入模式（向Excel文件插入數(shù)據(jù)時(shí)使用）
2 is Linked mode (full update capabilities) -----Linked模式（需要完全更新Excel文件時(shí)使用）
這個(gè)值具體有啥影響呢?
上傳的Excel數(shù)據(jù)中如果既有數(shù)字型，又有文本型，則上傳后讀到DataTable中，發(fā)現(xiàn)文本的單元格都是空（有時(shí)是第一列的數(shù)字型值為空）；嘗試用模板文件來(lái)限定數(shù)據(jù)類(lèi)型，根本不頂用！
使用Remark在Excel模板中標(biāo)注，這個(gè)只能提醒User，但也不能保證User輸入的結(jié)果??！
使用宏定義來(lái)強(qiáng)制轉(zhuǎn)化類(lèi)型,大多情況下，都是禁用宏的，客戶(hù)端的事，更是難以控制，所以這個(gè)方法用在這里也不是最好的~！
所以，還是把解決問(wèn)題的思路回歸到Server端： 解決讀不到混合格式的Excel數(shù)據(jù)的問(wèn)題；
關(guān)于Excel中的數(shù)據(jù)類(lèi)型缺省值，MS是這樣說(shuō)的：Excel 驅(qū)動(dòng)程序讀取指定源中一定數(shù)量的行（默認(rèn)情況下為 8 行）以推測(cè)每列的數(shù)據(jù)類(lèi)型。如果推測(cè)出列可能包含混合數(shù)據(jù)類(lèi)型（尤其是混合了文本數(shù)據(jù)的數(shù)值數(shù)據(jù)時(shí)），驅(qū)動(dòng)程序?qū)Q定采用占多數(shù)的數(shù)據(jù)類(lèi)型，并對(duì)包含其他類(lèi)型數(shù)據(jù)的單元返回空值。（如果各種數(shù)據(jù)類(lèi)型的數(shù)量相當(dāng)，則采用數(shù)值類(lèi)型。）Excel 工作表中大部分單元格格式設(shè)置選項(xiàng)不會(huì)影響此數(shù)據(jù)類(lèi)型判斷?？梢酝ㄟ^(guò)指定導(dǎo)入模式來(lái)修改 Excel 驅(qū)動(dòng)程序的此行為。
簡(jiǎn)單點(diǎn)，其實(shí)對(duì)我們有用就是這樣：IMEX=0的時(shí)候讀取Excel中的數(shù)據(jù)時(shí)，它還是根據(jù)上述的默認(rèn)8行規(guī)則來(lái)確定數(shù)據(jù)類(lèi)型； IMEX設(shè)為1時(shí)，將把所有單元格數(shù)據(jù)都作為文本類(lèi)型來(lái)處理； 當(dāng)設(shè)置IMEX為2時(shí)，用來(lái)更新Excel中的數(shù)據(jù)用；
問(wèn)題還沒(méi)有結(jié)束！！ 如果在前八行內(nèi)的數(shù)據(jù)類(lèi)型是數(shù)字型，且在第八行之后如果出現(xiàn)字符，則字符數(shù)據(jù)一樣的讀不到~！
這個(gè)問(wèn)題需要修改注冊(cè)表來(lái)解決：
將Server上注冊(cè)表中的HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\ 下的TypeGuessRows這個(gè)鍵值改為一個(gè)較大的數(shù)； 如果預(yù)估一次要上傳的資料數(shù)量最大為1000的話，那就設(shè)為一個(gè)比1000稍大的值即可；（注：也不要改太大，因?yàn)樘髸?huì)降低上傳Excel時(shí)的性能；這個(gè)值最大可為65535，）
原因：讀取Excel的Microsoft Jet4.0驅(qū)動(dòng)默認(rèn)按照前八行（標(biāo)題行除外）來(lái)判斷該列的數(shù)據(jù)類(lèi)型，之前有微軟有說(shuō)法是在讀取Excel的數(shù)據(jù)時(shí)，設(shè)置連接字符串的擴(kuò)展參數(shù)IMEX=1 就會(huì)把混合類(lèi)型的數(shù)據(jù)列當(dāng)作文本來(lái)處理，但實(shí)際上效果并不完全是這樣的； 即使設(shè)置了IMEX=1，或者把上傳的Excel的列數(shù)據(jù)格式都設(shè)置為文本型，Jet驅(qū)動(dòng)其實(shí)還是按照前八行來(lái)判斷數(shù)據(jù)類(lèi)型的，只有當(dāng)前八行數(shù)據(jù)中出現(xiàn)既有文本型又有數(shù)字型（也即前八行為混合型數(shù)據(jù)），并設(shè)置IMEX=1時(shí)，該列數(shù)據(jù)才會(huì)當(dāng)作文本來(lái)處理； 所以我們的解決辦法就是把MS Jet這個(gè)定義的“8”這個(gè)值改大，這樣當(dāng)在第八行以后出現(xiàn)字符時(shí)，Jet驅(qū)動(dòng)就會(huì)把這列值當(dāng)作文本來(lái)處理，這樣就能讀到值了。
如此一來(lái)，只要設(shè)置IMEX=1，則任何在設(shè)置的這個(gè)值范圍內(nèi)的行如果出現(xiàn)混合類(lèi)型的數(shù)據(jù)，都將會(huì)作為文本來(lái)處理，這樣就能保證Jet驅(qū)動(dòng)一定能讀到Excel中的值（不是混合類(lèi)型的列會(huì)不會(huì)讀不到？ 這點(diǎn)相信微軟的實(shí)力，對(duì)同一類(lèi)型的數(shù)據(jù)Jet是絕對(duì)能識(shí)別的；）
最后舉一個(gè)取Excel中值的SQL語(yǔ)句的例子，如：select 姓名,年齡 from [Sheet1$]，
"Sheet1"可換成你更改的當(dāng)前Sheet頁(yè)的名稱(chēng)，如:elect 姓名,年齡 from [學(xué)生名單$]
轉(zhuǎn)自：
http://blog.163.com/hold_fate/blog/static/311362872007103012152469/
