none
How to change data Excel data label shape Using C# RRS feed

  • Question

  • Trying to add data labels with shapes.  

    Tried this so far

    System.Collections.IEnumerator iEChartSeries = seriesCollection.GetEnumerator();

    if(iEChartSeries.MoveNext()){   

    var oSeries = (Excel.Series)(iEChartSeries.Current);

      Excel.Points pts = (Excel.Points) oSeries.Points(Type.Missing);

      System.Collections.IEnumerator iPoints = pts.GetEnumerator();

        while(iPoints.MoveNext())    {

            var pt = (Excel.Point)(iPoints.Current);

            pt.HasDataLabel = true;

            pt.DataLabel.Position = Excel.XlDataLabelPosition.xlLabelPositionAbove;

            Microsoft.Office.Interop.Excel.DataLabels db = pt.DataLabels();

            db.Select();

            db.Format.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeOctagon;

            pt.DataLabel.Font.Name = "Arial";

            pt.DataLabel.Font.FontStyle = "Bold";

            pt.DataLabel.Font.Size = 8;

            pt.DataLabel.Text = "N";

        }}


    but not able to Add shape label.  Tried https://social.msdn.microsoft.com/Forums/silverlight/en-US/3041e898-155c-45ec-9c67-ab48567e8b91/the-shape-of-datalabel-on-the-chart-c-excel?forum=exceldev and https://social.msdn.microsoft.com/Forums/silverlight/en-US/3041e898-155c-45ec-9c67-ab48567e8b91/the-shape-of-datalabel-on-the-chart-c-excel?forum=exceldev but no luck.

    Can anyone please help me.



    • Edited by sorb999 Thursday, July 6, 2017 4:31 AM
    Wednesday, June 28, 2017 7:11 AM

All replies

  • Hi sorb999,

    What is series1? Did you get any error message? I suggest you iterate through seriesCollection and points collection to set DataLabel. Here is the example.

    foreach (Excel.Series ser in seriesCollection) {             
    foreach (Excel.Point pt in ser.Points(Type.Missing)) {              
    pt.HasDataLabel = true;                  
    pt.DataLabel.Position= Excel.XlDataLabelPosition.xlLabelPositionInsideBase;                  
    Microsoft.Office.Interop.Excel.DataLabel db = pt.DataLabel;                 
    db.Select();                   
    db.Format.AutoShapeType =
    Microsoft.Office.Core.MsoAutoShapeType.msoShapeOctagon;                 
    pt.DataLabel.Font.Name = "Arial";                  
    pt.DataLabel.Font.FontStyle = "Bold";                 
    pt.DataLabel.Font.Size = 8;                 
    pt.DataLabel.Text = "N";             
    }          
    }

    Besides, the value of DataLabel.Position are different according to different chart type so we  need select correct value.

    Best Regards,

    Terry

    Thursday, June 29, 2017 9:34 AM
  • I am trying to add data labels in excel line charts using C#.

    But I have some questions in it

    1. Is it really compatible to use data label in excel line charts? What if data is too much and it starts looking so hotchpotch?
    2. Can I add more then one data label at the same point in the series? Because I did it in C# but it is showing only last one.
    3. How to change data label shape?
    4. How to change data label size?

    If anyone has solution or suggestion please suggest me. It will be highly appreciated. 

    Thanks

    Monday, July 3, 2017 7:15 AM
  • Hello,

    I would suggest you follow Add or remove data labels in a chart to add data labels and change its shape or size for your requirement and record a marco at the same time (please visit Automate tasks with the Macro Recorder). Then you could get the properties and method you should use in C#. 

    For example, the code below is adding datalabels and re-setting and formatting one of label. 

        ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.SetElement (msoElementDataLabelTop)
        ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.FullSeriesCollection(1).DataLabels.Select
        Selection.AutoText = True
        ActiveChart.FullSeriesCollection(1).Points(4).DataLabel.Select
        Selection.Width = 35.56
        Selection.Height = 30.985
        ActiveChart.SeriesCollection(1).DataLabels(4).Format.TextFrame2.TextRange. _
            Characters.Text = ""
        ActiveChart.SeriesCollection(1).DataLabels(4).Format.TextFrame2.TextRange. _
            InsertAfter "4" & Chr(13) & ""
        With ActiveChart.SeriesCollection(1).DataLabels(4).Format.TextFrame2.TextRange. _
            Characters(1, 2).Font
            .BaselineOffset = 0
            .Bold = msoFalse
            .NameComplexScript = "+mn-cs"
            .NameFarEast = "+mn-ea"
            .Fill.Visible = msoTrue
            .Fill.ForeColor.RGB = RGB(64, 64, 64)
            .Fill.Transparency = 0
            .Fill.Solid
            .Size = 24
            .Italic = msoFalse
            .Kerning = 12
            .Name = "+mn-lt"
            .UnderlineStyle = msoNoUnderline
            .Strike = msoNoStrike
        End With
        With ActiveChart.SeriesCollection(1).DataLabels(4).Format.TextFrame2.TextRange. _
            Characters(1, 2).ParagraphFormat
            .TextDirection = msoTextDirectionLeftToRight
            .Alignment = msoAlignCenter
        End With

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 5, 2017 7:21 AM
    Moderator
  • Hi Terry,

    Thanks for your reply, But as far as I can check here in Microsoft.Office.Interop.Excel.ChartFormat no property exist named AutoShapeType. it is giving me following error.

    Error 88 'Microsoft.Office.Interop.Excel.ChartFormat' does not contain a definition for 'AutoShapeType' and no extension method 'AutoShapeType' accepting a first argument of type 'Microsoft.Office.Interop.Excel.ChartFormat' could be found (are you missing a using directive or an assembly reference?).

    I am using it in Microsoft.Office.Interop.Excel.XlChartType.xlLine so is their some compatibility issue with xlLine chart?

    And series1 was a typo :p, Please ignore it. I have edited my question.

    I am also not able to resize shape of data label.


    • Edited by sorb999 Thursday, July 6, 2017 4:33 AM
    Thursday, July 6, 2017 4:28 AM
  • Hi sorb999,

    Which version of Excel are you using? According to your error message, I think you are using Excel 2010, right? Excel 2010 does not support to change shape type of data label, so there is no AutoShapeType property in ChartFormat. I suggest you upgrade version of your Excel so that you could change the shape of data label. You could not change label shape size, the label will auto size according to text in it. Point doesn't have a data label collection but only the DataLabel Property which returns a DataLabel object. So you could not add more than one data label at the same point.

    Best Regards,

    Terry

    Tuesday, July 11, 2017 2:58 AM