Create Data Bound Excel Chart with vb.net

Good Morning! This post is being written early because I knew I would be busy the next couple of days. I am hoping this is published at the time I ask that it be done! If not, I guess you got two posts early!

Today and tomorrow’s topic are interrelated. Basically first we want to create a databound Excel Chart using a specified Excel worksheet as its data source using vb.net. Tomorrow we will address adding that Excel Chart to a Microsoft Word document with vb.net.

This assumes you already have a worksheet populated with data. If you are unsure of how to do this with vb.net see here.

Make it a great day!


Join me on Facebook

 

‘ASSUMED Dim WSheet As New Excel.Worksheet
        ‘ASSUMED WSheet = Excel.Workbooks.Add.Worksheets.Add

Label3.Text = "Adding RX Chart….."
        WSheet.Application.Charts.Add()
        ‘ WSheet.Application.ActiveChart.Name = "TxChart"
        ‘WSheet.Application.ActiveChart.ApplyCustomType(ChartType:=21, TypeName:="Column – Area")
        WSheet.Application.ActiveChart.SetSourceData(Source:=Excel.Application.Sheets(thisname).Range("B1:L14"), PlotBy _
        :=2)

        WSheet.Application.ActiveChart.SeriesCollection(1).XValues = "=" & thisname & "!R2C1:R14C1"
        With WSheet.Application.ActiveChart
            .HasTitle = True
            .ChartTitle.Characters.Text = "Network Use – RX for " & CustomerName & vbCrLf & CombinedDate
            .Axes(1, 1).HasTitle = True
            .Axes(1, 1).AxisTitle.Characters.Text = "Hours"
            .Axes(2, 1).HasTitle = True
            .Axes(2, 1).AxisTitle.Characters.Text = "WAN%"
            .Name = "rxChart"
            .ChartType = 76
            .Legend.Font.Size = 12
        End With
        With WSheet.Application.ActiveChart.PageSetup
            .ChartSize = 2
            .CenterHorizontally = False
            .CenterVertically = False
            .Orientation = 2
            .Draft = False
            .BlackAndWhite = False
            .Zoom = 70
        End With
        WSheet.Application.ActiveChart.ChartArea.Select()

        WSheet.Application.Selection.Height = 400

        WSheet.Application.ActiveChart.HasDataTable = False
        Label3.Text = "Page Setup For RX Worksheet…."
        With WSheet.PageSetup
            .RightHeader = "Page &P Of &N"

            .RightHeader = "Page &P Of &N"
            .CenterHeader = ""
            ‘.LeftHeader = ‘arrgen.ItemNumberGlobal & " " & ReportType & vbCrLf & ReportComments
            .LeftHeader = CustomerName & " Network Usage (Rx) on " & CombinedDate & " for " & CustomerName ‘arrgen.ItemNumberGlobal & " " & ReportType & vbCrLf & ReportComments
            .LeftFooter = "Property of Kelly Martens"

            .RightFooter = "&D / &T"

            .LeftMargin = WSheet.Application.InchesToPoints(0.54)

            .RightMargin = WSheet.Application.InchesToPoints(0.3)
            .TopMargin = WSheet.Application.InchesToPoints(1.0)

            .BottomMargin = WSheet.Application.InchesToPoints(1.0)
            .PrintHeadings = False
            .PrintGridlines = True
            .PrintComments = -4142 ‘XlPrintLocation.xlPrintNoComments
            .CenterHorizontally = True
            .CenterVertically = False
            .Orientation = 2 ‘XlPageOrientation.xlLandscapeXlPageOrientation.xlLandscape
            .PrintTitleRows = "$1:$1"
            .Draft = False
            .Order = 1 ‘XlOrder.xlDownThenOver
            .BlackAndWhite = False
            .Zoom = False
            .FitToPagesWide = 100
            .FitToPagesTall = 100
            .Zoom = 80
        End With


Join me on Facebook

Technorati Tags: ,,,,,,,,,,,,
,,,,
,,,,,,,,,,,,
,,,,,,,,,,,,,,
,,,,,,,,,,,,
,,,,,,,,,
,,,,,,,,

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: