Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Nothing generated when using Excel 11.0 com 1

Status
Not open for further replies.

AndyH1

Programmer
Jan 11, 2004
350
GB
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
 
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
 
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.
 
Thanks Gruka, think you are probably right about saving to server
 
hi andy,

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

Known is handfull, Unknown is worldfull
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top