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

Question

Brad 2015-5-15

1
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/216917-can-matlab-pre-format-individual-cells-when-writing-data-to-an-excel-spreadsheet

编辑： 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 个评论
显示 1更早的评论隐藏 1更早的评论

Image Analyst 2023-2-20

编辑：Image Analyst 2023-2-20

Excel_utils.m

@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 2023-2-20

Thank you!

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Image Analyst 2015-5-15

4
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/216917-can-matlab-pre-format-individual-cells-when-writing-data-to-an-excel-spreadsheet#answer_179245

在 MATLAB Online 中打开

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 个评论
显示 5更早的评论隐藏 5更早的评论

Image Analyst 2019-9-13

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 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 2020-12-4

Excel_utils.m

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 2020-1-31

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/216917-can-matlab-pre-format-individual-cells-when-writing-data-to-an-excel-spreadsheet#answer_413150

编辑：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 2020-1-31

Excel_utils.m

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 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.

请先登录，再进行评论。