Nothing generated when using Excel 11.0 com 1

[AndyH1]

I'm trying to use the Microsoft Excel 11.0 Library com to generate an Excel spreadsheet with charts. After many problems with permissions I finally got the sample to run without errors, but it doesn't seem to generate anything. Can anyone advise me what I'm doing wrong. Iv'e added a reference to the library and have Office 2003 installed on a win 2003 server. The code gives no errors, just ssems to wait for a few seconds with the progress bar showwing then stops, but nothing generated.

Thanks in advance
Andy

Try
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet

xlWorkBook = New Excel.Application().Workbooks.Add(Missing.Value)
xlWorkBook.Application.Visible = True
xlWorkSheet = xlWorkBook.ActiveSheet

' Gets the dataset containing the data
Dim dsData As DataSet = getData()
Dim i As Integer = 2

' Outputting the fieldnames in pink bold color
xlWorkSheet.Cells(1, 1) = "Student ID"
xlWorkSheet.Cells(1, 2) = "Student Name"
xlWorkSheet.Cells(1, 3) = "Mathematics"
xlWorkSheet.Cells(1, 4) = "Geography"
xlWorkSheet.Cells(1, 5) = "Total"

xlWorkSheet.Range("$A1:$E1").Font.ColorIndex = Excel.Constants.xlColor1
xlWorkSheet.Range("$A1:$E1").Font.Bold = True

' Outputting the data
For Each dr As DataRow In dsData.Tables(0).Rows
xlWorkSheet.Cells(i, 1) = dr(0)
xlWorkSheet.Cells(i, 2) = dr(1)
xlWorkSheet.Cells(i, 3) = dr(2)
xlWorkSheet.Cells(i, 4) = dr(3)

' Building the formula for calculating the sum
xlWorkSheet.Cells(i, 5).Formula = "=SUM($C{0}:$D{0})".Replace("{0}", i.ToString())

' Going to the next row
i = i + 1
Next

' Auto fit the columns
xlWorkSheet.Columns.AutoFit()

' Generating the graph
Dim chart As Excel.Chart
chart = xlWorkBook.Charts.Add()

With chart
.ChartType = Excel.XlChartType.xlColumnClustered
.SetSourceData(xlWorkSheet.Range("A1:E11"), 2)

.HasTitle = True
.ChartTitle.Characters.Text = "Students' marks"

.Axes(1, Excel.XlAxisGroup.xlPrimary).HasTitle = True
.Axes(1, Excel.XlAxisGroup.xlPrimary).AxisTitle.Characters.Text = "Students"
.Axes(2, Excel.XlAxisGroup.xlPrimary).HasTitle = True
.Axes(2, Excel.XlAxisGroup.xlPrimary).AxisTitle.Characters.Text = "Marks"
End With
Catch ex As Exception
Throw ex
End Try

 

[Gruuka]

Did you try saving it to your server with the SaveAs method?
Like xlWorkBook.SaveAs("path\filename")

http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.saveas(VS.80).aspx

 

[AndyH1]

Thanks Gruuka. If I do a saveas the file is generated in the folder I specify

However I really want the user to be able to save to where they want. Was expecting the normal open/save dialog. How would I go about this

Thanks
Andy

 

[Gruuka]

That can be done by changing the response headers of your page.

Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("content-disposition", "attachment; filename=" & yourFileName)
Response.BinaryWrite(fileContents)

I think that you'd still need to save the file to the server.

 

[AndyH1]

Thanks Gruka, think you are probably right about saving to server

 

[vbkris]

hi andy,

under what windows account permissions are you executing this code? ASPNET???

Known is handfull, Unknown is worldfull

 

[AndyH1]

Network services but I'm running on a win 2003 server

See

http://blog.crowe.co.nz/archive/2006/03/02/589.aspx

seems to run fine now, but only saving locally to server
hope this helps
Andy