问题简述:对于数据稀疏的 Excel 文件,在用 ADO 链接获取数据时,如果数据都出现在第九行以后,会导致该列数据都读取不到。
问题分析:
1. Excel 约定
ODBC 在读取 Excel 时,会有个值设置 TypeGuessRows=8 ,指定 Excel 给出的列字段类型是基于前八行数据的。
另外, Excel 默认 Sheet 的名称即为表名,表名在引用的时候需要写作 [Sheet1$] ,其中, Sheet1 即为表名,可以替换为任意的 Sheet 名称。另外还有一种指定表名的方法,鼠标选定表区域,菜单:插入 - 名称 - 定义。
2. 问题解决
在用 ADO 读取 Excel 文件数据,常容易发生丢失数据的现象。举例来说,这可能发生在第一行是数字,第二行是文字的情况。因为在读取 excel 的时候, ODBC 会根据第一行的文字来设定返回的 recordset 的字段类型,当第一行是数字时,就会认为整列都是数字的。结果后面的字符无法读出。
这时,需要把连接字符串改为
sSql . Format ( "ODBC;DRIVER={%s};IMEX=1;READONLY=FALSE;DSN='''';DBQ=%s" , sDriver , sExcelFile );
其中, IMEX=1; 这个属性的意思是,以文本方式读取 excel 表。 那么就算第一行是数字,后面的字符串也可以正常读出了。
另外,如果某个字段文本长度很长的时候,读取到的数值也可能发生错误。那是因为, ODBC 在读取 Excel 时,会有个值设置 TypeGuessRows=8 , 意思是 ODBC 会先在前 8 行的数据中去比较,如果所有的数据都在 255 或以下,那这个字段长度就是 varchar(255), 否则,这个字段将是 text 类型。但是,我们加上 TypeGuessRow=100 ,再试,结果错误依然存在。 原来,虽然可以设置这个属性,但 ODBC 是不会去用它的,要使这个属性有效,必须修改注册表。对于注册表键是
• Excel 97
HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/3.5/Engines/Excel
• Excel 2000 and later versions
HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/4.0/Engines/Excel
修改了里面的 TypeGuessRow 后,这个问题暂时解决了。但是,如果大的数据在表的后面位置的话,这个问题还是有可能发生的。所以要彻底的解决的话,在打开一个 recordset 前,就要先定义好它的字段长度了。
3. 编程
CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)" ; // Excel 安装驱动
CString sExcelFile = "c://demo.xls" ; // Excel 文件
CString sSql ;
sSql . Format ( "ODBC;DRIVER={%s};IMEX=1;READONLY=FALSE;DSN='''';DBQ=%s" ,
sDriver , sExcelFile );
// 打开 Excel 文件, ODBC 读取数据
CDatabase db ;
db . Open ( NULL , false , false , sSql , false );
//db.ExecuteSQL("CREATE TABLE demo (Name TEXT,Age NUMBER)");
CRecordset rs ( & db );
rs . Open ( CRecordset :: forwardOnly ,
_T ( "SELECT * FROM [Sheet1$]" ) ); // 注意这里的写法, Excel 默认 Sheet 的名称即为表名
// Create a CDBVariant object to tore field data
CString varValue ;
// Loop through the recordset,
short nFields = rs . GetODBCFieldCount ( );
while ( ! rs . IsEOF ( ) )
{
for ( short index = 0; index < nFields ; index ++ )
{
// 在这里如果某个字符串字段的长度大于 255, 就只能获得前 255 个字符
rs . GetFieldValue ( index , varValue );
if ( varValue != "" )
TRACE ( "/t= %s =" , varValue );
}
rs . MoveNext ( );
}
rs . Close ( );
db . Close ( );
4. 其它
读写注册表
void RegEdit ()
{
HKEY hKey ;
//modify TypeGuessRow
//for excel 2000 or later version
if ( RegOpenKey ( HKEY_LOCAL_MACHINE , "Software//Microsoft//Jet//4.0//Engines//Excel" ,
& hKey ) == ERROR_FILE_NOT_FOUND )
{
//97 version
RegOpenKey ( HKEY_LOCAL_MACHINE , "Software//Microsoft//Jet//3.5//Engines//Excel" ,
& hKey );
}
BYTE wordVal [ sizeof ( DWORD )];
unsigned long size = sizeof ( DWORD );
RegQueryValueEx ( hKey , "TypeGuessRows" , NULL , NULL , wordVal , & size );
DWORD dGuessRow = *( DWORD *) wordVal ;
if (( int ) dGuessRow < 10)
{
dGuessRow = 600;
*( DWORD *) wordVal = dGuessRow ;
RegSetValueEx ( hKey , "TypeGuessRows" , NULL , REG_DWORD , wordVal , sizeof ( DWORD ));
}
}