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