none
Graph in Word - how to set vertical axis title? RRS feed

  • Question

  • Hi,

    This is how I add a chart into Word - it's working - I just want to find out how to set the Y-axis title to for example "Dollar Amount":

                object missing = Type.Missing;
                Word.Application application = new Microsoft.Office.Interop.Word.Application();
                application.Visible = true;
                Word.Document document = application.Documents.Add();
                object classtype = "Excel.Chart.8";
                object oEndOfDoc = "\\endofdoc";
                Word.InlineShape wrdInlineShape = document.InlineShapes.AddOLEObject(classtype);
                object verb = Word.WdOLEVerb.wdOLEVerbHide;
                wrdInlineShape.OLEFormat.DoVerb(ref verb);
                Random rn = new Random();
                Excel.Workbook workBook = (Excel.Workbook)wrdInlineShape.OLEFormat.Object;
                Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets["Sheet1"];
                wrdInlineShape.Width = 400;
                workBook.ActiveChart.ChartType = Excel.XlChartType.xlLine;
                workBook.ActiveChart.HasTitle = true;
                workBook.ActiveChart.ChartTitle.Text = "My chart title";
                wrdInlineShape.ConvertToShape();


    • Edited by Silan Liu Monday, May 6, 2013 8:16 AM
    Monday, May 6, 2013 8:15 AM

Answers

  • Hi,

    Thank you for posting in the MSDN Forum.

    You can try below code

    static void Main(string[] args)
    {
        object missing = Type.Missing;
        Word.Application application = new Word.Application();
        application.Visible = true;
        Word.Document document = application.Documents.Add();
        object classtype = "Excel.Chart.8";
        object oEndOfDoc = "\\endofdoc";
        Word.InlineShape wrdInlineShape = document.InlineShapes.AddOLEObject(classtype);
        object verb = Word.WdOLEVerb.wdOLEVerbHide;
        wrdInlineShape.OLEFormat.DoVerb(ref verb);
        Random rn = new Random();
        Excel.Workbook workBook = (Excel.Workbook)wrdInlineShape.OLEFormat.Object;
        Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets["Sheet1"];
        wrdInlineShape.Width = 400;
        workBook.ActiveChart.ChartType = Excel.XlChartType.xlLine;
        workBook.ActiveChart.HasTitle = true;
        workBook.ActiveChart.ChartTitle.Text = "My chart title";
        
        Excel.Axis axis = workBook.ActiveChart.Axes(Excel.XlAxisType.xlValue);
        axis.HasTitle = true;
        axis.AxisTitle.Caption = "Dollar Amount";
        
        wrdInlineShape.ConvertToShape();
    }

    AxisTitle.Caption Property would do the trick.

    Hope it helps.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, May 8, 2013 6:46 AM
    Moderator
  • Hi,

    I've edited my last reply to correct some mistakes. The code would work for you now.

    Thank you very much for your patience.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Silan Liu Friday, May 10, 2013 12:13 PM
    Friday, May 10, 2013 10:00 AM
    Moderator
  • Hi,

    After you edited the worksheet, you'll need to update the chart datasource.

    See Chart.SetSourceData Method (Excel).

    Hope it helps.


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Silan Liu Monday, May 13, 2013 8:15 AM
    Monday, May 13, 2013 6:53 AM
    Moderator
  • I worked it out:

                    object[,] aoValues = new object[15, 3];
    
                    for (int iRow = 0; iRow < 15; iRow++)
                    {
                        aoValues[iRow, 0] = "Week " + (iRow + 1);
                        aoValues[iRow, 1] = rn.Next(10, 50);
                        aoValues[iRow, 2] = rn.Next(10, 50);
                    }
    
                    //Transfer the array to the worksheet starting at cell A2
                    workSheet.Range["A2:C16"].Value = aoValues;
    

    • Marked as answer by Silan Liu Wednesday, May 15, 2013 6:52 AM
    Wednesday, May 15, 2013 6:52 AM

