DataSet和DataTable转换为excel文件的类，最基本的转换

 这几天完成老板的项目，做一个叫中拼网的招聘网站。涉及到了DataSet和DataTable转换为excel文件的需求。参考了csdn上大多数人的方法，自己写了一个类。可以关闭用过的excel进程。可是需要你先自己先得到由com转换来的excel.dll   NET 组件。

这个代码也是参考csdn上的帖子写出来的。所以发出来给大家参考。就是进行最简单的转换。没有加上任何复杂设置。其实是我门的项目没有这个需要。保留了很大的扩展性。

希望对大家有启发。

//-----------------------------------------------------------------------------------------------------------------------------------

using Excel;
using System.Reflection;
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace ParentClassNameSpace
{
 /// <summary>
 /// 生成Excel类说明
 /// 需要Excel.dll，Office.dll
 /// VBIDE.dll，Word.dll
 /// 四个NET组件
 /// </summary>
 public class MyExcel
 {
  public MyExcel()
  {

  }

  /// <summary>
  /// 传入DataSet，路径。写excel文件函数
  /// </summary>
  /// <param name="ds">DataSet对象</param>
  /// <param name="strurl">文件要保留路径</param>
  /// <returns>文件所在位置的字符串</returns>
  public string DataSetToExcelXls(DataSet ds,string strurl)
  { 
   try
   {
    if(ds.Tables.Count<1)
     return "DataSet对象错误";
    if(ds.Tables.Count==1)
    {
     return DataTableToExcelXls(ds.Tables[0],strurl);
    }
    else
    {
     Random rd=new Random(int.Parse(DateTime.Now.ToString("MMddhhmmss")));
     string strFileName =DateTime.Now.ToString("yyyyMMdd")+DateTime.Now.Hour+DateTime.Now.Minute+DateTime.Now.Second+rd.Next(999999).ToString()+ ".XLS";
 
     object my_object=Missing.Value;
     Excel.Application excel= new Excel.ApplicationClass();
     Excel.WorkbookClass oWB;   
     Excel.Worksheet oSheet;

     oWB = (Excel.WorkbookClass)(excel.Workbooks.Add(true));
     oWB.Sheets.Add(my_object,my_object,ds.Tables.Count==1?1:ds.Tables.Count-1,my_object);

     int dstabcount=ds.Tables.Count;
     for(int dst=0;dst<dstabcount;dst++)
     {
      oSheet=(Excel.Worksheet)oWB.Sheets.get_Item(dst+1);
      for (int i = 0; i < ds.Tables[dst].Columns.Count; i++)
      {
       oSheet.Cells[1,i+1] =ds.Tables[dst].Columns[i].ToString();       
      }
      DataRow dr;
      int count=ds.Tables[dst].Rows.Count+1;
      int I,J;
      for(I = 2;I< count;I++)
      {    
       dr=ds.Tables[dst].Rows[I-2];    
       for(J = 1;J<(ds.Tables[dst].Columns.Count +1);J++)
       {
        oSheet.Cells[I,J] = dr[J-1].ToString().Trim() ;
       }   
      }
      oSheet.Name=ds.Tables[dst].TableName.ToString();
     }

     string path=HttpContext.Current.Server.MapPath(strurl) + "//" + strFileName;  
     oWB.SaveAs(path,my_object,"","",true,false,Excel.XlSaveAsAccessMode.xlShared,1,false,my_object,my_object);
   
     oWB.Close(false,null,null);
     excel.Workbooks.Close();   
     excel.Quit();

     System.Runtime.InteropServices.Marshal.ReleaseComObject (excel);
     System.Runtime.InteropServices.Marshal.ReleaseComObject (oWB); 

     oSheet = null;
     oWB= null;
     excel = null;
     GC.Collect();
     GC.WaitForPendingFinalizers();
     return path;
    }
   }
   catch
   {
    return null;
   }
  }

  
  /// <summary>
  /// 传入DataTable，路径。写excel文件函数
  /// </summary>
  /// <param name="ds">DataTable对象</param>
  /// <param name="strurl">文件要保留路径</param>
  /// <returns>文件所在位置的字符串</returns>
  public string DataTableToExcelXls(System.Data.DataTable ds,string strurl)
  { 
   try
   {
    Random rd=new Random(int.Parse(DateTime.Now.ToString("MMddhhmmss")));
    string strFileName =DateTime.Now.ToString("yyyyMMdd")+DateTime.Now.Hour+DateTime.Now.Minute+DateTime.Now.Second+rd.Next(999999).ToString()+ ".XLS";
      
    Excel.Application excel = new Excel.ApplicationClass();
    Excel.WorkbookClass oWB;
    Excel.Worksheet oSheet;
    oWB = (Excel.WorkbookClass)(excel.Workbooks.Add(true));
    oSheet = (Excel.Worksheet)oWB.ActiveSheet;
    object my_object=Missing.Value;

    for (int i = 0; i < ds.Columns.Count; i++)
    {
     oSheet.Cells[1,i+1] =ds.Columns[i].ToString() ;
    }
    DataRow dr;
    int count=ds.Rows.Count+2;
    int I,J;
    for(I = 2;I< count;I++)
    {    
     dr=ds.Rows[I-2];   
     for(J = 1;J<(ds.Columns.Count +1) ;J++)
     {
      oSheet.Cells[I,J] = dr[J-1].ToString().Trim() ;   
     }   
    }
    oSheet.Name=ds.TableName;
    string path=HttpContext.Current.Server.MapPath(strurl) + "//" + strFileName;  
    oWB.SaveAs(path,my_object,"","",true,false,Excel.XlSaveAsAccessMode.xlNoChange,1,false,my_object,my_object);
   
    oWB.Close(false,null,null);
    excel.Workbooks.Close();   
    excel.Quit();

    System.Runtime.InteropServices.Marshal.ReleaseComObject (excel);
    System.Runtime.InteropServices.Marshal.ReleaseComObject (oWB);
    System.Runtime.InteropServices.Marshal.ReleaseComObject (oSheet);

    oSheet = null;
    oWB= null;
    excel = null;
    GC.Collect();
    GC.WaitForPendingFinalizers();
    return path;
   }
   catch
   {
    return null;
   }  
  }

 }
}