ExportToExcel for .net

using System;
using Excel;
using System.Reflection;
using System.Data;

namespace TMS_Web.DataBaseAccess
{
 /// <summary>
 /// ExportToExcel 的摘要说明。
 /// </summary>
 public class OutputToExcel
 {
  public OutputToExcel()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   //
  }

  /// <summary>
  /// 将DataSet的内容导入到Excel，采用直接写Excel文件
  /// </summary>
  /// <param name="ds">
  /// DataSet
  /// </param>
  public static void DataSetToExcel(System.Data.DataSet ds)
  {   
   
   // ds为要输出到Excel的数据，str为标题名称
   GC.Collect();
   Application excel;// = new Application();
   
   _Workbook xBk;
   _Worksheet xSt;

   object miss = System.Reflection.Missing.Value;

   excel= new ApplicationClass();

   xBk = excel.Workbooks.Add(true);
   // 标题，暂不设置
   string str = "";
   try
   {

    for (int i = ds.Tables.Count - 1; i >= 0; i --)
    {
     int rowIndex = 3;
     int colIndex = 1;

     DataView dv = new DataView();
     dv = ds.Tables[i].DefaultView;
     xSt = (_Worksheet)xBk.Worksheets.Add(miss, miss, 1, miss);
               
     //
     // 取得标题
     //
     foreach(DataColumn col in dv.Table.Columns)
     {
      colIndex++;
      excel.Cells[rowIndex,colIndex] = col.ColumnName;
      xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter; // 设置标题格式为居中对齐    
      //
      // 设置选中的部分的颜色
      //
      xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).Select();
      xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).Interior.ColorIndex = 15; // 设置为浅灰色，共计有56种
     }

     //
     // 取得表格中的数据
     //
     foreach(DataRowView row in dv)
     {
      rowIndex ++;
      colIndex = 1;
      foreach(DataColumn col in dv.Table.Columns)
      {
       colIndex ++;
       try
       {
        if(col.DataType == System.Type.GetType("System.DateTime"))
        {
         if (row[col.ColumnName] != null)
         {
          excel.Cells[rowIndex,colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
          xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
         }
        }
        else
         if(col.DataType == System.Type.GetType("System.String"))
        {
         excel.Cells[rowIndex,colIndex] = "'"+row[col.ColumnName].ToString();
         xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
        }
        else
        {
         excel.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString();
        }
       }
       catch
       {
        excel.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString();
       }
      }
     }
    
     //
     // 取得整个报表的标题
     //
     excel.Cells[2,colIndex] = str;
     //
     // 设置整个报表的标题格式
     //
     xSt.get_Range(excel.Cells[2,colIndex],excel.Cells[2,colIndex]).Font.Bold = false;
     xSt.get_Range(excel.Cells[2,colIndex],excel.Cells[2,colIndex]).Font.Size = 16;
     //
     // 设置报表表格为最适应宽度
     //
     xSt.get_Range(excel.Cells[3,2],excel.Cells[rowIndex,colIndex]).Select();
     xSt.get_Range(excel.Cells[3,2],excel.Cells[rowIndex,colIndex]).Columns.AutoFit();
     //
     // 设置整个报表的标题为跨列居中
     //
     xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).Select();
     xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).HorizontalAlignment = XlHAlign.xlHAlignLeft;
     //
     // 绘制边框
     //
     xSt.get_Range(excel.Cells[3,2],excel.Cells[rowIndex,colIndex]).Borders.LineStyle = 1;
    
     //
     // 显示效果
     //
     excel.Visible=true;   
    }

    string fileName = CombinFileName() + ".xls";
    // 设置目录为当前工作目录
    System.IO.Directory.SetCurrentDirectory(System.Web.HttpContext.Current.Server.MapPath("~/ExcelReportLib/"));
    // 保存进ExcelReportLib目录
    xBk.SaveCopyAs(System.Web.HttpContext.Current.Server.MapPath("~/ExcelReportLib/") + fileName);
    OpenFile(fileName);
   }
   catch (Exception ex)
   {
    // 提示错误信息
    System.Web.HttpContext.Current.Response.Write("<script>javascript:window.alert('" + ex.Message + "');</script>");
   }
   finally
   {
    // 释放句柄及内存
    xBk.Close(false, null,null);
    excel.Quit();
    xBk = null;
    excel = null;
    GC.Collect();
   }   
  }

  /// <summary>
  /// 给出提示框，选择是否保存文件还是直接打开文件
  /// </summary>
  /// <param name="fileName">文件名</param>
  private static void OpenFile(string fileName)
  {
   // 设置目录为当前工作目录
   System.IO.Directory.SetCurrentDirectory(System.Web.HttpContext.Current.Server.MapPath("~/ExcelReportLib/"));

   string filePath = System.Web.HttpContext.Current.Server.MapPath("~/ExcelReportLib/") + fileName;

   System.Web.HttpContext.Current.Session["filePath"] = filePath;
   System.Web.HttpContext.Current.Response.Write( "<script language=javascript>window.open('../Module/OpenExcelFile.aspx')</script>");

//   System.IO.FileStream fs = System.IO.File.OpenRead( filePath );
//   byte[] FileData = new byte[ fs.Length ];
//   fs.Read( FileData, 0, (int)fs.Length );   
//
//   // long fileLength = fs.Length;
//   fs.Close();
//   System.Web.HttpContext.Current.Response.Clear();
//   System.Web.HttpContext.Current.Response.Buffer = true;
//
//   System.Web.HttpContext.Current.Response.AppendHeader( "Content-Type", "application/vnd.ms-excel" );   
//   System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "inline;filename=" + filePath );
//   // System.Web.HttpContext.Current.Response.AppendHeader("Content-Length", fileLength.ToString());
//   
//   System.Web.HttpContext.Current.Response.BinaryWrite(FileData);
//   
//   System.Web.HttpContext.Current.Response.Flush();   
   // System.Web.HttpContext.Current.Response.End();     
  }
  
  /// <summary>
  /// 组合名称
  /// </summary>
  public static string CombinFileName()
  {
   // 将系统时间组合，从年份开始，精确到毫秒结束
   return
    (System.DateTime.Now.Year.ToString() + System.DateTime.Now.Month.ToString() + System.DateTime.Now.Day.ToString()
    + System.DateTime.Now.Hour.ToString() + System.DateTime.Now.Minute.ToString() + System.DateTime.Now.Second.ToString()
    + System.DateTime.Now.Millisecond.ToString());
  }
 }
}