All replies

  • Hi,

    Thank you for posting in the MSDN Forum.

    You can try below code

    static void Main(string[] args)
    {
        object missing = Type.Missing;
        Word.Application application = new Word.Application();
        application.Visible = true;
        Word.Document document = application.Documents.Add();
        object classtype = "Excel.Chart.8";
        object oEndOfDoc = "\\endofdoc";
        Word.InlineShape wrdInlineShape = document.InlineShapes.AddOLEObject(classtype);
        object verb = Word.WdOLEVerb.wdOLEVerbHide;
        wrdInlineShape.OLEFormat.DoVerb(ref verb);
        Random rn = new Random();
        Excel.Workbook workBook = (Excel.Workbook)wrdInlineShape.OLEFormat.Object;
        Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets["Sheet1"];
        wrdInlineShape.Width = 400;
        workBook.ActiveChart.ChartType = Excel.XlChartType.xlLine;
        workBook.ActiveChart.HasTitle = true;
        workBook.ActiveChart.ChartTitle.Text = "My chart title";
        
        Excel.Axis axis = workBook.ActiveChart.Axes(Excel.XlAxisType.xlValue);
        axis.HasTitle = true;
        axis.AxisTitle.Caption = "Dollar Amount";
        
        wrdInlineShape.ConvertToShape();
    }

    AxisTitle.Caption Property would do the trick.

    Hope it helps.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, May 8, 2013 6:46 AM
    Moderator
  • Thanks Quist for your reply! It is helpful but not totally solved my problem: can I just put any text as the title for the vertical (Y) axis? For example can I just display "Total Sales ($Million)" along the Y axis?

    I am switching from the Microsoft.Interop.Graph.dll. The graph it draws are ugly compared with this new way of graphing, but I could put such vertical (Y) axis titles.

    Wednesday, May 8, 2013 7:11 AM
  • Hi "dude"

    Even though you're displaying the chart in Word, the object model you're using is actually Excel. You might be better off asking this in an Excel forum, where you'll find a lot more people who work with this object model...


    Cindy Meister, VSTO/Word MVP, my blog

    Wednesday, May 8, 2013 10:21 AM
    Moderator
  • Hi,

    I've edited my last reply to correct some mistakes. The code would work for you now.

    Thank you very much for your patience.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Silan Liu Friday, May 10, 2013 12:13 PM
    Friday, May 10, 2013 10:00 AM
    Moderator
  • Hi Quist,

    I tried to plot a chart with 30 data points (there are 30 rows on the spreadsheet). But the chart only shows the first seven data points. How to let the chart squeeze all points into the display area?

    Here is my code:

                    Random rn = new Random();
                    Excel.Workbook workBook = (Excel.Workbook)wrdInlineShape.OLEFormat.Object;
                    Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets["Sheet1"];
                    workSheet.Cells.Clear();
                    ((Excel.Range)workSheet.Cells[1, 2]).Value = "Cost";
                    ((Excel.Range)workSheet.Cells[1, 3]).Value = "Sales";
    
                    for (int iRow = 2; iRow <= 32; iRow++)
                    {
                        ((Excel.Range)workSheet.Cells[iRow, 1]).Value = "Week " + iRow;
    
                        for (int iCol = 2; iCol <= 3; iCol++)
                        {
                            ((Excel.Range)workSheet.Cells[iRow, iCol]).Value = rn.Next(10, 50);
                        }
                    }


    • Edited by Silan Liu Monday, May 13, 2013 6:35 AM
    Monday, May 13, 2013 6:33 AM
  • Hi,

    After you edited the worksheet, you'll need to update the chart datasource.

    See Chart.SetSourceData Method (Excel).

    Hope it helps.


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Silan Liu Monday, May 13, 2013 8:15 AM
    Monday, May 13, 2013 6:53 AM
    Moderator
  • Thank you so much for your timely answer - it worked!
    Monday, May 13, 2013 8:16 AM
  • Hi Quist,

    I ran into a performance issue. I have a System.Data.DataTable, which contains 30 thousand rows. I want to plot a line chart, and here is how I populate the Excel work sheet. You don't need to understand the code - basically I am populating cell by cell. It takes roughly 30 minutes - obviously it is too long.

    There must be a way when I can simply say: "Excel, here is a DataTable. Use it as your data source, and populate yourself!"

    Or at least I can say "Here is a long array, use it to populate the A2 ~ A30002 cells"?

                for (int iRow = 0; iRow < dataTable.Rows.Count; iRow++)
                {
                    // Plot the tick mark on the X axis
                    ((Excel.Range)workSheet.Cells[iRow + 2, 1]).NumberFormat = "@"; // Set to to text
                    ((Excel.Range)workSheet.Cells[iRow + 2, 1]).Value = 
                        dataTable.Rows[iRow][dataTable.Columns[chartFormat.XAxisColumnName]].ToString();
    
                    // Plot the data points
                    for (int iCol = 0; iCol < chartFormat.ColumnsToPlot.Length; iCol++)
                    {
                        ((Excel.Range)workSheet.Cells[iRow + 2, iCol + 2]).Value = 
                            dataTable.Rows[iRow][dataTable.Columns[chartFormat.ColumnsToPlot[iCol]]];
                    }
                }


    • Edited by Silan Liu Wednesday, May 15, 2013 6:18 AM
    Wednesday, May 15, 2013 6:16 AM
  • I worked it out:

                    object[,] aoValues = new object[15, 3];
    
                    for (int iRow = 0; iRow < 15; iRow++)
                    {
                        aoValues[iRow, 0] = "Week " + (iRow + 1);
                        aoValues[iRow, 1] = rn.Next(10, 50);
                        aoValues[iRow, 2] = rn.Next(10, 50);
                    }
    
                    //Transfer the array to the worksheet starting at cell A2
                    workSheet.Range["A2:C16"].Value = aoValues;
    

    • Marked as answer by Silan Liu Wednesday, May 15, 2013 6:52 AM
    Wednesday, May 15, 2013 6:52 AM