Use Excel to expand. Net Applications

Extend your. NET application with Excel extension. NET application with Excel

By Tony Patton
Author: Tony Patton
Translation: Purple endurer

Keywords:. Net | Microsoft Office | office suites | programming ages | software engineering/Development
Keywords:. Net | Microsoft Office | office suites | programming language | software engineering/Development

Http://techrepublic.com.com/5100-3513_11-5815515.html? Tag = NL. e101

Takeaway:
A. NET application may be greatly enhanced by providing additional functional via Excel integration. this includes des the number-crunching capabilities inherent in Excel, as well as charting and much more. learn more about Excel and.. Net integration.

Overview:
Using Excel integration to provide additional functions, A. NET application can be greatly enhanced. This includes Excel's inherent digital processing capabilities and charts. Let's learn more about Excel and. Net integration.

In a recent column, We Have ed the process of integrating Microsoft Word With. net Framework. there are numerous integration possibilities as the full power of the Microsoft Office suite is available. in this article, we examine another scenario involving Microsoft Excel.

In our recent column, we have browsed the process of integrating Microsoft Word With. NET Framework. There are a lot of integration possibilities when all the functions of Microsoft Office suite are available. In this article, we examine other situations involving Microsoft Excel.

VBA persists

We must point out that the Microsoft Office product suite utilizes the Visual Basic for Applications (VBA), so a little knowledge of the Component Object Model (COM) object is helpful. however,. net com InterOP feature makes it easy to utilize COM objects within. NET application. let's begin with an overview of the Excel object model.

VBA continuity
We must note that Microsoft Office product suite uses VBA, so it is helpful to know the Component Object Model (COM. However, the. NET com interoperability feature makes it easy to use COM objects in. NET applications.

Excel Object Model

Microsoft Excel provides literally hundreds of objects for programmatically working within its environment. the whole set is beyond the scope of this article, so let's examine a few objects to get up and running. here are four common objects:

Excel Object Model
Microsoft Excel precisely provides many objects that are conducive to working in its environment. This complete set is beyond the scope of this article, So we test some objects to obtain and run them. There are four common objects.

• Application:Represents the entire Excel application. It exposes a great deal of information about the running application, the options applied to that instance, and the current user objects open within the instance.
  Application:Represents the entire Excel application. It displays a large amount of information about running applications, options applied to entities, and current user objects in the real body.
• Workbook:A single Excel Workbook that may contain one or more worksheets.Workbook (workbook ):A single Excel worksheet may contain one or more worksheets ).
• Worksheet:An individual Excel worksheet. Most of the properties, methods, and events of the worksheet object are identical or similar to members provided by the application and/or workbook classes.
  Worksheets (worksheet ):A separate worksheet. The vast majority of worksheet attributes, methods, and events are equivalent to or similar to members provided by application and/or workbook objects.
• Range:A range of cells within a worksheet. A range object represents a cell, a row, a column, a selection of cells containing one or more blocks of cells, or even a group of cells on multiple sheets.
  Range:Is the cell area in the worksheet. A range object represents a cell, a row, a column, a selected area that contains one or more cell blocks, or even a group of cells in multiple worksheets.

The object model begins with the application class at the top, since it is the starting point for accessing excel. before you can begin working with the Excel Object Model. net com InterOP, you must make it available to your project.

The object mode starts from the application class at the top, because this is the starting point for accessing excel. Before you may start working with an Excel image model through. Net com interoperability, you must make it available to your project.

Using Microsoft Excel

The Microsoft Excel Object Library must be made available to your. net project. if using Visual Studio. net, a reference may be added to a project via the project | add reference menu selection. the com tab within the Add reference window provides access to com libraries installed on the system. excel is listed as Microsoft. excel, and the specific name will depend on the Excel version installed. I have Excel 2003 on my system, so the com library is listed as Microsoft. excel 11.0 Object Library. in addition, two namespaces are necessary:

