用Visual Studio .NET自动化MS Excel
介绍
我写这篇文章是为了回应那些我在的e-mail中得到的要求,就是如何自动化MS Excel。许多正在看这篇文章的人也许也对下面关于如何自动化MS Word文档有兴趣,这儿有链接Automating MS Word using C#, 还有另外一个链接 Automating MS Word using C++.
回到主题,在商业世界中有许多电子表格,越来越我们不会只创建和使用简单的Excel,例如存储敏感和非常重要的试验数据等等……
作为开始,在这篇文章中我们不会探究非常高级的在Excel中可用的自动化,但它会给出一个有望供其他人使用的框架,以便能改进和让它更加具有可扩展性。该框架允许你创建一个Excel对象,并控制一些基本功能例如得到工作表信息,还有从给定范围的工作表中提取数据。
我们不得不开发的程序有更大的范围,我将集中精力在Excel部分。但是有少数几个我们为文件系统导航使用到线程而开发的简洁的类,如果对这篇文章有足够多的响应,或则我有机会去做它,我会继续把它发布,期间我希望下面的文章对你有用。
背景
充分理解OOP思想,熟悉C#编程语言。
用到的代码
我将提供一个能用在你的项目中的Excel外覆类。这些代码将在下面讨论。我将不会太深入Excel对象模型,因为,首先它将是一个巨大的任务,第二已经存在一些Microsoft写的类似文档了,这只是一个对想了解如何建立一个Office自动化工程的初学者的快速入门教程:
建立一个新工程,为了简便起见,建立一个Windows应用程序,建好后,右键单击“解决方案资源管理器”,选择“添加引用”,等添加引用窗口出现后,选择“COM”标签,这将显示你机器上所有可用的组件名称。既然我们将使用MS Excel,你就向下移动滚动条直到找到: Microsoft Excel 11.0 Object Library.
注意:你的也许是不同的版本,这将取决于你机器上安装的Office的版本。这是MS Excel 2003.
1
using System;
2![](/Images/OutliningIndicators/None.gif)
3
using System.IO;
4![](/Images/OutliningIndicators/None.gif)
5
using System.Collections;
6![](/Images/OutliningIndicators/None.gif)
7
using System.Threading;
8![](/Images/OutliningIndicators/None.gif)
9
using Office = Microsoft.Office.Core;
10![](/Images/OutliningIndicators/None.gif)
11
using Excel = Microsoft.Office.Interop.Excel;
12![](/Images/OutliningIndicators/None.gif)
13
using System.Diagnostics;
14![](/Images/OutliningIndicators/None.gif)
15
16![](/Images/OutliningIndicators/None.gif)
17
namespace ATPMain
18![](/Images/OutliningIndicators/None.gif)
19![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
{
20![](/Images/OutliningIndicators/InBlock.gif)
21![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
22![](/Images/OutliningIndicators/InBlock.gif)
23
/// Project: Code Project Demo
24![](/Images/OutliningIndicators/InBlock.gif)
25
/// Author: Vahe Karamian
26![](/Images/OutliningIndicators/InBlock.gif)
27
/// Date: 03/01/2005
28![](/Images/OutliningIndicators/InBlock.gif)
29
/// Version: 1.0
30![](/Images/OutliningIndicators/InBlock.gif)
31
/// </summary>
32![](/Images/OutliningIndicators/InBlock.gif)
33
public class VkExcel
34![](/Images/OutliningIndicators/InBlock.gif)
35![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
36![](/Images/OutliningIndicators/InBlock.gif)
37
private Excel.Application excelApp = null;
38![](/Images/OutliningIndicators/InBlock.gif)
39
private Excel.Workbook excelWorkbook = null;
40![](/Images/OutliningIndicators/InBlock.gif)
41
private Excel.Sheets excelSheets = null;
42![](/Images/OutliningIndicators/InBlock.gif)
43
private Excel.Worksheet excelWorksheet = null;
44![](/Images/OutliningIndicators/InBlock.gif)
45
46![](/Images/OutliningIndicators/InBlock.gif)
47
![](https://www.cnblogs.com/Images/dot.gif)
48![](/Images/OutliningIndicators/InBlock.gif)
49
using Office = Microsoft.Office.Core;
50![](/Images/OutliningIndicators/InBlock.gif)
51
using Excel = Microsoft.Office.Interop.Excel;
52![](/Images/OutliningIndicators/InBlock.gif)
53![](/Images/OutliningIndicators/InBlock.gif)
你要想能够在你的代码中使用Excel 对象,需要包含上面两个名称空间。我们需要一个Excel.Application 对象,一个Excel.Workbook 对象,一个 Excel.Sheets 对象, 还有一个Excel.Worksheet 对象。这些对象将用来控制和从Excel中提取数据,因此我们声明如下的一些变量来代表提及到的对象:excelApp, excelWorkbook, excelSheets, 和 excelWorksheet.
1
.
2![](/Images/OutliningIndicators/None.gif)
3
4![](/Images/OutliningIndicators/None.gif)
5
private static object vk_missing = System.Reflection.Missing.Value;
6![](/Images/OutliningIndicators/None.gif)
7
8![](/Images/OutliningIndicators/None.gif)
9
private static object vk_visible = true;
10![](/Images/OutliningIndicators/None.gif)
11
private static object vk_false = false;
12![](/Images/OutliningIndicators/None.gif)
13
private static object vk_true = true;
14![](/Images/OutliningIndicators/None.gif)
15
16![](/Images/OutliningIndicators/None.gif)
17
private bool vk_app_visible = false;
18![](/Images/OutliningIndicators/None.gif)
19
20![](/Images/OutliningIndicators/None.gif)
21
private object vk_filename;
22![](/Images/OutliningIndicators/None.gif)
23
24![](/Images/OutliningIndicators/None.gif)
25![](/Images/OutliningIndicators/ContractedBlock.gif)
OPEN WORKBOOK VARIABLES#region OPEN WORKBOOK VARIABLES
26![](/Images/OutliningIndicators/InBlock.gif)
27
private object vk_update_links = 0;
28![](/Images/OutliningIndicators/InBlock.gif)
29
private object vk_read_only = vk_true;
30![](/Images/OutliningIndicators/InBlock.gif)
31
private object vk_format = 1;
32![](/Images/OutliningIndicators/InBlock.gif)
33
private object vk_password = vk_missing;
34![](/Images/OutliningIndicators/InBlock.gif)
35
private object vk_write_res_password = vk_missing;
36![](/Images/OutliningIndicators/InBlock.gif)
37
private object vk_ignore_read_only_recommend = vk_true;
38![](/Images/OutliningIndicators/InBlock.gif)
39
private object vk_origin = vk_missing;
40![](/Images/OutliningIndicators/InBlock.gif)
41
private object vk_delimiter = vk_missing;
42![](/Images/OutliningIndicators/InBlock.gif)
43
private object vk_editable = vk_false;
44![](/Images/OutliningIndicators/InBlock.gif)
45
private object vk_notify = vk_false;
46![](/Images/OutliningIndicators/InBlock.gif)
47
private object vk_converter = vk_missing;
48![](/Images/OutliningIndicators/InBlock.gif)
49
private object vk_add_to_mru = vk_false;
50![](/Images/OutliningIndicators/InBlock.gif)
51
private object vk_local = vk_false;
52![](/Images/OutliningIndicators/InBlock.gif)
53
private object vk_corrupt_load = vk_false;
54![](/Images/OutliningIndicators/InBlock.gif)
55
#endregion
56![](/Images/OutliningIndicators/None.gif)
57
58![](/Images/OutliningIndicators/None.gif)
59![](/Images/OutliningIndicators/ContractedBlock.gif)
CLOSE WORKBOOK VARIABLES#region CLOSE WORKBOOK VARIABLES
60![](/Images/OutliningIndicators/InBlock.gif)
61
private object vk_save_changes = vk_false;
62![](/Images/OutliningIndicators/InBlock.gif)
63
private object vk_route_workbook = vk_false;
64![](/Images/OutliningIndicators/InBlock.gif)
65
#endregion
66![](/Images/OutliningIndicators/None.gif)
67
68![](/Images/OutliningIndicators/None.gif)
69![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//// <summary>
70![](/Images/OutliningIndicators/InBlock.gif)
71
/// Vahe Karamian - 03/04/2005 - Excel Object Constructor.
72![](/Images/OutliningIndicators/InBlock.gif)
73
/// </summary> 74![](/Images/OutliningIndicators/None.gif)
75
public VkExcel()
76![](/Images/OutliningIndicators/None.gif)
77![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
78![](/Images/OutliningIndicators/InBlock.gif)
79
this.startExcel();
80![](/Images/OutliningIndicators/InBlock.gif)
81
}
82![](/Images/OutliningIndicators/None.gif)
83
84![](/Images/OutliningIndicators/None.gif)
85![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//// <summary>
86![](/Images/OutliningIndicators/InBlock.gif)
87
/// Vahe Karamian - 03/04/2005 - Excel Object Constructor
88![](/Images/OutliningIndicators/InBlock.gif)
89
/// visible is a parameter, either TRUE or FALSE, of type object.
90![](/Images/OutliningIndicators/InBlock.gif)
91
/// </summary>
92![](/Images/OutliningIndicators/InBlock.gif)
93
/// Visible parameter, true for visible, false for non-visible 94![](/Images/OutliningIndicators/None.gif)
95
public VkExcel(bool visible)
96![](/Images/OutliningIndicators/None.gif)
97![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
98![](/Images/OutliningIndicators/InBlock.gif)
99
this.vk_app_visible = visible;
100![](/Images/OutliningIndicators/InBlock.gif)
101
this.startExcel();
102![](/Images/OutliningIndicators/InBlock.gif)
103
}
104![](/Images/OutliningIndicators/None.gif)
105
![](https://www.cnblogs.com/Images/dot.gif)
106![](/Images/OutliningIndicators/None.gif)
107![](/Images/OutliningIndicators/None.gif)
在上面的代码块中,我们预先定义了一些常量,将会使用这些来打开一个给定的Excel文件,要找出更多关于每个参数代表什么,能做什么,你应该查询与Excel一起的文档。
我们有两个构造函数: VkExcel() ,默认构造函数将启动一个隐藏的Excel,而另外一个VkExcel(bool visible) 构造函数,将给你一个选择,确认Excel应用程序是否可见。
1
![](https://www.cnblogs.com/Images/dot.gif)
2![](/Images/OutliningIndicators/None.gif)
3![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//// <summary>
4![](/Images/OutliningIndicators/InBlock.gif)
5
/// Vahe Karamian - 03/04/2005 - Start Excel Application
6![](/Images/OutliningIndicators/InBlock.gif)
7
/// </summary> 8![](/Images/OutliningIndicators/None.gif)
9![](/Images/OutliningIndicators/ContractedBlock.gif)
START EXCEL#region START EXCEL
10![](/Images/OutliningIndicators/InBlock.gif)
11
private void startExcel()
12![](/Images/OutliningIndicators/InBlock.gif)
13![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
14![](/Images/OutliningIndicators/InBlock.gif)
15
if( this.excelApp == null )
16![](/Images/OutliningIndicators/InBlock.gif)
17![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
18![](/Images/OutliningIndicators/InBlock.gif)
19
this.excelApp = new Excel.ApplicationClass();
20![](/Images/OutliningIndicators/InBlock.gif)
21
}
22![](/Images/OutliningIndicators/InBlock.gif)
23
24![](/Images/OutliningIndicators/InBlock.gif)
25
// Make Excel Visible
26![](/Images/OutliningIndicators/InBlock.gif)
27
this.excelApp.Visible = this.vk_app_visible;
28![](/Images/OutliningIndicators/InBlock.gif)
29
}
30![](/Images/OutliningIndicators/InBlock.gif)
31
#endregion
32![](/Images/OutliningIndicators/None.gif)
33
34![](/Images/OutliningIndicators/None.gif)
35![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//// <summary>
36![](/Images/OutliningIndicators/InBlock.gif)
37
/// Vahe Karamian - 03/23/2005 - Kill the current Excel Process
38![](/Images/OutliningIndicators/InBlock.gif)
39
/// </summary>40![](/Images/OutliningIndicators/None.gif)
41![](/Images/OutliningIndicators/ContractedBlock.gif)
STOP EXCEL#region STOP EXCEL
42![](/Images/OutliningIndicators/InBlock.gif)
43
public void stopExcel()
44![](/Images/OutliningIndicators/InBlock.gif)
45![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
46![](/Images/OutliningIndicators/InBlock.gif)
47
if( this.excelApp != null )
48![](/Images/OutliningIndicators/InBlock.gif)
49![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
50![](/Images/OutliningIndicators/InBlock.gif)
51
Process[] pProcess;
52![](/Images/OutliningIndicators/InBlock.gif)
53
pProcess = System.Diagnostics.Process.GetProcessesByName("Excel");
54![](/Images/OutliningIndicators/InBlock.gif)
55
pProcess[0].Kill();
56![](/Images/OutliningIndicators/InBlock.gif)
57
}
58![](/Images/OutliningIndicators/InBlock.gif)
59
}
60![](/Images/OutliningIndicators/InBlock.gif)
61
#endregion
62![](/Images/OutliningIndicators/None.gif)
63
![](https://www.cnblogs.com/Images/dot.gif)
64![](/Images/OutliningIndicators/None.gif)
65![](/Images/OutliningIndicators/None.gif)
上面的代码启动和停止Excel应用程序。startExcel() 检查excelApp 对象是否初始化,如果已经初始化,仅确认设定在visible属性的可见性;如果没有初始化,就继续向前,初始化对象。stopExcel() 也检查对象是否正在使用,如果是就关闭该进程。
注意:pProcess[0].Kill() 将确认Excel正常运行!有些做Excel自动化的人总是抱怨,在他们退出应用程序后,Excel消失了,但是Excel进程仍然在任务监视器里,这代码将为你处理好那情况!
1
![](https://www.cnblogs.com/Images/dot.gif)
2![](/Images/OutliningIndicators/None.gif)
3![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//// <summary>
4![](/Images/OutliningIndicators/InBlock.gif)
5
/// Vahe Karamian - 03/09/2005 - Open File function for Excel 2003
6![](/Images/OutliningIndicators/InBlock.gif)
7
/// The following function will take in a filename, and a password
8![](/Images/OutliningIndicators/InBlock.gif)
9
/// associated, if needed, to open the file.
10![](/Images/OutliningIndicators/InBlock.gif)
11
/// </summary>12![](/Images/OutliningIndicators/None.gif)
13![](/Images/OutliningIndicators/ContractedBlock.gif)
OPEN FILE FOR EXCEL#region OPEN FILE FOR EXCEL
14![](/Images/OutliningIndicators/InBlock.gif)
15
public string OpenFile(string fileName, string password)
16![](/Images/OutliningIndicators/InBlock.gif)
17![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
18![](/Images/OutliningIndicators/InBlock.gif)
19
vk_filename = fileName;
20![](/Images/OutliningIndicators/InBlock.gif)
21
22![](/Images/OutliningIndicators/InBlock.gif)
23
if( password.Length > 0 )
24![](/Images/OutliningIndicators/InBlock.gif)
25![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
26![](/Images/OutliningIndicators/InBlock.gif)
27
vk_password = password;
28![](/Images/OutliningIndicators/InBlock.gif)
29
}
30![](/Images/OutliningIndicators/InBlock.gif)
31
32![](/Images/OutliningIndicators/InBlock.gif)
33
try
34![](/Images/OutliningIndicators/InBlock.gif)
35![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
36![](/Images/OutliningIndicators/InBlock.gif)
37
// Open a workbook in Excel
38![](/Images/OutliningIndicators/InBlock.gif)
39
this.excelWorkbook = this.excelApp.Workbooks.Open(
40![](/Images/OutliningIndicators/InBlock.gif)
41
fileName, vk_update_links, vk_read_only, vk_format, vk_password,
42![](/Images/OutliningIndicators/InBlock.gif)
43
vk_write_res_password, vk_ignore_read_only_recommend, vk_origin,
44![](/Images/OutliningIndicators/InBlock.gif)
45
vk_delimiter, vk_editable, vk_notify, vk_converter, vk_add_to_mru,
46![](/Images/OutliningIndicators/InBlock.gif)
47
vk_local, vk_corrupt_load);
48![](/Images/OutliningIndicators/InBlock.gif)
49
}
50![](/Images/OutliningIndicators/InBlock.gif)
51
catch(Exception e)
52![](/Images/OutliningIndicators/InBlock.gif)
53![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
54![](/Images/OutliningIndicators/InBlock.gif)
55
this.CloseFile();
56![](/Images/OutliningIndicators/InBlock.gif)
57
return e.Message;
58![](/Images/OutliningIndicators/InBlock.gif)
59
}
60![](/Images/OutliningIndicators/InBlock.gif)
61
return "OK";
62![](/Images/OutliningIndicators/InBlock.gif)
63
}
64![](/Images/OutliningIndicators/InBlock.gif)
65
#endregion
66![](/Images/OutliningIndicators/None.gif)
67
68![](/Images/OutliningIndicators/None.gif)
69
public void CloseFile()
70![](/Images/OutliningIndicators/None.gif)
71![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
72![](/Images/OutliningIndicators/InBlock.gif)
73
excelWorkbook.Close( vk_save_changes, vk_filename, vk_route_workbook );
74![](/Images/OutliningIndicators/InBlock.gif)
75
}
76![](/Images/OutliningIndicators/None.gif)
77
![](https://www.cnblogs.com/Images/dot.gif)
78![](/Images/OutliningIndicators/None.gif)
79![](/Images/OutliningIndicators/None.gif)
好的,这样看来,上面的代码允许我们打开一个Excel文件。OpenFile(string fileName, string password) 携带两个参数,文件名,或者是完整名称,路径+文件名,还有就是密码参数,该密码是用来保护表格的。注意这个打开函数携带了一大串参数,我们在类中已经定义了它们。CloseFile() 将会关闭文件。
注意: 这些代码是为MS Excel 2003提供的,对于更早的版本,参数有一点不同,你需要去查看下文档。如果你需要帮助,给我发一个e-mail,我将尽量帮助你解决。
1
![](https://www.cnblogs.com/Images/dot.gif)
2![](/Images/OutliningIndicators/None.gif)
3![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//// <summary>
4![](/Images/OutliningIndicators/InBlock.gif)
5
/// Vahe Karamian - 03/20/2005 - Get Excel Sheets
6![](/Images/OutliningIndicators/InBlock.gif)
7
/// Get the collection of sheets in the workbook
8![](/Images/OutliningIndicators/InBlock.gif)
9
/// </summary>10![](/Images/OutliningIndicators/None.gif)
11![](/Images/OutliningIndicators/ContractedBlock.gif)
GET EXCEL SHEETS#region GET EXCEL SHEETS
12![](/Images/OutliningIndicators/InBlock.gif)
13
public void GetExcelSheets()
14![](/Images/OutliningIndicators/InBlock.gif)
15![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
16![](/Images/OutliningIndicators/InBlock.gif)
17
if( this.excelWorkbook != null )
18![](/Images/OutliningIndicators/InBlock.gif)
19![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
20![](/Images/OutliningIndicators/InBlock.gif)
21
excelSheets = excelWorkbook.Worksheets;
22![](/Images/OutliningIndicators/InBlock.gif)
23
}
24![](/Images/OutliningIndicators/InBlock.gif)
25
}
26![](/Images/OutliningIndicators/InBlock.gif)
27
#endregion
28![](/Images/OutliningIndicators/None.gif)
29
30![](/Images/OutliningIndicators/None.gif)
31![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//// <summary>
32![](/Images/OutliningIndicators/InBlock.gif)
33
/// Vahe Karamian - 03/21/2005 - Find Excel ATP Worksheet
34![](/Images/OutliningIndicators/InBlock.gif)
35
/// Search for ATP worksheet, if found return TRUE
36![](/Images/OutliningIndicators/InBlock.gif)
37
/// </summary>
38![](/Images/OutliningIndicators/InBlock.gif)
39
/// <returns>bool</returns>40![](/Images/OutliningIndicators/None.gif)
41![](/Images/OutliningIndicators/ContractedBlock.gif)
FIND EXCEL ATP WORKSHEET#region FIND EXCEL ATP WORKSHEET
42![](/Images/OutliningIndicators/InBlock.gif)
43
public bool FindExcelATPWorksheet(string worksheetName)
44![](/Images/OutliningIndicators/InBlock.gif)
45![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
46![](/Images/OutliningIndicators/InBlock.gif)
47
bool ATP_SHEET_FOUND = false;
48![](/Images/OutliningIndicators/InBlock.gif)
49
50![](/Images/OutliningIndicators/InBlock.gif)
51
if( this.excelSheets != null )
52![](/Images/OutliningIndicators/InBlock.gif)
53![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
54![](/Images/OutliningIndicators/InBlock.gif)
55
// Step thru the worksheet collection and see if ATP sheet is
56![](/Images/OutliningIndicators/InBlock.gif)
57
// available. If found return true;
58![](/Images/OutliningIndicators/InBlock.gif)
59
for( int i=1; i<=this.excelSheets.Count; i++ )
60![](/Images/OutliningIndicators/InBlock.gif)
61![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
62![](/Images/OutliningIndicators/InBlock.gif)
63
this.excelWorksheet =
64![](/Images/OutliningIndicators/InBlock.gif)
65
(Excel.Worksheet)excelSheets.get_Item((object)i);
66![](/Images/OutliningIndicators/InBlock.gif)
67
if( this.excelWorksheet.Name.Equals(worksheetName) )
68![](/Images/OutliningIndicators/InBlock.gif)
69![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
70![](/Images/OutliningIndicators/InBlock.gif)
71
this.excelWorksheet.Activate();
72![](/Images/OutliningIndicators/InBlock.gif)
73
ATP_SHEET_FOUND = true;
74![](/Images/OutliningIndicators/InBlock.gif)
75
return ATP_SHEET_FOUND;
76![](/Images/OutliningIndicators/InBlock.gif)
77
}
78![](/Images/OutliningIndicators/InBlock.gif)
79
}
80![](/Images/OutliningIndicators/InBlock.gif)
81
}
82![](/Images/OutliningIndicators/InBlock.gif)
83
return ATP_SHEET_FOUND;
84![](/Images/OutliningIndicators/InBlock.gif)
85
}
86![](/Images/OutliningIndicators/InBlock.gif)
87
#endregion
88![](/Images/OutliningIndicators/None.gif)
89
![](https://www.cnblogs.com/Images/dot.gif)
90![](/Images/OutliningIndicators/None.gif)
91![](/Images/OutliningIndicators/None.gif)
上面的代码演示怎样得到一个工作簿中所有的电子表格,还有得到一个确定的表格并从中提取数据。GetExcelSheets()得到所有表格. FindExcelATPWorkSheet(string worksheetName) 搜索名为worksheetName表格。
1
![](https://www.cnblogs.com/Images/dot.gif)
2![](/Images/OutliningIndicators/None.gif)
3![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//// <summary>
4![](/Images/OutliningIndicators/InBlock.gif)
5
/// Vahe Karamian - 03/22/2005 - Get Range from Worksheet
6![](/Images/OutliningIndicators/InBlock.gif)
7
/// Return content of range from the selected range
8![](/Images/OutliningIndicators/InBlock.gif)
9
/// </summary>
10![](/Images/OutliningIndicators/InBlock.gif)
11
/// Range parameter: Example, GetRange("A1:D10")12![](/Images/OutliningIndicators/None.gif)
13![](/Images/OutliningIndicators/ContractedBlock.gif)
GET RANGE#region GET RANGE
14![](/Images/OutliningIndicators/InBlock.gif)
15
public string[] GetRange(string range)
16![](/Images/OutliningIndicators/InBlock.gif)
17![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
18![](/Images/OutliningIndicators/InBlock.gif)
19
Excel.Range workingRangeCells = excelWorksheet.get_Range(range,Type.Missing);
20![](/Images/OutliningIndicators/InBlock.gif)
21
//workingRangeCells.Select();
22![](/Images/OutliningIndicators/InBlock.gif)
23
System.Array array = (System.Array)workingRangeCells.Cells.Value2;
24![](/Images/OutliningIndicators/InBlock.gif)
25
string[] arrayS = this.ConvertToStringArray(array);
26![](/Images/OutliningIndicators/InBlock.gif)
27
28![](/Images/OutliningIndicators/InBlock.gif)
29
return arrayS;
30![](/Images/OutliningIndicators/InBlock.gif)
31
}
32![](/Images/OutliningIndicators/InBlock.gif)
33
#endregion
34![](/Images/OutliningIndicators/None.gif)
35
![](https://www.cnblogs.com/Images/dot.gif)
36![](/Images/OutliningIndicators/None.gif)
37![](/Images/OutliningIndicators/None.gif)
GetRange(string range) 这个函数用来从Excel表格中实际上得到数据,我们转化返回的值把它们放入一个string[]. 我们用接下来的函数完成这工作:this.ConvertToStringArray(array). 然后这个string[]被返回给调用者,调用者可用以任何方式来使用它。
1
![](https://www.cnblogs.com/Images/dot.gif)
2![](/Images/OutliningIndicators/None.gif)
3![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//// <summary>
4![](/Images/OutliningIndicators/InBlock.gif)
5
/// Vahe Karamian - 03/22/2005 - Convert To String Array
6![](/Images/OutliningIndicators/InBlock.gif)
7
/// Convert System.Array into string[]
8![](/Images/OutliningIndicators/InBlock.gif)
9
/// </summary>
10![](/Images/OutliningIndicators/InBlock.gif)
11
/// Values from range object
12![](/Images/OutliningIndicators/InBlock.gif)
13
/// <returns>String[]</returns>14![](/Images/OutliningIndicators/None.gif)
15![](/Images/OutliningIndicators/ContractedBlock.gif)
CONVERT TO STRING ARRAY#region CONVERT TO STRING ARRAY
16![](/Images/OutliningIndicators/InBlock.gif)
17
private string[] ConvertToStringArray(System.Array values)
18![](/Images/OutliningIndicators/InBlock.gif)
19![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
20![](/Images/OutliningIndicators/InBlock.gif)
21
string[] newArray = new string[values.Length];
22![](/Images/OutliningIndicators/InBlock.gif)
23
24![](/Images/OutliningIndicators/InBlock.gif)
25
int index = 0;
26![](/Images/OutliningIndicators/InBlock.gif)
27
for ( int i = values.GetLowerBound(0); i <= values.GetUpperBound(0); i++ )
28![](/Images/OutliningIndicators/InBlock.gif)
29![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
30![](/Images/OutliningIndicators/InBlock.gif)
31
for ( int j = values.GetLowerBound(1); j <= values.GetUpperBound(1); j++ )
32![](/Images/OutliningIndicators/InBlock.gif)
33![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
34![](/Images/OutliningIndicators/InBlock.gif)
35
if(values.GetValue(i,j)==null)
36![](/Images/OutliningIndicators/InBlock.gif)
37![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
38![](/Images/OutliningIndicators/InBlock.gif)
39
newArray[index]="";
40![](/Images/OutliningIndicators/InBlock.gif)
41
}
42![](/Images/OutliningIndicators/InBlock.gif)
43
else
44![](/Images/OutliningIndicators/InBlock.gif)
45![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
46![](/Images/OutliningIndicators/InBlock.gif)
47
newArray[index]=(string)values.GetValue(i,j).ToString();
48![](/Images/OutliningIndicators/InBlock.gif)
49
}
50![](/Images/OutliningIndicators/InBlock.gif)
51
index++;
52![](/Images/OutliningIndicators/InBlock.gif)
53
}
54![](/Images/OutliningIndicators/InBlock.gif)
55
}
56![](/Images/OutliningIndicators/InBlock.gif)
57
return newArray;
58![](/Images/OutliningIndicators/InBlock.gif)
59
}
60![](/Images/OutliningIndicators/InBlock.gif)
61
#endregion
62![](/Images/OutliningIndicators/None.gif)
63
}
64![](/Images/OutliningIndicators/None.gif)
65![](/Images/OutliningIndicators/None.gif)
在最后的代码部分: ConvertToStringArray(System.Array values) 将接收这个从GetRange(...) 传来的数组,把数组放入一个字符串数组中,并返回它。
我们已经到达了我们对象的结尾,就像你能看到的,它是一个只有最小功能的非常简单的对象,但它对任何需要快速入门的人是一个很好的起点,而且你能非常简单地把它扩展成一个更加复杂的对象。
我没有包含一个demo工程。原因是使用这个工程是相当简单的。你只要按照下面的步骤初始化和使用 VkExcel对象。
- 建立一个VkExcel类型的对象:VkExcel excel = new VkExcel(false);. 记住那个VkExcel(...) 带了一个参数,确认Excel程序是否可见。
- 打开一个Excel文件:file: string status = excel.OpenFile( filename, password );, 传人文件名或者完整文件名(如果你使用的是一个OpenFileDialog),如果文件没有密码保护,把password设为null。
- 检查文件是否成功打开: if( status.equal("OK")) ...
- 重新得到Excel表格: excel.GetExcelSheets(); 将得到对象内的所有表格。
- 搜索特定的表格:excel.FindExcelWorksheet(worksheetName); 将查找给定文件中任何的表格,传人worksheetName作为参数。
- 获取给定范围的数据:string[] A4D4 = excel.GetRange("A4:D4"); 这将以string[]形式返回范围内的值。
以上就是全部了!