필터 지우기
필터 지우기

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

조회 수: 39 (최근 30일)
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
Image Analyst
Image Analyst 2023년 2월 20일
편집: Image Analyst 2023년 2월 20일
@Grunu, try these 2 links:
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.

댓글을 달려면 로그인하십시오.

채택된 답변

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

댓글을 달려면 로그인하십시오.

추가 답변 (1개)

Bruce Stirling
Bruce Stirling 2020년 1월 31일
편집: Bruce Stirling 2020년 1월 31일
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
Image Analyst
Image Analyst 2020년 1월 31일
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 2020년 2월 3일
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.

댓글을 달려면 로그인하십시오.

카테고리

Help CenterFile Exchange에서 ActiveX에 대해 자세히 알아보기

Community Treasure Hunt

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

Start Hunting!

Translated by