如何在 Microsoft Visual C# .NET 中实现 Microsoft Excel 自动化_.NET_编程开发_程序员俱乐部

通过自动化过程，使用诸如 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 知识库中相应的文章： 328912 Microsoft Office XP 主 interop 程序集 (PIA) 可供下载
   3. 在添加引用对话框中单击确定以接受您的选择。如果系统提示您为选定的库生成包装，请单击是。
4. 在视图菜单上，选择工具箱以显示工具箱，然后向 Form1 添加一个按钮。
5. 双击 Button1。出现该窗体的代码窗口。
6. 在代码窗口中，将以下代码

   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; 

   对自动化客户端进行测试

   1. 按 F5 生成并运行该程序。
   2. 在窗体上，单击 Button1。该程序将启动 Excel 并将数据填充到一个新的工作表中。
   3. 在提示您输入季度销售数据时，单击是。一个链接到季度数据的图表就会被添加到工作表中。