?
Solved

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

Posted on 2014-02-26
9
Medium Priority
?
628 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
  • 4
  • 3
8 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
Independent Software Vendors: 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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Screencast - Getting to Know the Pipeline
Suggested Courses

864 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