从DataTable导出Excel，并下载，删除Excel进程。

页面上加入一个button即可.
源代码如下:

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;
using  Microsoft.Office.Interop.Excel;

namespace  Test
{
    /**//// <summary>
    /// TestExcel 的摘要说明。
    /// </summary>
    public class TestExcel : System.Web.UI.Page
    {
        protected System.Web.UI.WebControls.Button Button1;
    
        private void Page_Load(object sender, System.EventArgs e)
        {
            
        }

        Web 窗体设计器生成的代码#region Web 窗体设计器生成的代码
        override protected void OnInit(EventArgs e)
        {
            //
            // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
            //
            InitializeComponent();
            base.OnInit(e);
        }
        
        /**//// <summary>
        /// 设计器支持所需的方法 - 不要使用代码编辑器修改
        /// 此方法的内容。
        /// </summary>
        private void InitializeComponent()
        {    
            this.Button1.Click += new System.EventHandler(this.Button1_Click);
            this.Load += new System.EventHandler(this.Page_Load);

        }
        #endregion

        private void Button1_Click(object sender, System.EventArgs e)
        {
            try
            {
                string DownloadPath=Server.MapPath(".");    //副本的文件夹路径。
                //副本的文件名。
                string TempFileName = DateTime.Now.ToString("yyyyMMdd") + DateTime.Now.Hour + DateTime.Now.Minute + DateTime.Now.Second + ".XLS"; 
                this.txtTempFileName.Text=TempFileName;
                object missing = System.Reflection.Missing.Value;
                object missing2 = System.Reflection.Missing.Value;
                ApplicationClass myExcel=new ApplicationClass();

                Workbook myBook=(Workbook)myExcel.Workbooks.Add(missing);
                Worksheet curSheet = (Worksheet)myBook.Sheets[1];
                //设置Excel样式
                Range r1=(Range)myExcel.Cells[1,2];

                Range r2=(Range)myExcel.Cells[3,4];

                r1.Font.Bold=true;
                r2.Font.Bold=true;


                string DownloadFilePath=DownloadPath+"\\"+TempFileName;

                 System.Data.DataTable dt=this.GetTable();
                int rc=dt.Rows.Count;
                //绘制边框
                Range rBorders=(Range)curSheet.get_Range(myExcel.Cells[7,1],myExcel.Cells[7+rc+1,10]);
                rBorders.Borders.LineStyle=1;
                curSheet.get_Range(myExcel.Cells[7,1],myExcel.Cells[7+rc+1,1]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;//设置左边线加粗
                curSheet.get_Range(myExcel.Cells[7,1],myExcel.Cells[7,10]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;//设置设置上边线加粗
                curSheet.get_Range(myExcel.Cells[7+rc+1,1],myExcel.Cells[7+rc+1,10]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;//设置下边线加粗
                curSheet.get_Range(myExcel.Cells[7,10],myExcel.Cells[7+rc+1,10]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//设置右边线加粗



                //Excel的表头信息
                myExcel.Cells[1,2]="表头信息";

                myExcel.Cells[2,2]="New Added:" + DateTime.Now.ToString();
                myExcel.Cells[3,4]="VENDOR  CODE  LIST  -  BY  PRODUCTS.";
                myExcel.Cells[4,4]="****************************************";

                myExcel.Cells[5,9]="DATE From:" + DateTime.Now.ToString();
                myExcel.Cells[6,9]="DATE To:" + DateTime.Now.ToString();

                myExcel.Cells[7,5]="PARTS SUPPLIER";

                //设置Excel表列头
                myExcel.Cells[8,1]="Item";
                myExcel.Cells[8,2]="OrgCode";
                myExcel.Cells[8,3]="VendorCode";
                myExcel.Cells[8,4]="VendorName";
                myExcel.Cells[8,5]="A";
                myExcel.Cells[8,6]="B";
                myExcel.Cells[8,7]="C";
                myExcel.Cells[8,8]="PayMentType";
                myExcel.Cells[8,9]="TermsCode";
                myExcel.Cells[8,10]="CreateTime";
                //设置表头字体风格
                curSheet.get_Range(myExcel.Cells[7,1],myExcel.Cells[8,10]).Font.Bold=true;
                int j=1;//j为总结的Item数目的变量
                int i=9;
                while (i-8<=dt.Rows.Count)
                {
                    myExcel.Cells[i,1]=j.ToString();
                    myExcel.Cells[i,2]=dt.Rows[i-9]["Name"].ToString().Trim();
                    myExcel.Cells[i,3]=dt.Rows[i-9]["cost"].ToString().Trim();
                    myExcel.Cells[i,4]=dt.Rows[i-9]["bug"].ToString().Trim();
                    myExcel.Cells[i,5]="";
                    myExcel.Cells[i,6]="";
                    myExcel.Cells[i,7]="";
                    
                    //设置颜色，否则日期显示成"######"格式。
                    Range rCol10=(Range)myExcel.Cells[i,10];
                    rCol10=null;
                    //从1开始循环
                    j++;
                    i++;
                } 

                myBook.Saved=true;
                myBook.SaveAs(DownloadFilePath,missing2,"","",false,false,XlSaveAsAccessMode.xlNoChange,1,false,missing,missing,missing);

                myBook.Close(false, null,null);
                myExcel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(myBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
                myBook = null;
                myExcel = null;
                GC.Collect();
                //下载文件
                HttpResponse response = HttpContext.Current.Response; 
                response.Clear();
                response.WriteFile(DownloadFilePath);
                string httpHeader="attachment;filename=backup.Xls";
                response.AppendHeader("Content-Disposition", httpHeader);
                response.Flush();
                //删除临时文件
                System.IO.File.Delete(DownloadFilePath);
                
                killExcelProcess();
            }
            catch(Exception Ex)
            {
                throw Ex;
            }
        }
        /**//// <summary>
        /// 删除Excel进程
        /// </summary>
        private void  killExcelProcess(){
            //结束 Excel 进程
            foreach(System.Diagnostics.Process xlProcess in System.Diagnostics.Process.GetProcesses()){
                if( xlProcess.ProcessName.ToUpper().Equals("EXCEL")) {
                    //结束 excel 进程 
                    xlProcess.Kill();
                }
            }
                
        }
        /**//// <summary>
        /// 构建临时DataTable
        /// </summary>
        /// <returns></returns>
        private System.Data.DataTable GetTable()
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            dt.Columns.Add("Name");
            dt.Columns.Add("cost");
            dt.Columns.Add("bug");
            DataRow rw = dt.NewRow();
            rw["Name"]= "梁";
            rw["Cost"]= "12";
            rw["bug"]= "5";
            dt.Rows.Add(rw);
            rw = dt.NewRow();
            rw["Name"]= "李";
            rw["Cost"]= "15";
            rw["bug"]= "2";
            dt.Rows.Add(rw);
            rw = dt.NewRow();
            rw["Name"]= "王";
            rw["Cost"]= "8";
            rw["bug"]= "1";
            dt.Rows.Add(rw);
            return dt;
        }
    }
}

 

转载于:https://www.cnblogs.com/echo/archive/2005/05/24/161461.html