Solved

ASP.net VB.net Stretch chart to fit all data in

Posted on 2014-02-26
9
599 Views
Last Modified: 2014-03-06
Hi

I am using the VB.net code further down to bind data to a GridView and then dynamically plot a chart using the GridView. As you will see in the image the chart does not contain all the data. How do I cause the chart to automatically stretch to fit all the data

1

    Sub oChart(ByVal oConnectionString As String, ByVal SQL As String, ByVal RowFields As String, ByVal DataField As String, ByVal sAggregate As String, _
               ByVal ColumnFields As String, ByVal oChartType As String)

        oChartGridViewID_Counter = oChartGridViewID_Counter + 1 'used to name charts and GridView behind it

        Dim oChart As New Chart()
        Dim oGridView As New GridView
        ' Initialize objects and elements
        Dim chartArea1 As New ChartArea()
        Dim legend1 As New Legend()

        Dim arrSeries(1000) As Series

        ' Set the Chart Properties
        'oChart.Width = CInt(sWidth)
        'oChart.Height = CInt(sHeight)

        oChart.Palette = ChartColorPalette.EarthTones
        oChart.Palette = ChartColorPalette.Excel
        oChart.BackColor = System.Drawing.Color.Gray
        oChart.BorderSkin.SkinStyle = BorderSkinStyle.Emboss

        oChart.BorderlineColor = System.Drawing.Color.Gray
        oChart.BorderlineDashStyle = ChartDashStyle.Solid
        oChart.BorderlineWidth = 4 'MDB

        ' Set the ChartArea properties
        chartArea1.Name = "Default"
        chartArea1.BackColor = System.Drawing.Color.Gray


        ' Add the ChartArea to the Chart
        oChart.ChartAreas.Add(chartArea1)

        ' Set the Legend properties
        legend1.Name = "Default"
        legend1.Docking = Docking.Bottom
        legend1.LegendStyle = LegendStyle.Row
        legend1.Alignment = System.Drawing.StringAlignment.Center

        legend1.BackColor = System.Drawing.Color.Transparent
        legend1.BorderColor = System.Drawing.Color.Black
        legend1.BorderWidth = 1

        ' Add the Legend to the Chart
        oChart.Legends.Add(legend1)



        ' Add points to each series.
        Dim rnd As New Random()

        'For Each ser As Series In chart1.Series


        'oGridView.DataSource = dt
        'First get the Aggregate function
        Dim oAggregate As AggregateFunction
        If sAggregate = "Count" Then
            oAggregate = AggregateFunction.Count
        ElseIf sAggregate = "Sum" Then
            oAggregate = AggregateFunction.Sum
        ElseIf sAggregate = "First" Then
            oAggregate = AggregateFunction.First
        ElseIf sAggregate = "Last" Then
            oAggregate = AggregateFunction.Last
        ElseIf sAggregate = "Average" Then
            oAggregate = AggregateFunction.Average
        ElseIf sAggregate = "Max" Then
            oAggregate = AggregateFunction.Max
        ElseIf sAggregate = "Min" Then
            oAggregate = AggregateFunction.Min
        ElseIf sAggregate = "Exists" Then
            oAggregate = AggregateFunction.Exists
        End If

        Dim dt As DataTable = SQL_Data_Table.GetSQLDataTable(oConnectionString, SQL)
        Dim pvt As New Pivot(dt)

        'Next get data field as only can be one type
        'Dim DataField As String = ListView_items_string(Me.ListView4)

        'Next find out which of the four scenarios there are
        '(1) Only one RowFields and one ColumnFields - so parse in as string
        '(2) Only one RowFields and multiple ColumnFields - so former is string and latter is array
        '(3) Multiple RowFields and one ColumnFields - so former is array and latter is string
        '(4) Both RowFields and ColumnFields are multiples so both are arrays
        'Dim RowFields As String = ListView_items_string(Me.ListView3)
        'Dim ColumnFields As String = ListView_items_string(Me.ListView2)
        Dim arrRowFields As Object = Split(RowFields, ",")
        Dim arrColumnFields As Object = Split(ColumnFields, ",")


        If UBound(arrRowFields) = 0 And UBound(arrColumnFields) = 0 Then
            ' Me.DataGridView1.DataSource = pvt.PivotData("Company", "CTC", AggregateFunction.Count, "Year")
            oGridView.DataSource = pvt.PivotData(RowFields, DataField, oAggregate, ColumnFields)
        ElseIf UBound(arrRowFields) = 0 And UBound(arrColumnFields) > 0 Then
            oGridView.DataSource = pvt.PivotData(RowFields, DataField, oAggregate, arrColumnFields)
        ElseIf UBound(arrRowFields) > 0 And UBound(arrColumnFields) = 0 Then
            oGridView.DataSource = pvt.PivotData(DataField, oAggregate, arrRowFields, arrColumnFields)
        ElseIf UBound(arrRowFields) > 0 And UBound(arrColumnFields) > 0 Then
            ' Me.DataGridView1.DataSource = pvt.PivotData("CTC", AggregateFunction.Max, New String() {"Designation", "Year"}, New String() {"Company", "Department"})
            oGridView.DataSource = pvt.PivotData(DataField, oAggregate, arrRowFields, arrColumnFields)
        End If
        oGridView.DataBind() 'BIND FOR ANY OF FOUR ABOVE

        Dim up As New UpdatePanel
        up.ContentTemplateContainer.Controls.Add(oGridView)
        'up.ContentTemplateContainer.Controls.Add(oTimer)
        Table1.Rows(0).Cells(0).Controls.Add(up)
        Table1.Rows(0).Cells(0).Controls.Add(oGridView)

        'SO I AM UPDATING JUST THE GRIDVIEW - MIGHT HAVE TO DO THE SAME WITH THE CHART
        'First add declare Update Panel then add GridView to it - finally add the update panel to the table cell


        Dim oCount As Integer = 0
        Dim oData As Double
        Dim strRowHeader As String
        Dim strColumnHeader As String
        'SO LOOP THROUGH ALL THE NUMERIC CELLS AND PLOT ON A CHART
        'For Each ser As Series In chart1.Series
        'ser.Label = ser.Name

        oChart.BackColor = Drawing.Color.Navy
        oChart.BackGradientStyle = GradientStyle.LeftRight
        chartArea1.BackColor = Drawing.Color.DarkGreen
        chartArea1.BackGradientStyle = GradientStyle.LeftRight

        Dim RowFields_Count As Integer = UBound(arrRowFields) + 1

        For r As Integer = 0 To oGridView.Rows.Count - 1

            oCount = 0


            'Now because multiple row headers will be stored in multiple columns concatanate
            'these columns if there are more than one
            If RowFields_Count = 1 Then
                strRowHeader = oGridView.Rows(r).Cells(0).Text
            ElseIf RowFields_Count = 2 Then
                strRowHeader = oGridView.Rows(r).Cells(0).Text & " " & oGridView.Rows(r).Cells(1).Text
            ElseIf RowFields_Count = 3 Then
                strRowHeader = oGridView.Rows(r).Cells(0).Text & " " & oGridView.Rows(r).Cells(1).Text & " " & oGridView.Rows(r).Cells(2).Text
            ElseIf RowFields_Count = 4 Then
                strRowHeader = oGridView.Rows(r).Cells(0).Text & " " & oGridView.Rows(r).Cells(1).Text & " " & oGridView.Rows(r).Cells(2).Text & " " & oGridView.Rows(r).Cells(3).Text
            ElseIf RowFields_Count = 5 Then
                strRowHeader = oGridView.Rows(r).Cells(0).Text & " " & oGridView.Rows(r).Cells(1).Text & " " & oGridView.Rows(r).Cells(2).Text & " " & oGridView.Rows(r).Cells(3).Text & " " & oGridView.Rows(r).Cells(4).Text
            ElseIf RowFields_Count = 6 Then
                strRowHeader = oGridView.Rows(r).Cells(0).Text & " " & oGridView.Rows(r).Cells(1).Text & " " & oGridView.Rows(r).Cells(2).Text & " " & oGridView.Rows(r).Cells(3).Text & " " & oGridView.Rows(r).Cells(4).Text & " " & oGridView.Rows(r).Cells(5).Text
            End If


            arrSeries(r) = New Series
            arrSeries(r).BorderColor = System.Drawing.Color.MidnightBlue
            arrSeries(r).BorderWidth = 4 'Changes width of ine in line chart
            'arrSeries(r).BackImageTransparentColor = Drawing.Color.Aqua
            arrSeries(r).Name = strRowHeader

            If oChartType = "Chart_Line" Then
                arrSeries(r).ChartType = SeriesChartType.Line
                oChart.BackColor = Drawing.Color.Navy
                chartArea1.BackColor = Drawing.Color.DarkGreen
            ElseIf (oChartType = "Chart_Spline") Then
                arrSeries(r).ChartType = SeriesChartType.Spline
                oChart.BackColor = Drawing.Color.Black
                chartArea1.BackColor = Drawing.Color.MintCream
            ElseIf (oChartType = "Chart_Bar") Then
                arrSeries(r).ChartType = SeriesChartType.Bar
                oChart.BackColor = Drawing.Color.Navy
                chartArea1.BackColor = Drawing.Color.DarkRed
            ElseIf (oChartType = "Chart_StackedBar") Then
                arrSeries(r).ChartType = SeriesChartType.StackedBar
                chartArea1.BackColor = Drawing.Color.Orange
                oChart.BackColor = Drawing.Color.DarkRed
            ElseIf (oChartType = "Chart_Column") Then
                arrSeries(r).ChartType = SeriesChartType.Column
                oChart.BackColor = Drawing.Color.Orange
                chartArea1.BackColor = Drawing.Color.OrangeRed
            ElseIf (oChartType = "Chart_StackedColumn") Then
                arrSeries(r).ChartType = SeriesChartType.StackedColumn
                oChart.BackColor = Drawing.Color.DarkSlateGray
                chartArea1.BackColor = Drawing.Color.MintCream
            ElseIf (oChartType = "Chart_Pie") Then
                arrSeries(r).ChartType = SeriesChartType.Pie
                oChart.BackColor = Drawing.Color.Navy
                chartArea1.BackColor = Drawing.Color.DarkSlateGray
            ElseIf (oChartType = "Chart_Doughnut") Then
                arrSeries(r).ChartType = SeriesChartType.Doughnut
                oChart.BackColor = Drawing.Color.DarkRed
                chartArea1.BackColor = Drawing.Color.Chocolate
            ElseIf (oChartType = "Chart_Doughnut") Then
                arrSeries(r).ChartType = SeriesChartType.Funnel
                oChart.BackColor = Drawing.Color.Black
                chartArea1.BackColor = Drawing.Color.Silver
            ElseIf (oChartType = "Chart_Area") Then
                arrSeries(r).ChartType = SeriesChartType.Area
                oChart.BackColor = Drawing.Color.DarkGreen
                chartArea1.BackColor = Drawing.Color.DarkTurquoise
            ElseIf (oChartType = "Chart_Funnel") Then
                arrSeries(r).ChartType = SeriesChartType.Funnel
                oChart.BackColor = Drawing.Color.Black
                chartArea1.BackColor = Drawing.Color.Silver
            ElseIf (oChartType = "Chart_Point") Then
                arrSeries(r).ChartType = SeriesChartType.Point
                oChart.BackColor = Drawing.Color.Navy
                chartArea1.BackColor = Drawing.Color.White
            ElseIf (oChartType = "Chart_Polar") Then
                arrSeries(r).ChartType = SeriesChartType.Polar
                oChart.BackColor = Drawing.Color.DodgerBlue
                chartArea1.BackColor = Drawing.Color.Red
            ElseIf (oChartType = "Chart_Radar") Then
                arrSeries(r).ChartType = SeriesChartType.Radar
                oChart.BackColor = Drawing.Color.Olive
                chartArea1.BackColor = Drawing.Color.OliveDrab

            End If

            arrSeries(r).BackGradientStyle = GradientStyle.DiagonalLeft

            oChart.Series.Add(arrSeries(r))

            '====================================================================================
            'Plot chart based on number of fields in RowFields



            If RowFields_Count = 1 Then
                For c As Integer = 1 To oGridView.Rows(0).Cells.Count - 1
                    'only add a point if the item is numeric
                    If IsNumeric(oGridView.Rows(r).Cells(c).Text) Then

                        oData = CDbl(oGridView.Rows(r).Cells(c).Text)
                        'strRowHeader = oGridView.Rows(r).Cells(0).Text
                        strColumnHeader = oGridView.HeaderRow.Cells(c).Text
                        'ser.Points.Add(oData)
                        arrSeries(r).Points.AddY(oData)
                        'ser.Points.Item(oCount).AxisLabel = strRowHeader & " " & strColumnHeader
                        arrSeries(r).Points.Item(oCount).AxisLabel = strColumnHeader
                        'ser.Points.Item(oCount).LabelAngle = 10
                        oCount = oCount + 1

                    End If
                Next
            ElseIf RowFields_Count = 2 Then
                'So first numeric GridView column index is same as Count of fields
                For c As Integer = RowFields_Count To oGridView.Rows(0).Cells.Count - 1
                    'only add a point if the item is numeric
                    If IsNumeric(oGridView.Rows(r).Cells(c).Text) Then

                        oData = CDbl(oGridView.Rows(r).Cells(c).Text)
                        'strRowHeader = oGridView.Rows(r).Cells(0).Text
                        strColumnHeader = oGridView.HeaderRow.Cells(c).Text
                        'ser.Points.Add(oData)
                        arrSeries(r).Points.AddY(oData)
                        'ser.Points.Item(oCount).AxisLabel = strRowHeader & " " & strColumnHeader
                        arrSeries(r).Points.Item(oCount).AxisLabel = strColumnHeader
                        'ser.Points.Item(oCount).LabelAngle = 10
                        oCount = oCount + 1

                    End If
                Next
            End If
        Next

        chartArea1.AxisX.LabelStyle.Enabled = True
        chartArea1.AxisX.IsLabelAutoFit = True
        chartArea1.AxisX.LabelAutoFitStyle = LabelAutoFitStyles.LabelsAngleStep30

        Table1.Rows(0).Cells(1).Controls.Add(oChart)
    End Sub

