Excel formatting using Matlab
190 vues (au cours des 30 derniers jours)
Afficher commentaires plus anciens
NIKHIL
le 28 Août 2020
Commenté : Geraldo Rebouças
le 24 Nov 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 commentaires
stozaki
le 29 Août 2020
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
Réponse acceptée
Image Analyst
le 29 Août 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.
16 commentaires
dpb
le 31 Déc 2021
Modifié(e) : dpb
le 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...
Geraldo Rebouças
le 24 Nov 2022
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.
Plus de réponses (0)
Voir également
Catégories
En savoir plus sur Data Import from MATLAB dans Help Center et File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!