Accurately Control Excel Chart Position in Matlab

This article tells how to accurately control Excel chart position by Matlab using row and column numbers.

Sometimes we want to align, in Excel spreadsheet, a chart with a cell which, for example, may contain a label for the chart. We can easily drag (with mouse) the chart to make it aligned with the cell, but it may not be so easy to do this by program. The challenge here is that in Excel spreadsheet a cell position is expressed in row and columns, while a chart position is expressed in points. Depending on the screen resolution, the number of points corresponding to one row or column may vary on different computers. When we want to insert a large amount of charts into a sheet by program, exact positioning of these charts will be a headache. This tutorial teaches you how to programmingly obtain and position the chart in terms of row/column numbers.

  1. Determine the chart position in terms of rows

    A VBA chart Object has properties TopLeftCell and BottomRightCell which return the cells in the upper left and lower right corners of the chart, respectively. For example, following VBA code is used to retrieve the row property of cells pertaining to the chart position:

    Dim TopRow As Long
    Dim BottomRow As Long
    With ActiveSheet.ChartObjects(1)
        TopRow = .TopLeftCell.Row
        BottomRow = .BottomRightCell.Row
    End With
  2. Determine the cell position in terms of points

    We can use the Left and Top properties to determine the distances (of a cell, row or column) to the left and top boundaries of the spreadsheet, in the unit of points. Here is an example using VBA code

    Worksheets("Sheet1").Range("A5").Top
    Worksheets("Sheet1").Range("B7").Left
    Worksheets("Sheet1").Columns(6).Left
    Worksheets("Sheet1").Rows(11).Top

    Following Matlab code shows how to insert 10 charts into an excel spreadsheet, each having a figure number printed in a cell beside it:
     

    ObjExcel = actxserver('Excel.Application');
    TheWorkbook = ObjExcel.Workbooks.Open('example.xls');
    DataSheet = TheWorkbook.Sheets.Item('Data');
    RowLastChartBottom = 0;
    for i = 1 : 10
        RowChartTitle = RowLastChartBottom + 2;
        DataSheet.Range('A', num2str(RowChartTitle)).Value = ['Chart ', num2str(i)];
        %---
        RowChartTop = RowChartTitle + 1;
        PointsChartTop = ChartSheet.Range(['B', num2str(RowChartTop)]).Top;
        ChartObject = ChartSheet.ChartObjects.Add(10, PointsChartTop, 1000, 250);
        ChartObject.Chart.HasTitle = 1;
        ChartObject.Chart.ChartTitle.Text = '......';
        ChartObject.Chart.Axes.Item(1).TickLabelPosition = 'xlTickLabelPositionLow';
        %---
        CurveObject = ChartObject.Chart.SeriesCollection.NewSeries;
        XRange = DataSheet.Range(......);
        YRange = DataSheet.Range(......);
        CurveObject.XValues = XRange;
        CurveObject.Values = YRange;
        CurveObject.ChartType = 'xlLineMarkers';
        CurveObject.MarkerSize = 2;
        CurveObject.Format.Line.Weight = 2;
        %---
        RowLastChartBottom = ChartObject.BottomRightCell.Row;
    end

     

Subject
Category
Coding language