你目前正处于脱机状态,正在等待 Internet 重新连接

如何在 Microsoft Visual C# .NET 中实现 Microsoft Excel 自动化

针对 Office 2003 的支持已终止

Microsoft 已于 2014 年 4 月 8 日终止了针对 Office 2003 的支持。该更改已影响到您的软件更新和安全选项。 了解这一措施对于您的含义以及如何继续保持受保护状态。

本文的发布号曾为 CHS302084
概要
本文阐述如何使用 Microsoft Visual C# .NET 为 Microsoft Excel 创建自动化客户端。
更多信息
通过自动化过程,使用诸如 Visual C# .NET 这样的语言编写的应用程序就可以用编程方式来控制其他应用程序。利用 Excel 的自动化功能,您可以执行诸如新建工作簿、向工作簿添加数据或创建图表等操作。对于 Excel 和其他 Microsoft Office 应用程序,几乎所有可以通过用户界面手动执行的操作也都可以通过使用“自动化”功能以编程方式来执行。

Excel 通过一种对象模型来公开这一程序功能。该对象模型是一些类和方法的集合,这些类和方法充当 Excel 的逻辑组件。例如,有 Application 对象、Workbook 对象和 Worksheet 对象,其中每一种对象都包含 Excel 中那些组件的功能。要从 Visual C# .NET 访问该对象模型,可以设置对类型库的项目引用。

本文将阐述如何为 Visual C# .NET 设置对 Excel 类型库的适当项目引用,并提供使 Excel 自动运行的代码示例。

