How to automate Microsoft Excel in Microsoft Visual C #. NET

Through automated processes, applications written in languages such as Visual C #. NET can be programmed to control other applications. With Excel Automation, you can perform operations such as creating a workbook, adding data to a workbook, or creating charts. For Excel and other Microsoft Office applications, almost all operations that can be performed manually on the user interface can also be performed programmatically using the "Automation" function.

Excel exposes this program function through an object model. This object model is a collection of classes and methods that act as the logical component of Excel. For exampleApplicationObject,WorkbookObject andWorksheetObjects, each of which contains the functions of those components in Excel. To access this object model from Visual C #. NET, you can set the project reference for the Type Library.

This article describes how to set up appropriate project references for the Excel Type Library for Visual C #. NET, and provides sample code that enables Excel to run automatically.

Create an automatic client for Microsoft Excel

InViewChooseToolboxTo display the toolbox, and then add a button to Form1.

Double-clickButton1. The code window for this form is displayed.

In the code window

1 private void button1_Click(object sender, System.EventArgs e)2 {3 }

Replace

  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                     

Scroll to the top of the code window. Add the following code linesUsingEnd of the command list:

1 using Excel = Microsoft.Office.Interop.Excel;2 using System.Reflection; 

Test the automated client