Hi,
I have been loking at this for a while now and haven't really found anything helpful searching on Google, so I thought I'd post my query here.
I have created a form that takes data held in an acces database, and then creates an excel chart, saves it as an image and displays it on my form.
I am trying to limit the Result value on the y axis to 10 because the maximum result value that can be achieved is 10, so it seems stupid having it display at 12 when any pupil can only score 10.
I also am looking to completely remove the ledgend on the right hand side. I have found code that can move the location of the ledgend (although I' not 100% sure how to use it), but not remove it.
If anyone has any ideas on how I could fix this it'd be a great help.
Thanks four your help and time.
Here is my code:
I have been loking at this for a while now and haven't really found anything helpful searching on Google, so I thought I'd post my query here.
I have created a form that takes data held in an acces database, and then creates an excel chart, saves it as an image and displays it on my form.
I am trying to limit the Result value on the y axis to 10 because the maximum result value that can be achieved is 10, so it seems stupid having it display at 12 when any pupil can only score 10.
I also am looking to completely remove the ledgend on the right hand side. I have found code that can move the location of the ledgend (although I' not 100% sure how to use it), but not remove it.
If anyone has any ideas on how I could fix this it'd be a great help.
Thanks four your help and time.
Here is my code:
VB.NET:
[SIZE="2"]Imports Excel = Microsoft.Office.Interop.Excel
Public Class ViewResultsByTestGraphForm
' Create Excel objects
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
' Create Image object
Dim img As Image
Private Sub btnReturnToMain_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReturnToMain.Click
' Close form
Me.Close()
End Sub
Private Sub ViewResultsByTestGraphForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
' Instantiate excel objects
xlApp = New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")
' Create first cell blank
xlWorkSheet.Cells(1, 1) = ""
' Add Results heading
xlWorkSheet.Cells(2, 1) = TestDateForGraph.ToString + " Test Results"
For i = 0 To ArrayListPupilNames.Count - 1
' Add date headings and results
xlWorkSheet.Cells(1, i + 2) = "" + ArrayListPupilNames(i).ToString
xlWorkSheet.Cells(2, i + 2) = "" + ArrayListPupilResults(i).ToString
Next
' Create chart
Dim chartPage As Excel.Chart
Dim xlCharts As Excel.ChartObjects
Dim myChart As Excel.ChartObject
Dim chartRange As Excel.Range
' Apply chart settings
xlCharts = xlWorkSheet.ChartObjects
myChart = xlCharts.Add(10, 80, 528, 264) '300, 250)
chartPage = myChart.Chart
' Set to ten places as class sizes at Groarty are usually never larger than 6/7 pupils,
' also this fixes problems displaying the names under each pupil as some will be missed out if the size is
' any larger
chartRange = xlWorkSheet.Range("A1", "K2")
chartPage.SetSourceData(Source:=chartRange)
chartPage.ChartType = Excel.XlChartType.xlColumnStacked
Try
' Save Excel Worlbook
xlWorkSheet.SaveAs("C:\Users\andrew\Desktop\Software engineering project\SchoolTestProgram\images\TestResultsGraph.xlsx") '+ TestDateForGraph.ToString + "ResultsGraph.xlsx")
' Export chart as picture file
xlWorkSheet.ChartObjects(1).chart.Export(FileName:="C:\Users\andrew\Desktop\Software engineering project\SchoolTestProgram\images\TestResultsGraph.jpg", FilterName:="JPG")
' Set img here to avoid exception that will be thrown if the print button is not pressed
img = Image.FromFile("C:\Users\andrew\Desktop\Software engineering project\SchoolTestProgram\images\TestResultsGraph.jpg")
' Set PBoxGraph
PBoxGraph.ImageLocation = "C:\Users\andrew\Desktop\Software engineering project\SchoolTestProgram\images\TestResultsGraph.jpg"
' Close Excel objects
xlWorkBook.Close()
xlApp.Quit()
Catch ex As Exception
MsgBox("Exception: " + ex.ToString)
End Try
' Realese the excel objects
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
End Sub
Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrint.Click
' Set PrintDialog1 print settings
PrintDialog1.Document = PrintDocument1
PrintDialog1.PrinterSettings.Copies = 1
PrintDialog1.PrinterSettings.DefaultPageSettings.Landscape = True
' Set Image
img = Image.FromFile("C:\Users\andrew\Desktop\Software engineering project\SchoolTestProgram\images\TestResultsGraph.jpg")
'OpenAccess PrintDialog1
If PrintDialog1.ShowDialog = DialogResult.OK Then
PrintDocument1.Print()
End If
End Sub
Private Sub ViewResultsByTestGraphForm_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing
' Delete the files to avoid any problems that occur when the file already exists when trying to ceate a new file of the
' same name. This will also ensure that you are getting the most up-to-date files every time you open the graph
Dim FileToDelete As String
Dim PictureFileToDelete As String
FileToDelete = "C:\Users\andrew\Desktop\Software engineering project\SchoolTestProgram\images\TestResultsGraph.xlsx"
PictureFileToDelete = "C:\Users\andrew\Desktop\Software engineering project\SchoolTestProgram\images\TestResultsGraph.jpg"
' Dispose img to avoid exceptions being thrown when closing the graph form
img.Dispose()
If System.IO.File.Exists(FileToDelete) = True Then
System.IO.File.Delete(FileToDelete)
End If
If System.IO.File.Exists(PictureFileToDelete) = True Then
System.IO.File.Delete(PictureFileToDelete)
End If
' Remove all values from Array Lists so if another test is selected, its graph will not show
' the previous tests results as well
ArrayListPupilNames.Clear()
ArrayListPupilResults.Clear()
End Sub
Private Sub PrintDocument1_PrintPage(ByVal sender As System.Object, ByVal e As System.Drawing.Printing.PrintPageEventArgs) Handles PrintDocument1.PrintPage
' Inform the printer of the image that is to be prited
e.Graphics.DrawImage(img, e.MarginBounds.Location)
' Dispose img to avoid exceptions being thrown when closing the graph form
img.Dispose()
End Sub
End Class[/SIZE]