locked
vb.net excel chart data label positioning RRS feed

  • Question

  • Hi,

    I have exported an Excel chart from my winform app and added some data labels to each point.  You can manually drag these data labels anywhere you want on the Excel chart but I would like to programmatically set them all to be displayed at the bottom of the chart (I have hidden the x axis labels so ideally where these would normally be displayed).  Is this possible from the vb.net app?

    Thanks
    Garry

    Saturday, January 30, 2016 9:55 PM

Answers

  • Hi Garry,

    >> but I would like to programmatically set them all to be displayed at the bottom of the chart

    Do you mean you want to set them under x axis? For setting position of data label, you could use DataLabels.Position.

    # DataLabels.Position property
    https://msdn.microsoft.com/en-us/library/office/microsoft.office.interop.excel.datalabels.position(v=office.15).aspx

    If you want to set it below point, you could try code below:

            Dim series As Excel.Series = chart.FullSeriesCollection(1)
            Dim db As Excel.DataLabels = series.DataLabels()
            db.Select()
            db.Position = Microsoft.Office.Interop.Excel.XlDataLabelPosition.xlLabelPositionBelow

    If you want to set custom position of data label, you could set it with a custom value.

      Dim series As Excel.Series = chart.FullSeriesCollection(1)
            Dim db As Excel.DataLabels = series.DataLabels()
            'db.Select()
            'db.Position = Microsoft.Office.Interop.Excel.XlDataLabelPosition.xlLabelPositionBelow
            For Each dl As Excel.DataLabel In db
                dl.Top = dl.Top + 100
            Next

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Proposed as answer by André Santo Monday, February 1, 2016 12:22 PM
    • Marked as answer by ResourceBox Wednesday, February 3, 2016 10:37 PM
    Monday, February 1, 2016 3:04 AM

All replies

  • Hi Garry,

    >> but I would like to programmatically set them all to be displayed at the bottom of the chart

    Do you mean you want to set them under x axis? For setting position of data label, you could use DataLabels.Position.

    # DataLabels.Position property
    https://msdn.microsoft.com/en-us/library/office/microsoft.office.interop.excel.datalabels.position(v=office.15).aspx

    If you want to set it below point, you could try code below:

            Dim series As Excel.Series = chart.FullSeriesCollection(1)
            Dim db As Excel.DataLabels = series.DataLabels()
            db.Select()
            db.Position = Microsoft.Office.Interop.Excel.XlDataLabelPosition.xlLabelPositionBelow

    If you want to set custom position of data label, you could set it with a custom value.

      Dim series As Excel.Series = chart.FullSeriesCollection(1)
            Dim db As Excel.DataLabels = series.DataLabels()
            'db.Select()
            'db.Position = Microsoft.Office.Interop.Excel.XlDataLabelPosition.xlLabelPositionBelow
            For Each dl As Excel.DataLabel In db
                dl.Top = dl.Top + 100
            Next

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Proposed as answer by André Santo Monday, February 1, 2016 12:22 PM
    • Marked as answer by ResourceBox Wednesday, February 3, 2016 10:37 PM
    Monday, February 1, 2016 3:04 AM
  • Thanks Edward,

    I need the labels at the bottom of the chart (where the x axis labels would normally be) so the loop seems to move the data points but I would need to work out the value of each point and then calculate how far I need to move the label down.  Is there a way to just move all points to the bottom of the grid (or a specific location / height in the grid)?  xlLabelPositionMixed sounds like something near what I am after but I get an error (it may only work on pie and bar charts?).  If not how difficult would it be to calculate the distance to move each label based on it's value (e.g. value of 4 needs to be moved down by 200, value of 6 needs to be moved down by 300 etc.)

    Thanks

    Garry

    Wednesday, February 3, 2016 9:30 PM
  • I have just realized I can set the Top to any value (e.g. dl.Top = 100).  As my chart is always the same size when created I just need to find the correct location (in my case it was 451) and it will always appear in the correct place.

    Thanks Edward

    Wednesday, February 3, 2016 10:37 PM
  • Hi ResourceBox,

    You are right, you could set any value to Top. If you have any other new issues, please feel free to post new threads in this forum.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, February 4, 2016 2:09 AM