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