为 Microsoft Excel 创建自动化客户端

  1. 启动 Microsoft Visual Studio .NET。
  2. 文件菜单上,单击新建,然后单击项目。从 Visual C# 项目类型中选择 Windows 应用程序。Form1 是默认创建的窗体。
  3. 添加对 Microsoft Excel 对象库的引用。为此,请按照下列步骤操作:
    1. 项目菜单上,单击添加引用
    2. COM 选项卡上,找到 Microsoft Excel 对象库,然后单击选择

      注意:Microsoft Office 2003 包含主 Interop 程序集 (PIA)。Microsoft Office XP 不包含 PIA,但您可以下载 PIA。 有关 Office XP PIA 的更多信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
      328912Microsoft Office XP 主 interop 程序集 (PIA) 可供下载
    3. 添加引用对话框中单击确定以接受您的选择。如果系统提示您为选定的库生成包装,请单击
  4. 视图菜单上,选择工具箱以显示工具箱,然后向 Form1 添加一个按钮。
  5. 双击 Button1。出现该窗体的代码窗口。
  6. 在代码窗口中,将以下代码
    private void button1_Click(object sender, System.EventArgs e){}					
    替换为:
    private void button1_Click(object sender, System.EventArgs e){	Excel.Application oXL;	Excel._Workbook oWB;	Excel._Worksheet oSheet;	Excel.Range oRng;	try	{		//Start Excel and get Application object.		oXL = new Excel.Application();		oXL.Visible = true;		//Get a new workbook.		oWB = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value ));		oSheet = (Excel._Worksheet)oWB.ActiveSheet;		//Add table headers going cell by cell.		oSheet.Cells[1, 1] = "First Name";		oSheet.Cells[1, 2] = "Last Name";		oSheet.Cells[1, 3] = "Full Name";		oSheet.Cells[1, 4] = "Salary";		//Format A1:D1 as bold, vertical alignment = center.		oSheet.get_Range("A1", "D1").Font.Bold = true;		oSheet.get_Range("A1", "D1").VerticalAlignment = 			Excel.XlVAlign.xlVAlignCenter;				// Create an array to multiple values at once.		string[,] saNames = new string[5,2];				saNames[ 0, 0] = "John";		saNames[ 0, 1] = "Smith";		saNames[ 1, 0] = "Tom";		saNames[ 1, 1] = "Brown";		saNames[ 2, 0] = "Sue";		saNames[ 2, 1] = "Thomas";		saNames[ 3, 0] = "Jane";		saNames[ 3, 1] = "Jones";		saNames[ 4, 0] = "Adam";		saNames[ 4, 1] = "Johnson";        	//Fill A2:B6 with an array of values (First and Last Names).	        oSheet.get_Range("A2", "B6").Value2 = saNames;		//Fill C2:C6 with a relative formula (=A2 & " " & B2).		oRng = oSheet.get_Range("C2", "C6");		oRng.Formula = "=A2 & \" \" & B2";		//Fill D2:D6 with a formula(=RAND()*100000) and apply format.		oRng = oSheet.get_Range("D2", "D6");		oRng.Formula = "=RAND()*100000";		oRng.NumberFormat = "$0.00";		//AutoFit columns A:D.		oRng = oSheet.get_Range("A1", "D1");		oRng.EntireColumn.AutoFit();		//Manipulate a variable number of columns for Quarterly Sales Data.		DisplayQuarterlySales(oSheet);		//Make sure Excel is visible and give the user control		//of Microsoft Excel's lifetime.		oXL.Visible = true;		oXL.UserControl = true;	}	catch( Exception theException ) 	{		String errorMessage;		errorMessage = "Error: ";		errorMessage = String.Concat( errorMessage, theException.Message );		errorMessage = String.Concat( errorMessage, " Line: " );		errorMessage = String.Concat( errorMessage, theException.Source );		MessageBox.Show( errorMessage, "Error" );	}}private void DisplayQuarterlySales(Excel._Worksheet oWS){	Excel._Workbook oWB;	Excel.Series oSeries;	Excel.Range oResizeRange;	Excel._Chart oChart;	String sMsg;	int iNumQtrs;	//Determine how many quarters to display data for.	for( iNumQtrs = 4; iNumQtrs >= 2; iNumQtrs--)	{		sMsg = "Enter sales data for ";		sMsg = String.Concat( sMsg, iNumQtrs );		sMsg = String.Concat( sMsg, " quarter(s)?");		DialogResult iRet = MessageBox.Show( sMsg, "Quarterly Sales?", 			MessageBoxButtons.YesNo );		if (iRet == DialogResult.Yes)			break;	}	sMsg = "Displaying data for ";	sMsg = String.Concat( sMsg, iNumQtrs );	sMsg = String.Concat( sMsg, " quarter(s)." );	MessageBox.Show( sMsg, "Quarterly Sales" );	//Starting at E1, fill headers for the number of columns selected.	oResizeRange = oWS.get_Range("E1", "E1").get_Resize( Missing.Value, iNumQtrs);	oResizeRange.Formula = "=\"Q\" & COLUMN()-4 & CHAR(10) & \"Sales\"";	//Change the Orientation and WrapText properties for the headers.	oResizeRange.Orientation = 38;	oResizeRange.WrapText = true;	//Fill the interior color of the headers.	oResizeRange.Interior.ColorIndex = 36;	//Fill the columns with a formula and apply a number format.	oResizeRange = oWS.get_Range("E2", "E6").get_Resize( Missing.Value, iNumQtrs);	oResizeRange.Formula = "=RAND()*100";	oResizeRange.NumberFormat = "$0.00";	//Apply borders to the Sales data and headers.	oResizeRange = oWS.get_Range("E1", "E6").get_Resize( Missing.Value, iNumQtrs);	oResizeRange.Borders.Weight = Excel.XlBorderWeight.xlThin;	//Add a Totals formula for the sales data and apply a border.	oResizeRange = oWS.get_Range("E8", "E8").get_Resize( Missing.Value, iNumQtrs);	oResizeRange.Formula = "=SUM(E2:E6)";	oResizeRange.Borders.get_Item( Excel.XlBordersIndex.xlEdgeBottom ).LineStyle 		= Excel.XlLineStyle.xlDouble;	oResizeRange.Borders.get_Item( Excel.XlBordersIndex.xlEdgeBottom ).Weight 		= Excel.XlBorderWeight.xlThick;	//Add a Chart for the selected data.	oWB = (Excel._Workbook)oWS.Parent;	oChart = (Excel._Chart)oWB.Charts.Add( Missing.Value, Missing.Value, 		Missing.Value, Missing.Value );	//Use the ChartWizard to create a new chart from the selected data.	oResizeRange = oWS.get_Range("E2:E6", Missing.Value ).get_Resize( 		Missing.Value, iNumQtrs);	oChart.ChartWizard( oResizeRange, Excel.XlChartType.xl3DColumn, Missing.Value,		Excel.XlRowCol.xlColumns, Missing.Value, Missing.Value, Missing.Value, 		Missing.Value, Missing.Value, Missing.Value, Missing.Value );	oSeries = (Excel.Series)oChart.SeriesCollection(1);	oSeries.XValues = oWS.get_Range("A2", "A6");	for( int iRet = 1; iRet <= iNumQtrs; iRet++)	{		oSeries = (Excel.Series)oChart.SeriesCollection(iRet);		String seriesName;		seriesName = "=\"Q";		seriesName = String.Concat( seriesName, iRet );		seriesName = String.Concat( seriesName, "\"" );		oSeries.Name = seriesName;	}														  		oChart.Location( Excel.XlChartLocation.xlLocationAsObject, oWS.Name );	//Move the chart so as not to cover your data.	oResizeRange = (Excel.Range)oWS.Rows.get_Item(10, Missing.Value );	oWS.Shapes.Item("Chart 1").Top = (float)(double)oResizeRange.Top;	oResizeRange = (Excel.Range)oWS.Columns.get_Item(2, Missing.Value );	oWS.Shapes.Item("Chart 1").Left = (float)(double)oResizeRange.Left;}					
  7. 滚动到代码窗口的顶部。将下面的代码行添加到 using 指令列表的末尾:
    using Excel = Microsoft.Office.Interop.Excel;using System.Reflection; 					

对自动化客户端进行测试

  1. 按 F5 生成并运行该程序。
  2. 在窗体上,单击 Button1。该程序将启动 Excel 并将数据填充到一个新的工作表中。
  3. 在提示您输入季度销售数据时,单击。一个链接到季度数据的图表就会被添加到工作表中。
参考
有关更多信息,请访问下面的 Microsoft Developer Network (MSDN) 网站:
Microsoft Office Development with Visual Studio(使用 Visual Studio 进行 Microsoft Office 开发)
http://msdn2.microsoft.com/en-us/library/aa188489(office.10).aspx
automation automating automate xl2003
属性

文章 ID:302084 - 上次审阅时间:01/17/2007 07:13:41 - 修订版本: 7.2

Microsoft Visual C# .NET 2003 标准版, Microsoft Visual C# .NET 2002 标准版, Microsoft Office Excel 2003, Microsoft Excel 2002 标准版

  • kbpia kbautomation kbhowto KB302084
反馈