フィルターのクリア

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

44 ビュー (過去 30 日間)
Brad
Brad 2015 年 5 月 15 日
編集済み: Image Analyst 2023 年 2 月 20 日
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.
Grunu
Grunu 2023 年 2 月 20 日
Thank you!

サインインしてコメントする。

採用された回答

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 Center および File ExchangeActiveX についてさらに検索

Community Treasure Hunt

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

Start Hunting!

Translated by