Summarize C# Control Excel Skills（C#操作Excel技巧总结）

最新推荐文章于 2023-10-07 17:03:21 发布

翻译最新推荐文章于 2023-10-07 17:03:21 发布 · 1.3k 阅读

C# 专栏收录该内容

30 篇文章

订阅专栏

原文地址链接：

http://www.codeproject.com/Articles/198572/Summarize-C-Control-Excel-Skills

示例代码下载地址：

0、引入命名空间。

using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;
using System.IO;
using System.Reflection;

1、打开或创建一个Excel文件。

Application app = new Application();
Workbooks wbks = app.Workbooks;
_Workbook _wbk = wbks.Add(xxx);

注意：如果要打开一个已经存在的Excel文件，则使用Excel的完整文件名（包括完整路径）替换XXX。

如果要创建一个新的Excel文件，xxx替换为true。

Excel文件中只有一个工作表worksheet。

PS：对象层级，Office应用程序->Excel应用程序->Excel文件->工作薄->工作表

2、获取、添加、删除Excel工作表。

Sheets shs = _wbk.Sheets;

2.1、获取

//i is index of the sheet which you want get:
_Worksheet _wsh = (_Worksheet)shs.get_Item(i)

2.2、删除

//must do when delete
app.DisplayAlerts = false;
_wsh.Delete();

2.3、添加

//a(before),b(after):Besure position;c:amount;d:type
app.Worksheets.Add(a,b,c,d);

2.4、重命名

_wsh.Name = "xxx";

3、删除行或列。

3.1、删除行

((Range)_wsh.Rows[3, Missing.Value]).Delete(XlDeleteShiftDirection.xlShiftUp);

3.2、删除列

_wsh.get_Range(
_wsh.Cells[1, 2],
_wsh.Cells[_wsh.Rows.Count, 2]).Delete(XlDeleteShiftDirection.xlShiftToLeft
);

4、添加行或列。

4.1、添加行

((Range)_wsh.Rows[11, Missing.Value])
.Insert(Missing.Value, XlInsertFormatOrigin.xlFormatFromLeftOrAbove);

4.2、添加列

_wsh.get_Range(
_wsh.Cells[1, 1], _wsh.Cells[_wsh.Rows.Count, 1])
.Insert(Missing.Value, XlInsertShiftDirection.xlShiftToRight);

5、设置单元格格式。

5.1、获取单元格

//Get cell object
_wsh.Cells[row, cell]

5.2、设置公式

//input formula in the cell
_wsh.Cells[row, cell] = "=Sum(A1/B1)";

5.3、合并单元格

((Range)_wsh.Rows[1, Missing.Value]).Merge(Missing.Value);

5.4、设置行高或列宽

((Range)_wsh.Rows[3, Missing.Value]).RowHeight = 5;
((Range)_wsh.Rows[3, Missing.Value]).ColumnWidth = 5;

5.5、设置单元格颜色（56种，依附于颜色表Color Table对象）

((Range)_wsh.Rows[1, Missing.Value]).Interior.ColorIndex = 3;

5.6、设置字体大小

((Range)_wsh.Cells[1, "B"]).Font.Size = 8;

5.7、设置加粗

((Range)_wsh.Rows[1, Missing.Value]).Font.Bold = false;

5.8、设置水平对齐方式

((Range)_wsh.Cells[2, 1]).HorizontalAlignment = XlVAlign.xlVAlignCenter;

5.9、设置边框

((Range)_wsh.Cells[3, 3]).Borders.LineStyle = 3;

5.10、设置上、下、左、右边框线形

//Left side border line
_wsh.get_Range(
_wsh.Cells[2, 1], _wsh.Cells[2, 2])
.Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;//
	 
//Right side border line
_wsh.get_Range(
_wsh.Cells[2, 1], _wsh.Cells[2, 2])
.Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//
	 
//Upside border line
_wsh.get_Range(
_wsh.Cells[2, 1], _wsh.Cells[2, 2])
.Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;//?
	 
//Downside border line
_wsh.get_Range(
_wsh.Cells[2, 1], _wsh.Cells[2, 2])
.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;

6、复制选中的区域

_Worksheet _wsh = (_Worksheet)shs.get_Item(1);// Copy selected field content
	 
Range range = _wsh.get_Range(_wsh.Cells[7, 1], _wsh.Cells[10, _wsh.Columns.Count]);
	
range.Select();
range.Copy(Type.Missing);
	 
//Select paste starting position
Range test = ((Range)_wsh.Cells[11, 1]);
test.Select();
	 
// Shield Alert, default confirm paste
app.DisplayAlerts = false;
test.Parse(Missing.Value, Missing.Value);

Note: Type Missing and Missing Value are considered as some parameters' default value. Most of time, they are form complemented parameters.

7、导出数据库到Excel文件。

Click here to know How to export Database to Excel.

Code from Free Data Export Component.

Friendly Reminder:This Free Data Export component has some limits. Before use it, conside those limits into your project.

8、保存文件和后续处理。

8.1、保存文件

// Shield Alert popped up from system
app.AlertBeforeOverwriting = false;
	 
// Save to selected file path
SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, 
Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, 
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

注意：这是保存Excel文件的唯一一种解决方案。否则会在“我的文档”下生成一个副本文件。

8.2、后续处理（退出和释放资源）

//_wbk.Close(null, null, null);
//wbks.Close();
app.Quit();
	 
// Release unnecessary excel processes
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;

注意：在关闭应用程序的过程中，有两种可行操作。

（1）直接关闭应用程序；（2）关闭工作表、关闭工作薄（Excel文档）、退出应用程序。

推荐使用第二种方式。

9、设置或获取单元格的域值。

9.1、如果单元格被设置为下拉列表格式

// Here the “1, 2, 3” means the drop-down box value
 ((Range)_wsh.Cells[2, 1])
.Validation.Modify(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, 
	Type.Missing, "1,2,3", Type.Missing);

9.2、单元格为普通格式

((Range)_wsh.Cells[2, 1])
.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, 
	Type.Missing,"1,2,3", Type.Missing);

9.3、获取下拉列表值

string strValue = ((Range)_wsh.Cells[2, 1]).Validation.Formula1;

Note: In the excel template, if set drop-down box value through effectiveness, strValue will get the formula of excel, which need be converted. After get strValue, you can get the numerical value according to index.

10、隐藏行或列。

10.1、隐藏行

_wsh.get_Range(_wsh.Cells[19, 1], _wsh.Cells[22, 1]).EntireRow.Hidden = true;

10.2、隐藏列

_wsh.get_Range(_wsh.Cells[1, 1], _wsh.Cells[_wsh.Rows.Count, 1])
.EntireColumn.Hidden = true;

PS：Excel颜色表。

Index	Color
1
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