Graham Cockram

Graham Cockram

  • NA
  • 3
  • 4.2k

Excel datalabels font size not being applied

Nov 22 2011 9:14 AM
Hi all,

I wonder if anybody can help me.  I'm generating an excel graph using C#, it has two lines with datapoints.  I would like to add my own bespoke text to the points and I have got this code working.

My issue is that although I specify many times that the font size should be 10 points when I open the excel spreadsheet the datalabels are not set to be font size 10.  I have tried many different permutations with no joy, I'm using MS Visual Studio 2008 Professional Edition with Excel 2010.

My code is below which loops through the series in the graph:

            Excel.SeriesCollection oSeriesCollection;
            oSeriesCollection = (Excel.SeriesCollection)chartPage.SeriesCollection(Type.Missing);
            IEnumerator iEChartSeries = oSeriesCollection.GetEnumerator();

            Excel.Series oSeries;
            Excel.Point pt;

            int seriesCnt = 1;
            int seriesCol = 3;
            int seriesRow = 11;
            int seriesPointCnt = 1;

            Excel.Range myrange;

            while(iEChartSeries.MoveNext())
            {
                if (seriesCnt == 1) // target
                {
                    seriesCol = 3;
                    seriesRow = 11;
                    myrange = xlWorkSheet.get_Range(xlWorkSheet.Cells[seriesRow, seriesCol], xlWorkSheet.Cells[seriesRow, seriesCol]);
                    myrange.Font.Name = "Calibri";
                    myrange.Font.Size = 10.0;

                    oSeries = (Excel.Series)(iEChartSeries.Current);
                    Excel.Points pts = (Excel.Points)oSeries.Points(Type.Missing);
                    IEnumerator iPoints = pts.GetEnumerator();
                    while (iPoints.MoveNext())
                    {
                        if (seriesPointCnt == 1)
                        {
                            pt = (Excel.Point)(iPoints.Current);
                            pt.HasDataLabel = false;
                        }
                        else
                        {
                            pt = (Excel.Point)(iPoints.Current);
                            pt.HasDataLabel = true;
                            pt.DataLabel.Position = Excel.XlDataLabelPosition.xlLabelPositionRight;
                            pt.DataLabel.Font.Size = 10.0;
                            pt.DataLabel.Font.Name = "Calibri";
                            pt.DataLabel.Text = myrange.Cells.get_Value(Type.Missing).ToString();

                            seriesCol = seriesCol + 1;
                            myrange = xlWorkSheet.get_Range(xlWorkSheet.Cells[seriesRow, seriesCol], xlWorkSheet.Cells[seriesRow, seriesCol]);
                            myrange.Font.Name = "Calibri";
                            myrange.Font.Size = 10.0;

                        }
                        seriesPointCnt = seriesPointCnt + 1;
                    }
                }
                else if (seriesCnt == 2)    // actual
                {
                    seriesCol = 3;
                    seriesRow = 10;
                    myrange = xlWorkSheet.get_Range(xlWorkSheet.Cells[seriesRow, seriesCol], xlWorkSheet.Cells[seriesRow, seriesCol]);
                    myrange.Font.Name = "Calibri";
                    myrange.Font.Size = 10.0;

                    oSeries = (Excel.Series)(iEChartSeries.Current);
                    Excel.Points pts = (Excel.Points)oSeries.Points(Type.Missing);
                    IEnumerator iPoints = pts.GetEnumerator();
                    while (iPoints.MoveNext())
                    {
                        if (seriesPointCnt == 1)
                        {
                            pt = (Excel.Point)(iPoints.Current);
                            pt.HasDataLabel = false;
                        }
                        else
                        {
                            if (myrange.Value2==null)
                            {
                                pt = (Excel.Point)(iPoints.Current);
                                pt.HasDataLabel = false;
                            }
                            else
                            {
                                pt = (Excel.Point)(iPoints.Current);
                                pt.HasDataLabel = true;
                                pt.DataLabel.Position = Excel.XlDataLabelPosition.xlLabelPositionRight;
                                pt.DataLabel.Font.Size.Equals(10);
                                pt.DataLabel.Font.Name = "Calibri";
                                pt.DataLabel.Text = myrange.Cells.get_Value(Type.Missing).ToString();
                                seriesCol = seriesCol + 1;
                                myrange = xlWorkSheet.get_Range(xlWorkSheet.Cells[seriesRow, seriesCol], xlWorkSheet.Cells[seriesRow, seriesCol]);
                                myrange.Font.Name = "Calibri";
                                myrange.Font.Size = 10.0;
                            }
                        }
                        seriesPointCnt = seriesPointCnt + 1;
                    }
                }
                seriesPointCnt = 1;
                seriesCnt = seriesCnt + 1;
            }

If anyone can give me some pointers I would be very grateful.

Thanks,

Graham

Answers (3)