Launching Excel client side

Discussion in 'ASP .Net' started by =?Utf-8?B?U3RhdGljYm9i?=, Aug 19, 2005.

  1. Guys,

    I have this code that loads some data from a SQL view into a datatable, then
    into arrays, I can then export it to a predefined Excel template that creates
    lots of fancy charts and stuff from the raw data.

    This code WORKS FINE on my local machine when developing, but obviously when
    I come to deploy it tries to launch excel on the server, where it is not
    installed.

    How do I modify this code so that it launches Excel on the client and
    exports the data. Or is there a better way to do this ?
    --
    Thanks in advance
    Bob

    Imports System.Data.SqlClient
    Imports Microsoft.Office.Interop

    Private Sub btn_excel_Click(ByVal sender As System.Object, ByVal e As
    System.EventArgs) Handles btn_excel.Click

    'Get data into datatable.

    Dim Qid As Integer = CInt(Session("QID"))
    Dim cnn As New SqlConnection("Data Source=BLS5TEST2;Initial
    Catalog=HPT;User Id=HPT;Password=kibby1;")
    Dim cmd As New SqlDataAdapter("SELECT * FROM vw_answers WHERE Qid =
    '" & Qid & "' and Staffname <> '" & User.Identity.Name & "'", cnn)
    Dim tlcmd As New SqlDataAdapter("SELECT * FROM vw_answers WHERE Qid
    = '" & Qid & "' and Staffname = '" & User.Identity.Name & "'", cnn)


    'Fill the dataset here.
    Dim ds As New DataSet
    cmd.Fill(ds, "TeamAnswers")
    tlcmd.Fill(ds, "LeaderAnswers")


    Dim RowNbr As Int32 = 0
    Dim Answers(ds.Tables(0).Rows.Count) As Object
    Dim Leader(ds.Tables(1).Rows.Count) As Object


    'get Teamanswers into array
    For Each Rw As DataRow In ds.Tables(0).Rows
    Answers(RowNbr) = Rw.ItemArray()
    RowNbr += 1
    Next Rw

    'get Leaderanswers into array
    RowNbr = 0
    For Each lRw As DataRow In ds.Tables(1).Rows
    Leader(RowNbr) = lRw.ItemArray()
    RowNbr += 1
    Next lRw

    'Get data into EXCEL
    Dim xl As Excel.Application
    Try
    xl = GetObject(, "Excel.Application")
    Catch ex As Exception
    xl = New Excel.Application
    End Try

    Dim wkbk As Excel.Workbook
    Dim wkst As Excel.Worksheet
    Dim wksttl As Excel.Worksheet

    xl.Visible = False
    xl.DisplayAlerts = False

    wkbk = xl.Workbooks.Open("\\bls2mbr25\HPT\HPT Questionnaire
    Results.xlt")
    wkst = wkbk.Sheets("rawdata")

    'Export Team Answers
    Dim x As Integer
    Dim y As Integer
    Dim range As String
    For x = 0 To Answers.GetUpperBound(0)
    y = x + 2
    range = "A" & y & ":D" & y
    wkst.Range(range).Value = Answers(x)
    Next

    'Export TeamLeader Answers
    For x = 0 To Leader.GetUpperBound(0)
    y = x + 2
    range = "E" & y & ":G" & y
    wkst.Range(range).Value = Leader(x)
    Next

    'Tidy up
    Answers = Nothing
    Leader = Nothing
    ds.Dispose()
    ds = Nothing
    wkbk.Sheets(1).activate()
    xl.Visible = True
    xl.DisplayAlerts = True

    End Sub
    =?Utf-8?B?U3RhdGljYm9i?=, Aug 19, 2005
    #1
    1. Advertising

  2. There are a variety of ways of launching Excel on the client and server
    side.

    This article covers most of your options and supplies sample code for them
    all:
    http://SteveOrr.net/Articles/ExcelExport.aspx

    --
    I hope this helps,
    Steve C. Orr, MCSD, MVP
    http://SteveOrr.net



    "Staticbob" <> wrote in message
    news:...
    > Guys,
    >
    > I have this code that loads some data from a SQL view into a datatable,
    > then
    > into arrays, I can then export it to a predefined Excel template that
    > creates
    > lots of fancy charts and stuff from the raw data.
    >
    > This code WORKS FINE on my local machine when developing, but obviously
    > when
    > I come to deploy it tries to launch excel on the server, where it is not
    > installed.
    >
    > How do I modify this code so that it launches Excel on the client and
    > exports the data. Or is there a better way to do this ?
    > --
    > Thanks in advance
    > Bob
    >
    > Imports System.Data.SqlClient
    > Imports Microsoft.Office.Interop
    >
    > Private Sub btn_excel_Click(ByVal sender As System.Object, ByVal e As
    > System.EventArgs) Handles btn_excel.Click
    >
    > 'Get data into datatable.
    >
    > Dim Qid As Integer = CInt(Session("QID"))
    > Dim cnn As New SqlConnection("Data Source=BLS5TEST2;Initial
    > Catalog=HPT;User Id=HPT;Password=kibby1;")
    > Dim cmd As New SqlDataAdapter("SELECT * FROM vw_answers WHERE Qid =
    > '" & Qid & "' and Staffname <> '" & User.Identity.Name & "'", cnn)
    > Dim tlcmd As New SqlDataAdapter("SELECT * FROM vw_answers WHERE Qid
    > = '" & Qid & "' and Staffname = '" & User.Identity.Name & "'", cnn)
    >
    >
    > 'Fill the dataset here.
    > Dim ds As New DataSet
    > cmd.Fill(ds, "TeamAnswers")
    > tlcmd.Fill(ds, "LeaderAnswers")
    >
    >
    > Dim RowNbr As Int32 = 0
    > Dim Answers(ds.Tables(0).Rows.Count) As Object
    > Dim Leader(ds.Tables(1).Rows.Count) As Object
    >
    >
    > 'get Teamanswers into array
    > For Each Rw As DataRow In ds.Tables(0).Rows
    > Answers(RowNbr) = Rw.ItemArray()
    > RowNbr += 1
    > Next Rw
    >
    > 'get Leaderanswers into array
    > RowNbr = 0
    > For Each lRw As DataRow In ds.Tables(1).Rows
    > Leader(RowNbr) = lRw.ItemArray()
    > RowNbr += 1
    > Next lRw
    >
    > 'Get data into EXCEL
    > Dim xl As Excel.Application
    > Try
    > xl = GetObject(, "Excel.Application")
    > Catch ex As Exception
    > xl = New Excel.Application
    > End Try
    >
    > Dim wkbk As Excel.Workbook
    > Dim wkst As Excel.Worksheet
    > Dim wksttl As Excel.Worksheet
    >
    > xl.Visible = False
    > xl.DisplayAlerts = False
    >
    > wkbk = xl.Workbooks.Open("\\bls2mbr25\HPT\HPT Questionnaire
    > Results.xlt")
    > wkst = wkbk.Sheets("rawdata")
    >
    > 'Export Team Answers
    > Dim x As Integer
    > Dim y As Integer
    > Dim range As String
    > For x = 0 To Answers.GetUpperBound(0)
    > y = x + 2
    > range = "A" & y & ":D" & y
    > wkst.Range(range).Value = Answers(x)
    > Next
    >
    > 'Export TeamLeader Answers
    > For x = 0 To Leader.GetUpperBound(0)
    > y = x + 2
    > range = "E" & y & ":G" & y
    > wkst.Range(range).Value = Leader(x)
    > Next
    >
    > 'Tidy up
    > Answers = Nothing
    > Leader = Nothing
    > ds.Dispose()
    > ds = Nothing
    > wkbk.Sheets(1).activate()
    > xl.Visible = True
    > xl.DisplayAlerts = True
    >
    > End Sub
    >
    >
    Steve C. Orr [MVP, MCSD], Aug 20, 2005
    #2
    1. Advertising

  3. I suggest you follow the security related instructions in the article.

    --
    I hope this helps,
    Steve C. Orr, MCSD, MVP
    http://SteveOrr.net


    <> wrote in message
    news:...
    > Thanks Steve,
    >
    > I had already studied your site and was trying to implement the 1st
    > option as I would really like to use a template as a master and create
    > a new file from it, throwing in some data from arrays.
    >
    > I have tried the code listed above but am now getting this error . . .
    >
    > Thanks,
    > Bob
    >
    >
    > Server Error in '/' Application.
    > --------------------------------------------------------------------------------
    >
    > Server execution failed
    > Description: An unhandled exception occurred during the execution of
    > the current web request. Please review the stack trace for more
    > information about the error and where it originated in the code.
    >
    > Exception Details: System.Runtime.InteropServices.COMException: Server
    > execution failed
    >
    > Source Error:
    >
    > An unhandled exception was generated during the execution of the
    > current web request. Information regarding the origin and location of
    > the exception can be identified using the exception stack trace below.
    >
    >
    > Stack Trace:
    >
    >
    > [COMException (0x80080005): Server execution failed]
    > HPT.HPTReports.btn_excel_Click(Object sender, EventArgs e) in
    > c:\inetpub\wwwroot\HPT\HPTReports.aspx.vb:205
    > System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
    >
    > System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
    > eventArgument) +57
    > System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
    > sourceControl, String eventArgument) +18
    > System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
    > +33
    > System.Web.UI.Page.ProcessRequestMain() +1277
    >
    Steve C. Orr [MVP, MCSD], Aug 22, 2005
    #3
  4. =?Utf-8?B?U3RhdGljYm9i?=

    Guest

    Steve, thanks again.

    I take it you are referring to these instructions ?

    "For starters, you need Excel installed on the server. To give ASP.NET
    the permissions it needs to use Excel, you might need to add the line
    <identity impersonate="true"/> to your web.config file or configure
    your app to run under an appropriate user account. For this code to
    work, you also might need to grant write privileges to your Web
    directory for this account (IUSR_machinename if you use identity
    impersonation)."

    Well all this is done apart from the last point, IUSR_machinename.
    Should this refer to the clients machine name or the server ? My
    problem is that I will have unlimited amounts of users ?

    Anyway, I now have the code creating the excel file and saving it
    correctly, I just cab't open it in the browser window now using
    Response.Redirect("Filename"), It says Page unavailable, even though I
    have just used that string variable in the save as line.

    Thanks
    Bob
    , Aug 24, 2005
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Peter Rilling

    Re: Launching client side applications

    Peter Rilling, Apr 14, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    381
    Peter Rilling
    Apr 14, 2005
  2. Boss302
    Replies:
    0
    Views:
    1,040
    Boss302
    Nov 21, 2006
  3. Bogdan
    Replies:
    2
    Views:
    649
    Bogdan
    Jun 9, 2008
  4. Replies:
    2
    Views:
    276
    Dave Anderson
    Aug 15, 2006
  5. Kanth
    Replies:
    0
    Views:
    144
    Kanth
    Jan 22, 2008
Loading...

Share This Page