locked
Add a "%" symbol with Excel Interop RRS feed

  • Question

  • Hello folks!

    How do I programmatically put a "%" symbol to a column in the Excel chart. I am using Excel Interop library.

    Any advice will be appreciated!

    Tuesday, April 7, 2015 8:21 AM

Answers

  • Hi hokushin,

    The Excel object model is similar to the Excel PIAs. To achieve the goal using C#, we can append the two lines of code to the last of code sample above:

      series1.DataLabels.Select();
                _sheet.Application.Selection.NumberFormat = "0.00%";

    Regards & Fei


    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, April 9, 2015 8:18 AM

All replies

  • Hello folks!

    How do I programmatically put a "%" symbol to a column in the Excel chart. I am using Excel Interop library.

    Any advice will be appreciated!


    Tuesday, April 7, 2015 7:50 AM
  • This isn't a wpf question, so you're less likely to get useful replies here.

    I suggest you ask in a more appropriate forum.

    And post the code you're using.


    Hope that helps.
    Recent Technet articles: Property List Editing; Dynamic XAML

    Tuesday, April 7, 2015 8:15 AM
  • I've just redirected this question to Excel Dev forum: https://social.msdn.microsoft.com/Forums/office/en-US/9733ff5a-0462-4440-8742-e63806943c04/add-a-symbol-with-excel-interop?forum=exceldev
    Tuesday, April 7, 2015 8:23 AM
  • This seems like a formatting issue.  What do you mean 'Excel Interop'?  Are you using VB.NET or C#.NET to open and manipulate an Excel file?

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, April 7, 2015 3:24 PM
  • You can try this Excel component, it can add almost every kind of charts completely, refer this article,and just change the data with % format. hope helpful.
    Wednesday, April 8, 2015 2:07 AM
  • Hi hokushin,

    According to the description, you want to format the data labels for the column chart. As far as I know, we can select the data labels and format it as we wanted. Here is an sample code that format the data label of first series for your reference:

        ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.FullSeriesCollection(1).DataLabels.Select
        Selection.NumberFormat = "0.00%"

    In addition, a better way to learn how to navigate the object models of Word, Excel, and Powerpoint, based on what you want to do as a user, is to use the Macro Recorder.Keep in mind the recorded macro won't be the best possible code in most cases, but it's does very well for a quick example.

    Also here is the link for the Excel object model for your reference:
    Object model reference (Excel 2013 developer reference)

    Regards & Fei


    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.

    Wednesday, April 8, 2015 9:49 AM
  • I am working on wpf desktop application in which I am using C# 4.0 and MVVM design pattern. The app generates several Excel reports and I am using Microsoft.Office.Interop.Excel.dll library to export data from wpf app to excel file. I have found that the Microsoft.Office.Interop.Excel.dll library is a good choice for dealing with Excel files. Below is the code I am using to generate the chart above. 

     private void CreateChart(string[] xValues, double[] yValues)
            {
                var xlCharts = (Excel.ChartObjects)_sheet.ChartObjects(Type.Missing);
                var myChart = (Excel.ChartObject)xlCharts.Add(48, 241, 750, 400);
                var chartPage = myChart.Chart;
    
                chartPage.HasLegend = false;
    
                chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
    
                chartPage.ChartStyle = 4;            
    
                var seriesCollection = chartPage.SeriesCollection();
                
                var series1 = seriesCollection.NewSeries();
                series1.Name = MetroNew.Localization.Properties.Resources.ReportFractionTitle;
    
                series1.XValues = xValues;
                series1.Values = yValues;
    
                var vertAxis = (Axis)chartPage.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary);
                vertAxis.HasMajorGridlines = true; 
                vertAxis.MaximumScaleIsAuto = false;
                vertAxis.MaximumScale = 100; 
                vertAxis.MinimumScaleIsAuto = false;
                vertAxis.MinimumScale = 0;
    
                var pointsCount = series1.Points().Count;
    
                Color color;
    
                for (int i = 1; i <= pointsCount; i++)
                {
                    if (i <= 50)
                    {
                        color = Color.FromName(i.GetColorByName());
                    }
                    else
                    {
                        var rnd = new Random();
                        int index = rnd.Next(1, 50);
                        color = Color.FromName(i.GetColorByName());
                    }
    
                    series1.Points(i).Interior.Color = color;
                }
    
                var axis = (Axis)chartPage.Axes(
          Excel.XlAxisType.xlValue,
          Excel.XlAxisGroup.xlPrimary);
    
                axis.HasTitle = true;
                axis.AxisTitle.Text = MetroNew.Localization.Properties.Resources.ReportFractionContentTitle;
    
                series1.ApplyDataLabels(
                     Excel.XlDataLabelsType.xlDataLabelsShowLabel,
            true, true, true, false, false, true, true,
            true, true);
    
            }

    Wednesday, April 8, 2015 10:21 AM
  • Nice library, but I have some restictions on using COTS in the app.
    Wednesday, April 8, 2015 10:25 AM
  • Thanks a lot for prompt reply. I can use your code snippet in VBA macros. I need the same for C# and Microsoft.Office.Interop.Excel.dll library.
    Wednesday, April 8, 2015 1:15 PM
  • Hi hokushin,

    The Excel object model is similar to the Excel PIAs. To achieve the goal using C#, we can append the two lines of code to the last of code sample above:

      series1.DataLabels.Select();
                _sheet.Application.Selection.NumberFormat = "0.00%";

    Regards & Fei


    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, April 9, 2015 8:18 AM
  • It did the trick. I appreciate it.
    • Edited by hokushin Thursday, April 9, 2015 8:35 AM
    Thursday, April 9, 2015 8:35 AM