解决ASP.NET导出Excel文件时 用Excel2007打开时弹出文件类型与扩展名不同的对话框

  protected void btnExport_Click(object sender, EventArgs e)
3     {
4         string strMapPath = Server.MapPath("~/");                                                       //获取Web应用程序的物理路径
5          string sourceExcelFileName = strMapPath + "Temp.xls";                                           //源Excel文件名
6          string targetExcelFileName = strMapPath + @"TempExcel\" + Guid.NewGuid().ToString() + ".xls";   //使用Guid生成全局唯一字符串，作为目标Excel文件的文件名
7  
8         File.Copy(sourceExcelFileName, targetExcelFileName);                                            //Copy文件，放在目标文件夹中
9
10         //Excel模型，用来操作Excel文件
11          Microsoft.Office.Interop.Excel.Application excelApp = null;
12         Microsoft.Office.Interop.Excel.Workbook excelWb = null;
13         Microsoft.Office.Interop.Excel.Worksheet excelWs = null;
14         Microsoft.Office.Interop.Excel.Range excelR = null;
15
16         try
17         {
18             excelApp = new Microsoft.Office.Interop.Excel.Application();
19             excelWb = excelApp.Workbooks.Open(targetExcelFileName);                             //打开Excel工作簿文件
20             excelWs = (Microsoft.Office.Interop.Excel.Worksheet)(excelWb.Sheets.get_Item(1));   //选择工作簿中第一个工作表
21
22             //向Excel中添加列名
23             for (int i = 0; i < dtbl.Columns.Count; i++)
24             {
25                 string rangeName = ((Char)(i + 65)).ToString() + "1";       //计算出单元格的位置（例：第一行第二列在Excel中为B1，即为值为1+65的字符+"1"。）
26                 excelR = excelWs.get_Range(rangeName);
27                 excelR.Value = dtbl.Columns[i].ColumnName;                  //在对应单元格中写入值
28             }
29
30             //把每一行数据写入Excel模型中
31             for (int i = 0; i < dtbl.Rows.Count; i++)
32             {
33                 for (int j = 0; j < dtbl.Columns.Count; j++)
34                 {
35                     string rangName = ((Char)(j + 65)).ToString() + (i + 2).ToString();     //表格内容的写入从第二行开始
36                     excelR = excelWs.get_Range(rangName);
37                     excelR.Value = dtbl.Rows[i][j];                                         //在Excel中写入对应单元格的内容
38                 }
39             }
40             excelWb.Save();
41
42         }
43         catch (Exception)
44         {
45             Response.Write("<script type='text/javascript'>alert('生成失败！');</script>");
46         }
47         finally
48         {
49             //关闭Excel，否则Excel文件将无法被打开
50             excelWb.Close();
51             excelApp.Workbooks.Close();
52             excelApp.Quit();
53         }
54
55         //向客户端发送文件...
56         Response.Clear();
57         Response.AddHeader("Content-Disposition", "attachment;filename=excel.xls");     //设置回发内容为Excel
58         Response.ContentType = "application/ms-excel";
59         Response.WriteFile(targetExcelFileName);                                        //把刚刚生成的Excel文件写入Http流
60         Response.End();
61
62     }

 

2.由于会在临时文件夹中存储Excel文件，所以写了一个类用来清理过期不用的Excel文件：

 1 /// <summary>
 2 /// TempExcelCleaner 清理TempExcel文件夹中已经不用的临时Excel文件
 3 /// </summary>
 4 public class TempExcelCleaner
 5 {
 6     private string tempDirectoryPath;
 7     private int overtime = 30;
 8     private int sleeptime = 60;
 9 
10     /// <summary>
11     /// 每次扫描文件夹的间隔时间，单位分钟
12     /// </summary>
13     public int Sleeptime
14     {
15         get { return sleeptime; }
16         set { sleeptime = value; }
17     }
18 
19     /// <summary>
20     /// 文件过期时间，单位分钟
21     /// </summary>
22     public int Overtime
23     {
24         get { return overtime; }
25         set { overtime = value; }
26     }
27 
28     /// <summary>
29     /// 构造TempExcelCleaner
30     /// </summary>
31     /// <param name="tempDirectoryPath">要清理的文件夹路径</param>
32     public TempExcelCleaner(string tempDirectoryPath)
33     {
34         this.tempDirectoryPath = tempDirectoryPath;
35     }
36 
37     private TempExcelCleaner()
38     {
39     }
40 
41     /// <summary>
42     /// 运行，开始清理指定文件夹
43     /// </summary>
44     public void Run()
45     {
46         DirectoryInfo directory = new DirectoryInfo(tempDirectoryPath);
47         //遍历文件夹中所有文件，超过指定时间就删除
48         foreach (FileInfo file in directory.GetFiles())
49         {
50             DateTime createTime = file.CreationTime;
51             if (createTime.AddMinutes(overtime) < DateTime.Now)
52             {
53                 file.Delete();
54             }
55         }
56         //遍历完所有文件，休息指定时间
57         System.Threading.Thread.Sleep(sleeptime * 60 * 1000);
58     }
59 }

3.在Golobal.asax中的代码，在Web应用程序启动时开始临时文件清理的服务：

 1     void Application_Start(object sender, EventArgs e)
 2     {
 3         // 在应用程序启动时运行的代码
 4         // Web应用程序启动时开始执行清理程序。
 5         System.Threading.Thread threadTempExcelCleaner = new System.Threading.Thread(new System.Threading.ThreadStart(FunCleanTempExcel));
 6         threadTempExcelCleaner.IsBackground = true;
 7         threadTempExcelCleaner.Start();
 8     }
 9 
10     void FunCleanTempExcel()
11     {
12         TempExcelCleaner cleaner = new TempExcelCleaner(@"F:\Users\Administrator\Documents\Visual Studio 2010\WebSites\ExportExcel\TempExcel");
13         cleaner.Run();
14     }