Launching Excel client side

[Staticbob]

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]]

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" <ja*********@nospampls.bluestone.plc.uk> wrote in message
news:63**********************************@microsof t.com...

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

[sweeney_jones]

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.IPo stBackEventHandler.RaisePostBackEvent(String
eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData)
+33
System.Web.UI.Page.ProcessRequestMain() +1277

[Steve C. Orr [MVP, MCSD]]

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

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net
<sw***********@hotmail.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...

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.IPo stBackEventHandler.RaisePostBackEvent(String
eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData)
+33
System.Web.UI.Page.ProcessRequestMain() +1277

[sweeney_jones]

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