C#学习笔记 ODBC 操作Excel

1，ODBC->开放数据库连接（Open Database Connectivity，ODBC）是微软公司开放服务结构（WOSA，Windows Open Services Architecture）中有关数据库的一个组成部分，它建立了一组规范，并提供了一组对数据库访问的标准API（应用程序编程接口）

2，检查ODBC-Excel驱动程序的安装：控制面板->管理工具->数据源(ODBC)->驱动程序
（类似Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)）
注：如果找不到Excel的驱动程序->根据对应位数的系统安装对应位数的Office(建议安装Office2007以上的版本)，会自动添加Excel的驱动程序。

3,在项目文件引用中添加Microsoft.Office.Interop.Excel.dll的引用（需安装Office）

using Microsoft.Office.Interop.Excel

4，创建Excel文件，这里我采用Microsoft.Office.Interop.Excel.dll中的方法来创建

object Nothing = System.Reflection.Missing.Value;
var app = new Microsoft.Office.Interop.Excel.Application();  //注意：将Microsoft.Office.Interop.Excel.dll的属性“嵌入互操作类型”改为false，否则会报错
app.Visible = false;  
Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Add(Nothing);  //工作薄
Microsoft.Office.Interop.Excel.Worksheet worksheet =(Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets[1];//工作表格
worksheet.Name = "Sheet1";//表格名称

worksheet.Cells[1, 1]="ID";//表格的列名
worksheet.Cells[1, 2]="Name";//表格的列名
worksheet.Cells[1, 3]="Age";//表格的列名

worksheet.SaveAs(filePath+fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);  //保存
workBook.Close(false, Type.Missing, Type.Missing);  //工作薄关闭
app.Quit(); //Excel程序退出

5，通过ODBC执行SQL语句来操作Excel（Select）

string strConn = @"Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};ReadOnly=False;Dbq=";//Driver驱动程序,ReadOnly=False必须有，否则插入数据会报错,Dbq
strConn += filePath + fileName;  //完整的文件路径+文件名（包括扩展名(类型)）
System.Data.Odbc.OdbcConnection odbcConn = new System.Data.Odbc.OdbcConnection(strConn);//ODBC连接
try
{
     odbcConn.Open();//打开连接
     string sSQL="select * form [Sheet1$]";//注意格式，SQL语句查询表格所有的数据
     System.Data.Odbc.OdbcCommand comm = new System.Data.Odbc.OdbcCommand(sSQL, odbcConn);//初始化ODBC命令
     comm.ExecuteNonQuery();//执行SQL语句
     if (comm != null)
     {
         comm.Dispose();
     }      
     if (odbcConn != null)
     {
         odbcConn.Close();
     }
 }
 catch (Exception excp)
 {
     if (odbcConn != null)
     {
         odbcConn.Close();
     }
 }

6，通过ODBC执行SQL语句来操作Excel（Insert）

  string strConn = @"Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};ReadOnly=False;Dbq=";
strConn += filePath + fileName;                                                        
System.Data.Odbc.OdbcConnection odbcConn = new System.Data.Odbc.OdbcConnection(strConn);//建立连接
try
{
odbcConn.Open();//连接打开
string strSql = "insert into [Sheet1$] （A,B,C）Values('F','T','Q')"; 
//SQL插入语句1: insert into 表格名称（列名1，列名2，列名3，...）Values（‘列值1’,'列值2','列值3',...）
System.Data.Odbc.OdbcCommand comm = new System.Data.Odbc.OdbcCommand(strSql, odbcConn);
comm.ExecuteNonQuery();
if (comm != null)
{
    comm.Dispose();
}
if (odbcConn != null)
{
    odbcConn.Close();
}
}
catch (Exception excp)
{
if (odbcConn != null)
{
    odbcConn.Close();
}
}

7，通过ODBC执行SQL语句来操作Excel（Update）

System.Data.DataTable dtExcel = new System.Data.DataTable();//表格
string strConn = @"Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Dbq=";
strConn += filePath + fileName + ";";       

System.Data.Odbc.OdbcConnection odbcConn = new System.Data.Odbc.OdbcConnection(strConn);
try
{
    odbcConn.Open();
    string strSql = "update [Sheet1$] set A='FF',B='TT' where A='F' And B='T'";
    //SQL更新语句1：update 表格名 set 字段=值，字段=值，.... where 条件
    System.Data.Odbc.OdbcCommand comm = new System.Data.Odbc.OdbcCommand(strSql, odbcConn);
    comm.ExecuteNonQuery();
    if (odbcConn != null)
    {
        odbcConn.Close();
    }
}
catch (Exception excp)
{
    if (odbcConn != null)
    {
        odbcConn.Close();
    }
}

异常1：找不到数据源或者没有指定默认驱动程序:
解决办法：
1）检查ODBC驱动程序是否有Excel的驱动（安装Office，注意Office的安装位数和操作系统的位数对应，x86安装x86，x64安装x64）

2）检查项目文件的属性->生成->目标平台->x86/x64(对应系统的位数)

3）检查连接语句是否正确

4）异常信息:ERROR [HY000] [Microsoft][ODBC Excel Driver] 操作必须使用一个可更新的查询。
ODBC连接字符串Driver={Microsoft Excel Driver (.xls, .xlsx, .xlsm, .xlsb)};Dbq=C:\Users\Administrator\Desktop\2.xlsx;HDR=yes;IMEX=1
ODBC操作字符串insert into [Sheet1$] (F1,F2,F3) values (9,45,4)

解决办法：
将ODBC连接字符串追加一个选项:ReadOnly=False
ODBC连接字符串Driver={Microsoft Excel Driver (.xls, .xlsx, .xlsm, .xlsb)};ReadOnly=False;Dbq=C:\Users\Administrator\Desktop\2.xlsx;HDR=yes;IMEX=1

5）异常信息:ERROR [HY000] [Microsoft][ODBC Excel Driver] 外部表不是预期的格式。
ERROR [01S00] [Microsoft][ODBC Excel Driver]非法的连接串属性 HDR
ERROR [01S00] [Microsoft][ODBC Excel Driver]非法的连接串属性 IMEX
…….
ERROR [HY000] [Microsoft][ODBC Excel Driver] 外部表不是预期的格式。
ODBC连接字符串Driver={Microsoft Excel Driver (.xls, .xlsx, .xlsm, .xlsb)};ReadOnly=False;Dbq=C:\Users\Administrator\Desktop\2.xlsx;HDR=yes;IMEX=2
ODBC操作字符串insert into [Sheet1$] (F1,F2,F3) values (10,14,14)

解决办法：
将ODBC连接字符串修改选项:IMEX=1