使用C#操作MS-Excel表格COM
1
使用C#操作MS-Excel表格
2
3
最近用C#写了一个有限元的计算程序,其中涉及到大量数据的输入输出问题。由于其中数据不仅量大,而且数据项比较杂,使用一般的txt和dat文件操作起来比较麻烦,不直观,不便于手动修改,而且不数据文件页不具有通用性,于是想到使用界面友好的Excel电子表格来编写数据文件,通过查阅相关文档,尝试了一下,发现效果还不错。其使用方法如下:
4
5
1.添加引用
6
使用Visual Studio .net 添加两个COM组件:
7
Microsoft Excel 12.0 Object Library
8
Microsoft Office 12.0 Object Library
9
10
2.添加命名空间:
11
using Microsoft.Office.Interop.Excel ;
12
using Microsoft.Office.Core;
13
14
3.用到的一些基本对象和方法,属性:
15
//创建Excel应用程序对象
16
Application xlApp = new Application();
17
18
//获取工作簿集合
19
Workbooks xlWorkbooks = xlApp.Workbooks;
20![](/Images/OutliningIndicators/None.gif)
21
Workbook xlBook, xlResBook;
22
Worksheet xlSheet, xlResSheet;
23
//打开已有的数据文件
24
xlBook = ExcelTools.OpenWorkbook(xlWorkbooks, dataFileDir,true );
25
xlSheet = (Worksheet)xlBook.Sheets["sheet1"];
26![](/Images/OutliningIndicators/None.gif)
27
//新建一个工作簿
28
xlResBook = xlWorkbooks.Add(XlWBATemplate.xlWBATWorksheet);
29
xlResSheet =(Worksheet)xlResBook.Sheets["sheet1"];
30
31
//从Excel表格中读取数据值,其中ExcelTools为自己手动编写的Excel工具箱类,见后文
32
int number = ExcelTools.ReadInt(xlSheet, RowIndex, 4);
33
double x = ExcelTools.ReadDouble(xlSheet, RowIndex, 5);
34
35
//获取单元格(区域)对象
36
Range rng = xlResSheet.get_Range(xlResSheet.Cells[2, 1], xlResSheet.Cells[2, 3]);
37
rng.Merge(Type.Missing); //合并单元格
38
rng.Value2 = "节点位移";
39
rng.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
40
//为单元格赋值
41
ExcelTools.WriteToCell(xlResSheet, 3, 1, "节点编号");
42
ExcelTools.WriteToCell(xlResSheet, 3, 2, "x-Disp");
43
ExcelTools.WriteToCell(xlResSheet, 3, 3, "y-Disp");
44
45
rng.Columns.AutoFit(); //列宽自动调整
46
47
//清理应用程序对象
48
xlSheet = null;
49
ExcelTools.CloseWorkbook(xlBook, false);
50
xlBook = null;
51
xlResSheet = null;
52
ExcelTools.SaveWorkbookAs(xlResBook, currentDir+"\\ResultData");
53
ExcelTools.CloseWorkbook(xlResBook, true);
54
xlResBook = null;
55
xlApp.Quit(); //关闭Excel程序
56
xlApp = null;
57
Console.WriteLine("\n按任意键结束本程序,从ResultData.xls数据文件中察看计算结果。");
58
Console.ReadKey();
59
60
4.Excel工具类:
61
由于MS-Office对象模型是为VBA设计的,其中的很多方法要使用缺省参数,并且有些方法的缺省参数有十多个,而C#不支持缺省参数,只能使用System.Type.Missing来代替,当遇到那些缺省参数很多的方法时,代码写起来比较罗嗦。为简化这些方法的使用,最好是自己手动为一些常用方法编写自己的简化的包装方法:
62
63
///
64
/// 用于简化Excel操作的Excel工具类
65
///
66
static class ExcelTools
67
{
68
///
69
/// 读取单元格中的整型值
70
///
71
/// 工作表
72
/// 单元格行索引
73
/// 单元格列索引
74
/// 返回单元格整型值
75
public static int ReadInt(Worksheet xlSheet, int rowIndex, int colIndex)
76
{
77
Range rng = (Range)xlSheet.Cells[rowIndex, colIndex];
78
int data = int.Parse((rng.Value2).ToString());
79
return data;
80
}
81
///
82
/// 读取单元格中的单精度值
83
///
84
/// 工作表
85
/// 单元格行索引
86
/// 单元格列索引
87
/// 返回单元格单精度值
88
public static float ReadFloat(Worksheet xlSheet, int rowIndex, int colIndex)
89
{
90
Range rng = (Range)xlSheet.Cells[rowIndex, colIndex];
91
float data =float.Parse((rng.Value2).ToString());
92
return data;
93
}
94
///
95
/// 读取单元格中的双精度值
96
///
97
/// 工作表
98
/// 单元格行索引
99
/// 单元格列索引
100
/// 返回单元格双精度值
101
public static double ReadDouble(Worksheet xlSheet, int rowIndex, int colIndex)
102
{
103
Range rng = (Range)xlSheet.Cells[rowIndex, colIndex];
104
double data = double.Parse((rng.Value2).ToString());
105
return data;
106
}
107
///
108
/// 将对象信息写入Excel单元格中
109
///
110
/// 工作表对象
111
/// 单元格行索引
112
/// 单元格列索引
113
/// 所要写入单元格的对象
114
public static void WriteToCell(Worksheet xlSheet, int rowIndex, int colIndex,
115
object objectWriteToCell)
116
{
117
Range rng = (Range)xlSheet.Cells[rowIndex, colIndex];
118
rng.Value2 = objectWriteToCell;
119
}
120
///
121
/// 用于打开工作簿的方法
122
///
123
/// 工作簿集合对象
124
/// 工作簿文件路径
125
/// 以只读方式打开
126
/// 工作簿对象
127
public static Workbook OpenWorkbook(Workbooks xlWorkbooks, string fileDirection,
128
bool readOnly)
129
{
130
Workbook xlBook=null;
131
try
132
{
133
xlBook = xlWorkbooks.Open(fileDirection,
134
Type.Missing, readOnly, Type.Missing, Type.Missing,
135
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
136
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
137
Type.Missing, Type.Missing);
138
}
139
catch (System.Exception )
140
{
141
throw;
142
}
143
return xlBook;
144
}
145
///
146
/// 保存工作簿文件
147
///
148
/// 工作簿对象
149
/// 工作簿文件保存路径
150
public static void SaveWorkbookAs(Workbook xlWorkbook,string fileDirection)
151
{
152
xlWorkbook.SaveAs(fileDirection, XlFileFormat.xlWorkbookNormal, Type.Missing,
153
Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange,
154
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
155
}
156
///
157
/// 关闭工作簿文件
158
///
159
/// 工作簿对象
160
/// 保存对工作簿的修改
161
public static void CloseWorkbook(Workbook xlWorkbook,bool saveChanges)
162
{
163
xlWorkbook.Close(saveChanges, Type.Missing, Type.Missing);
164
}
165
}
166
167
5.对于Excel中一些常用的操作,比如合并单元格,要想在Excel对象模型和相关文档中找到其对应的方法不太容易,我找到了解决这个问题的一种比较简单的方法:借助于VBA宏。可以先在Excel中录制一段VBA宏,然后查看相应的宏代码,就可以轻松的找到相关对象和方法了,(学C#正是爽,VB中的东西基本上可以直接搬过来,哈哈)
168
169
6.不足之处:
170
程序在读写Excel文件的时候速度比较慢,不知道有没有相关的优化方法,寻求中……
171
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
17
![](/Images/OutliningIndicators/None.gif)
18
![](/Images/OutliningIndicators/None.gif)
19
![](/Images/OutliningIndicators/None.gif)
20
![](/Images/OutliningIndicators/None.gif)
21
![](/Images/OutliningIndicators/None.gif)
22
![](/Images/OutliningIndicators/None.gif)
23
![](/Images/OutliningIndicators/None.gif)
24
![](/Images/OutliningIndicators/None.gif)
25
![](/Images/OutliningIndicators/None.gif)
26
![](/Images/OutliningIndicators/None.gif)
27
![](/Images/OutliningIndicators/None.gif)
28
![](/Images/OutliningIndicators/None.gif)
29
![](/Images/OutliningIndicators/None.gif)
30
![](/Images/OutliningIndicators/None.gif)
31
![](/Images/OutliningIndicators/None.gif)
32
![](/Images/OutliningIndicators/None.gif)
33
![](/Images/OutliningIndicators/None.gif)
34
![](/Images/OutliningIndicators/None.gif)
35
![](/Images/OutliningIndicators/None.gif)
36
![](/Images/OutliningIndicators/None.gif)
37
![](/Images/OutliningIndicators/None.gif)
38
![](/Images/OutliningIndicators/None.gif)
39
![](/Images/OutliningIndicators/None.gif)
40
![](/Images/OutliningIndicators/None.gif)
41
![](/Images/OutliningIndicators/None.gif)
42
![](/Images/OutliningIndicators/None.gif)
43
![](/Images/OutliningIndicators/None.gif)
44
![](/Images/OutliningIndicators/None.gif)
45
![](/Images/OutliningIndicators/None.gif)
46
![](/Images/OutliningIndicators/None.gif)
47
![](/Images/OutliningIndicators/None.gif)
48
![](/Images/OutliningIndicators/None.gif)
49
![](/Images/OutliningIndicators/None.gif)
50
![](/Images/OutliningIndicators/None.gif)
51
![](/Images/OutliningIndicators/None.gif)
52
![](/Images/OutliningIndicators/None.gif)
53
![](/Images/OutliningIndicators/None.gif)
54
![](/Images/OutliningIndicators/None.gif)
55
![](/Images/OutliningIndicators/None.gif)
56
![](/Images/OutliningIndicators/None.gif)
57
![](/Images/OutliningIndicators/None.gif)
58
![](/Images/OutliningIndicators/None.gif)
59
![](/Images/OutliningIndicators/None.gif)
60
![](/Images/OutliningIndicators/None.gif)
61
![](/Images/OutliningIndicators/None.gif)
62
![](/Images/OutliningIndicators/None.gif)
63
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
64
![](/Images/OutliningIndicators/InBlock.gif)
65
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
66
![](/Images/OutliningIndicators/None.gif)
67
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
68
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
69
![](/Images/OutliningIndicators/InBlock.gif)
70
![](/Images/OutliningIndicators/InBlock.gif)
71
![](/Images/OutliningIndicators/InBlock.gif)
72
![](/Images/OutliningIndicators/InBlock.gif)
73
![](/Images/OutliningIndicators/InBlock.gif)
74
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
75
![](/Images/OutliningIndicators/InBlock.gif)
76
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
77
![](/Images/OutliningIndicators/InBlock.gif)
78
![](/Images/OutliningIndicators/InBlock.gif)
79
![](/Images/OutliningIndicators/InBlock.gif)
80
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
81
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
82
![](/Images/OutliningIndicators/InBlock.gif)
83
![](/Images/OutliningIndicators/InBlock.gif)
84
![](/Images/OutliningIndicators/InBlock.gif)
85
![](/Images/OutliningIndicators/InBlock.gif)
86
![](/Images/OutliningIndicators/InBlock.gif)
87
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
88
![](/Images/OutliningIndicators/InBlock.gif)
89
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
90
![](/Images/OutliningIndicators/InBlock.gif)
91
![](/Images/OutliningIndicators/InBlock.gif)
92
![](/Images/OutliningIndicators/InBlock.gif)
93
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
94
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
95
![](/Images/OutliningIndicators/InBlock.gif)
96
![](/Images/OutliningIndicators/InBlock.gif)
97
![](/Images/OutliningIndicators/InBlock.gif)
98
![](/Images/OutliningIndicators/InBlock.gif)
99
![](/Images/OutliningIndicators/InBlock.gif)
100
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
101
![](/Images/OutliningIndicators/InBlock.gif)
102
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
103
![](/Images/OutliningIndicators/InBlock.gif)
104
![](/Images/OutliningIndicators/InBlock.gif)
105
![](/Images/OutliningIndicators/InBlock.gif)
106
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
107
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
108
![](/Images/OutliningIndicators/InBlock.gif)
109
![](/Images/OutliningIndicators/InBlock.gif)
110
![](/Images/OutliningIndicators/InBlock.gif)
111
![](/Images/OutliningIndicators/InBlock.gif)
112
![](/Images/OutliningIndicators/InBlock.gif)
113
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
114
![](/Images/OutliningIndicators/InBlock.gif)
115
![](/Images/OutliningIndicators/InBlock.gif)
116
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
117
![](/Images/OutliningIndicators/InBlock.gif)
118
![](/Images/OutliningIndicators/InBlock.gif)
119
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
120
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
121
![](/Images/OutliningIndicators/InBlock.gif)
122
![](/Images/OutliningIndicators/InBlock.gif)
123
![](/Images/OutliningIndicators/InBlock.gif)
124
![](/Images/OutliningIndicators/InBlock.gif)
125
![](/Images/OutliningIndicators/InBlock.gif)
126
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
127
![](/Images/OutliningIndicators/InBlock.gif)
128
![](/Images/OutliningIndicators/InBlock.gif)
129
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
130
![](/Images/OutliningIndicators/InBlock.gif)
131
![](/Images/OutliningIndicators/InBlock.gif)
132
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
133
![](/Images/OutliningIndicators/InBlock.gif)
134
![](/Images/OutliningIndicators/InBlock.gif)
135
![](/Images/OutliningIndicators/InBlock.gif)
136
![](/Images/OutliningIndicators/InBlock.gif)
137
![](/Images/OutliningIndicators/InBlock.gif)
138
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
139
![](/Images/OutliningIndicators/InBlock.gif)
140
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
141
![](/Images/OutliningIndicators/InBlock.gif)
142
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
143
![](/Images/OutliningIndicators/InBlock.gif)
144
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
145
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
146
![](/Images/OutliningIndicators/InBlock.gif)
147
![](/Images/OutliningIndicators/InBlock.gif)
148
![](/Images/OutliningIndicators/InBlock.gif)
149
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
150
![](/Images/OutliningIndicators/InBlock.gif)
151
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
152
![](/Images/OutliningIndicators/InBlock.gif)
153
![](/Images/OutliningIndicators/InBlock.gif)
154
![](/Images/OutliningIndicators/InBlock.gif)
155
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
156
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
157
![](/Images/OutliningIndicators/InBlock.gif)
158
![](/Images/OutliningIndicators/InBlock.gif)
159
![](/Images/OutliningIndicators/InBlock.gif)
160
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
161
![](/Images/OutliningIndicators/InBlock.gif)
162
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
163
![](/Images/OutliningIndicators/InBlock.gif)
164
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
165
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
166
![](/Images/OutliningIndicators/None.gif)
167
![](/Images/OutliningIndicators/None.gif)
168
![](/Images/OutliningIndicators/None.gif)
169
![](/Images/OutliningIndicators/None.gif)
170
![](/Images/OutliningIndicators/None.gif)
171
![](/Images/OutliningIndicators/None.gif)