C#中操作Excel（6）

三、使用OLEDB技术操作Excel

        上文介绍了使用COM技术读写Excel的方法，接下来我们介绍下使用OLEDB技术读写Excel。

3.1 使用OLEDB读取Excel文件。

        要读取文件我们第一步要建立一个数据库连接字符串，连接指向我们的D: \\Test.xlsx文件。

bool hasTitle = false;
string path="D:\\Test.xlsx";
string fileType = System.IO.Path.GetExtension(path);
string strCon = string.Empty;
if (fileType == ".xls")
{
strCon = string.Format("Provider=Microsoft.Jet.OLEDB.{0}.0;" +
"Extended Properties=\"Excel {1}.0;HDR={2};IMEX=1;\";" +
"Data Source={3};",
(fileType == ".xls" ? 4 : 12), (fileType == ".xls" ? 8 : 12), (hasTitle ? "Yes" : "NO"), path);
}
else
{
strCon = string.Format("Provider=Microsoft.ACE.OLEDB.{0}.0;" +
"Extended Properties=\"Excel {1}.0;HDR={2};IMEX=1;\";" +
"Data Source={3};",
(fileType == ".xls" ? 4 : 12), (fileType == ".xls" ? 8 : 12), (hasTitle ? "Yes" : "NO"), path);
}

        OLEDB是类数据库的访问方式操作Excel，上述代码就是建立了一个OLEDB连接字符串，strCon就是进行数据库连接的字符串，根据Excel文件后缀的不同，连接的字符串也不同，这主要和Excel版本有关。
        然后，利用SQL语句查询Excel的数据，这里我们把一个sheet看成是数据库的一个表格，下面的代码获取名称为sheet1的所有数据：

string sheetName = "sheet1";
string strCom = " SELECT * FROM [" + sheetName + "$]";

        接下来，创建一个连接：

OleDbConnection myConn = new OleDbConnection(strCon);

        然后，创建一个sql语句的适配器：

OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);

        执行并获取数据：

System.Data.DataTable dt = new System.Data.DataTable();
myConn.Open();
myCommand.Fill(dt);

        如果不出意外，我们成功获取了该数据：

3.2 使用OLEDB创建Excel并写入数据

        接下来我们要创建一个Excel，名称就叫做D://Test2.xls。首先重新构建连接字符串并建立连接，这里面由于我们要建的文件版本已经很明确，是office97~2003版本的.xls文件，所以构建字符串时我们不再做版本的判断。

String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=D:/Test2.xls;" +
"Extended Properties=Excel 8.0;";
OleDbConnection cn = new OleDbConnection(sConnectionString);

        然后创建一个sheet，OLEDB中一个sheet就是一个表，所以创建sheet时我们要用sql中的建表语句：

string sqlCreate = "CREATE TABLE TestSheet ([姓?名?] VarChar,[成¨¦绩¡§] INTEGER)";

        紧接着创建命令对象用来执行建表和插数据的命令。值得注意的是OLEDB中插叙数据和写入数据的命令执行器是不一样的，查询数据中我们使用的是OleDbDataAdapter对象执行查询命令，而在建表、插入数据时我们用的是OleDbCommand对象来执行命令。

OleDbCommand cmd = new OleDbCommand(sqlCreate, cn);

        然后创建文件，我们已经建立了连接cn，只要打开了这个连接，就自动创建了Excel文件：

cn.Open();

        执行创建sheet的语句：

cmd.ExecuteNonQuery();

       然后循环添加数据：

for (int i = 1; i < dt.Rows.Count;i++ )
{
DataRow row = dt.Rows[i];
cmd.CommandText = "INSERT INTO TestSheet VALUES('" + row["F1"] + "'," + row["F2"] + ")";
cmd.ExecuteNonQuery();
}

最后，关闭连接。 

cn.Close();

如果不出意外，我们成功的将D://Test. xlsx文件中的内容写入到D://Test2. xls

本文完整源代码：

 /*定义连接字符串*/
            bool hasTitle = false;
            string path="D:\\Test.xlsx";
            string fileType = System.IO.Path.GetExtension(path);
            string strCon = string.Empty;
            if (fileType == ".xls")
            {
                strCon = string.Format("Provider=Microsoft.Jet.OLEDB.{0}.0;" +
                            "Extended Properties=\"Excel {1}.0;HDR={2};IMEX=1;\";" +
                            "Data Source={3};",
                            (fileType == ".xls" ? 4 : 12), (fileType == ".xls" ? 8 : 12), (hasTitle ? "Yes" : "NO"), path);
            }
            else
            {
                strCon = string.Format("Provider=Microsoft.ACE.OLEDB.{0}.0;" +
                            "Extended Properties=\"Excel {1}.0;HDR={2};IMEX=1;\";" +
                            "Data Source={3};",
                            (fileType == ".xls" ? 4 : 12), (fileType == ".xls" ? 8 : 12), (hasTitle ? "Yes" : "NO"), path);
            }

            /*使用SQL语句读取数据*/
            string sheetName = "sheet1";
            string strCom = " SELECT * FROM [" + sheetName + "$]";

            /*建立数据库连接*/
            OleDbConnection myConn = new OleDbConnection(strCon);

            /*建立sql语句执行器*/
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);

            /*执行并读取数据*/
            System.Data.DataTable dt = new System.Data.DataTable();
            myConn.Open();//打开连接
            myCommand.Fill(dt);//填充数据
            myConn.Close();//关闭连接

            /*将数据集里的数据写入到Excel*/
            //1.创建连接字符串

            String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +

     "Data Source=D:/Test2.xls;" +

     "Extended Properties=Excel 8.0;";

            OleDbConnection cn = new OleDbConnection(sConnectionString);

            string sqlCreate = "CREATE TABLE TestSheet ([姓名] VarChar,[成绩] INTEGER)";

            OleDbCommand cmd = new OleDbCommand(sqlCreate, cn);

            //创建Excel文件：D:/Test2.xls

            cn.Open();

            //创建TestSheet工作表

            cmd.ExecuteNonQuery();

            //添加数据
            for (int i = 1; i < dt.Rows.Count;i++ )
            {
                DataRow row = dt.Rows[i];
                cmd.CommandText = "INSERT INTO TestSheet VALUES('" + row["F1"] + "'," + row["F2"] + ")";
                cmd.ExecuteNonQuery();
            }

            //关闭连接
            cn.Close();

Github位置：
https://github.com/HymanLiuTS/OfficeTestByC-
克隆本项目：
git clone [e-mail]:HymanLiuTS/OfficeTestByC-.git
获取本文源代码：
git checkout L15