Excel formatting using Matlab

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.

[stozaki]

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?

https://jp.mathworks.com/matlabcentral/answers/95482-matlab-excel-spreadsheet

This program requires VB as well as MATLAB.

Write Data to Excel Spreadsheet

Regards,

stozaki

[NIKHIL]

Thank you Stozaki for your response. I was mainly looking for creating outline/border for the table data.

Answer 1

[Image Analyst]

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.

[NIKHIL]

Hello Image Analyst, thank you for Excel_utils class. It is working really good for coloring cells, font and aligning cells.

I am still facing issue with creating outline/borders for table data.

Demo_table = [a, b, c; 1, 2, 3; 4, 5, 6];

I am writing this data in excel sheet using xlswrite and it is working fine.

I want to outline/border this table and autofit column width accordingly. i used below commands for border similar to your sample calls but its not working for me.

%Excel_utils.FormatRightBorder(sheetReference, columnNumbers, startingRow, endingRow)
Excel_utils.FormatRightBorder(Excel.ActiveSheet, [1, 2, 3], 1, 3);
%FormatBottomBorder(sheetReference, rowNumbers, startingCol, endingCol, weight)
Excel_utils.FormatBottomBorder(Excel.ActiveSheet, [1, 2, 3], 1, 3, 4);

Not sure what might be going wrong. No warning or any error in command window.

Please help for creating outline/border for table.

Thank you

[Image Analyst]

This worked just fine:

fprintf('Beginning to run %s.m ...\n', mfilename);
% Initialization steps.
clc;    % Clear the command window.
close all;  % Close all figures (except those of imtool.)
clear;  % Erase all existing variables. Or clearvars if you want.
workspace;  % Make sure the workspace panel is showing.
format long g;
format compact;
fontSize = 20;

% Check if Excel is running and ask user for permission to shut down Excel.
warningMessage = sprintf('Excel is running.\nTo prevent problems, I would like to shut it down.\n(If you do not see Excel running on the taskbar, then\ntype control-shift-esc and shut it down from the task manager.\nCan I shut down Excel now?');
IsExcelRunning(warningMessage);

a=111;
b=222;
c=333;
Demo_table = [a, b, c; 1, 2, 3; 4, 5, 6]
% Save the data to a workbook file.
excelFullFileName = fullfile(pwd, 'Test Excel Formatting.xlsx');
xlswrite(excelFullFileName, Demo_table);

% Get Excel as an ActiveX server.
fprintf('Getting Excel as an ActiveX server ...\n');
Excel = actxserver('Excel.Application');
% Let's see it.
Excel.Visible = true;

% Open our workbook.
fprintf('Opening workbook %s into Excel ...\n', excelFullFileName);
excelWorkbook = Excel.Workbooks.Open(excelFullFileName);

fprintf('Formatting right borders of cells in Excel via ActiveX commands...\n');
% Format the right border of columns 1, 2, and 3 from row 1 to row3.
%Excel_utils.FormatRightBorder(sheetReference, columnNumbers, startingRow, endingRow)
Excel_utils.FormatRightBorder(Excel.ActiveSheet, [1, 2, 3], 1, 3);

