excel的导出

1. #region (使用模版)将DataTable的数据导出显示为报表
3.     private DateTime beforeTime;            //Excel启动之前时间
4.         private DateTime afterTime;             //Excel启动之后时间
5.         /// <summary>
6.         /// 将DataTable的数据导出显示为报表
7.         /// </summary>
8.         /// <param name="dt">要导出的数据</param>
9.         /// <param name="FilePath">保存文件的路径</param>
10.         /// <param name="SheetsNumber">导出报表的Sheet数</param>
11.         /// <param name="RowIndex">导出报表的起始行</param>
12.         /// <param name="ColIndex">导出报表的起始列</param>
13.         /// <param name="TemplateFile">模版文件</param>
14.         /// <returns></returns>
15.         public string OutputExcel(Dictionary<string, System.Data.DataTable> dts, string FileName, string FilePath, int RowIndex, int ColIndex, string TemplateFile)
16.         {
17.             beforeTime = DateTime.Now;
19.             Excel.Application excel;
20.             Excel._Workbook xBk;
21.             Excel._Worksheet xSt = null;
23.             excel = new Excel.ApplicationClass();
24.             xBk = excel.Workbooks._Open(@TemplateFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
25.                 Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
26.             int index = 0;
28.             if (dts.Count > xBk.Sheets.Count)
29.             {
30.                 afterTime = DateTime.Now;
31.                 return "TemplateError";
32.             }
34.             foreach (string key in dts.Keys)
35.             {
36.                 index++;
37.                 int rowIndex = RowIndex;
38.                 xSt = (Excel.Worksheet)xBk.Worksheets.get_Item(index);
40.                 //取得表格中的数据
41.                 System.Data.DataTable dt = dts[key];                
42.                 foreach (DataRow row in dt.Rows)
43.                 {
44.                     int colIndex = ColIndex;
45.                     rowIndex++;
46.                     foreach (DataColumn col in dt.Columns)
47.                     {
48.                         colIndex++;
49.                         if (col.DataType == System.Type.GetType("System.DateTime"))
50.                         {
51.                             string strDate = row[col.ColumnName].ToString();
52.                             if (!"".Equals(strDate))
53.                             {
54.                                 xSt.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
55.                                 //xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
56.                             }
57.                         }
58.                         else
59.                             if (col.DataType == System.Type.GetType("System.String"))
60.                             {
61.                                 xSt.Cells[rowIndex, colIndex] = row[col.ColumnName];
62.                                 //xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
63.                             }
64.                             else
65.                             {
66.                                 xSt.Cells[rowIndex, colIndex] = row[col.ColumnName];
67.                             }
68.                     }
69.                 }
71.                 afterTime = DateTime.Now;
73.                 //显示效果
74.                 //excel.Visible = true;                
75.             }
77.             //ClearFile(FilePath);
78.             //string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
79.             string filename = FileName + ".xls";
80.             //判断临时文件是否存在，如不存在，创建
81.             creatTempFolder(FilePath);
82.             excel.ActiveWorkbook.SaveAs(FilePath + filename, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
83.             
84.             #region  结束Excel进程
86.             //需要对Excel的DCOM对象进行配置:dcomcnfg
87.             
88.             xBk.Close(null, null, null);
89.             excel.Workbooks.Close();
90.             excel.Quit();
92.             //注意：这里用到的所有Excel对象都要执行这个操作，否则结束不了Excel进程            
93.             if (xSt != null)
94.             {
95.                 System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
96.                 xSt = null;
97.             }
98.             if (xBk != null)
99.             {
100.                 System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
101.                 xBk = null;
102.             }
103.             if (excel != null)
104.             {
105.                 System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
106.                 excel = null;
107.             }
108.             GC.Collect();//垃圾回收
109.             
110.             #endregion
111.                         
112.             return filename;
114.         }
115.         #endregion
116.         
118.         #region Kill Excel进程
120.         /// <summary>
121.         /// 结束Excel进程
122.         /// </summary>
123.         public void KillExcelProcess()
124.         {
125.             Process[] myProcesses;
126.             DateTime startTime;
127.             myProcesses = Process.GetProcessesByName("Excel");
129.             //得不到Excel进程ID，暂时只能判断进程启动时间
130.             foreach (Process myProcess in myProcesses)
131.             {
132.                 startTime = myProcess.StartTime;
133.                 if (startTime > beforeTime && startTime < afterTime)
134.                 {
135.                     myProcess.Kill();
136.                 }
137.             }
138.         }
139.         #endregion