Matlab to excel and back! Q about using ActiveX
10 views (last 30 days)
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?
Edited: dpb on 3 Jul 2020
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.
More Answers (2)
Fangjun Jiang on 2 Jul 2020
See if you have this file in your MATLAB release
Edited: dpb on 3 Jul 2020
>> Excel = matlab.io.internal.getExcelInstance
>> excelWorkbook = Excel.Workbooks.Open(fullfile(pwd,'test.xlsx'))
>> 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.