?
Solved

.NET Web Forms and Automating Excel on Client PC.

Posted on 2002-03-20
6
Medium Priority
?
252 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 44

Accepted Solution

by:
bruintje earned 1200 total points
ID: 6884174
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
ID: 6884177
especially the line on security settings is important to keep in mind i guess
0
 

Author Comment

by:thePrisoner
ID: 6884202
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:thePrisoner
ID: 6884206
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
ID: 7634200
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
ID: 7666068
Moving to the paq

kb
Experts Exchange Moderator
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
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 process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

800 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