% Format the bottom border of rows 1, 2, and 3 from column 1 to column 3.  Use a thick line.
fprintf('Formatting bottom borders of cells in Excel via ActiveX commands...\n');
%FormatBottomBorder(sheetReference, rowNumbers, startingCol, endingCol, weight)
Excel_utils.FormatBottomBorder(Excel.ActiveSheet, [1, 2, 3], 1, 3, 4);
Excel_utils.DeleteEmptyExcelSheets(Excel); % Delete any sheets (like the default "Sheet1" that don't have any cells filled on them.
% Auto size all columns.
Excel_utils.AutoSizeAllSheets(Excel);

% Save the workbook.
fprintf('Saving workbook %s in Excel via ActiveX commands...\n', excelFullFileName);
Excel.ActiveWorkbook.Save;
% Shutdown Excel.
fprintf('Shutting down Excel ...\n');
Excel.Quit;
delete(Excel);
clear('Excel')
fprintf('Done Running %s.m ...\n', mfilename);

[NIKHIL]

Hi Image Analyst, thank you for help. Now this is working really good but can we use the excel_utils in GUIDE also.

I have tried same code in standlone vs GUIDE, but in GUI i am having same issue as earlier (No outline/borders) for table data or range specified. For standalone script working well.

Excel = actxserver('Excel.Application');
excelFullFileName = fullfile(pwd, 'Test_data.xls');
excelWorkbook = Excel.Workbooks.Open(excelFullFileName);
n = Excel_utils.GetNumberOfExcelSheets(Excel);
Workbook = Excel.ActiveWorkbook;
Worksheets = Workbook.sheets;
nCols = Excel.ActiveSheet.UsedRange.Columns.Count
nRows = Excel.ActiveSheet.UsedRange.Rows.Count
Excel_utils.FormatRightBorder(Excel.ActiveSheet, [1:nCols] , 1, nRows);
Excel_utils.FormatBottomBorder(Excel.ActiveSheet, [1:nRows], 1, nCols, 2);
Excel_utils.CenterCellsAndAutoSizeColumns(Excel);
Excel_utils.AutoSizeColumns(Excel);
Excel.ActiveWorkbook.Save;
Excel.Quit;
Excel.delete;
clear Excel;

[Image Analyst]

It should work in GUIDE as well. It opens up Excel. Look in both Excel and the command window as you step through and see if there is anything unusual or if the things get formatted step-by-step as they should.

[dpb]

IA, I had lost your utilities class so came searching again as I knew it was here somewhere...thanks for posting these, they are extremely helpful to not have to reinvent the wheel and not get so terribly bogged down in the Excel object model doc hunting for the proverbial needle.

"And one to let you put a formula into a cell."

BTW, you can use the 'UseExcel','true' named parameter with the writeXXX series and writing a formula as text just as would enter it into the cell interactively will interpret the text as the formula. Works like a champ for automating building templates.

[dpb]

One other "gotcha'!" I had forgotten about -- in the examples you use the sequence

Excel = actxserver('Excel.Application');
excelWorkbook = Excel.Workbooks.Open(excelFullFileName);
Excel_utils.{whichever method used}
Excel.ActiveWorkbook.Save;
Excel.Quit;
delete(Excel);
clear('Excel')

If the method chosen is one that alters anything in the workbook, then the above sequence will leave the workbook locked for editing when try to open in Excel. The sequence is missing

Excel.ActiveWorkbook.Close(false);

to close the workbook cleanly (the 'false' argument prevents the popup window from showing, forcing the closure without user intervention.

[Image Analyst]

Thanks dpb. I have not noticed that -- you'd think shutting down Excel would automatically close the workbook. However manually and explicitly closing the workbook is probably a good idea and it certainly can't hurt so I'll do it. I'll make the change in my code. New code attached.

[dpb]

That's interesting you've not noticed the problem of the locked-for-editing -- wonder how/why could be the difference. I always end up with a zombie Excel process because .Quit just shuts down the COM engine, it doesn't close the Excel session itself. If I forget, I end up having to use the Task Manager to kill that Excel process.

Ah, well, it's a MS product--bizarre behavior is the norm...

[Image Analyst]

Well I have noticed zombie Excels running but I never knew why they existed. I guess I assumed they were due to me stopping debugging and not shutting it down properly. But I never noticed that when I tried to open the file from the OS I had it say it was locked. Maybe I just never tried. And when I try to recreate/overwrite the file in MATLAB I just delete the file beforehand if it exists and it never seems to complain about that.

[dpb]

Certainly a crash debugging COM or just exiting debugging is a good way to end up with zombie Excel processes, indeed.

This one isn't quite the same, but it has been orphaned by deleting the handle before the file was closed so there's no way to get to it by code any longer. I've not tried to see if one could find it and reattach; probably there is some manner in which can.

My use case is somewhat different than you describe; these workbooks are incrementally updated such that previous data are preserved; only new is being added/revised. Hence, deleting the file first isn't an option.

I think the difference in the case of deleting is, however, that the OS is doing that with the file system and it doesn't really care; it isn't marked as hidden or read-only by OS; only in how Excel opens it. When the file is opened, there is a hidden file of the same name created with the prefix "~$" that is used in the recovery process. When the file is normally closed, this file is deleted.

In order to prevent crashing during updates, I check for the existence of that file and nag the user to close the target output file before proceeding to try to write the updates. Otherwise, it'll crash when it gets there.

ADDENDUM: Deleting the file in this case still doesn't affect the zombie process; it's still there until killed by either shutting down Excel completely or using Task Manager. Just noticed this after a boo-boo in adding some extra niceties in the utilities class and crashed it owing to a typo outside a try...catch...end block.

[dpb]

I munged on your version some and generalized the border-setting routine --

function SetBorder(sheetReference, ranges, border, weight, style)
% borders is a collections of all cell borders. Must set each.
%
% my_border = get(borders, 'Item', XlBordersIndex);
% set(my_border, 'ColorIndex', 3);
% set(my_border, 'LineStyle', 9);
%
% Excel XlBordersIndex Enumeration
%  1 -                    all vertical but not rightmost
%  2 -                    all vertical but not leftmost
%  3 -                    all horizontal but not bottommost
%  4 -                    all horizontal but not topmost
%  5 - xlDiagonalDown     UL diagonal down to LR
%  6 - xlDiagonalUp       LL diagonal up to UR
%  7 - xlEdgeLeft         Border at the left edge of the range.leftmost only
%  8 - xlEdgeTop          Border at the top of the range.
%  9 - xlEdgeBottom       Border at the bottom of the range.
% 10 - xlEdgeRight        Border at the right edge of the range.
% 11 - xlInsideVertical 	Vertical borders for all the cells in the range except borders on the outside of the range.
% 12 - xlInsideHorizontal Horizontal borders for all cells in the range except borders on the outside of the range.
%
% Excel XlBorderWeight Enumeration
% Specifies the weight of the border around a range.
% Name          Value     Description
% xlHairline    1         Hairline (thinnest border).
% xlMedium      -4138     Medium.
% xlThick       4         Thick (widest border).
% xlThin        2         Thin.
% 
% Excel XlLineStyle Enumeration
% Specifies the line style for the border.
% Name            Value 	Description
% 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 ischar(ranges), ranges=cellstr(ranges); end
  try
    for i=1:numel(ranges)
      theCell=sheetReference.Range(ranges{i});
      borders=get(theCell, 'Borders');
      thisBorder=get(borders, 'Item', border);
      set(thisBorder,'LineStyle', style,'Weight',weight);
    end
  catch ME
    errorMessage = sprintf('Error in function FormatLeftBorder.\n\nError Message:\n%s', ME.message);
    warning(errorMessage);
  end
end

 function thisSheet=sheetReference(Excel, sheetNameOrNumber)
 % Usage:
 %   thisSheetObj=sheetReference(ExcelCOM, sheet)
 %
 % returns the sheet reference object for input sheet.
 % Can pass sheet number sheet (1,2,3,etc.) or name ('Results').
    
try
  worksheets = Excel.sheets;
  numSheets = worksheets.Count;
  if isnumeric(sheetNameOrNumber)
    thisSheet = get(worksheets, 'Item', sheetNameOrNumber);
  else
    for currentSheet=1:numSheets
      thisSheet = get(worksheets, 'Item', currentSheet);
      thisSheetName = strtrim(thisSheet.Name);
      if strcmpi(thisSheetName, sheetNameOrNumber), break; end
    end
  end
catch ME
  errorMessage = sprintf('Error in function ActivateSheet.\n\nError Message:\n%s', ME.message);
  warning(errorMessage);
end
return;
end

Finding/copying the list of the various Excel XL enumeration properties constants took the longest time -- the inability in MATLAB to have include files and enumerations is a real weak link and lacking the VBA intellisense when trying to write COM code is also a killer to productivity (not that it's TMW's job to build it into MATLAB, but if one is forced into using Excel, it's just SO painful without. I didn't think to try it yesterday, probably the more effective way is to write in VBA and then translate/move over unless one is truly fluent with the Excel object model.

I've never written a class so a la Sgt Schultz I "know nothink"! about the syntax/construction thereof, but thought about trying to rearrange to have an initializing routine and then have the Excel object be global so don't have to pass it around all the time, but decided expediency was the thing needed at the moment.

I wish the writeXXX class of routines had the facility to have callbacks or somesuch so one could get access to the COM handle and execute such niceties while in the process of writing the file instead of then having to reopen and patch it after the fact.

The old xlswrite was mostly m-code so one could build specialized versions of it that are using to write piecemeal into files that brought the new versions to a painful halt with the overhead.

[dpb]

Above I said "- the inability in MATLAB to have include files and enumerations is a real weak link..."

I did a search and discovered there is an enumeration that can be included in a classdef module -- it isn't as convenient for this purpose as being able to just include, but one can make it work -- albeit with some learning pains that took a while to figure out what the cryptic

>> XlBordersIndex
Error using XlBordersIndex
In definition of enumeration member 'xlDiagonalDown' in class 'XlBordersIndex':
Too many input arguments. 
>> 

means. Finally figured out that since the MATLAB enumeration is not just a character substitution a la the C-style implementation it takes using an inheritance from a type class to be able to assign the values.

With that, a start along the path could look something like:

classdef XlBordersIndex < int32
  enumeration
    xlDiagonalDown      (5)
    xlDiagonalUp        (6)
    xlEdgeLeft          (7)
    xlEdgeTop           (8)
    xlEdgeBottom        (9)
    xlEdgeRight        (10)
    xlInsideVertical   (11)
    xlInsideHorizontal (12)
  end
end

I didn't try to make up names for the ones missing in the MS doc list I found. While it's more wordy by having to refer to the class name to dereference, one at least can get the intellisense engine to bring up the members so don't have to try to remember numbers or guess spelling.

That's some progress although it would be agoodthing™ if there were a full package somewhere already built. The process could be automated if one had a file of the definitions, but that's more time/effort than have at the moment...

With that

>> XlBordersIndex.xlDiagonalDown
ans = 
  XlBordersIndex enumeration
    xlDiagonalDown
>> double(XlBordersIndex.xlDiagonalDown)
ans =
     5
>> 

works....

[dpb]

So, here are the other two I needed (so far)...

classdef XlBorderWeight < int32
  enumeration
    xlHairline   (    1)    % Hairline (thinnest border).
    xlMedium     (-4138)    % Medium.
    xlThick      (    4)    % Thick (widest border).
    xlThin       (    2)    % Thin.
  end
end

classdef XlLineStyle < int32
  enumeration
    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.
  end
end

Put each in its own m-file and enjoy!

[dpb]

ADDENDUM/ERRATUM:

NB: Passing an enumeration to get, set with at least certain objects as the above with border index and weight fails as apparently MATLAB passes something more than the integer value of the enumeration.

Had to cast the enumeration variable to a native numeric type to be able to use it -- see the Q? I posted that illustrates problem and the club to get around it...

https://www.mathworks.com/matlabcentral/answers/844695-enumeration-fails-with-get-set-arguments?s_tid=srchtitle

[dpb]

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]

Hi @Image Analyst and @dpb,

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.