Excel formatting using Matlab
210 次查看(过去 30 天)
显示 更早的评论
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 个评论
stozaki
2020-8-29
Hello NIKHIL,
An example program to change the background color and font color of Excel cells is described in the thread below.
However, this answer thread is in Japanese, so could you translate from Japanese to English with automatic translation?
This program requires VB as well as MATLAB.
Regards,
stozaki
采纳的回答
Image Analyst
2020-8-29
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.
16 个评论
dpb
2021-12-31
编辑:dpb
2022-1-1
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...
Geraldo Rebouças
2022-11-24
I am wondering why didn't you put the attached files into FEX? From the comments, it seems quite useful, so maybe others can benefit from it as well.
更多回答(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!