Matlab to Excel via Activex - How to iteratively add Matlab variables?

Question

10B 2015-9-18

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/243843-matlab-to-excel-via-activex-how-to-iteratively-add-matlab-variables

评论： 10B 2015-9-28

Hello Community,

I need some help with 'Housekeeping' following the running of a Matlab script which produces a series of variables that I need to store externally (Excel). The script will be run several times, so the variable contents will change each time the script is run. What I want to do is sequentially add new data to an excel workbook each time the script runs. From elsewhere on the forum, I have some ActiveX code that opens the workbook and adds the 'titles' I want to the w.book:

% First open an Excel Server
Excel = actxserver('Excel.Application');
set(Excel, 'Visible', 1);
% Insert a new workbook
Workbooks = Excel.Workbooks;
Workbook = invoke(Workbooks, 'Add');
% Make the first sheet active
Sheets = Excel.ActiveWorkBook.Sheets;
sheet1 = get(Sheets, 'Item', 1);
invoke(sheet1, 'Activate');
% Get a handle to the active sheet
Activesheet = Excel.Activesheet;
%set(ActivesheetRange, 'Value', A);
ActivesheetRange = get(Activesheet,'Range','A1:G1');
set(ActivesheetRange, 'Value', xlsxcol); %xlsxcol is a variable from the workspace containing 7 'titles'

Now the next step is to add the data from the other variables, beneath the titles in the appropriate place. eg:

title1...title2...etc.

var1......var2...etc.

Then on the next run of the script, I would want new variables to be written beneath the previous variables, eg:

title1...title2...etc.

var1......var2...etc.

I feel that by having to use this:

ActivesheetRange = get(Activesheet,'Range','A1:G1');

I am predetermining the range that I put the data in which is problematic for repeats of say 300 iterations! I know I need a 'for' loop - but am uncertain how to do this with ActiveX as well.

So could you help with the next step that I am missing here?

Thanks for your time.

10B.

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

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

Answer 1

Kirby Fears 2015-9-18

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/243843-matlab-to-excel-via-activex-how-to-iteratively-add-matlab-variables#answer_192900

Hi 10B,

If you want to output 300 separate tables, stacking them vertically in a single excel sheet will probably be hard to use later.

You could write each table to a separate excel sheet by using the optional sheet argument of xlswrite.

Alternatively you can write each table to a separate csv file using writetable.

Writing to csv files will be much faster, the files will take up less space on your disk, and they are generally easier to use later.

4 个评论
显示 2更早的评论隐藏 2更早的评论

10B 2015-9-21

编辑：10B 2015-9-21

Hello Kirkby,

Thanks for your comment on my question. I appreciate your view that writing to excel may be slower then to CSV, so I can use that approach as CSV is more portable as well - good idea.

However, I feel that for what I am trying to do, that making separate CSV's in separate excel sheets (and potentially different files which happens at another step in my workflow) would actually cause me greater difficulties later on. I am not actually trying to output 300 tables, rather, the script that runs before this housekeeping section, may be run say 300 times, hence there will be additional lines of data to add to the spreadsheet.

I need all the output variables in the one sheet so I can run analyses on them, with each column in the sheet becoming a new variable in itself. Hence the need to write it as I initially described.

If you could help a little further, or perhaps I have prompted an idea for another solution, then please feel free to respond again.

Kind regards,

10B.

Kirby Fears 2015-9-21

编辑：Kirby Fears 2015-9-21

在 MATLAB Online 中打开

10B,

You can iteratively manipulate the Excel range to step down the worksheet. Is the number of columns fixed for each table? How about the number of rows?

Below is a simple example assuming each table is the same size. If the columns or rows are not fixed, it can be generalized easily enough.

% Initializing fixed values
ntab=300;
startcol='A';
endcol='G';
startrow=2;
nrows=10;
xlsxcol=arrayfun(@(d)['col' num2str(d)],1:7,'UniformOutput',false);
% First open an Excel Server
Excel = actxserver('Excel.Application');
set(Excel, 'Visible', 1);
% Insert a new workbook
Workbooks = Excel.Workbooks;
Workbook = invoke(Workbooks, 'Add');
% Make the first sheet active
Sheets = Excel.ActiveWorkBook.Sheets;
sheet1 = get(Sheets, 'Item', 1);
invoke(sheet1, 'Activate');
% Get a handle to the active sheet
Activesheet = Excel.Activesheet;
% Write headers
ActivesheetRange = get(Activesheet,'Range','A1:G1');
set(ActivesheetRange, 'Value', xlsxcol);
% After opening the workbook and writing headers:
for j=1:ntab,
    % Calculate table J before printing
    tableout=j*ones(nrows,7); % This is only a sample
    % Write table to active Excel sheet
    excelrange=[startcol num2str((j-1)*nrows+startrow) ':' ...
        endcol num2str(j*nrows+startrow-1)];
    ActivesheetRange = get(Activesheet,'Range',excelrange);
    set(ActivesheetRange, 'Value', tableout);
end
% Save and close workbook
% Close Excel

Hope this helps.

10B 2015-9-28

Hello Kirkby,

Sorry, for some reason I did not get a notification for this answer. I will give this a run and get back to you shortly.

Regards,

10B.

请先登录，再进行评论。

Matlab to Excel via Activex - How to iteratively add Matlab variables?

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

4 个评论
显示 2更早的评论隐藏 2更早的评论

更多回答（0 个）

另请参阅

类别

标签

产品

Community Treasure Hunt

Matlab to Excel via Activex - How to iteratively add Matlab variables?

0 个评论 显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

4 个评论 显示 2更早的评论隐藏 2更早的评论

更多回答（0 个）

另请参阅

类别

标签

产品

Community Treasure Hunt

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

4 个评论
显示 2更早的评论隐藏 2更早的评论