Use Microsoft Excel
The Microsoft Excel Object Library must be available to your. Net project. If Visual Studio. NET is used, you must add a reference by selecting project | add reference. The com tab in the Add reference window provides access to the com library installed in the system. Excel is listed as Microsoft. Excel, and specific name depends on the Excel version you have installed. My system has Excel 2003, so the com library column is Microsoft. Excel 11.0 Object Library. In addition, two namespaces are required:

• Microsoft. Office. InterOP. Excel:Allows you to work with Excel objects via. Net InterOP.
  Microsoft. Office. InterOP. Excel:Allows you to work with Excel objects through. Net interoperability.
• System. runtime. interopservices:Except des the comexception class, allowing you to properly handle com-related exceptions.
  System. runtime. interopservices:Including com exception classes, allowing you to correctly handle com-related exceptions.

The code snippet in{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Mm_openbrwindow ('/5110-3513-5815641.html', '', 'width = 680, Height = 600, Top = 0, Left = 0, toolbar = No, location = Yes, directories = No, status = No, menubar = No, scrollbars = Yes, resizable = Yes ')
}
}
}
}
}
}
}
}
}
}
}
} "Href =" http://techrepublic.com.com/5100-3513_11-5815515.html? Tag = NL. e101 # listing % 20a "> listingLoads and opens an Excel file from the local file system.

The code segment in listing a loads from the local file system and opens an Excel file.

Microsoft. Office. InterOP. Excel. Application Excel = NULL;
Microsoft. Office. InterOP. Excel. Workbook WB = NULL;

Object missing = type. missing;

Try {

Excel = new Microsoft. Office. InterOP. Excel. Application ();
WB = excel. workbooks. open ("C: // test.xls", missing, and missing );
Excel. Visible = true;
WB. Activate ();

} Catch (comexception ex ){
MessageBox. Show ("Error accessing Excel:" + ex. tostring ());

} Catch (exception ex ){
MessageBox. Show ("error:" + ex. tostring ());

}

({
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Mm_openbrwindow ('/5110-3513-5815642.html', '', 'width = 680, Height = 600, Top = 0, Left = 0, toolbar = No, location = Yes, directories = No, status = No, menubar = No, scrollbars = Yes, resizable = Yes ')
}
}
}
}
}
}
}
}
}
}
}
} "Href =" http://techrepublic.com.com/5100-3513_11-5815515.html? Tag = NL. e101 # listing % 20B "> listing BContains the equivalent VB. NET code .)
(List B contains the equivalent VB. NET code)

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Try
excel = New Microsoft.Office.Interop.Excel.Application
wb = excel.Workbooks.Open("c://test.xls")
excel.Visible = True
wb.Activate()
Catch ex As COMException
MessageBox.Show("Error accessing Excel: " + ex.ToString())
Catch ex As Exception
MessageBox.Show("Error: " + ex.ToString())
End Try
 

Here are a few notes on the Code:

Some annotations in the Code:

• The open method of the workbooks object (accessed via the application object) allows you to access an existing Excel file. note: As a C # developer, the type. missing Value is necessary since the Excel VBA object model accepts numerous optional parameters. the type. missing Value allows you to pass nothing to the parameter, but still recognize it. VB. net supports optional parameters to this approach.
  The open method of the workbooks object (accessed through the Application Object) allows you to access an existing Excel file. Note: As a C # developer, the type. Missing Value is necessary because Excel VBA receives a large number of optional parameters for the image model. The type. Missing Value allows you to not transmit parameters, but still recognize them. VB. NET supports optional parameters.
• The application object is set to visible and the workbook object is activated to make it show on the screen.
  The application object is set to visible, and the workbook object is activated to display on the screen.
• The catch blocks handle specific com-related exceptions as well as general exceptions.
  Catch blocks handle special com-related exceptions and general exceptions.

