首页 > 代码库 > 如何在 Microsoft Visual C# .NET 中实现 Microsoft Excel 自动化
如何在 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 对象库的引用。为此,请按照下列步骤操作:
- 在项目菜单上,单击添加引用。
- 在 COM 选项卡上,找到 Microsoft Excel 对象库,然后单击选择。
注意:Microsoft Office 2003 包含主 Interop 程序集 (PIA)。Microsoft Office XP 不包含 PIA,但您可以下载 PIA。 有关 Office XP PIA 的更多信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:328912 Microsoft Office XP 主 interop 程序集 (PIA) 可供下载 - 在添加引用对话框中单击确定以接受您的选择。如果系统提示您为选定的库生成包装,请单击是。
- 在视图菜单上,选择工具箱以显示工具箱,然后向 Form1 添加一个按钮。
- 双击 Button1。出现该窗体的代码窗口。
- 在代码窗口中,将以下代码
1 private void button1_Click(object sender, System.EventArgs e)2 {3 }
替换为
1 private void button1_Click(object sender, System.EventArgs e) 2 { 3 Excel.Application oXL; 4 Excel._Workbook oWB; 5 Excel._Worksheet oSheet; 6 Excel.Range oRng; 7 8 try 9 { 10 //Start Excel and get Application object. 11 oXL = new Excel.Application(); 12 oXL.Visible = true; 13 14 //Get a new workbook. 15 oWB = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value )); 16 oSheet = (Excel._Worksheet)oWB.ActiveSheet; 17 18 //Add table headers going cell by cell. 19 oSheet.Cells[1, 1] = "First Name"; 20 oSheet.Cells[1, 2] = "Last Name"; 21 oSheet.Cells[1, 3] = "Full Name"; 22 oSheet.Cells[1, 4] = "Salary"; 23 24 //Format A1:D1 as bold, vertical alignment = center. 25 oSheet.get_Range("A1", "D1").Font.Bold = true; 26 oSheet.get_Range("A1", "D1").VerticalAlignment = 27 Excel.XlVAlign.xlVAlignCenter; 28 29 // Create an array to multiple values at once. 30 string[,] saNames = new string[5,2]; 31 32 saNames[ 0, 0] = "John"; 33 saNames[ 0, 1] = "Smith"; 34 saNames[ 1, 0] = "Tom"; 35 saNames[ 1, 1] = "Brown"; 36 saNames[ 2, 0] = "Sue"; 37 saNames[ 2, 1] = "Thomas"; 38 saNames[ 3, 0] = "Jane"; 39 saNames[ 3, 1] = "Jones"; 40 saNames[ 4, 0] = "Adam"; 41 saNames[ 4, 1] = "Johnson"; 42 43 //Fill A2:B6 with an array of values (First and Last Names). 44 oSheet.get_Range("A2", "B6").Value2 = saNames; 45 46 //Fill C2:C6 with a relative formula (=A2 & " " & B2). 47 oRng = oSheet.get_Range("C2", "C6"); 48 oRng.Formula = "=A2 & \" \" & B2"; 49 50 //Fill D2:D6 with a formula(=RAND()*100000) and apply format. 51 oRng = oSheet.get_Range("D2", "D6"); 52 oRng.Formula = "=RAND()*100000"; 53 oRng.NumberFormat = "$0.00"; 54 55 //AutoFit columns A:D. 56 oRng = oSheet.get_Range("A1", "D1"); 57 oRng.EntireColumn.AutoFit(); 58 59 //Manipulate a variable number of columns for Quarterly Sales Data. 60 DisplayQuarterlySales(oSheet); 61 62 //Make sure Excel is visible and give the user control 63 //of Microsoft Excel‘s lifetime. 64 oXL.Visible = true; 65 oXL.UserControl = true; 66 } 67 catch( Exception theException ) 68 { 69 String errorMessage; 70 errorMessage = "Error: "; 71 errorMessage = String.Concat( errorMessage, theException.Message ); 72 errorMessage = String.Concat( errorMessage, " Line: " ); 73 errorMessage = String.Concat( errorMessage, theException.Source ); 74 75 MessageBox.Show( errorMessage, "Error" ); 76 } 77 } 78 79 private void DisplayQuarterlySales(Excel._Worksheet oWS) 80 { 81 Excel._Workbook oWB; 82 Excel.Series oSeries; 83 Excel.Range oResizeRange; 84 Excel._Chart oChart; 85 String sMsg; 86 int iNumQtrs; 87 88 //Determine how many quarters to display data for. 89 for( iNumQtrs = 4; iNumQtrs >= 2; iNumQtrs--) 90 { 91 sMsg = "Enter sales data for "; 92 sMsg = String.Concat( sMsg, iNumQtrs ); 93 sMsg = String.Concat( sMsg, " quarter(s)?"); 94 95 DialogResult iRet = MessageBox.Show( sMsg, "Quarterly Sales?", 96 MessageBoxButtons.YesNo ); 97 if (iRet == DialogResult.Yes) 98 break; 99 }100 101 sMsg = "Displaying data for ";102 sMsg = String.Concat( sMsg, iNumQtrs );103 sMsg = String.Concat( sMsg, " quarter(s)." );104 105 MessageBox.Show( sMsg, "Quarterly Sales" );106 107 //Starting at E1, fill headers for the number of columns selected.108 oResizeRange = oWS.get_Range("E1", "E1").get_Resize( Missing.Value, iNumQtrs);109 oResizeRange.Formula = "=\"Q\" & COLUMN()-4 & CHAR(10) & \"Sales\"";110 111 //Change the Orientation and WrapText properties for the headers.112 oResizeRange.Orientation = 38;113 oResizeRange.WrapText = true;114 115 //Fill the interior color of the headers.116 oResizeRange.Interior.ColorIndex = 36;117 118 //Fill the columns with a formula and apply a number format.119 oResizeRange = oWS.get_Range("E2", "E6").get_Resize( Missing.Value, iNumQtrs);120 oResizeRange.Formula = "=RAND()*100";121 oResizeRange.NumberFormat = "$0.00";122 123 //Apply borders to the Sales data and headers.124 oResizeRange = oWS.get_Range("E1", "E6").get_Resize( Missing.Value, iNumQtrs);125 oResizeRange.Borders.Weight = Excel.XlBorderWeight.xlThin;126 127 //Add a Totals formula for the sales data and apply a border.128 oResizeRange = oWS.get_Range("E8", "E8").get_Resize( Missing.Value, iNumQtrs);129 oResizeRange.Formula = "=SUM(E2:E6)";130 oResizeRange.Borders.get_Item( Excel.XlBordersIndex.xlEdgeBottom ).LineStyle 131 = Excel.XlLineStyle.xlDouble;132 oResizeRange.Borders.get_Item( Excel.XlBordersIndex.xlEdgeBottom ).Weight 133 = Excel.XlBorderWeight.xlThick;134 135 //Add a Chart for the selected data.136 oWB = (Excel._Workbook)oWS.Parent;137 oChart = (Excel._Chart)oWB.Charts.Add( Missing.Value, Missing.Value, 138 Missing.Value, Missing.Value );139 140 //Use the ChartWizard to create a new chart from the selected data.141 oResizeRange = oWS.get_Range("E2:E6", Missing.Value ).get_Resize( 142 Missing.Value, iNumQtrs);143 oChart.ChartWizard( oResizeRange, Excel.XlChartType.xl3DColumn, Missing.Value,144 Excel.XlRowCol.xlColumns, Missing.Value, Missing.Value, Missing.Value, 145 Missing.Value, Missing.Value, Missing.Value, Missing.Value );146 oSeries = (Excel.Series)oChart.SeriesCollection(1);147 oSeries.XValues = oWS.get_Range("A2", "A6");148 for( int iRet = 1; iRet <= iNumQtrs; iRet++)149 {150 oSeries = (Excel.Series)oChart.SeriesCollection(iRet);151 String seriesName;152 seriesName = "=\"Q";153 seriesName = String.Concat( seriesName, iRet );154 seriesName = String.Concat( seriesName, "\"" );155 oSeries.Name = seriesName;156 } 157 158 oChart.Location( Excel.XlChartLocation.xlLocationAsObject, oWS.Name );159 160 //Move the chart so as not to cover your data.161 oResizeRange = (Excel.Range)oWS.Rows.get_Item(10, Missing.Value );162 oWS.Shapes.Item("Chart 1").Top = (float)(double)oResizeRange.Top;163 oResizeRange = (Excel.Range)oWS.Columns.get_Item(2, Missing.Value );164 oWS.Shapes.Item("Chart 1").Left = (float)(double)oResizeRange.Left;165 }166
滚动到代码窗口的顶部。将下面的代码行添加到 using 指令列表的末尾:
1 using Excel = Microsoft.Office.Interop.Excel;2 using System.Reflection;
对自动化客户端进行测试
- 按 F5 生成并运行该程序。
- 在窗体上,单击 Button1。该程序将启动 Excel 并将数据填充到一个新的工作表中。
- 在提示您输入季度销售数据时,单击是。一个链接到季度数据的图表就会被添加到工作表中。
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。