c#导出数据到Excel

1、filename是导出的文件名

//导出数据到Excel表
    public void ExportExcel(DataSet my_Ds, string filename)
    {
        //DataSet ds = this.SqlDataSource1;

        Excel.Application oExcel;
        oExcel = new Excel.Application();
        try
        {

            Excel.Workbook oBook;
            Object oMissing = System.Reflection.Missing.Value;

            oBook = oExcel.Workbooks.Add(oMissing);
            HttpResponse response = HttpContext.Current.Response;

            int lie = my_Ds.Tables[0].Columns.Count;
            int hang = my_Ds.Tables[0].Rows.Count;
            int i, j, t;
            string panduanstring = "";
            i = 1;
            for (j = 0; j < lie; j++)//标题
            {
                oExcel.Cells[1, i++] = my_Ds.Tables[0].Columns[j].ColumnName;
            }

            t = 1;
            for (i = 0; i < hang; i++)//内容
            {
                for (j = 0; j < lie; j++)
                {

                    panduanstring = my_Ds.Tables[0].Rows[i][j].ToString();
                    if (panduanstring.GetType().ToString() == "System.String")
                    {
                        oExcel.Cells[i + 2, t++] = "'" + my_Ds.Tables[0].Rows[i][j].ToString();
                    }
                    else
                        oExcel.Cells[i + 2, t++] = my_Ds.Tables[0].Rows[i][j].ToString();

                }
                t = 1;
            }

            oExcel.Visible = true;
            oBook.Saved = true;
            oExcel.UserControl = false;

            string path = Server.MapPath("excel/");
            string mm = path + filename + ".xls";

            oExcel.ActiveWorkbook.SaveCopyAs(mm);
            oExcel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject((object)oExcel);
            GC.Collect();
            response.Redirect("excel/" + filename + ".xls");
            //oExcel.Quit();

            //System.IO.File.Delete(path + filename + ".xls");
        }

        catch
        {
            //oExcel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject((object)oExcel);
            GC.Collect();
        }

    } 

 

2、

using System;
using System.Data;
using System.Windows.Forms;
using System.IO;

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

  //DataGrid导出Excel
  public void ExptExcel(DataSet dataset)
  {
   DataSet ds = dataset;//取得dataGrid绑定的DataSet
   if(ds==null) return;

   string saveFileName="";
   //bool fileSaved=false;
   SaveFileDialog saveDialog=new SaveFileDialog();
   saveDialog.DefaultExt ="xls";
   saveDialog.Filter="Excel文件|*.xls";
   saveDialog.FileName ="Sheet1";
   saveDialog.ShowDialog();
   saveFileName=saveDialog.FileName;
   if(saveFileName.IndexOf(":")<0) return; //被点了取消

   Excel.Application xlApp=new Excel.Application();

   if(xlApp==null)
   {
    MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
    return;
   }
   System.Windows.Forms.Application.DoEvents();

   Excel.Workbooks workbooks=xlApp.Workbooks;
   Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
   Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
   Excel.Range range;

   //   string oldCaption=this
   long totalCount=ds.Tables[0].Rows.Count;//ds.Tables[0].Rows.Count;
   //long rowRead=0;
   //float percent=0;

   //worksheet.Cells[1,1]=title;
   //写入字段
   for(int i=0;i<ds.Tables[0].Columns.Count;i++)
   {
    worksheet.Cells[1,i+1]=ds.Tables[0].Columns[i].ColumnName;
    range=(Excel.Range)worksheet.Cells[1,i+1];
    range.Interior.ColorIndex = 15;
    range.Font.Bold = true;

   }
   //写入数值
   //this.CaptionVisible = true;
   for(int r=0;r<ds.Tables[0].Rows.Count;r++)
   {
    for(int i=0;i<ds.Tables[0].Columns.Count;i++)
    {
     worksheet.Cells[r+2,i+1]=ds.Tables[0].Rows[r][i];
    }
    //rowRead++;
    //percent=((float)(100*rowRead))/totalCount;
    //this.CaptionText = "正在导出数据["+ percent.ToString("0.00") +"％]...";
    System.Windows.Forms.Application.DoEvents();
   }
   //this.CaptionVisible = false;
   //this.CaptionText = oldCaption;

   range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[ds.Tables[0].Rows.Count+2,ds.Tables[0].Columns.Count]);
   range.BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin,Excel.XlColorIndex.xlColorIndexAutomatic,null);

//   range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
//   range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle =Excel.XlLineStyle.xlContinuous;
//   range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight =Excel.XlBorderWeight.xlThin;

   if(ds.Tables[0].Columns.Count>1)
   {
    range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex =Excel.XlColorIndex.xlColorIndexAutomatic;
    range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
    range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
   }

   if(saveFileName!="")
   {
    try
    {
     workbook.Saved =true;
     workbook.SaveCopyAs(saveFileName);
     //fileSaved=true;
    }
    catch(Exception ex)
    {
     //fileSaved=false;
     MessageBox.Show("导出文件时出错,文件可能正被打开！/n"+ex.Message);
    }
   }
   //else
   //{
    //fileSaved=false;
   //}
   xlApp.Quit();
   GC.Collect();//强行销毁
   //导出后启动Excel
   //if(fileSaved && File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName);
   MessageBox.Show("成功导出Excell！","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
  }

 }
}