Excel formatting using Matlab

221 views (last 30 days)
NIKHIL
NIKHIL on 28 Aug 2020
Edited: dpb on 1 Jan 2022
I have saved data in excel using xlswrite but i am facing issue with alignment of cells and borders for tables, colouring of titles. can someone help me with formatting excel ? Some useful threads or links will also be helpful.
  2 Comments
NIKHIL
NIKHIL on 29 Aug 2020
Thank you Stozaki for your response. I was mainly looking for creating outline/border for the table data.

Sign in to comment.

Accepted Answer

Image Analyst
Image Analyst on 29 Aug 2020
See my attached Excel_utils class. It lets you format a bunch of things like font, coloring, borders, number of decimal points, etc. It has sample calls at each function.
I'm also attaching a standalone ActiveX demo that I think may do some formatting. And one to let you put a formula into a cell.
  15 Comments
dpb
dpb on 31 Dec 2021
Edited: dpb on 1 Jan 2022
OK, I revisted the problem of trying to set borders reliably and finally think I got a simplified syntax that actually does work -- the sidetrack of trying to use MATLAB enumerations expecting them to be equivalent to C in function got me hung up before, besides the confusion on how to address the collection of lines.
I added another function to the toolbox to set outside borders of a selected range based on the VBA code of a coded macro...and with the conversion of the enumerations to a class that returns a (Constant) instead, the following is short and functions as advertised...
function SetOutsideBorder(Excel,range,weight,style)
% Set Outside Border of Selected Range to Line Weight and Style
% Usage:
% SetOutsideBorder(Excel,RangeExpression,LineWeight,LineStyle)
%
% Excel XlBorderWeight Enumeration Constants
% xlHairline 1 Hairline (thinnest border).
% xlMedium -4138 Medium.
% xlThick 4 Thick (widest border).
% xlThin 2 Thin.
% Excel XlLineStyle Enumeration Constants
% xlContinuous 1 Continuous line.
% xlDash -4115 Dashed line.
% xlDashDot 4 Alternating dashes and dots.
% xlDashDotDot 5 Dash followed by two dots.
% xlDot -4118 Dotted line.
% xlDouble -4119 Double line.
% xlLineStyleNone -4142 No line. (Alias xlNone)
% xlSlantDashDot 13 Slanted dashes.
if style==XlLineStyle.xlDouble, weight=XlBorderWeight.xlThick; end % only combination that works
if ~isstring(range), range=string(range); end
try
Excel.Range(range).Select;
Excel.Selection.Borders.Item(XlBordersIndex.xlDiagonalDown).LineStyle=XlLineStyle.xlNone;
Excel.Selection.Borders.Item(XlBordersIndex.xlDiagonalUp).LineStyle=XlLineStyle.xlNone;
for b=XlBordersIndex.xlEdgeLeft:XlBordersIndex.xlEdgeRight
Excel.Selection.Borders.Item(b).LineStyle=XlLineStyle.xlContinuous;
Excel.Selection.Borders.Item(b).Weight=weight;
Excel.Selection.Borders.Item(b).ColorIndex=0;
Excel.Selection.Borders.Item(b).TintAndShade=0;
end
catch ME
fprintf('Error in function SetBorder.\nError Message:\n%s\n', ME.message)
%warning('Error in function SetBorder.\n%s', ME.message)
end
end % SetOutsideBorder method
The above mimics a recorded macro which had four repeated group selections and duplicated .With constructs that I replaced with the loop for the four outside border indices.
The last syntax item to be solved is that while VBA can write
Borders(XlBordersIndex.xlDiagonalUp).LineStyle=XlLineStyle.xlNone
inside the .With construct, to use COM one must explicitly index into the Borders collection by indexing into the Item
Experimentation showed that only the xlThick border works with a double line; no error returned but anything else is a "do nothing" operation.
The other thing I learned is that the COM interface fails for a range expression if try to pass a cell string instead of a string. Hence the cast to string() inside the function to let existing higher level code continue to use cellstring operations. A straight char() string would also work, but they're so hard to deal with programmatically, I presumed that other than a literal string nobody would ever use them for string handling any more.
@Image Analyst, thanks again for the basic outline, wouldn't have gotten anywhere without it...

Sign in to comment.

More Answers (0)

Community Treasure Hunt

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

Start Hunting!