C#操作Excel,套用模板并对数据进行分页
1
using System;
2
using System.IO;
3
using System.Data;
4
using System.Reflection;
5
using System.Diagnostics;
6
using cfg = System.Configuration;
7
//using Excel;
8
9
namespace ExcelHelperTest
10
{
11
/// <summary>
12
/// 功能说明:套用模板输出Excel,并对数据进行分页
13
/// 作 者:Lingyun_k
14
/// 创建日期:2005-7-12
15
/// </summary>
16
public class ExcelHelper
17
{
18
protected string templetFile = null;
19
protected string outputFile = null;
20
protected object missing = Missing.Value;
21
22
/// <summary>
23
/// 构造函数,需指定模板文件和输出文件完整路径
24
/// </summary>
25
/// <param name="templetFilePath">Excel模板文件路径</param>
26
/// <param name="outputFilePath">输出Excel文件路径</param>
27
public ExcelHelper(string templetFilePath,string outputFilePath)
28
{
29
if(templetFilePath == null)
30
throw new Exception("Excel模板文件路径不能为空!");
31
32
if(outputFilePath == null)
33
throw new Exception("输出Excel文件路径不能为空!");
34
35
if(!File.Exists(templetFilePath))
36
throw new Exception("指定路径的Excel模板文件不存在!");
37
38
this.templetFile = templetFilePath;
39
this.outputFile = outputFilePath;
40
41
}
42
43
/// <summary>
44
/// 将DataTable数据写入Excel文件(套用模板并分页)
45
/// </summary>
46
/// <param name="dt">DataTable</param>
47
/// <param name="rows">每个WorkSheet写入多少行数据</param>
48
/// <param name="top">行索引</param>
49
/// <param name="left">列索引</param>
50
/// <param name="sheetPrefixName">WorkSheet前缀名,比如:前缀名为“Sheet”,那么WorkSheet名称依次为“Sheet-1,Sheet-2
”</param>
51
public void DataTableToExcel(DataTable dt,int rows,int top,int left,string sheetPrefixName)
52
{
53
int rowCount = dt.Rows.Count; //源DataTable行数
54
int colCount = dt.Columns.Count; //源DataTable列数
55
int sheetCount = this.GetSheetCount(rowCount,rows); //WorkSheet个数
56
DateTime beforeTime;
57
DateTime afterTime;
58
59
if(sheetPrefixName == null || sheetPrefixName.Trim() == "")
60
sheetPrefixName = "Sheet";
61
62
//创建一个Application对象并使其可见
63
beforeTime = DateTime.Now;
64
Excel.Application app = new Excel.ApplicationClass();
65
app.Visible = true;
66
afterTime = DateTime.Now;
67
68
//打开模板文件,得到WorkBook对象
69
Excel.Workbook workBook = app.Workbooks.Open(templetFile,missing,missing,missing,missing,missing,
70
missing,missing,missing,missing,missing,missing,missing);
71
72
//得到WorkSheet对象
73
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
74
75
//复制sheetCount-1个WorkSheet对象
76
for(int i=1;i<sheetCount;i++)
77
{
78
((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing,workBook.Worksheets[i]);
79
}
80
81
#region 将源DataTable数据写入Excel
82
for(int i=1;i<=sheetCount;i++)
83
{
84
int startRow = (i - 1) * rows; //记录起始行索引
85
int endRow = i * rows; //记录结束行索引
86
87
//若是最后一个WorkSheet,那么记录结束行索引为源DataTable行数
88
if(i == sheetCount)
89
endRow = rowCount;
90
91
//获取要写入数据的WorkSheet对象,并重命名
92
Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
93
sheet.Name = sheetPrefixName + "-" + i.ToString();
94
95
//将dt中的数据写入WorkSheet
96
for(int j=0;j<endRow-startRow;j++)
97
{
98
for(int k=0;k<colCount;k++)
99
{
100
sheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString();
101
}
102
}
103
104
//写文本框数据
105
Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes("txtAuthor");
106
Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes("txtDate");
107
Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes("txtVersion");
108
109
txtAuthor.Text = "KLY.NET的Blog";
110
txtDate.Text = DateTime.Now.ToShortDateString();
111
txtVersion.Text = "1.0.0.0";
112
}
113
#endregion
114
115
//输出Excel文件并退出
116
try
117
{
118
workBook.SaveAs(outputFile,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);
119
workBook.Close(null,null,null);
120
app.Workbooks.Close();
121
app.Application.Quit();
122
app.Quit();
123
124
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
125
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
126
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
127
128
workSheet=null;
129
workBook=null;
130
app=null;
131
132
GC.Collect();
133
}
134
catch(Exception e)
135
{
136
throw e;
137
}
138
finally
139
{
140
Process[] myProcesses;
141
DateTime startTime;
142
myProcesses = Process.GetProcessesByName("Excel");
143
144
//得不到Excel进程ID,暂时只能判断进程启动时间
145
foreach(Process myProcess in myProcesses)
146
{
147
startTime = myProcess.StartTime;
148
149
if(startTime > beforeTime && startTime < afterTime)
150
{
151
myProcess.Kill();
152
}
153
}
154
}
155
156
}
157
158
159
/// <summary>
160
/// 获取WorkSheet数量
161
/// </summary>
162
/// <param name="rowCount">记录总行数</param>
163
/// <param name="rows">每WorkSheet行数</param>
164
private int GetSheetCount(int rowCount,int rows)
165
{
166
int n = rowCount % rows; //余数
167
168
if(n == 0)
169
return rowCount / rows;
170
else
171
return Convert.ToInt32(rowCount / rows) + 1;
172
}
173
174
175
/// <summary>
176
/// 将二维数组数据写入Excel文件(套用模板并分页)
177
/// </summary>
178
/// <param name="arr">二维数组</param>
179
/// <param name="rows">每个WorkSheet写入多少行数据</param>
180
/// <param name="top">行索引</param>
181
/// <param name="left">列索引</param>
182
/// <param name="sheetPrefixName">WorkSheet前缀名,比如:前缀名为“Sheet”,那么WorkSheet名称依次为“Sheet-1,Sheet-2
”</param>
183
public void ArrayToExcel(string[,] arr,int rows,int top,int left,string sheetPrefixName)
184
{
185
int rowCount = arr.GetLength(0); //二维数组行数(一维长度)
186
int colCount = arr.GetLength(1); //二维数据列数(二维长度)
187
int sheetCount = this.GetSheetCount(rowCount,rows); //WorkSheet个数
188
DateTime beforeTime;
189
DateTime afterTime;
190
191
if(sheetPrefixName == null || sheetPrefixName.Trim() == "")
192
sheetPrefixName = "Sheet";
193
194
//创建一个Application对象并使其可见
195
beforeTime = DateTime.Now;
196
Excel.Application app = new Excel.ApplicationClass();
197
app.Visible = true;
198
afterTime = DateTime.Now;
199
200
//打开模板文件,得到WorkBook对象
201
Excel.Workbook workBook = app.Workbooks.Open(templetFile,missing,missing,missing,missing,missing,
202
missing,missing,missing,missing,missing,missing,missing);
203
204
//得到WorkSheet对象
205
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
206
207
//复制sheetCount-1个WorkSheet对象
208
for(int i=1;i<sheetCount;i++)
209
{
210
((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing,workBook.Worksheets[i]);
211
}
212
213
#region 将二维数组数据写入Excel
214
for(int i=1;i<=sheetCount;i++)
215
{
216
int startRow = (i - 1) * rows; //记录起始行索引
217
int endRow = i * rows; //记录结束行索引
218
219
//若是最后一个WorkSheet,那么记录结束行索引为源DataTable行数
220
if(i == sheetCount)
221
endRow = rowCount;
222
223
//获取要写入数据的WorkSheet对象,并重命名
224
Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
225
sheet.Name = sheetPrefixName + "-" + i.ToString();
226
227
//将二维数组中的数据写入WorkSheet
228
for(int j=0;j<endRow-startRow;j++)
229
{
230
for(int k=0;k<colCount;k++)
231
{
232
sheet.Cells[top + j,left + k] = arr[startRow + j,k];
233
}
234
}
235
236
Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes("txtAuthor");
237
Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes("txtDate");
238
Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes("txtVersion");
239
240
txtAuthor.Text = "KLY.NET的Blog";
241
txtDate.Text = DateTime.Now.ToShortDateString();
242
txtVersion.Text = "1.0.0.0";
243
}
244
#endregion
245
246
//输出Excel文件并退出
247
try
248
{
249
workBook.SaveAs(outputFile,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);
250
workBook.Close(null,null,null);
251
app.Workbooks.Close();
252
app.Application.Quit();
253
app.Quit();
254
255
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
256
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
257
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
258
259
workSheet=null;
260
workBook=null;
261
app=null;
262
263
GC.Collect();
264
}
265
catch(Exception e)
266
{
267
throw e;
268
}
269
finally
270
{
271
Process[] myProcesses;
272
DateTime startTime;
273
myProcesses = Process.GetProcessesByName("Excel");
274
275
//得不到Excel进程ID,暂时只能判断进程启动时间
276
foreach(Process myProcess in myProcesses)
277
{
278
startTime = myProcess.StartTime;
279
280
if(startTime > beforeTime && startTime < afterTime)
281
{
282
myProcess.Kill();
283
}
284
}
285
}
286
287
}
288
}
289
}
290

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50


51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182


183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290
