Matlab to excel and back! Q about using ActiveX
Show older comments
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?
Accepted Answer
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.
5 Comments
Stephen
on 2 Jul 2020
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
on 2 Jul 2020
Fangjun Jiang
on 2 Jul 2020
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.
"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).
More Answers (2)
Fangjun Jiang
on 2 Jul 2020
See if you have this file in your MATLAB release
edit ChangeRowHeightInRangeOfSpreadsheetCellsExample.m
6 Comments
Stephen
on 2 Jul 2020
Stephen
on 2 Jul 2020
Stephen
on 2 Jul 2020
Fangjun Jiang
on 2 Jul 2020
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
%% 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
on 2 Jul 2020
Edited: Fangjun Jiang
on 2 Jul 2020
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.
>> 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.
1 Comment
dpb
on 3 Jul 2020
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.
Categories
Find more on Spreadsheets in Help Center and File Exchange