The previous Code does not encompass a complete application, but demonstrates how Excel may be used in both C # and VB. net. let's take it a step further by manipulating the data within a worksheet.

The previous Code does not contain a complete application, but demonstrates how to use excel in C # and VB. NET. Let's proceed further by operating the data in the worksheet.

The VB. NET code in{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Mm_openbrwindow ('/5110-3513-5815643.html', '', 'width = 680, Height = 600, Top = 0, Left = 0, toolbar = No, location = Yes, directories = No, status = No, menubar = No, scrollbars = Yes, resizable = Yes ')
}
}
}
}
}
}
}
}
}
}
} "Href =" http://techrepublic.com.com/5100-3513-5815515-2.html#Listing%20C "> listing cCreates a new Excel sheet, inserts numbers, and performs a calculation.

In list C, the VB. NET Code creates a new Excel worksheet, inserts a number, and computes IT.

Private sub button#click (byval sender as system. Object, byval e
System. eventargs) handles button1.click

Dim excel as Microsoft. Office. InterOP. Excel. Application

Dim WB as Microsoft. Office. InterOP. Excel. Workbook

Dim ws as Microsoft. Office. InterOP. Excel. Worksheet

Dim RNG as Microsoft. Office. InterOP. Excel. Range

Try

Excel = new Microsoft. Office. InterOP. Excel. Application
WB = excel. workbooks. Add ()
Ws = WB. activesheet ()
RNG = ws. Range ("A1 ")
RNG. value = "techrepublic.com"
RNG = ws. Range ("A3 ")
RNG. value = "quarter"
RNG = ws. Range ("B3 ")
RNG. value = "sales"
RNG = ws. Range ("A5 ")
RNG. value = "first"
RNG = ws. Range ("B5 ")
RNG. value = 1000.0
RNG = ws. Range ("A6 ")
RNG. value = "second"
RNG = ws. Range ("B6 ")
RNG. value = 2000.0
RNG = ws. Range ("A7 ")
RNG. value = "third"
RNG = ws. Range ("B7 ")
RNG. value = 4500.0
RNG = ws. Range ("A8 ")
RNG. value = "fourth"
RNG = ws. Range ("B8 ")
RNG. value = 4000.0
RNG = ws. Range ("A10 ")
RNG. value = "Total"
RNG = ws. Range ("B10 ")
RNG. formula = "= @ sum (b5.. B8 )"
RNG. Calculate ()
Excel. Visible = true
WB. Activate ()

Catch ex as comexception
MessageBox. Show ("Error accessing Excel:" + ex. tostring ())

Catch ex as exception
MessageBox. Show ("error:" + ex. tostring ())

End try

End sub

