MATLAB Answers

Brad
0

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

Asked by Brad
on 15 May 2015
Latest activity Commented on by Image Analyst
on 13 Sep 2019 at 22:49
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?

  0 Comments

Sign in to comment.

1 Answer

Answer by Image Analyst
on 15 May 2015
 Accepted Answer

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

  5 Comments

Hi, have you ever published the class notes that you reference in this chain? Thanks.
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!).

Sign in to comment.