Matlab to excel and back! Q about using ActiveX

74 次查看（过去 30 天）
Stephen
Stephen 2020-7-2
编辑： dpb 2020-7-3
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?

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

采纳的回答

dpb
dpb 2020-7-2
编辑：dpb 2020-7-3
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 个评论
显示 3更早的评论
Fangjun Jiang
Fangjun Jiang 2020-7-2
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
dpb 2020-7-2
编辑：dpb 2020-7-2
"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).

请先登录，再进行评论。

更多回答（2 个）

Fangjun Jiang
Fangjun Jiang 2020-7-2
See if you have this file in your MATLAB release
edit ChangeRowHeightInRangeOfSpreadsheetCellsExample.m
  6 个评论
显示 4更早的评论
Fangjun Jiang
Fangjun Jiang 2020-7-2
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
Fangjun Jiang 2020-7-2
编辑：Fangjun Jiang 2020-7-2
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.

请先登录，再进行评论。

dpb
dpb 2020-7-2
编辑：dpb 2020-7-3
>> 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 个评论
dpb
dpb 2020-7-3
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.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by