Question Editing Excel charts

ucp8

Active member
Joined
Feb 9, 2009
Messages
28
Programming Experience
3-5
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:

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]
 
Editing Excel charts in VB.NET

Hi,

I have managed to create an Excel chart in VB.NET with my code, but have come up against two problems that I cannot find the answer too.

Firstly, does anyone know what code can be used to set a maximum range for the X and Y axis of the chart? For example, I have a chart where the maximum range is 10, and if there are elements with a value of 10, the charts Y-axis range is increased to 12, or if the highest value is say 7, the Y-axis range only goes up to 9. I want to be able to set it at 10 so that it will always show 10, no matter what the values in the graph are.

Secondly, does anyone know how to remove the ledgend from the right hand side of the chart? I have come across ways of moving it to a different location, but I can't remove it completely.

If anyone has any ideas or knows how to access these two attributes I'd be very grateful as I have spent a while trying to find the answers.

Thanks very much!

Here is my code:

VB.NET:
[SIZE="2"]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
        chartRange = xlWorkSheet.Range("A1", "K2")
        chartPage.SetSourceData(Source:=chartRange)
        chartPage.ChartType = Excel.XlChartType.xlColumnStacked

        Try
            ' Save Excel Worlbook
            xlWorkSheet.SaveAs("C:\Users\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\Desktop\Software engineering project\SchoolTestProgram\images\TestResultsGraph.jpg", FilterName:="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[/SIZE]
 

Latest posts

Back
Top