将查询结果导出到客户端的EXCEL中（二）

第二种方式是采用OLEDB的数据库连接引擎连接Excel，并将获取的数据循环插入到Excel中。

先建立好Excel模版，设好列名等，获取想要导出的数据到DataSet或者DataTable中。然后逐行插入到Excel中，虽然效率不是很高，但2，3000条应该没什么问题。示例代码如下

public string ExportDataToExcel()
            {
                string sql=string.Empty;    //将要运行的SQL
                //得到文件
                string fileName=this.GetFile(strPath);

                DataSet ds=QueryDutyByConditions();
                //实例化操作Excel的类
                CommonGetDataFromExcel getDataFromExcel=new CommonGetDataFromExcel(fileName,false);
                try
                {
                    //打开链接
                    getDataFromExcel.Open();        
        
                    foreach(DataRow dr in ds.Tables[0].Rows)
                    {
                        sql=" INSERT INTO [SHEET1$](工号,姓名) "
                            +"VALUES('"+dr["STAFFNUMBER"]+"','"+dr["NAME"]+"')";
                        //执行插入数据动作
                        getDataFromExcel.ExecuteNoquery(sql);
                    }
                    //返回文件路径名
                    return(fileName);
                }
                catch(Exception ex)
                {
                    throw new DCMSqlException(ex);
                }
                finally
                {    
                    //关闭连接
                    getDataFromExcel.Close();
                }
            }

其中CommonGetDataFromExcel是我写的一个对Excel进行操作的类，写得比较简单也很烂，很多东西没深入考虑，也列在下面吧，哈哈.

using System;
using System.Data;
using System.Data.OleDb;

namespace BenQGuru.eSCM.ePromoter.Commons
{
    /**//// <summary>
    /// 从Excel中获取数据,并以DataSet的方式返回给调用者
    /// Author:Farseer Wang
    /// Date:2005.01.22
    /// </summary>
    public class CommonGetDataFromExcel
    {
        private System.Data.OleDb.OleDbConnection excelConnection; //excelConnection连接
        private string excelConnectionString;//连接字符串
        private System.Data.OleDb.OleDbDataAdapter excelAdapter; //执行SQL
        private System.Data.OleDb.OleDbCommand excelCommand;    //执行SQL
        private DataSet dataSet;//数据集

        /**//// <summary>
        /// 构造函数,实例化的时候创建一个excelConnection对象
        /// </summary>
        /// <param name="strFilePath">Excel的存放路径</param>
        /// <param name="isQuery">是否对Excel做只读操作</param>
        public CommonGetDataFromExcel(string strFilePath,bool isQuery)
        {
            //连接字符串
                //查询时
            if(isQuery)
            {
                excelConnectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source= "+strFilePath+"; Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
            }
                //插入更新时
            else
            {
                excelConnectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source= "+strFilePath+"; Extended Properties=Excel 8.0";
            }
            //打开连接
            excelConnection=new OleDbConnection(excelConnectionString);
        }

        /**//// <summary>
        /// 打开Excel数据库连接
        /// </summary>
        public void Open()
        {
            //如果当前对象存在
            if(excelConnection!=null)
            {
                excelConnection.Open();
            }
        }

        /**//// <summary>
        /// 关闭Excel数据库连接
        /// </summary>
        public void Close()
        {
            //如果当前对象存在
            if(excelConnection!=null)
            {
                excelConnection.Close();
            }
            
        }
        /**//// <summary>
        /// 以DataSet的方式返回Excel中的数据
        /// </summary>
        /// <param name="sql">查询的SQL</param>
        /// <returns>返回结果集</returns>
        public DataSet ExcuteSqlForDst(string sql)
        {
            try
            {
                //实例化Adapter类
                excelAdapter=new OleDbDataAdapter(sql,excelConnection);
                //数据集
                dataSet=new DataSet();

                //填充dataSet
                excelAdapter.Fill(dataSet);

                //返回dataSet
                return(dataSet);
            }
            catch
            {
                throw new Exception("查询失败!");
            }    
        }

        /**//// <summary>
        /// 通过传递参数的方式,执行Sql语句
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="SqlParameter">参数</param>
        /// <param name="SqlParameterValue">参数值</param>
        /// <returns></returns>
        public DataSet ExcuteParameterSqlForDst(string sql,string[] SqlParameter,object[] SqlParameterValue)
        {
            
            DataSet ds=new DataSet();
            excelAdapter=new OleDbDataAdapter();
            if(SqlParameter.Length==SqlParameterValue.Length)
            {
                excelCommand=new OleDbCommand();
                //对于每一个参数,直接匹配
                for(int i=0;i<SqlParameter.Length;i++)
                {
                    OleDbParameter para=new OleDbParameter();
                    excelCommand.Connection=excelConnection;//数据库连接
                    para.ParameterName=SqlParameter[i];    //参数名
                    para.Value=SqlParameterValue[i];    //参数值
                    excelCommand.Parameters.Add(para);    //增加参数
                }
                try
                {
                    excelAdapter.SelectCommand=excelCommand;
                    excelAdapter.Fill(ds);
                    return(ds);
                }
                catch
                {
                    throw new Exception("获取数据出错!");
                }
            }
            else
            {
                throw new Exception("给定的参数和参数值不匹配！");
            }

        }

        /**//// <summary>
        /// 执行不需要返回值的SQL语句,比如插入,删除操作
        /// 如不能正确执行,回滚操作,并抛出失败异常
        /// 如果回滚失败,抛出回滚失败异常
        /// </summary>
        /// <param name="sql">待执行的SQL</param>
        public void ExecuteNoquery(string sql)
        {
            try
            {
                //实例化Adapter类
                excelCommand=new OleDbCommand(sql,excelConnection);
                excelCommand.Transaction=excelConnection.BeginTransaction();
                excelCommand.ExecuteNonQuery();
                try
                {
                    excelCommand.Transaction.Commit();

                }
                catch
                {
                    try
                    {
                        excelCommand.Transaction.Rollback();
                    }
                    catch
                    {
                        throw new Exception("数据库事务回滚失败！");
                    }
                }
                
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
    }
}

至于这个对读Excel和写Excel为什么要用不同的连接字符串，将专门写一篇Blog来论述此事。