Solved

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

Posted on 2014-02-26
9
552 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
Comment Utility
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
Comment Utility
Do you mean the x-axis?
0
 

Author Comment

by:murbro
Comment Utility
Hi CodeCruiser

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

Author Comment

by:murbro
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 83

Expert Comment

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

Author Comment

by:murbro
Comment Utility
I want to see the email addresses
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
Comment Utility
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
Comment Utility
Thanks for the help
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
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 …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 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

10 Experts available now in Live!

Get 1:1 Help Now