Solved

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

Posted on 2014-02-26
9
566 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:murbro
  • 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:murbro
ID: 39893912
Hi CodeCruiser

Thanks for the advice and yes I do mean the x axis
0
 

Author Comment

by:murbro
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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:murbro
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:murbro
ID: 39909097
Thanks for the help
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

932 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now