Excel数据提取问题

问题简述：对于数据稀疏的 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 ));

         }

}