asp.net操作EXCEL

asp.net操作EXCEL
 1        /**//// <summary>
 2        /// 拷贝EXCEL模板并且填上相应的数据并且下载到客户端
 3        /// </summary>
 4        /// <param name="ds">填充EXCEL的数据集</param>
 5        /// <param name="FileName">Excel模板的名字,在数据库里面用表的名字代替</param>
 6        /// <returns>字符串为空表示成功；否则表示失败</returns>
 7        public string downexcel(DataSet ds,string TableName)
 8        {
 9            Excel.ApplicationClass myexcel=null;
10            Excel.Workbook mybook;
11            Excel.Worksheet mysheet;
12            myexcel=new Excel.ApplicationClass();
13            try
14            {
15                string urlPath = HttpContext.Current.Request.ApplicationPath + "/Template/";
16                string physicPath = HttpContext.Current.Server.MapPath(urlPath);
17                string filenamer;
18                filenamer=TableName +".xls";
19                string filenamet=Guid.NewGuid()+".xls";
20                string pathr=physicPath+filenamer;//源路径
21                string patht=physicPath+filenamet;//目标路径
22                File.Copy(pathr,patht,true);
23                //操作COPY的EXCEL文件
24                清空对象#region 清空对象
25                myexcel=null;
26                mybook=null;
27                mysheet=null;
28                #endregion
29                myexcel.Visible =false;
30                if (myexcel==null)
31                {
32                    return "";
33                }
34                object missing=Missing.Value;//表示缺少的 Object。无法继承此类。
35                myexcel.Visible=false;
36                myexcel.Application.Workbooks.Open(patht,missing,missing,missing,missing,
37                    missing,missing,missing,missing,missing,missing, missing,missing,missing,missing);
38                mybook=myexcel.Workbooks[1];
39                mysheet=(Excel.Worksheet)mybook.ActiveSheet;
40                开始填充数据#region 开始填充数据
41                mysheet.Cells[7,4]=ds.Tables[0].Rows[0][0].ToString();
42                mysheet.Cells[7,5]=ds.Tables[0].Rows[0][1].ToString();
43                mysheet.Cells[7,6]=ds.Tables[0].Rows[0][2].ToString();
44                mysheet.Cells[7,7]=ds.Tables[0].Rows[0][3].ToString();
45                mysheet.Cells[7,8]=ds.Tables[0].Rows[0][4].ToString();
46                mysheet.Cells[7,9]=ds.Tables[0].Rows[0][5].ToString();
47                mysheet.Cells[7,10]=ds.Tables[0].Rows[0][6].ToString();
48                #endregion
49                mybook.Save();
50                mybook.Close(true,patht,true);
51                释放所有对象#region 释放所有对象
52                Marshal.ReleaseComObject(myexcel);
53                Marshal.ReleaseComObject(mybook);
54                Marshal.ReleaseComObject(mysheet);
55                #endregion
56                myexcel.Quit();
57                myexcel=null;
58                GC.Collect();//强制对所有变量进行回收
59                下载已经修改过的EXCEL#region 下载已经修改过的EXCEL
60                System.IO.FileInfo file = new System.IO.FileInfo(patht); 
61                HttpContext.Current.Response.Clear(); 
62                HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(file.Name)); 
63                HttpContext.Current.Response.AddHeader("Content-Length", file.Length.ToString()); 
64                HttpContext.Current.Response.ContentType = "application/octet-stream"; 
65                HttpContext.Current.Response.WriteFile(file.FullName); 
66                HttpContext.Current.Response.Flush();   //向客户端发送当前所有的缓冲输出
67                File.Delete(patht); //只有加上上面的那句才能删除掉
68                HttpContext.Current.Response.End(); 
69                return "";
70                #endregion
71        
72            }
73            catch(Exception ex)
74            {
75                Marshal.ReleaseComObject(myexcel);
76                myexcel.Quit();
77                myexcel=null;
78                GC.Collect();//强制对所有变量进行回收
79                return ex.Message;
80            }
81
82        }