解决Oledb连接Excel数据类型不统一的问题

在使用Microsoft.Jet.OLEDB.4.0连接Excel，进行读取数据，相对使用传统的COM来读取数据，效率是很高的。但相对传统COM操作Excel来说，及存在数据类型转换的问题。

因为使用OLEDB连接Excel读取数据时，需要确定数据的类型。默认情况使用连接字符串：

1. string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties='Excel 8.0;'";

使用上面的连接字符串连接Excel时，可能会遇到数据类型不一致的问题。所谓数据类型不一致，是指同一列里面数据类型可能出现多种，如浮点数、字符串、日期等；当出现此类情况时，读取出来的数据就为空，甚至会报错，如“非法的日期格式”等异常。出现这种问题，我们大家都会想到把数据全部都按字符数据来读取，但是按什么数据类型来读取不是我们能控制的，是OLEDB控制的，至少暂时我还没有找到能控制输出数据类型的方法。因为我当初也尝试使用convert，cast函数对输出的列进行类型转换，但oledb连接Excel时，使用的SQL不支持这些函数。因此只能从其他角度来解决该问题。我也在网上搜索了很多解决方法，最全面的解决方法是：http://www.douban.com/note/18510346/。下面列出了网上出现解决该问题方法的比较：

这里提供一个更加方便的办法，不过前提是第一行必须是作为字段名或者第一行的数据类型就为字符型。这样一说，大家就明白了。首先修改连接字符串为：

/2003（Microsoft.Jet.Oledb.4.0） 
string strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", excelFilePath); 
 
//2010（Microsoft.ACE.OLEDB.12.0） 
string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", excelFilePath);

这里将HDR设为NO，因为我就是将第一行做为数据读取，而IMEX=1就表示根据前8行判断列的数据类型，如果有字符型数据，那么就强制混合数据转换为 文本。这里就明白为什么要保证第一行为字符型的原因了。能将列的数据类型强制设为字符型，那么列中出现什么类型的数据都不怕了。需要做的工作就是，在获取 完数据后，将字段名重新设置，并删除第一条记录即可。代码如

view source 
print ? 
01. DataTable dt = new DataTable(); 
02.    
03. using(OleDbCommand cmd = new OleDbCommand()){ 
04.     cmd.Connection = conn; 
05.     cmd.CommandType = CommandType.Text; 
06.     cmd.CommandTimeout = 6; 
07.     cmd.CommandText = string.Format("select * from [{0}$]", sheetName); 
08.    
09.     OleDbDataAdapter adapter = new OleDbDataAdapter(cmd); 
10.     adapter.Fill(dt); 
11. } 
12.    
13. if (dt.Rows.Count > 0) { 
14.     DataRow dr = dt.Rows[0]; 
15.    
16.     for (int col = 0; col < dt.Columns.Count; col++) { 
17.         dt.Columns[col].ColumnName = dr[col].ToString(); 
18.     } 
19.    
20.     dt.Rows[0].Delete(); 
21.     dt.AcceptChanges(); 
22. } 

自己项目中使用

  public static DataSet GetDataSetFromExcelFileWhere(string file, string where, params string[] sheetNames)
        {
            if (!System.IO.File.Exists(file))
                throw new System.IO.IOException(string.Format("文件:{0}不存在!", file));
            DataSet ds = null;
            string strConn = "";
            // 读取excel2003
            strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};Extended Properties=Excel 8.0;", file);
            //2007
            //strConn = string.Format("Provider=Microsoft.Jet.OLEDB.12.0; Data Source={0};Extended Properties=Excel 12.0;HDR=yes;IMEX=1;", file);
            //strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file +"; Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
            try
            {
                conn.Open();
                string strExcel = "";
                System.Data.OleDb.OleDbDataAdapter myCommand = null;
                ds = new DataSet();
                if (sheetNames != null)
                {
                    foreach (string sheetName in sheetNames)
                    {
                       

                        strExcel = string.Format("select * from [{0}$] ", sheetName);
                        myCommand = new System.Data.OleDb.OleDbDataAdapter(strExcel, conn);
                        myCommand.Fill(ds, sheetName);
                    }
                }
                else
                {
                    System.Data.DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    foreach (DataRow dr in dt.Rows)
                    {
                        string sheetName = Convert.ToString(dr["TABLE_NAME"]);

                        strExcel = string.Format("select * from [{0}] ", sheetName);
                        myCommand = new System.Data.OleDb.OleDbDataAdapter(strExcel, conn);
                        myCommand.Fill(ds, sheetName);
                    }
                }
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return ds;
        }