1 using System;
2 using System.Data;
3 using System.Configuration;
4 using System.Linq;
5 using System.Web;
6 using System.Web.Security;
7 using System.Web.UI;
8 using System.Web.UI.HtmlControls;
9 using System.Web.UI.WebControls;
10 using System.Web.UI.WebControls.WebParts;
11 using System.Xml.Linq;
12 using System.IO;
13 using System.Reflection;
14 using System.Diagnostics;
15 using System.Collections;
16
17 /// <summary>
18 ///ExcelHelper 的摘要说明
19 /// </summary>
20 public class ExcelHelper
21 {
22 private string reportModelPath = null;
23 private string outPutFilePath = null;
24 private object missing = Missing.Value;
25 Excel.Application app;
26 Excel.Workbook workBook;
27 Excel.Worksheet workSheet;
28 Excel.Range range;
29
30 /// <summary>
31 /// 获取或设置报表模板路径
32 /// </summary>
33 public string ReportModelPath
34 {
35 get { return reportModelPath; }
36 set { reportModelPath = value; }
37 }
38
39 /// <summary>
40 /// 获取或设置输出路径
41 /// </summary>
42 public string OutPutFilePath
43 {
44 get { return outPutFilePath; }
45 set { outPutFilePath = value; }
46 }
47
48
49 public ExcelHelper()
50 {
51 //
52 //TODO: 在此处添加构造函数逻辑
53 //
54 }
55
56 /// <summary>
57 /// 带参ExcelHelper构造函数
58 /// </summary>
59 /// <param name="reportModelPath">报表模板路径</param>
60 /// <param name="outPutFilePath">输出路径</param>
61 public ExcelHelper(string reportModelPath, string outPutFilePath)
62 {
63 //路径验证
64 if (null == reportModelPath || ("").Equals(reportModelPath))
65 throw new Exception("报表模板路径不能为空!");
66 if (null == outPutFilePath || ("").Equals(outPutFilePath))
67 throw new Exception("输出路径不能为空!");
68 if (!File.Exists(reportModelPath))
69 throw new Exception("报表模板路径不存在!");
70
71 //设置路径值
72 this.ReportModelPath = reportModelPath;
73 this.OutPutFilePath = outPutFilePath;
74
75 //创建一个应用程序对象
76 app = new Excel.ApplicationClass();
77
78 //打开模板文件,获取WorkBook对象
79 workBook = app.Workbooks.Open(reportModelPath, missing, missing, missing, missing, missing, missing,
80 missing, missing, missing, missing, missing, missing);
81
82 //得到WorkSheet对象
83 workSheet = workBook.Sheets.get_Item(1) as Excel.Worksheet;
84 }
85
86 /// <summary>
87 /// 给单元格设值
88 /// </summary>
89 /// <param name="rowIndex">行索引</param>
90 /// <param name="colIndex">列索引</param>
91 /// <param name="content">填充的内容</param>
92 public void SetCells(int rowIndex,int colIndex,object content)
93 {
94 if (null != content)
95 {
96 content = content.ToString();
97 }
98 else
99 {
100 content = string.Empty;
101 }
102
103 try
104 {
105 workSheet.Cells[rowIndex, colIndex] = content;
106 }
107 catch
108 {
109 GC();
110 throw new Exception("向单元格[" + rowIndex + "," + colIndex + "]写数据出错!");
111 }
112 }
113
114 /// <summary>
115 /// 保存文件
116 /// </summary>
117 public void SaveFile()
118 {
119 try
120 {
121 workBook.SaveAs(outPutFilePath, missing, missing, missing, missing, missing,
122 Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
123 }
124 catch
125 {
126 throw new Exception("保存至文件失败!");
127 }
128 finally
129 {
130 Dispose();
131 }
132 }
133
134 /// <summary>
135 /// 垃圾回收处理
136 /// </summary>
137 protected void GC()
138 {
139 if (null != app)
140 {
141 int generation = 0;
142 System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
143
144 generation = System.GC.GetGeneration(app);
145 System.GC.Collect(generation);
146 app = null;
147 missing = null;
148 }
149 }
150
151 /// <summary>
152 /// 释放资源
153 /// </summary>
154 protected void Dispose()
155 {
156 workBook.Close(null, null, null);
157 app.Workbooks.Close();
158 app.Quit();
159
160 if (null != workSheet)
161 {
162 System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
163 workSheet = null;
164 }
165 if (workBook != null)
166 {
167 System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
168 workBook = null;
169 }
170 if (app != null)
171 {
172 int generation = 0;
173 System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
174 generation = System.GC.GetGeneration(app);
175 System.GC.Collect(generation);
176 app = null;
177 missing = null;
178 }
179 }
180 }