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

最新推荐文章于 2024-11-07 08:51:26 发布

H20914010

最新推荐文章于 2024-11-07 08:51:26 发布

阅读量149

点赞数

文章标签： c# 操作系统

原文链接：http://www.cnblogs.com/buduan/archive/2013/03/01/2938841.html

版权

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

文章编号: 302084 - 查看本文应用于的产品

本文的发布号曾为 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 创建自动化客户端

启动 Microsoft Visual Studio .NET。
在文件菜单上，单击新建，然后单击项目。从 Visual C# 项目类型中选择 Windows 应用程序。Form1 是默认创建的窗体。
添加对 Microsoft Excel 对象库的引用。为此，请按照下列步骤操作：
1. 在项目菜单上，单击添加引用。
2. 在 COM 选项卡上，找到 Microsoft Excel 对象库，然后单击选择。
  
  注意：Microsoft Office 2003 包含主 Interop 程序集 (PIA)。Microsoft Office XP 不包含 PIA，但您可以下载 PIA。有关 Office XP PIA 的更多信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章：
  328912 Microsoft Office XP 主 interop 程序集 (PIA) 可供下载
3. 在添加引用对话框中单击确定以接受您的选择。如果系统提示您为选定的库生成包装，请单击是。
在视图菜单上，选择工具箱以显示工具箱，然后向 Form1 添加一个按钮。
双击 Button1。出现该窗体的代码窗口。

在代码窗口中，将以下代码

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;
}