Only the code for a button is wrongly ded .({
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Function anonymous ()
{
Mm_openbrwindow ('/5110-3513-5815644.html', '', 'width = 680, Height = 600, Top = 0, Left = 0, toolbar = No, location = Yes, directories = No, status = No, menubar = No, scrollbars = Yes, resizable = Yes ')
}
}
}
}
}
}
}
}
}
}
} "Href =" http://techrepublic.com.com/5100-3513-5815515-2.html#Listing%20D "> listing dContains the equivalent C # code .)
This is just the code for a button. (Listing D contains the equivalent C # code .)

 private void button1_Click(object sender, System.EventArgs e) {
Microsoft.Office.Interop.Excel.Application excel = null;
Microsoft.Office.Interop.Excel.Workbook wb = null;
Microsoft.Office.Interop.Excel.Worksheet ws = null;
Microsoft.Office.Interop.Excel.Range rng = null;
object  missing  = Type.Missing;
try {
excel = new Microsoft.Office.Interop.Excel.Application();
wb = excel.Workbooks.Add(missing);
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;
rng = ws.get_Range("A1", missing);
rng.Value2 = "Techrepublic.com";
rng = ws.get_Range("A3", missing);
rng.Value2 = "Quarter";
rng = ws.get_Range("B3", missing);
rng.Value2 = "Sales";
rng = ws.get_Range("A5", missing);
rng.Value2 = "First";
rng = ws.get_Range("B5", missing);
rng.Value2 = 1000.0;
rng = ws.get_Range("A6", missing);
rng.Value2 = "Second";
rng = ws.get_Range("B6", missing);
rng.Value2 = 2000.0;
rng = ws.get_Range("A7", missing);
rng.Value2 = "Third";
rng = ws.get_Range("B7", missing);
rng.Value2 = 4500.0;
rng = ws.get_Range("A8", missing);
rng.Value2 = "Fourth";
rng = ws.get_Range("B8", missing);
rng.Value2 = 4000.0;
rng = ws.get_Range("A10", missing);
rng.Value2 = "Total";
rng = ws.get_Range("B10", missing);
rng.Formula = "=@Sum(B5..B8)";
rng.Calculate();
excel.Visible = true;
wb.Activate();
} catch (COMException ex) {
MessageBox.Show("Error accessing Excel: " + ex.ToString());
} catch (Exception ex) {
MessageBox.Show("Error: " + ex.ToString());
} }
 

Here are a few notes on the Code:
Some annotations in the Code:

• A new Excel Workbook is created with the add method of the workbook's property of the Application object. This creates a workbook with one blank worksheet.
  Use the add method of the workbook attribute of the Application object to create a new workbook. Create a workbook with an empty worksheet.
• The current sheet is accessed via the activesheet property of the worksheet object.
  The current worksheet can be accessed through the activesheet attribute of the worksheet object.
• A range object is used to work with individual cells. the cell is accessed via its location on the sheet. for example, a title for the sheet (techrepublic.com) is inserted at the first cell (a1 = column A and row 1 ). the value property of the range object is used to populate the cell.
  A range object is used to work with individual cells. A cell can be accessed at the position of a worksheet. For example, the worksheet title (techrepublic.com) is inserted into the first cell (a1 = column A, Row 1 ).
  The Value Attribute of the range object is used to set the value of a cell.
• The formula property of the range object allows you to assign a formula to a cell or group of cells. In this example, the total of the second column of values is displayed.
  The formula of the range object allows you to assign a formula to a cell or a group of cells. In this example, the total value of the second column is displayed.
• The Calculate Method of the range object processes the formula.
  The Calculate Method of the range object processing formula.

You may notice some differences between the C # and VB. net versions. the get_range method of the worksheet class is used to instantiate the range object in C #. in addition, the get_range method features a second optional parameter so the type. missing value is used. finally, the activesheet object must be cast to the worksheet class to use it. you shoshould be prepared for such differences when using C # To utilize vba com objects.

You may notice some differences between C # and VB. NET. In C #, The get_range method of the worksheet class is used to instantiate the range object. The get_range method uses the second optional parameter, So type. Missing is used. Finally, the activesheet object must be used in the worksheet class. When you use C # To use vba com objects, you need to prepare for these differences.

The Environment

Some readers have questioned the vulnerability of a user's system when working with Excel, but the examples in this article are built as windows form-Applications based. the environment envisioned is an internal application, so security shoshould not be as big an issue as if it is opened to the world. using Excel via ASP. NET provides its own set of issues, and it is beyond the scope of this article.

Environment

Some readers have asked about the weakness of the user system when working in Excel, but the example in this article is to create an application based on the Windows platform. It is expected that the environment is an internal application, so whether or not it is open to the world, security will not be a big problem. Using Excel through ASP. NET provides its own result set, which is beyond the scope of this article.

Extending functionality

Integrating excel with A. NET application allows you to easily and quickly provide powerful functionality within an application. The calculation and presentation features of Excel offer a wealth of options.

Extended functionality

The integration of Excel. Net Applications allows you to provide powerful functions more easily and quickly. The computing and presentation features of Excel provide diversity.