Creating apps with Excel 2007 and C # is not always uncomplicated as lots of of us have uncovered out. For example you can not use a macro to compose code for you as you would with VB.net (VBA). To incorporate to the problem you will find when programming with C # in Excel 2007 or other Business solutions you should access the APIs in different ways when compared to using VBA. So there is a little bit of a studying curve.
That explained, Microsoft does provide full help for C # in the Excel 2007 API, by way of the Visual Studio 2008 Equipment for Business 2007 (or 2003). The two are out there as individual downloads from the MSDN web-site.
This article is supposed as an entry position for builders who, like me, both have to have to use C # to programmatic-ally interface with Excel (2007) or simply because it is their language of decision. This write-up will define which references to consist of how-to initialize a workbook accessibility a team of worksheets Egypt just one in particular how-to accessibility and modify a cell. All the illustrations will use the VSTO (Visual Studio Instruments for Business office 2007) venture templates.
Workbook Project Example
This case in point is a uncomplicated walk as a result of to show you the principles of programming with Microsoft C # with Excel 2007. When programming with the VSTO 2007 you have to see the Excel 2007 file as a Home windows Forms client. You interact with this type as you would with any other Windows Variety in.Web.
If you have not performed so, you are going to want to download and install VSTO 2008. You will come across the toolkit on MSDN. The installation is speedy and straightforward. Once you have setup the VSTO, create a venture in Visible Studio 2008:
Alternately click Ctrl + Change + N.
From the New Undertaking Dashboard, extend the C # node and choose the Business node. On the rigth side, you will have various Project Answer template to pick out from. For this case in point, select the Excel 2007 Workbook template. In the ideal fields, give your software, pick a spot and a Solution Identify. Make guaranteed the “Make Directory for Solution” is checked.
In the future screen accept the defaults which to use a new workbook. You can also pick out to use an existing workbook if you want to add some performance to an existing application. Preserve the default “xlsx” file format and click on Ok to end creating the undertaking.
At the time the venture is created you will have an Excel workbook as an alternative of the regular Windows Kind. In the Answer Explorer there will have a C # (cs) file for each and every Excel worksheet and the Workbook.cs file. This follows the exact same Excel template when making a regular Excel Spreadsheet.
At the top of the open up ThisWorkbook.cs are the a variety of references that are incorporated in the template. The VSTO Option template ought to have added the following two references (see down below) when the job was set up. Even so if it is not the case, you can copy and paste the code references into the code editor of the Workbook.cs file.
employing Excel = Microsoft.Business.Interop.Excel
applying Place of work = Microsoft.Place of work.Core
Most of the code is established from the template when the venture is set up. For the illustrations in this article you are going to insert code to the “ThisWorkbook_Startup” which will be identified as when the application is released.
If you want to reference the best sheet (Sheet1) when the application is released, you would use the following code:
Microsoft.Business.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet) this.ActiveSheet
Alternately, if you wanted to reference one more worksheet when the application is released, you would use initially get the collection of the worksheets
Microsoft.Workplace.Interop.Excel.Sheets sheets = (Microsoft.Business office.Interop.Excel.Sheets) this.Worksheets
Then you could get a manage on the worksheet in the collection:
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet) sheets.get_Product (“Sheet2”)
To accessibility a mobile and enter a cell you require to access a Array. This can be one cell or a selection of cells:
Microsoft.Office.Interop.Excel.Vary afield = (Microsoft.Office.Interop.Excel.Range) sheet.get_Assortment (“A1”, System.Reflection.Missing.Price)
afield.set_Value (Method.Reflection.Missing.Value, “Hi there Planet”)
The entire code for each illustrations are supplied beneath:
Example on How To Entry the Active Sheet
public partial class ThisWorkbook
non-public void ThisWorkbook_Startup (item sender, System.EventArgs e)
// Case in point 1 – To get the top sheet (eg Sheet1) or the Energetic Sheet use this syntax
Microsoft.Workplace.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet) this.ActiveSheet
// To get a mobile or team of cells, you can use the subsequent syntax
Microsoft.Workplace.Interop.Excel.Vary afield = (Microsoft.Business.Interop.Excel.Array) sheet.get_Array (“A1”, Technique.Reflection.Missing.Price)
// Established the price of the A1 cell equal to “Good day Globe”
afield.established_Benefit (Program.Reflection.Missing.Worth, “Good day Environment”)
Case in point Accessibility a Unique Worksheet
public partial course ThisWorkbook
private void ThisWorkbook_Startup (item sender, Process.EventArgs e)
// Case in point 2 – To get a distinct sheet in a workbook
// Get a take care of on all the worksheets in the Workbook
Microsoft.Office.Interop.Excel.Sheets sheets = (Microsoft.Place of work.Interop.Excel.Sheets) this.Worksheets
// Get a distinct sheet in the Workbook
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office environment.Interop.Excel.Worksheet) sheets.get_Item (“Sheet2”)
// To get a mobile or group of cells, you can use the adhering to syntax
Microsoft.Office.Interop.Excel.Array afield = (Microsoft.Business office.Interop.Excel.Variety) sheet.get_Vary (“A1”, Method.Reflection.Missing.Price)
// Set the benefit of the A1 cell equivalent to “Howdy Planet”
afield.established_Value (Process.Reflection.Missing.Worth, “Hi there Earth”)
This bit of information and facts, even though minimalistic, is tough to arrive by and will with any luck , be of excellent aid in your quest to establish Excel software with the VSTO in C #.