Can MATLAB pre-format individual cells when writing data to an EXCEL spreadsheet?

I've been researching ways to manipulate data written to EXCEL spreadsheets using MATLAB. I came across the following example code and began manipulating individual lines of code & observing the outcome.

%%Clear out the environement
clear all;
close all;
clc;

%%OPEN EXCEL APPLICATION
Excel = actxserver('Excel.Application');
% Show the Excel window
set(Excel, 'Visible', 1);

%%INSERT NEW WORKBOOK
W = Excel.Workbooks.Add;

%%WORKBOOKS CONTAIN WORKSHEETS
Sheets = Excel.ActiveWorkBook.Sheets;
Sheets.Add( [], Sheets.Item(3) );
 %%ADD DATA AND CHARTS
 j=2;

%%Rename
Sheets.Item(j).Name = ['s' int2str(j)];

%%Make it "Active"
Sheets.Item(j).Activate;
Activesheet = Excel.Activesheet;
Activesheet.Columns.Item('A').NumberFormat='$#,##0.00'; 
Activesheet.Columns.Item('A').ColumnWidth='20'; 
Activesheet.Range('A1').EntireColumn.HorizontalAlignment = 3; 
Activesheet.Range('A1').EntireColumn.VerticalAlignment = 2; 
%%Insert (random) data
A = floor(256*rand(10,1));
ActivesheetRange = get(Activesheet,'Range','A1:A10');
set(ActivesheetRange, 'Value', A);

One thing I can't seem to figure out is how to manipulate a single cell within a column of data. For example, I would like to align the contents in cell A1 to the left, leaving all other cells as-is.

Can this be done?

[Grunu]

Can you tell me where to find a proper documentation of the excel formatting functions you are using? All my knowledge about this is from forum entries.

e.g. Activesheet.Columns.Item('A').ColumnWidth='20';

[Image Analyst]

@Grunu, try these 2 links:

https://learn.microsoft.com/en-us/search/?terms=Excel%20Developer%20Reference

https://learn.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.range?redirectedfrom=MSDN&view=excel-pia#methods_

In addition, what I usually do is, in Excel, record a macro to do what I want/need, then open the macro and look at the VBA code it generated. Then implement the same methods in my MATLAB code.

[Grunu]

Thank you!

Answer 1

[Image Analyst]

Yes you can. See this method from my Excel class:

%-------------------------------------------------------------------------------------------------------
  % Selects all cells in the current worksheet in the specified range.
  % Horizontally aligns all the specified cell range.
  % horizAlign = 1 for left alignment.
  % horizAlign = 3 for center alignment.
  % horizAlign = 4 for right alignment.
  % Leaves with cell A1 selected.
  function AlignCells(Excel, cellReference, horizAlign, autoFit)
    try
      % Select the range
      Excel.Range(cellReference).Select;
      % Align the cell contents.
      Excel.Selection.HorizontalAlignment = horizAlign;
      Excel.Selection.VerticalAlignment = 2;
      if autoFit
        % Auto fit all the columns.
        Excel.Cells.EntireColumn.AutoFit;
      end
      % Put "cursor" or active cell at A1, the upper left cell.
      Excel.Range('A1').Select;
    catch ME
      errorMessage = sprintf('Error in function AlignCells.\nError Message:\n%s', ME.message);
      fprintf('%s\n', errorMessage);
      WarnUser(errorMessage);
    end % from AlignCells
    return;
  end

[Brad]

Image Analyst, thanks for taking a look at this. This is my first attempt at formatting data in EXCEL. It appears a MATLAB user has quite a bit of control on how data is written to EXCEL files. I'm a fan!!

[Image Analyst]

Thanks for accepting. I have a whole class that does tons of stuff like underlining, setting decimal places, etc. If you need anything else, just ask. It's a little inconsistent and has some other things about it that aren't quite up to my high standards to publish the whole thing right now, but if you have something specific, just ask.

By the way, this is really ActiveX functionality that can be called from most programming languages, so it's not really MATLAB-specific. MATLAB just gives us a way to make ActiveX method calls.

[Jason Elias]

Hi, have you ever published the class notes that you reference in this chain? Thanks.

[Michael Baker]

Nice code comment !

[Image Analyst]

No I haven't posted the entire class to the File Exchange.

There are a bunch of functions in there that use ActiveX to do all kinds of fun stuff like putting up borders, formatting number of decimal points, bolding text or setting font size, copying sheets, deleting rows, finding first unused cell in a column or row of a worksheet, etc.

Excel_utils

Static methods:

ActivateSheet DeleteEmptyExcelSheets FormatDecimalPlaces LeftAlignSheet

AlignCells DeleteExcelSheets FormatLeftBorder WrapText

AutoSizeAllSheets DuplicateExcelSheet FormatRightBorder

CenterCellsAndAutoSizeColumns FormatBottomBorder GetNumberOfExcelSheets

CenterCellsAndAutoSizeSpecificColumns FormatCellColor GoToNextRowInColumn

ClearCells FormatCellFont InsertComments

I just haven't made it consistent enough yet in the inputs, and I'm thinking I might want to do that first. For example some methods want the whole Excel object as an input argument, and others want the object of just the worksheets, so I'd like to make that consistent (some day!).

[Louis]

Hi, I was wondering if you could share some functions handling following fomatting in excel:

• number of decimal points
• merging cells (then aligning contents within the merged cells)

Thank you in advance,

[Image Analyst]

I don't have a merging method, but I do have a FormatDecimalPlaces() method and it's in the attached static class.

Answer 2

[Bruce Stirling]

I'm slightly late to this thread but is there a way to copy a "template" worksheet and paste just the formats using ActiveX? Or better yet, can you write a table to and Excel worksheet and not reset the existing Excel formatting?

Thanks,

Bruce

[Image Analyst]

What I do is to have a template workbook, and when it comes time to write your data workbook use copyfile() to make a copy of the template with the new name you want. Then just write to it.

With xlswrite() you can blast data over existing cells and they retain the original formatting they have in Excel. Since xlswrite() is deprecated, I decided to try their replacement writecell() and writematrix(). The good news is that they're MUCH faster. However they seem to undo all the formatting in my Excel cells. I'm going to call tech support on this next week.

I haven't tried writetable() to check whether formatting is retained or not.

Of course you can do anything you want to ActiveX in Windows. I attach a static class that makes that easier.

[Bruce Stirling]

Thanks for the static class code! I will look to implement portions this week. I thought I'd used the xlswrite() and saw that it changed the formats but it may have been some other code, xlswrite1(). I like the idea of using writecell() and writematrix(). Hopefully tech support can fix that or better yet, give us an option to overwrite the formats or not. Those are likely better than the xlswrite1() code. Until tech support gives feedback I'll do that along with the ActiveX to set font colors, etc.