Clear Filters
Clear Filters

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

76 views (last 30 days)
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?
  3 Comments

Sign in to comment.

Accepted Answer

Image Analyst
Image Analyst on 15 May 2015
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
  7 Comments
Louis
Louis on 4 Dec 2020
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
Image Analyst on 4 Dec 2020
I don't have a merging method, but I do have a FormatDecimalPlaces() method and it's in the attached static class.

Sign in to comment.

More Answers (1)

Bruce Stirling
Bruce Stirling on 31 Jan 2020
Edited: Bruce Stirling on 31 Jan 2020
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
  2 Comments
Image Analyst
Image Analyst on 31 Jan 2020
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
Bruce Stirling on 3 Feb 2020
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.

Sign in to comment.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!