为简化使用Excel COM写的类

经常需要在开发中使用Excel COM，为简化使用写了这个类，感觉还是不太方便。

using System;
2

namespace ExcelHandle
4

{
5

/// <summary>
6

/// ExcelHandle 的摘要说明。
7

/// </summary>
8

public class ExcelHandle
9

{
10

/// <summary>
12

/// Excel
13

/// </summary>
14

public Excel.Application CurExcel = null;
15

/// <summary>
17

/// 工作簿
18

/// </summary>
19

public Excel._Workbook CurBook = null;
20

/// <summary>
22

/// 工作表
23

/// </summary>
24

public Excel._Worksheet CurSheet = null;
25

private object mValue = System.Reflection.Missing.Value;
27

/// <summary>
29

/// 构造函数
30

/// </summary>
31

public ExcelHandle()
32

{
33

//
34

// TODO: 在此处添加构造函数逻辑
35

//
36

this.dtBefore = System.DateTime.Now;
38

CurExcel = new Excel.Application();
40

this.dtAfter = System.DateTime.Now;
42

this.timestamp = System.DateTime.Now.ToShortDateString().Replace("-", "") + System.DateTime.Now.ToShortTimeString().Replace(":", "") + System.DateTime.Now.Second.ToString() + System.DateTime.Now.Millisecond.ToString();
44

}
46

/// <summary>
48

/// 构造函数
49

/// </summary>
50

/// <param name="strFilePath">加载的Excel文件名</param>
51

public ExcelHandle(string strFilePath)
52

{
53

this.dtBefore = System.DateTime.Now;
55

CurExcel = new Excel.Application();
57

this.dtAfter = System.DateTime.Now;
59

CurBook = (Excel._Workbook)CurExcel.Workbooks.Open(strFilePath, mValue, false, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue);
61

}
65

/// <summary>
67

/// 释放内存空间
68

/// </summary>
69

public void Dispose()
70

{
71

try
72

{
73

System.Runtime.InteropServices.Marshal.ReleaseComObject(CurSheet);
74

CurSheet = null;
75

CurBook.Close(false, mValue, mValue);
77

System.Runtime.InteropServices.Marshal.ReleaseComObject(CurBook);
78

CurBook = null;
79

CurExcel.Quit();
81

System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel);
82

CurExcel = null;
83

GC.Collect();
85

GC.WaitForPendingFinalizers();
86

}
88

catch(System.Exception ex)
89

{
90

this.MessageWarning("在释放Excel内存空间时发生了一个错误：", ex);
91

}
92

finally
93

{
94

foreach(System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel"))
95

if (pro.StartTime > this.dtBefore && pro.StartTime < this.dtAfter)
96

pro.Kill();
97

}
98

System.GC.SuppressFinalize(this);
99

}
100

101

102

private string filepath;
103

private string timestamp;
104

private System.DateTime dtBefore;
105

private System.DateTime dtAfter;
106

107

108

/// <summary>
109

/// Excel文件名
110

/// </summary>
111

public string FilePath
112

{
113

get
114

{
115

return this.filepath;
116

}
117

set
118

{
119

this.filepath = value;
120

}
121

}
122

123

/// <summary>
124

/// 是否打开Excel界面
125

/// </summary>
126

public bool Visible
127

{
128

set
129

{
130

CurExcel.Visible = value;
131

}
132

}
133

134

/// <summary>
135

/// 以时间字符串作为保存文件的名称
136

/// </summary>
137

public string TimeStamp
138

{
139

get
140

{
141

return this.timestamp;
142

}
143

set
144

{
145

this.timestamp = value;
146

}
147

}
148

149

150

/// <summary>
151

/// 加载Excel文件
152

/// </summary>
153

public void Load()
154

{
155

if (CurBook == null && this.filepath != null)
156

CurBook = (Excel._Workbook)CurExcel.Workbooks.Open(this.filepath, mValue, false, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue);
157

}
158

159

/// <summary>
160

/// 加载Excel文件
161

/// </summary>
162

/// <param name="strFilePath">Excel文件名</param>
163

public void Load(string strFilePath)
164

{
165

if (CurBook == null)
166

CurBook = (Excel._Workbook)CurExcel.Workbooks.Open(strFilePath, mValue, false, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue);
167

}
168

169

/// <summary>
170

/// 新建工作表
171

/// </summary>
172

/// <param name="strWorkSheetName">工作表名称</param>
173

public void NewWorkSheet(string strWorkSheetName)
174

{
175

CurSheet = (Excel._Worksheet)CurBook.Sheets.Add(CurBook.Sheets[1], mValue, mValue, mValue);
176

CurSheet.Name = strWorkSheetName;
177

}
178

179

/// <summary>
180

/// 在指定单元格插入指定的值
181

/// </summary>
182

/// <param name="strCell">单元格，如“A4”</param>
183

/// <param name="objValue">文本、数字等值</param>
184

public void WriteCell(string strCell, object objValue)
185

{
186

CurSheet.get_Range(strCell, mValue).Value2 = objValue;
187

}
188

189

/// <summary>
190

/// 在指定Range中插入指定的值
191

/// </summary>
192

/// <param name="strStartCell">Range的开始单元格</param>
193

/// <param name="strEndCell">Range的结束单元格</param>
194

/// <param name="objValue">文本、数字等值</param>
195

public void WriteRange(string strStartCell, string strEndCell, object objValue)
196

{
197

CurSheet.get_Range(strStartCell, strEndCell).Value2 = objValue;
198

}
199

200

201

/// <summary>
202

/// 合并单元格，并在合并后的单元格中插入指定的值
203

/// </summary>
204

/// <param name="strStartCell"></param>
205

/// <param name="strEndCell"></param>
206

/// <param name="objValue"></param>
207

public void WriteAfterMerge(string strStartCell, string strEndCell, object objValue)
208

{
209

CurSheet.get_Range(strStartCell, strEndCell).Merge(mValue);
210

CurSheet.get_Range(strStartCell, mValue).Value2 = objValue;
211

}
212

213

/// <summary>
214

/// 在连续单元格中插入一个DataTable中的值
215

/// </summary>
216

/// <param name="strStartCell">开始的单元格</param>
217

/// <param name="dtData">存储数据的DataTable</param>
218

public void WriteTable(string strStartCell, System.Data.DataTable dtData)
219

{
220

object[,] arrData = new object[dtData.Rows.Count, dtData.Columns.Count];
221

222

for (int i = 0; i < dtData.Rows.Count; i ++)
223

for (int j = 0; j < dtData.Columns.Count; j ++)
224

arrData[i, j] = dtData.Rows[i][j];
225

226

CurSheet.get_Range(strStartCell, this.GetEndCell(strStartCell, dtData.Rows.Count - 1, dtData.Columns.Count - 1)).Value2 = arrData;
227

228

arrData = null;
229

}
230

231

/// <summary>
232

/// 在连续单元格中插入一个DataTable并作超级链接
233

/// </summary>
234

/// <param name="strStartCell">起始单元格标识符</param>
235

/// <param name="dtData">存储数据的DataTable</param>
236

/// <param name="strLinkField">链接的地址字段</param>
237

/// <param name="strTextField">链接的文本字段</param>
238

public void WriteTableAndLink(string strStartCell, System.Data.DataTable dtData, string strLinkField, string strTextField)
239

{
240

object[,] arrData = new object[dtData.Rows.Count, dtData.Columns.Count - 1];
241

242

for (int i = 0; i < dtData.Rows.Count; i ++)
243

{
244

for (int j = 0; j < dtData.Columns.Count; j ++)
245

{
246

if (j > dtData.Columns.IndexOf(strLinkField))
247

arrData[i, j - 1] = dtData.Rows[i][j];
248

else if (j < dtData.Columns.IndexOf(strLinkField))
249

arrData[i, j] = dtData.Rows[i][j];
250

}
251

}
252

253

CurSheet.get_Range(strStartCell, this.GetEndCell(strStartCell, dtData.Rows.Count - 1, dtData.Columns.Count - 2)).Value2 = arrData;
254

255

for (int i = 0; i < dtData.Rows.Count; i ++)
256

this.AddHyperLink(this.NtoL(this.LtoN(this.GetCellLetter(strStartCell)) + dtData.Columns.IndexOf(strTextField)) + System.Convert.ToString(this.GetCellNumber(strStartCell) + i), dtData.Rows[i][strLinkField].ToString() + ".htm", "点击查看详细", dtData.Rows[i][strTextField].ToString());
257

258

arrData = null;
259

}
260

261

/// <summary>
262

/// 为单元格设置公式
263

/// </summary>
264

/// <param name="strCell">单元格标识符</param>
265

/// <param name="strFormula">公式</param>
266

public void SetFormula(string strCell, string strFormula)
267

{
268

CurSheet.get_Range(strCell, mValue).Formula = strFormula;
269

}
270

271

/// <summary>
272

/// 设置单元格或连续区域的字体为黑体
273

/// </summary>
274

/// <param name="strCell">单元格标识符</param>
275

public void SetBold(string strCell)
276

{
277

CurSheet.get_Range(strCell, mValue).Font.Bold = true;
278

}
279

280

/// <summary>
281

/// 设置连续区域的字体为黑体
282

/// </summary>
283

/// <param name="strStartCell">开始单元格标识符</param>
284

/// <param name="strEndCell">结束单元格标识符</param>
285

public void SetBold(string strStartCell, string strEndCell)
286

{
287

CurSheet.get_Range(strStartCell, strEndCell).Font.Bold = true;
288

}
289

290

/// <summary>
291

/// 设置单元格或连续区域的字体颜色
292

/// </summary>
293

/// <param name="strCell">单元格标识符</param>
294

/// <param name="clrColor">颜色</param>
295

public void SetColor(string strCell, System.Drawing.Color clrColor)
296

{
297

CurSheet.get_Range(strCell, mValue).Font.Color = System.Drawing.ColorTranslator.ToOle(clrColor);
298

}
299

300

/// <summary>
301

/// 设置连续区域的字体颜色
302

/// </summary>
303

/// <param name="strStartCell">开始单元格标识符</param>
304

/// <param name="strEndCell">结束单元格标识符</param>
305

/// <param name="clrColor">颜色</param>
306

public void SetColor(string strStartCell, string strEndCell, System.Drawing.Color clrColor)
307

{
308

CurSheet.get_Range(strStartCell, strEndCell).Font.Color = System.Drawing.ColorTranslator.ToOle(clrColor);
309

}
310

311

/// <summary>
312

/// 设置单元格或连续区域的边框：上下左右都为黑色连续边框
313

/// </summary>
314

/// <param name="strCell">单元格标识符</param>
315

public void SetBorderAll(string strCell)
316

{
317

CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
318

CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
319

320

CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
321

CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
322

323

CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
324

CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
325

326

CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
327

CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
328

329

CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
330

CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
331

332

CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
333

CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
334

335

336

}
337

338

/// <summary>
339

/// 设置连续区域的边框：上下左右都为黑色连续边框
340

/// </summary>
341

/// <param name="strStartCell">开始单元格标识符</param>
342

/// <param name="strEndCell">结束单元格标识符</param>
343

public void SetBorderAll(string strStartCell, string strEndCell)
344

{
345

CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
346

CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
347

348

CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
349

CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
350

351

CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
352

CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
353

354

CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
355

CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
356

357

CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
358

CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
359

360

CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
361

CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
362

}
363

364

/// <summary>
365

/// 设置单元格或连续区域水平居左
366

/// </summary>
367

/// <param name="strCell">单元格标识符</param>
368

public void SetHAlignLeft(string strCell)
369

{
370

CurSheet.get_Range(strCell, mValue).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
371

}
372

373

/// <summary>
374

/// 设置连续区域水平居左
375

/// </summary>
376

/// <param name="strStartCell">开始单元格标识符</param>
377

/// <param name="strEndCell">结束单元格标识符</param>
378

public void SetHAlignLeft(string strStartCell, string strEndCell)
379

{
380

CurSheet.get_Range(strStartCell, strEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
381

}
382

383

/// <summary>
384

/// 设置单元格或连续区域水平居左
385

/// </summary>
386

/// <param name="strCell">单元格标识符</param>
387

public void SetHAlignCenter(string strCell)
388

{
389

CurSheet.get_Range(strCell, mValue).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
390

}
391

392

/// <summary>
393

/// 设置连续区域水平居中
394

/// </summary>
395

/// <param name="strStartCell">开始单元格标识符</param>
396

/// <param name="strEndCell">结束单元格标识符</param>
397

public void SetHAlignCenter(string strStartCell, string strEndCell)
398

{
399

CurSheet.get_Range(strStartCell, strEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
400

}
401

402

/// <summary>
403

/// 设置单元格或连续区域水平居右
404

/// </summary>
405

/// <param name="strCell">单元格标识符</param>
406

public void SetHAlignRight(string strCell)
407

{
408

CurSheet.get_Range(strCell, mValue).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
409

}
410

411

/// <summary>
412

/// 设置连续区域水平居右
413

/// </summary>
414

/// <param name="strStartCell">开始单元格标识符</param>
415

/// <param name="strEndCell">结束单元格标识符</param>
416

public void SetHAlignRight(string strStartCell, string strEndCell)
417

{
418

CurSheet.get_Range(strStartCell, strEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
419

}
420

421

/// <summary>
422

/// 设置单元格或连续区域的显示格式
423

/// </summary>
424

/// <param name="strCell">单元格标识符</param>
425

/// <param name="strNF">如"#,##0.00"的显示格式</param>
426

public void SetNumberFormat(string strCell, string strNF)
427

{
428

CurSheet.get_Range(strCell, mValue).NumberFormat = strNF;
429

}
430

431

/// <summary>
432

/// 设置连续区域的显示格式
433

/// </summary>
434

/// <param name="strStartCell">开始单元格标识符</param>
435

/// <param name="strEndCell">结束单元格标识符</param>
436

/// <param name="strNF">如"#,##0.00"的显示格式</param>
437

public void SetNumberFormat(string strStartCell, string strEndCell, string strNF)
438

{
439

CurSheet.get_Range(strStartCell, strEndCell).NumberFormat = strNF;
440

}
441

442

/// <summary>
443

/// 设置单元格或连续区域的字体大小
444

/// </summary>
445

/// <param name="strCell">单元格或连续区域标识符</param>
446

/// <param name="intFontSize"></param>
447

public void SetFontSize(string strCell, int intFontSize)
448

{
449

CurSheet.get_Range(strCell, mValue).Font.Size = intFontSize.ToString();
450

}
451

452

/// <summary>
453

/// 设置连续区域的字体大小
454

/// </summary>
455

/// <param name="strStartCell">开始单元格标识符</param>
456

/// <param name="strEndCell">结束单元格标识符</param>
457

/// <param name="intFontSize">字体大小</param>
458

public void SetFontSize(string strStartCell, string strEndCell, int intFontSize)
459

{
460

CurSheet.get_Range(strStartCell, strEndCell).Font.Size = intFontSize.ToString();
461

}
462

463

/// <summary>
464

/// 设置列宽
465

/// </summary>
466

/// <param name="strColID">列标识，如A代表第一列</param>
467

/// <param name="decWidth">宽度</param>
468

public void SetColumnWidth(string strColID, double dblWidth)
469

{
470

((Excel.Range)CurSheet.Columns.GetType().InvokeMember("Item", System.Reflection.BindingFlags.GetProperty, null, CurSheet.Columns, new object[]{(strColID + ":" + strColID).ToString()})).ColumnWidth = dblWidth;
471

}
472

473

/// <summary>
474

/// 为单元格添加超级链接
475

/// </summary>
476

/// <param name="strCell">单元格标识符</param>
477

/// <param name="strAddress">链接地址</param>
478

/// <param name="strTip">屏幕提示</param>
479

/// <param name="strText">链接文本</param>
480

public void AddHyperLink(string strCell, string strAddress, string strTip, string strText)
481

{
482

CurSheet.Hyperlinks.Add(CurSheet.get_Range(strCell, mValue), strAddress, mValue, strTip, strText);
483

}
484

485

/// <summary>
486

/// 已知开始的单元格标识，求intR行、intColumn列后的单元格标识
487

/// </summary>
488

/// <param name="strStartCell">开始单元格标识</param>
489

/// <param name="intR">行数</param>
490

/// <param name="intC">列数</param>
491

/// <returns>单元格标识符结果</returns>
492

public string GetEndCell(string strStartCell, int intR, int intC)
493

{
494

495

System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"^(?<vLetter>[A-Z]+)(?<vNumber>\d+)");
496

497

return this.NtoL(this.LtoN(regex.Match(strStartCell).Result("${vLetter}")) + intC) + System.Convert.ToString((System.Convert.ToInt32(regex.Match(strStartCell).Result("${vNumber}")) + intR));
498

499

}
500

501

/// <summary>
502

/// 获取单元格标识符中的字母
503

/// </summary>
504

/// <param name="strCell">单元格标识符</param>
505

/// <returns>单元格标识符对应的字母</returns>
506

public string GetCellLetter(string strCell)
507

{
508

System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"^(?<vLetter>[A-Z]+)(?<vNumber>\d+)");
509

return regex.Match(strCell).Result("${vLetter}");
510

}
511

512

/// <summary>
513

/// 获取单元格标识符中的数字
514

/// </summary>
515

/// <param name="strCell">单元格标识符</param>
516

public int GetCellNumber(string strCell)
517

{
518

System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"^(?<vLetter>[A-Z]+)(?<vNumber>\d+)");
519

return System.Convert.ToInt32(regex.Match(strCell).Result("${vNumber}"));
520

}
521

522

523

/// <summary>
524

/// 另存为xls文件
525

/// </summary>
526

/// <param name="strFilePath">文件路径</param>
527

public void Save(string strFilePath)
528

{
529

CurBook.SaveCopyAs(strFilePath);
530

}
531

532

/// <summary>
533

/// 另存为html文件
534

/// </summary>
535

/// <param name="strFilePath">文件路径</param>
536

public void SaveHtml(string strFilePath)
537

{
538

CurBook.SaveAs(strFilePath, Excel.XlFileFormat.xlHtml, mValue, mValue, mValue, mValue, Excel.XlSaveAsAccessMode.xlNoChange, mValue, mValue, mValue, mValue, mValue);
539

}
540

541

public void CreateHtmlFile()
542

{
543

544

}
545

546

547

548

辅助函数辅助函数
615

616

617

618

}
619

}
620

621

posted @ 2005-10-28 19:00 蜡人张阅读(7591) 评论(29) 收藏举报

刷新页面返回顶部

为简化使用Excel COM写的类

公告