How-To Develop Excel Purposes With C-Sharp and VSTO 2008
Creating functions with Excel 2007 and C# just isn't all the time simple as many people have discovered. For instance you can not use a macro to jot down code for you as you'd with VB.internet (VBA). So as to add to the dilemma you will discover when programming with C# in Excel 2007 or different Workplace merchandise it's essential to entry the APIs in a different way in comparison with utilizing VBA. So there's a little bit of a studying curve.
That stated, Microsoft does present full help for C# within the Excel 2007 API, by means of the Visible Studio 2008 Instruments for Workplace 2007 (or 2003). Each can be found as separate downloads from the MSDN web site.
This text is meant as an entry level for builders who, like me, both want to make use of C# to programmatic-ally interface with Excel (2007) or as a result of it's their language of selection. This text will define which references to incorporate; how-to initialize a workbook; entry a gaggle of worksheets; or only one particularly; how-to entry and alter a cell. All of the examples will use the VSTO (Visible Studio Instruments for Workplace 2007) mission templates.
Workbook Undertaking Instance
This instance is a straightforward stroll by means of to point out you the fundamentals of programming with Microsoft C# with Excel 2007. When programming with the VSTO 2007 you need to view the Excel 2007 file as a Home windows Kinds shopper. You work together with this type as you'd with every other Home windows Type in.Web.
If you have not achieved so, you'll have to obtain and set up VSTO 2008. One can find the toolkit on MSDN. The set up is fast and straightforward. Upon getting setup the VSTO, create a mission in Visible Studio 2008:
->File
->New
->Undertaking
Alternatively click on Ctrl+Shift+N.
From the New Undertaking Dashboard, Increase the C# node and choose the Workplace node. On the rigth aspect, you'll have a number of Undertaking Answer template to select from. For this instance, choose the Excel 2007 Workbook template. Within the applicable fields, give your software, select a location and a Answer Identify. Make sure that the "Create Listing for Answer" is checked.
Within the subsequent display settle for the defaults which to make use of a brand new workbook. You can too select to make use of an current workbook if you wish to add some performance to an current software. Maintain the default "xlsx" file format and click on OK to complete creating the mission.
As soon as the mission is created you'll have an Excel workbook as an alternative of the standard Home windows Type. Within the Answer Explorer there can have a C# (cs) file for every Excel worksheet and the Workbook.cs file. This follows the identical Excel template when creating an ordinary Excel Spreadsheet.
On the prime of the open ThisWorkbook.cs are the assorted references which can be included within the template. The VSTO Answer template ought to have added the next two references (see under) when the mission was setup. Nevertheless if it isn't the case, you possibly can copy and paste the code references into the code editor of the Workbook.cs file.
...
utilizing Excel = Microsoft.Workplace.Interop.Excel;
utilizing Workplace = Microsoft.Workplace.Core;
Many of the code is created from the template when the mission is setup. For the examples on this article you'll add code to the "ThisWorkbook_Startup" which shall be known as when the applying is launched.
If you want to reference the highest sheet (Sheet1) when the Utility is launched, you'd use the next code:
Microsoft.Workplace.Interop.Excel.Worksheet sheet = (Microsoft.Workplace.Interop.Excel.Worksheet)this.ActiveSheet;
Alternatively, if you happen to wanted to reference one other worksheet when the applying is launched, you'd use first get the gathering of the worksheets
Microsoft.Workplace.Interop.Excel.Sheets sheets = (Microsoft.Workplace.Interop.Excel.Sheets)this.Worksheets;
Then you can get a deal with on the worksheet within the assortment:
Microsoft.Workplace.Interop.Excel.Worksheet sheet = (Microsoft.Workplace.Interop.Excel.Worksheet)sheets.get_Item("Sheet2");
To entry a cell and enter a cell you want to entry a Vary. This may be one cell or a spread of cells:
Microsoft.Workplace.Interop.Excel.Vary afield = (Microsoft.Workplace.Interop.Excel.Vary)sheet.get_Range("A1",System.Reflection.Lacking.Worth);
afield.set_Value(System.Reflection.Lacking.Worth, "Good day World");
The entire code for each examples are supplied under:
Instance on How To Entry the Energetic Sheet
namespace Take a look at
{
public partial class ThisWorkbook
{
personal void ThisWorkbook_Startup(object sender, System.EventArgs e)
Instance Entry a Explicit Worksheet
namespace Take a look at
{
public partial class ThisWorkbook
{
personal void ThisWorkbook_Startup(object sender, System.EventArgs e)
Conclusion
This bit of knowledge, though minimalistic, is tough to return by and can hopefully be of nice assist in your quest to develop Excel software with the VSTO in C#.