将数据写入EXCEL多个表

首先在项目中添加引用-Microsoft.Office.Interop.Excel

 

 1 using Excel = Microsoft.Office.Interop.Excel;
 2 using System.Reflection;
 3 using System.Text;
 4 using System.Collections.Generic;
 5 
 6 public static void creat_excel(List<DataTable> d)
 7     {
 8         Excel.Application ex = new Excel.Application();
 9         Excel.Workbook workbook = ex.Workbooks._Open(HttpContext.Current.Server.MapPath("y.xls"), Missing.Value,
10             Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
11             Missing.Value, Missing.Value, Missing.Value);
12         write_excel(ex, d[0], 1, 8);
13         write_excel(ex, d[1], 2, 3);
14         ex.Visible = false;
15         
16        
17         ex.DisplayAlerts = false;
18         ex.AlertBeforeOverwriting = false;
19         
20         string sv = HttpContext.Current.Server.MapPath("X.xls");
21         workbook.SaveCopyAs(sv);
22         workbook.Close(false, null, null);
23 
24         ex.Quit();
25         ex=null;
26         GC.Collect();
27         downloadFile(sv);
28 
29     }
30     private  static void write_excel(Excel.Application ex,  DataTable dt,int sheetNum, int rowNum)
31     {
32         Excel._Worksheet wSheet = ex.Worksheets[sheetNum] as Excel.Worksheet;
33         
34         for (int i = 1; i < 3; i++)
35         {
36             for (int m = 0; m < dt.Columns.Count; m++)
37             {
38                 if (i == 2 && m == 0)
39                     wSheet.Cells[rowNum + 1, 1] = "合计";
40                 else
41                     wSheet.Cells[rowNum + i - 1, m + 1] = dt.Rows[0][m].ToString();
42             }
43         }
44 
45     }
46 
47  public static void downloadFile(string sv)
48     {
49         FileInfo file = new FileInfo(sv);
50         HttpContext.Current.Response.Clear();
51         HttpContext.Current.Response.HeaderEncoding = Encoding.GetEncoding("GB2312");
52         HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
53         HttpContext.Current.Response.ContentType = "application/ms-excel";
54         HttpContext.Current.Response.Charset = "GB2312";
55         HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(sv) + "");
56         // 添加头信息，指定文件大小，让浏览器能够显示下载进度 
57         HttpContext.Current.Response.AddHeader("Content-Length", file.Length.ToString());
58 
59         HttpContext.Current.Response.TransmitFile(file.FullName);
60         HttpContext.Current.Response.Flush();
61         HttpContext.Current.Response.Close();
62         file.Delete();
63     }

 

 

最后输出到浏览器并删除临时EXCEL文件