Matlab to excel and back! Q about using ActiveX

Can anyone direct me to a website or a manual or a tutorial that will help explain how to use ActiveX to control how data are displayed in an excel file (e.g. number format, font size, etc)? And that shows simple examples. Yes, I've read many previous such quierries and the suggestions to read the MatLab documentation. But frankly, I find those exremely confusing and not helpful for someone at my relatively beginning stage. The documentation often uses jargon and unfamiliar terms.

I've generated a Table of data with a MatLab script and have written this to an excel file using xlswrite and writetable. That works fine!

Now, however, I want to be able to change the number formats and fonts in some of the excel rows.

Any ideas where best to learn how to manipulate excel files using MatLab and ActiveX?

Answer 1

[dpb]

I'm unaware of anybody having written the specific tutorial/book you're looking for re: using COM for Excel with MATLAB...don't disagree there's probably an audience for having the pieces distilled and the particular peculiarities wrt MATLAB in conjunction with.

As Fanguin Jiang notes, it's really the VBA syntax/reference that is the Rosetta stone -- "all" COM is is another way to execute the VBA methods without using VBA itself -- what gets lost is the convenience of VBA interpreter/compiler that understands its syntax and data types and the builtin tab completion in the VBA editor for argument lists, etc, so you have to do the translation from that description of the desired function to the allowable COM syntax making allowances for MATLAB variable types, etc, along the way.

I complained about some troubles I was having here <Answers/533148-yet-another-excel-com-problem> and Image Analyst was kind enough to post his Excel utilities class that is undoubtedly the most complete and well-documented example I've seen as well as probably having 80-90% of what you're after already done.

[Stephen]

Got it. I'll look at ImageAnalyst's Excel utilities class, too. But I can tell right away there will be problems for me to understand. I mean, what kind of normal person uses the verb "instantiate" as in "...instantiate the Excel variable...". And when you look up a definition for instantiate, you get

"Instantiation is the creation of a new instance of a class and is part of object-oriented programming, which is when an object is an instance of a class. ... If no constructor is defined by the programmer"

In short, is there nothing reasonable out there for reasonably intelligent, but not uber-matlab people?

[dpb]

Well, one has got to stop somewhere in writing eplainations; Walter R had a pretty nice compendium a couple days ago to somebody's request for detailed line-by-line code precis. The difficulty is one just never knows where that somewhere is.

It also means to write code, one has to start somewhere as well, or be willing to take the digressions when don't follow something. While I'm not an OO kinda' guy, having never written a classdef in my life, I'll admit that also by dint of being an old codger I've been around long enough that "instantiate" seems normal and reasonable use of English to describe what it does. Then again, 40+ years consulting will warp most anybody I suppose! :)

The problem as far as documentation of this particular area goes is that you're tying two vendors' products together and neither is exactly in league with the other but in particular, TMW knows it has to support MS capability to some extent simply by the numbers of people who use it.

But, as said earlier, it's not really their job to teach you VBA; nor is it Microsoft's to teach other vendor's products. They provide the toolset and the documentation of how to use it in their supported languages; translating between the two is another task entirely.

The problem in my view is that the Excel object model is so complex -- it simply is not something you can pick up in 15 minutes to solve what seems to be a simple task. From that standpoint I fully sympathize having been through the exercise I sorta' described in the linked Q?.

I again would suggest two(*) alternative approaches -- as IA says, if he wants to put data into a formatted spreadsheet, he will tend to use a spreadsheet template workbook that contains the specialty stuff and then just populate it with the data.

Alternatively, if his class module doesn't have something in it you think you simply must have, record a macro in Excel and use it to guide you as to the steps you need to duplicate in COM.

(*)Actually, I generally take the third alternative--just live with it and go on and solve the real problem. What it looks like is generally of little consequence. Then again, I don't do guis, either! :)

[Stephen]

OK, thanks. I get the mssg. This is too deep for me. I'll live with 8 decimal place numbers in xlswrite, etc., and later just change Excel manually.

bummer

[Fangjun Jiang]

Did you try my example above? It's just a few standard lines. You can change wsRange=ws.Range('A1:C100') for example to select a broad range.

[dpb]

"I'll live with 8 decimal place numbers in xlswrite, etc., and later just change Excel manually."

What's wrong with the template idea?

xlswrite stores full precision data of what's in the ML variable; it's only the display precision that is being affected, just as changing from, say, format short to format long at the ML command window. The data in memory are still the same.

Only some of the text formats like csvwrite and friends will truncate at fewer decimal digits.

But, besides FJ's example, there's FormatDecimalPlaces() in IA's class -- did you try it? Looks like you could do what you asked for with it (or modify it pretty easily to do something slightly different I'd guess).

Answer 2

[Fangjun Jiang]

See if you have this file in your MATLAB release

edit ChangeRowHeightInRangeOfSpreadsheetCellsExample.m

