Important Notice !!!

This blog has been discarded and no update any more.

Please visit http://www.quantlego.com for active updates.

Sorry for any inconvenience.





Monday, June 17, 2013

Control Excel chart position with row and column numbers in Matlab

| |
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
  2. 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

  3. Determine the cell position in terms of points
  4. 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

0 comments:

Post a Comment

Powered by Blogger.