Effacer les filtres
Effacer les filtres

Excel formatting using Matlab

190 vues (au cours des 30 derniers jours)
NIKHIL
NIKHIL le 28 Août 2020
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
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
NIKHIL
NIKHIL le 29 Août 2020
Thank you Stozaki for your response. I was mainly looking for creating outline/border for the table data.

Connectez-vous pour commenter.

Réponse acceptée

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

Connectez-vous pour commenter.

Plus de réponses (0)

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!

Translated by