[Stephen]

I don't see it. I ran the above and only got a blank page.

I'm running R2017b (sept 14, 2017). I can update if necessary. Would that help?

[Fangjun Jiang]

Follow this

https://www.mathworks.com/help/matlab/matlab_external/change-row-height-in-range-of-spreadsheet-cells.html

[Stephen]

OK, thanks! It looks a bit complicated, but I'll have a go at it. I'm still in the market for a simpler tutorial if you or anyone else out there knows of one.

And from the number of ActiveX/Excel questions I see in this forum, others are also looking for a straightforward, simple tutorial!

[Stephen]

For example, deep in the website you suggested, I found the following method to specify the decimal places in a number, which is one of my main goals" (from https://docs.microsoft.com/en-us/office/vba/api/excel.modelformatdecimalnumber.decimalplaces):

ModelFormatDecimalNumber.DecimalPlaces property (Excel)

Specifies the number of decimal places after the dot. Read/write Long.

Syntax

expression.DecimalPlaces

expression A variable that represents a ModelFormatDecimalNumber object.

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

I don't have a CLUE what any of this means! This is typical of the "tutorials" I have found so far. It's like preaching to the choir!

[Fangjun Jiang]

That page lists all the other methods. The actual example is quite simple as below. I think it should work in R2017b.

The actual document you need in fact is the VB help reference in Microsoft Excel document. Or it can be found here

https://docs.microsoft.com/en-us/office/client-developer/excel/excel-home?redirectedfrom=MSDN

%% Change Row Height in Range of Spreadsheet Cells
% This example shows how to change the height of a row, defined by a |Range| 
% object, in a spreadsheet.
%% 
% The Excel(R) |Range| object is a property that takes input arguments.
% MATLAB(R) treats such a property as a method. Use the |methods| function 
% to get information about creating a |Range| object.
%% 
% Create a |Worksheet| object |ws| .
e = actxserver('Excel.Application');
wb = Add(e.Workbooks);
e.Visible = 1;
ws = e.Activesheet;
%% 
% Display the default height of all the rows in the worksheet.
ws.StandardHeight
%% 
% Display the function syntax for creating a |Range| object. Search the 
% displayed list for the |Range| entry: 
% |handle Range(handle,Variant,Variant(Optional))|
methods(ws,'-full')
%% 
% Create a |Range| object consisting of the first row.
wsRange = Range(ws,'A1');
%% 
% Increase the row height.
wsRange.RowHeight = 25;
%% 
% Open the worksheet, click in row 1, and notice the height.
%% 
% Close the workbook without saving.
wb.Saved = 1;
Close(e.Workbook)
%% 
% Close the application.
Quit(e)
delete(e)

%% 
% Copyright 2012 The MathWorks, Inc.

[Fangjun Jiang]

I don't know how you get there. I usually look for properties and methods.

For example, if you try to step through the code above, once the Excel "Book1" is created and visible, after the methods(ws,'-full') line, you can go to Excel file cell A1 and put in a number and manually format it to display different number of decimal places. Then in MATLAB Command Window

K>> wsRange = Range(ws,'A1')
wsRange =
	Interface.00020846_0000_0000_C000_000000000046
K>> wsRange.NumberFormat
ans =
    '0.0000'
K>> wsRange.NumberFormat='0.00'
wsRange =
	Interface.00020846_0000_0000_C000_000000000046

Go back to the Excel file to check, I have changed the decimal place from 4 to 2, programably.

Answer 3

[dpb]

>> Excel = matlab.io.internal.getExcelInstance
Excel =
	COM.Excel_Application
>> excelWorkbook = Excel.Workbooks.Open(fullfile(pwd,'test.xlsx'))
excelWorkbook =
	Interface.000208DA_0000_0000_C000_000000000046
>> Excel_utils.GetNumberOfExcelSheets(Excel)
ans =
     1
>> Excel_utils.FormatDecimalPlaces(Excel,5,'B:B')
>> Excel.ActiveWorkbook.Save;
>> delete(Excel); clear Excel

Thanks to IA, your job is basically done! (Of course, this is essentially the same code internally as the other example, just nicely packaged in a set of callable functions by Image Analyst. You could pull bits and pieces from the class package and string them together "for purpose" to accomplish specific tasks if don't want to use the general-purpose routines.

I just checked the result of the above; the displayed number of decimals in column B of the above workbook went from 14 to 5 when reopened after the Save.

There's another function FormatCellFont that looks like can do whatever with fonts...with this outline and some trial and error, looks to me like you should have no real problems accomplishing what you're looking for.

Starting from scratch, yeah, there's a learning curve but if you don't "just dive in!" you'll remain paralyzed waiting for the perfect answer before starting.

[dpb]

NB: The NumberFormat property has different syntax for the format strings than does the VBA Format function--one of the examples of where VBA has wrappers around the lower-level innards for convenience that don't have access to via COM.