[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

.NET Web Forms and Automating Excel on Client PC.

Posted on 2002-03-20
6
Medium Priority
?
253 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Suggested Courses

649 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