利用ADO.NET将SQL表导出到Excel中

  #region SQL数据库导入到Excel表中(运用ADO.NET)
  /// <summary>
  /// 单表导入Excel单页中
  /// </summary>
  /// <param name="dtname">表名</param>
  /// <param name="index"></param>
  public static void ExportDataByQueryTableA(string dtname,int index,Label msg)
  {
   long totalCount=0;
   long rowRead=0;
   float percent=0;
   DataTable dt=new DataTable ();
   string cmdStr=String.Format ("select * from {0}",dtname);
   string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath +";Extended Properties=Excel 8.0;";
   OleDbConnection objConn = new OleDbConnection(connString);
   OleDbCommand objCmd = new OleDbCommand();
   objCmd.Connection = objConn;
   objConn.Open();
   if(dtname.Equals ("Tran_list1"))//将表[Tran_list1]记录导出
   {
    cmdStr = String.Format ("select Tbname,Fldname from {0}",dtname);
    dt=dataTrans.GetDataBySQL (cmdStr);
    totalCount=dt.Rows .Count ;
    OleDbParameter[] parm=new OleDbParameter[2];
    objCmd.CommandText = @"CREATE TABLE Sheet1(Tbname varchar,Fldname varchar)";
    objCmd.ExecuteNonQuery();
    objCmd.CommandText = "INSERT INTO Sheet1(Tbname,Fldname) VALUES(@aa,@bb)";
    parm[0]=new OleDbParameter("@aa", OleDbType.VarChar);
    objCmd.Parameters.Add(parm[0]);
    parm[1]=new OleDbParameter("@bb", OleDbType.VarChar);
    objCmd.Parameters.Add(parm[1]);
    
    //遍历DataTable将数据插入新建的Excel文件中
    for(int i=0;i<dt.Rows.Count;i++)
    { 
     for(int j=0;j<parm.Length;j++)
     {
      if(dt.Rows [i][j]!=null)
      {
       parm[j].Value =dt.Rows[i][j];
      }
     }
     objCmd.ExecuteNonQuery();
    }
   }
   
   else
   {
    dt=dataTrans.GetDataBySQL (cmdStr);
    totalCount=dt.Rows .Count ;
    string [] val=dtname.Split(new char []{'_'});
    OleDbParameter[] parm=new OleDbParameter[3];
    //建立表结构
    objCmd.CommandText = String.Format ("CREATE TABLE {0}({1} text,{2}_en text,myid Integer)" ,val[1]+"_"+val[2],val[2],val[2]);
    objCmd.ExecuteNonQuery();
    //建立插入动作的Command
    objCmd.CommandText =String.Format ( "INSERT INTO {0}({1},{2}_en,myid) VALUES(@aa,@bb,@cc)",val[1]+"_"+val[2],val[2],val[2]);
    parm[0]=new OleDbParameter("@aa", OleDbType.LongVarChar);
    objCmd.Parameters.Add(parm[0]);
    parm[1]=new OleDbParameter("@bb", OleDbType.LongVarChar);
    objCmd.Parameters.Add(parm[1]);
    parm[2]=new OleDbParameter("@cc", OleDbType.Integer);
    objCmd.Parameters.Add(parm[2]);
    //遍历DataTable将数据插入新建的Excel文件中
    for(int i=0;i<dt.Rows.Count;i++)
    { 
     for(int j=0;j<parm.Length;j++)
     {
      if(dt.Rows [i][j]!=null)
      {
       parm[j].Value =dt.Rows[i][j];
      }
     }
     try
     {
      objCmd.ExecuteNonQuery();
      rowRead++;
      percent=((float)(100*rowRead))/totalCount;
      msg.Text =String.Format ("正在导出第{0}个表的数据，已导出[{1}%]...",index,percent.ToString("0.00"));
      msg.Refresh ();
      
     }
     catch(Exception ex)
     {
      Log.StrFileName ="Sql导出数据到Excel";
      Log.StrDepartment ="ADO.NET导出数据到Excel";
      Log.StrDescription =ex.ToString ();
      Log.WriteLog ();
      continue;
     }
    }
   }
   objConn.Close();

  }

  /// <summary>
  /// 多表导出到Excel中
  /// </summary>
  /// <param name="dt"></param>
  public static void ExportDataByQueryTableA(DataTable dt,Label msg)
  {
   //----------------------------datatabale排序,使得第一个表单是索引表---------------------
   DataTable newdt = new DataTable(); 
   newdt=dt.Clone();
   DataRow[] dr1=dt.Select ("[name]='Tran_list1'");
   DataRow[] drs=dt.Select ("[name]<>'Tran_list1'");
   for(int i=0;i<dr1.Length ;i++)
   {
    newdt.ImportRow((DataRow)dr1[i]);
   }
   for(int i=0;i<drs.Length;i++) 
   { 
    newdt.ImportRow((DataRow)drs[i]);
   } 
   //---------------------------------------------------------------------------------------
           
   for(int i=0;i<newdt.Rows .Count ;i++)
   {
    ExportDataByQueryTableA(newdt.Rows [i][0].ToString (),i,msg);
   }
  }

  public static void ExportToExcelA(Label msg)
  {
   ArrayList Tlist=new ArrayList ();
   StringBuilder sb=new StringBuilder  ();
   sb.Append("select ta.[name],tb.[rows] from [sysobjects] ta inner join [sysindexes] tb on ta.id = tb.id where (ta.[name] like '%NeedTran' or ta.[name]='Tran_list1') and ta.xtype='U' and tb.[rows]>0");
   DataTable dt=new DataTable ();
   dt=dataTrans.GetDataBySQL(sb.ToString ());
   ExportDataByQueryTableA(dt,msg);
   MessageBox.Show ("数据已经成功导出!");
   
   
  }
  #endregion

转载于:https://www.cnblogs.com/SysAlen/archive/2008/07/16/1244390.html