Solved

.NET Web Forms and Automating Excel on Client PC.

Posted on 2002-03-20
6
243 Views
Last Modified: 2013-11-25
From a VB .NET Web Project Web Form, I want to:

1. Change a label's text to "Report Started" when a button on the form in IE is clicked,
2. Instance Excel on the user's PC,
3. Create a new Workbook in that instance,
4. Create a new worksheet in that workbook,
5. Set cell A1's text to "Bob",
6. Save & Close the workbook, and dispose of the Excel instance.
7. Change the label to "Reported Compeleted".

From a Windows form, this code looks like:

lbl1.text="Report Started"
Dim EXL As New Excel.Application()
Dim WSheet As New Excel.Worksheet()
EXL.Visible = True
WSheet = CType(EXL.Workbooks.Add.Worksheets.Add, Excel.Worksheet)
With WSheet
            .Cells(1, 1).Value = "Bob"
end with
EXL.Workbooks.saveas()
EXL.Workbooks.Close()
lbl1.Text = "Report Completed"

So how do I accomplish the same thing in a Web Form? I realize (and want) the user to manually grant permission in IE to start Excel on their PC, and I understand Automation, but doing the above in a web form is another matter.
0
Comment
Question by:thePrisoner
6 Comments
 
LVL 44

Accepted Solution

by:
bruintje earned 300 total points
Comment Utility
Hi thePrisoner,

found this sample in ASP got no .net here but assume the info will at least give you and idea

<snippet>

- Using Excel Automation
===============================================================================

With client-side code, you can extract the HTML from the DataGrid and then
Automate Excel to display the HTML in a new workbook. With Excel Automation, the
data will always appear outside of the browser in an Excel application window.
One advantage to Automation is that you can programmatically control Excel if
you wish to make further modifications to the workbook after the data has been
exported; however, since Excel is not marked safe for scripting, your clients
must have applied security settings in Internet Explorer that will allow
Automation.

Here's the sample:
==================

File: ExcelDataGrid.aspx.
Copy the following in the HTML view of an ASPX page (say ExcelDataGrid.aspx, within a
an ASP.NET Visual Basic Web Application Project called say DataStuff)
----------------------------------------------------------------------------

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="ExcelDataGrid.aspx.vb" Inherits="DataStuff.ExcelDataGrid"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
      <HEAD>
            <title>ExcelDataGrid</title>
            <meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
            <meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
            <meta name="vs_defaultClientScript" content="JavaScript">
            <meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
            <script language="vbscript">
            Sub Button1_onclick()
                  Dim sHTML
                  sHTML = window.Form1.children("DataGrid1").outerhtml
                  Dim oXL, oBook
                  Set oXL = CreateObject("Excel.Application")
                  Set oBook = oXL.Workbooks.Add
                  oBook.HTMLProject.HTMLProjectItems("Sheet1").Text = sHTML
                  oBook.HTMLProject.RefreshDocument
                  oXL.Visible = true
                  oXL.UserControl = true
            end sub
            </script>
      </HEAD>
      <body MS_POSITIONING="GridLayout">
            <form id="Form1" method="post" runat="server">
                  <asp:DataGrid id="DataGrid1" runat="server"></asp:DataGrid>
                  <INPUT id="Button1" type="button" value="Go!" name="Button1" />
            </form>
      </body>
</HTML>

-------------------------------------------------------------------------------
Explanation of the above code: In the above code we simply are adding
a DataGrid web control to the form and an HTML button control.

We wire up the HTML button control to a click event handler. Inside this
event handler, we're using Automation to create an Instance of the Excel application
on the client. Then we're extracting the HTML from the DataGrid and then
Automating Excel to display the HTML in a new workbook.
Note that you may have to modify the security settings on the
client's browser for the code to work.


Code Behind File: ExcelDataGrid.aspx.vb
Copy the following in the code behind file ExcelDataGrid.aspx.vb
--------------------------------------------
Public Class ExcelDataGrid
    Inherits System.Web.UI.Page
    Protected WithEvents Button1 As System.Web.UI.WebControls.Button
    Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        Dim objConn As New System.Data.SqlClient.SqlConnection("User ID=sa;Initial Catalog=pubs;server=localhost;Password=password")
        objConn.Open()

        Dim strSQL As String
        Dim objDataset As New DataSet()
        Dim objAdapter As New System.Data.SqlClient.SqlDataAdapter()

        ' Get all the customers from the USA
        strSQL = "Select * from authors"
        objAdapter.SelectCommand = New System.Data.SqlClient.SqlCommand(strSQL, objConn)
        ' Fill the dataset
        objAdapter.Fill(objDataset)
        ' Create a new view
        Dim oView As New DataView(objDataset.Tables(0))
        ' Set up the data grid and bind the data
        DataGrid1.DataSource = oView
        DataGrid1.DataBind()

    End Sub


End Class

-------------------------------------------
Explanation of the above code: In the Page_Load event handler we're simply creating
the data connection, dataset and databinding the DataGrid.

Hope this helps.
Thanks
-Sachin
Microsoft Developer Support

</snippet>

:O)Bruintje
0
 
LVL 44

Expert Comment

by:bruintje
Comment Utility
especially the line on security settings is important to keep in mind i guess
0
 

Author Comment

by:thePrisoner
Comment Utility
bruintje,

That gets me part of the way, but how do I access to oView directly, not a datagrid that is populated from the oView?

Thanks!
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:thePrisoner
Comment Utility
bruintje,

That gets me part of the way, but how do I access to oView directly, not a datagrid that is populated from the oView?

Thanks!
0
 
LVL 49

Expert Comment

by:DanRollins
Comment Utility
Hi thePrisoner,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept bruintje's comment(s) as an answer.

thePrisoner, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.
==========
DanRollins -- EE database cleanup volunteer
0
 
LVL 1

Expert Comment

by:kodiakbear
Comment Utility
Moving to the paq

kb
Experts Exchange Moderator
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now