In this article I will show you how to export data from a SQL Database (Northwind example) to an Excel spreadsheet, using Office Web Components (OWC). In order to use the OWC COM component, we will have to create a COM wrapper. The data to be exported will be displayed in a DataGrid, and there will be an option to generate the Excel file using the SqlDataReader class.
First of all, I have to say that the strict Microsoft licensing policies regarding OWC prevents you from using OWC on an Internet web server. Personally I think these particular licensing policies are way too strict. Anyway, you can find more info on licensing issues on OWC here.
Now let’s move on with the real deal.
Creating the COM wrapper for OWC
Visual Studio .NET will automatically create the wrapper for you, when adding a reference to the Office Web Components library. Alternatively, when using a basic editor, you might have to create the wrapper yourself using the tlbimp.exe command line utility. We will look at these ways of creating the wrapper.
First, we’ll look at how this is done using VS.NET.
![]()
We select Add Reference (right click on the project), select the COM tab, and select the Microsoft Office Web Components DLL. Click OK, and VS.NET will add the reference to your project and create a corresponding wrapper. You will see that the OWC reference has been added to your project.
Using intelli-sense you can now explore the classes, interfaces, delegates and enums of the OWC ‘namespace’.
To manually create the wrapper we use the tlbimp.exe utility. We can find the OWC COM DLL here: \Program Files\Microsoft Office\Office\MSOWC.DLL.
We open up a command prompt and navigate to the directory where MSOWC.DLL resides. Then we issue the following command:
The /namespace option defines which namespace the wrapper will be known by.
Now we need to move the dotnetmsowc.dll assembly, which contains our .NET wrapper class to the web application’s bin directory.
To find out which classes, interfaces etc. are available (assuming we’re not using VS.NET), we can use the Intermediary Language Disassembler (ildasm.exe). Open ildasm (Start, Run, ildasm) and open the dotnetmsowc.dll assembly. We can now explore all classes, their constructors, methods, attributes etc. as you can see below.
![]()
The web form and codebehind file
Our web form will show the data in the DataGrid control. Additionally a textbox for the Excel file name to be exported and a button to perform the export will be displayed at the top of the page.
Have a look at the ASPX file:
No rocket science there. Note that there is no OnClick or OnServerClick attribute for our ‘Export to Excel’ button. This is because VS.NET adds its own EventHandler for the button’s onclick event, as we can see when we have a look at the codebehind file.
There’s two private methods I’ve added to the WebForm1 class. The BindDataGrid(), which opens the SqlConnection and binds a SqlDataReader object to the DataGrid.
The WriteDataGrid2Excel uses the class properties sql and cnn to open a SqlDataReader object, creates an instance of the SpreadSheetClass class – which we can find in OWC - and loops through all the records. For every record, all column values are being put into a separate cell, using the Cells property on the ActiveSheet object.
NOTE: The error messages you get back from the COM wrapper are not very useful. I had a permissions issue going on when writing the file to disk, and solved it by granting the ASP.NET worker process account write rights to my application folder.
Running the page results in the following screen:
![]()
Typing in a filename (ending with .xls) and clicking the ‘Export to Excel’ button will result in an Excel file being written in your web application directory containing the exported data.
Conclusion
In this article we’ve briefly seen how to use existing COM components in the .NET framework (also called COM interoperability). We’ve seen how to generate a COM wrapper, using the tlbimp.exe utility or simply VS.NET.
Also we have made use of Office Web Components to Export data from a SqlDataReader object to an Excel spreadsheet.
Comments
First of all, I have to say that the strict Microsoft licensing policies regarding OWC prevents you from using OWC on an Internet web server. Personally I think these particular licensing policies are way too strict. Anyway, you can find more info on licensing issues on OWC here.
Now let’s move on with the real deal.
Creating the COM wrapper for OWC
Visual Studio .NET will automatically create the wrapper for you, when adding a reference to the Office Web Components library. Alternatively, when using a basic editor, you might have to create the wrapper yourself using the tlbimp.exe command line utility. We will look at these ways of creating the wrapper.
First, we’ll look at how this is done using VS.NET.
We select Add Reference (right click on the project), select the COM tab, and select the Microsoft Office Web Components DLL. Click OK, and VS.NET will add the reference to your project and create a corresponding wrapper. You will see that the OWC reference has been added to your project.
Using intelli-sense you can now explore the classes, interfaces, delegates and enums of the OWC ‘namespace’.
To manually create the wrapper we use the tlbimp.exe utility. We can find the OWC COM DLL here: \Program Files\Microsoft Office\Office\MSOWC.DLL.
We open up a command prompt and navigate to the directory where MSOWC.DLL resides. Then we issue the following command:
| tlbimp msowc.dll /out:dotnetmsowc.dll /namespace:OWC |
The /namespace option defines which namespace the wrapper will be known by.
Now we need to move the dotnetmsowc.dll assembly, which contains our .NET wrapper class to the web application’s bin directory.
To find out which classes, interfaces etc. are available (assuming we’re not using VS.NET), we can use the Intermediary Language Disassembler (ildasm.exe). Open ildasm (Start, Run, ildasm) and open the dotnetmsowc.dll assembly. We can now explore all classes, their constructors, methods, attributes etc. as you can see below.
The web form and codebehind file
Our web form will show the data in the DataGrid control. Additionally a textbox for the Excel file name to be exported and a button to perform the export will be displayed at the top of the page.
Have a look at the ASPX file:
| <%@ Page language="c#" Codebehind="WebForm1.aspx.cs" AutoEventWireup="false" Inherits="cominterop.WebForm1" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" > <HTML> <HEAD> <title>Exporting Data To Excel, using COM Interop and OWC</title> </HEAD> <body> <form id="Form1" method="post" runat="server"> Export File Name: <asp:TextBox ID="xlfile" Runat="server"></asp:TextBox> <asp:Button ID="export2excel" Runat="server" Text="Export to Excel" /><br /> <br /> <asp:DataGrid id="DataGrid1" runat="server"></asp:DataGrid> </form> </body> </HTML> |
No rocket science there. Note that there is no OnClick or OnServerClick attribute for our ‘Export to Excel’ button. This is because VS.NET adds its own EventHandler for the button’s onclick event, as we can see when we have a look at the codebehind file.
| using System;
using System.Data; using System.Data.SqlClient; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using OWC; namespace cominterop { public class WebForm1 : System.Web.UI.Page { protected System.Web.UI.WebControls.DataGrid DataGrid1; private SqlCommand sql; protected System.Web.UI.WebControls.Button export2excel; protected System.Web.UI.WebControls.TextBox xlfile; private SqlConnection cnn; private void Page_Load(object sender, System.EventArgs e) { this.BindDataGrid(); } private void BindDataGrid() { cnn = new SqlConnection("Initial Catalog=Northwind;Data Source=localhost;uid=sa;pwd="); sql = new SqlCommand("select * from products",cnn); cnn.Open(); SqlDataReader reader = sql.ExecuteReader(); this.DataGrid1.DataSource = reader; this.DataGrid1.DataBind(); reader.Close(); cnn.Close(); } private void WriteDataGrid2Excel() { SpreadsheetClass xlsheet = new SpreadsheetClass(); cnn.Open(); SqlDataReader reader = this.sql.ExecuteReader(); int numbercols = reader.FieldCount; int row=1; while (reader.Read()) { for (int i=0;i<numbercols;i++) { xlsheet.ActiveSheet.Cells[row,i+1] = reader.GetValue(i).ToString(); } row++; } reader.Close(); cnn.Close(); xlsheet.ActiveSheet.Export(Server.MapPath(".")+"\\"+this.xlfile.Text,OWC.SheetExportActionEnum.ssExportActionNone); } private void export2excel_Click(object sender, System.EventArgs e) { if (this.xlfile.Text.Trim()!="") { this.WriteDataGrid2Excel(); } } #region Web Form Designer generated code override protected void OnInit(EventArgs e) { InitializeComponent(); base.OnInit(e); } private void InitializeComponent() { this.export2excel.Click += new System.EventHandler(this.export2excel_Click); this.Load += new System.EventHandler(this.Page_Load); } #endregion } } |
There’s two private methods I’ve added to the WebForm1 class. The BindDataGrid(), which opens the SqlConnection and binds a SqlDataReader object to the DataGrid.
The WriteDataGrid2Excel uses the class properties sql and cnn to open a SqlDataReader object, creates an instance of the SpreadSheetClass class – which we can find in OWC - and loops through all the records. For every record, all column values are being put into a separate cell, using the Cells property on the ActiveSheet object.
NOTE: The error messages you get back from the COM wrapper are not very useful. I had a permissions issue going on when writing the file to disk, and solved it by granting the ASP.NET worker process account write rights to my application folder.
Running the page results in the following screen:
Typing in a filename (ending with .xls) and clicking the ‘Export to Excel’ button will result in an Excel file being written in your web application directory containing the exported data.
Conclusion
In this article we’ve briefly seen how to use existing COM components in the .NET framework (also called COM interoperability). We’ve seen how to generate a COM wrapper, using the tlbimp.exe utility or simply VS.NET.
Also we have made use of Office Web Components to Export data from a SqlDataReader object to an Excel spreadsheet.
Comments
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
atagrid id = "d" runat = "server" autogeneratecolumns = "true" cellspacing = "0" cellpadding = "4" BorderWidth="1" bordercolor = "red"/>
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
|
||||||
|
浙公网安备 33010602011771号