Applying a border to Excel cells when using COM

78 次查看（过去 30 天）
Kenneth Sandberg
Kenneth Sandberg 2016-11-2
评论： Jared 2024-10-23
I am writing simulation results from Matlab to Excel. In order to make it easier to read I apply some formating to the output. It works fine to change font size, font color, number format etc but applying a border around a selected number of cells doesn't work.
Example:
% -- Create excel sheet --
AppObj = actxserver('Excel.Application');
AppObj.Visible = true;
WkbkObj = AppObj.Workbooks;
DataWkbkObj = WkbkObj.Add;
DataWkbkObj.Sheets.Add().Name = 'Test';
DataSheetObj = DataWkbkObj.Sheets.Item('Test');
% -- Write some data --
DataSheetObj.Range('B2').Value = 43;
DataSheetObj.Range('B3').Value = 5;
DataSheetObj.Range('B4').Value = 7;
% -- Apply blue color --
DataSheetObj.Range('B2:B4').Font.Color = -4165632;
% -- Change number format --
DataSheetObj.Range('B2:B4').NumberFormat = '0,00';
% -- Apply a line on the left side --
DataSheetObj.Range('B2:B4').Borders('xlEdgeLeft').LineStyle = 'xlContinuous';
DataSheetObj.Range('B2:B4').Borders('xlEdgeLeft').Weight = 'xlMedium';
The last two lines doesn't work. The problem is the 'xlEdgeLeft' part. I have tried to analyze the object and use constant values without result. Any ideas?

请先登录，再回答此问题。

采纳的回答

Abhinav Gurram
Abhinav Gurram 2016-11-11
To apply specific borders to a range of cells in Excel, you would be required to use the 'Borders.Item' property that returns a Border object, as listed in the MSDN reference here: Borders.Item Property.
In order to set the border style for the range of cells in your example program, you can modify the last two statements as:
DataSheetObj.Range('B2:B4').Borders.Item('xlEdgeLeft').LineStyle = 1;
DataSheetObj.Range('B2:B4').Borders.Item('xlEdgeLeft').Weight = -4138;
Hope this helps!
  2 个评论
Steven
Steven 2019-9-27
This does not work on R2019a. I get this error:
Expected one output from a curly brace or dot indexing expression, but there were 5 results.
Jared
Jared 2024-10-23
I know this is eight (8) years late, but this will be edify anyone else having this problem with all of the Excel Object library functionality not being available to MATLAB through the COM (component object model) operator (via actxserver).
Most of these types of inputs must be referred to by their Excel enumeration constant when operating from MATLAB through the COM operator.
This link has an exhaustive list of these constants: http://www.smarterdatacollection.com/Blog/?p=374
This is Microsofts list of these constants (but it isn't as exhaustive as the one above): https://learn.microsoft.com/en-us/office/vba/api/excel.constants
So replace 'xlEdgeLeft' with numeral 7.
DataSheetObj.Range('B2:B4').Borders.Item(7).LineStyle = 1;
DataSheetObj.Range('B2:B4').Borders.Item(7).Weight = -4138;

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

产品

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by