• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

.NET Web Forms and Automating Excel on Client PC.

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
thePrisoner
Asked:
thePrisoner
1 Solution
 
bruintjeCommented:
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
 
bruintjeCommented:
especially the line on security settings is important to keep in mind i guess
0
 
thePrisonerAuthor Commented:
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
thePrisonerAuthor Commented:
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
 
DanRollinsCommented:
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
 
kodiakbearCommented:
Moving to the paq

kb
Experts Exchange Moderator
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now