C# 将多张sql表格导出到Excel表中

private Excel.Application m_objExcel=null;
  private Excel.Range m_objRange=null;
  private Excel.Workbooks m_objBooks=null;
  private Excel._Workbook m_objBook=null;
  private Excel.Sheets m_objSheets=null;
  private Excel._Worksheet m_objSheet=null;
  private Excel.QueryTable m_objQryTable = null;
  private object m_objOpt = System.Reflection.Missing.Value;
  private double dbSheetSize = 65535;//the hight limit number in one sheet


public void DeclareExcelApp()
  {
   m_objExcel = new Excel.ApplicationClass();
   m_objBooks = (Excel.Workbooks) m_objExcel.Workbooks;
   m_objBook = (Excel._Workbook) (m_objBooks.Add(m_objOpt));
   m_objSheets = (Excel.Sheets) m_objBook.Worksheets;
   ArrayList Tlist=new ArrayList ();
   StringBuilder sb=new StringBuilder  ();
   sb.Append("select name from sysobjects where name like '%NeedTran' and xtype='U'");
   DataTable dt=new DataTable ();
   dt=GetDataBySQL(sb.ToString ());
   ExportDataByQueryTable(dt);
   
  }

  private void ExportDataByQueryTable(DataTable dt)
  {
   
   
   if(dt.Rows .Count <=3)
   {
    for(int i=0;i<dt.Rows .Count  ;i++)
    {  
     string dtname=dt.Rows [i][0].ToString ();
     ExportDataByQueryTable(dtname,i+1);
    }
   }
   for (int i = 3; i < dt.Rows .Count; i++)
   {
    m_objSheets.Add(m_objOpt, m_objSheets.get_Item(i), m_objOpt, m_objOpt);
   }
   ExportDataByQueryTable(dt.Rows[0][0].ToString (), 1);
   for (int i = 2; i <= m_objSheets.Count; i++)
   {
    ExportDataByQueryTable(dt.Rows[i-1][0].ToString (),i);
   }
  
 }

  public void ExportDataByQueryTable(string dtname,int i)
  {
   string strCon=String.Format ("Data Source='{0}';Password = {1};User ID={2};Initial Catalog={3}",LocaServer,LocaLogPass,LocaLogName,LocaDataBase);
   DataTable recordsdt=new DataTable() ;
   StringBuilder sb=new StringBuilder ();
   sb.AppendFormat ("select count(0) from dbo.{0}",dtname);
   recordsdt=GetDataBySQL(sb.ToString() );
   int totalRecords=Convert.ToInt32 (recordsdt.Rows [0][0]);
   string strQuery=String.Format ("select * from {0}",dtname);
   string [] val=dtname.Split(new char []{'_'});
    
   m_objSheet = (Excel._Worksheet)m_objSheets[i];
   m_objSheet.Cells[1, 1] = val[2];
   m_objSheet.Cells[1, 2] = val[2]+"_en";
   m_objSheet.Cells[1, 3] = "myid";  
  
    
   if(totalRecords < dbSheetSize)
   {
    if(dtname.Length <31)
    {
     m_objSheet.Name =dtname;
    }
    else
    {
     m_objSheet.Name =val[2]+"NeedTran" ;
    }
    m_objRange = m_objSheet.get_Range("A2", m_objOpt);
    m_objQryTable = m_objSheet.QueryTables.Add("OLEDB;Provider=SQLOLEDB.1;" + strCon, m_objRange, strQuery);
    m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
    m_objQryTable.FieldNames = false;
    m_objQryTable.Refresh(true);
   }
   else
   {
    //提示
   }
  }

 public void SaveExcelApp()
{
 string excelFileName=string.Empty ;
 SaveFileDialog dlog=new SaveFileDialog ();
 dlog.Filter ="Excel 文件(*.xls)|*.xls|All files(*.*)|*.*";
 dlog.AddExtension =true;
 dlog.FilterIndex =1;
 dlog.RestoreDirectory =true;
 if(dlog.ShowDialog ()==DialogResult.OK )
    {
 excelFileName = dlog.FileName;
    }
 else
    {
 return;
    }
  
  try
  {
   m_objBook.SaveAs(excelFileName, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,Excel.XlSaveAsAccessMode.xlNoChange,m_objOpt, m_objOpt, m_objOpt, m_objOpt);
   MessageBox.Show ("导出成功！");
  }
  catch
  {
   MessageBox.Show ("保存出错！");
  }
  finally
  {
   //退出Excel进程
   m_objExcel.Quit ();
   m_objRange=null;
   m_objBooks=null;
   m_objBook=null;
   m_objSheets=null;
   m_objSheet=null;
   
  }

}