将DataSet里所有数据导入Excel

 

           /**/ ///   <summary>
         ///  将DataSet里所有数据导入Excel
         ///  需要添加COM: Microsoft Excel Object Library
         ///  using Excel;
         ///   </summary>
         ///   <param name="filePath"> 文件路径 </param>
         ///   <param name="ds"> 需要转存的DataSet </param>
         public   static   void  ExportToExcel( string  filePath, DataSet ds)
        {
             object  oMissing  =  System.Reflection.Missing.Value;
            Excel.ApplicationClass xlApp  =   new  Excel.ApplicationClass();
             try
            {
                 //  打开Excel文件。以下为Office 2000.
                Excel.Workbook xlWorkbook  =
                    xlApp.Workbooks.Open(filePath, oMissing, oMissing,
                                          oMissing, oMissing, oMissing,
                                          oMissing, oMissing, oMissing,
                                          oMissing, oMissing, oMissing,
                                          oMissing);
 
                Excel.Worksheet xlWorksheet;
                 //  循环所有DataTable
                 for (  int  i = 0 ; i < ds.Tables.Count; i ++  )
                {
                     //  添加入一个新的Sheet页
                    xlWorksheet  =  (Excel.Worksheet)xlWorkbook.Worksheets.Add(oMissing,oMissing, 1 ,oMissing);
 
                     //  以TableName作为新加的Sheet页名
                    xlWorksheet.Name  =  ds.Tables[i].TableName;
 
                     //  取出这个DataTable中的所有值,暂存于stringBuffer中
                    System.Text.StringBuilder stringBuffer  =   new  System.Text.StringBuilder();
 
                     for (  int  j = 0 ; j < ds.Tables[i].Rows.Count; j ++  )
                    {
                         for (  int  k = 0 ; k < ds.Tables[i].Columns.Count; k ++  )
                        {
                                stringBuffer.Append(( string )ds.Tables[i].Rows[j][k]);
                             if ( k  <  ds.Tables[i].Columns.Count  -   1  )
                                stringBuffer.Append( " " );
                        }
                        stringBuffer.Append( " " );
                    }
                     //  利用系统剪切板
                    System.Windows.Forms.Clipboard.SetDataObject( "" );
 
                     //  将stringBuffer放入剪切板
                    System.Windows.Forms.Clipboard.SetDataObject(stringBuffer);
 
                     //  选中这个sheet页中的第一个单元格（下标从1开始）
                    ((Excel.Range)xlWorksheet.Cells[ 1 , 1 ]).Select();
 
                     //  将剪切板里的内容粘贴在Sheet中
                    xlWorksheet.Paste(oMissing,oMissing);
 
                     //  清空系统剪切板
                    System.Windows.Forms.Clipboard.SetDataObject( "" );
                }
 
                 //  保存并关闭这个工作簿
                xlWorkbook.Close( Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing );
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
                xlWorkbook  =   null ;
            }
             catch (Exception ex)           
            {
                MessageBox.Show(ex.Message);
            }
             finally
            {
                 //  释放COM对象
                xlApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                GC.Collect();
            }
        }