Open in new window

0
Comment
Question by:Murray Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
9 Comments
 
LVL 1

Expert Comment

by:JB4375
ID: 39889588
I never attempted this but try this link:
http://msdn.microsoft.com/en-us/library/system.windows.forms.pictureboxsizemode.aspx

Specifically StretchImage
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39891854
Do you mean the x-axis?
0
 

Author Comment

by:Murray Brown
ID: 39893912
Hi CodeCruiser

Thanks for the advice and yes I do mean the x axis
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Murray Brown
ID: 39893940
Hi CodeCruiser
 
Would this be the right code to stretch the X Axix?

chartArea1.AxisX.MinorTickMark.Enabled = True
            chartArea1.AxisX.Interval = 1
            chartArea1.AxisX.IsLabelAutoFit = True
            'chartArea1.AxisX.LabelStyle.IsStaggered = True
            chartArea1.AxisX.LabelAutoFitStyle = LabelAutoFitStyles.DecreaseFont
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39894453
Do you still want to see "1, 2" on x-axis or do you want to see email addresses?
0
 

Author Comment

by:Murray Brown
ID: 39907529
I want to see the email addresses
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 39908976
Then your data binding is incorrect I think because it is showing your columns on xaxis instead of showing values from column 1.
0
 

Author Closing Comment

by:Murray Brown
ID: 39909097
Thanks for the help
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question