C# .NET 中实现 Microsoft Excel 自动化

最新推荐文章于 2024-09-03 23:10:16 发布

原创最新推荐文章于 2024-09-03 23:10:16 发布 · 685 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#microsoft #excel #.net #c# #interop #office

本文介绍如何使用Visual C#.NET通过编程方式控制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 对象库的引用。为此，请按照下列步骤操作：在项目菜单上，单击添加引用。在 COM 选项卡上，找到 Microsoft Excel 对象库，然后单击选择。注意：Microsoft Office 2003 包含主 Interop 程序集 (PIA)。Microsoft Office XP 不包含 PIA，但您可以下载 PIA。有关 Office XP PIA 的更多信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章： 328912 (http://support.microsoft.com/kb/328912/ ) Microsoft Office XP 主 interop 程序集 (PIA) 可供下载在添加引用对话框中单击确定以接受您的选择。如果系统提示您为选定的库生成包装，请单击是。在视图菜单上，选择工具箱以显示工具箱，然后向 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; } 滚动到代码窗口的顶部。将下面的代码行添加到 using 指令列表的末尾： using Excel = Microsoft.Office.Interop.Excel; using System.Reflection; 对自动化客户端进行测试按 F5 生成并运行该程序。在窗体上，单击 Button1。该程序将启动 Excel 并将数据填充到一个新的工作表中。在提示您输入季度销售数据时，单击是。一个链接到季度数据的图表就会被添加到工作表中。

srouce:http://support.microsoft.com/kb/302084/zh-cn

http://support.microsoft.com/kb/302084/zh-cn

如何使用 Visual C# .NET 向 Excel 工作簿传输数据

http://support.microsoft.com/kb/306023/zh-cn