フィルターのクリア

Update an Excel file after each Matlab calculation

14 ビュー (過去 30 日間)
Timo
Timo 2024 年 4 月 25 日
コメント済み: Stephen23 2024 年 4 月 25 日
Dear everyone,
I need to communicate between Matlab file and a very complicated excel file with several sheets which are connected to each other. All Excel sheets will be calculated after the matlab result was filled in one certain cell in Excel. Does anybody know a code line in Matlab how to refresh the whole excel file automatically from Matlab (last code line)? Excel is not able to this by its own.
Thank you very much!
add_in_path = 'xxx'; % Ordnerpfad
add_in_filename = 'REFPROP.xla'; % Dateiname
%---------- Excel connection
file_path = 'xxx'; % Ordnerpfad
filename = 'xxx'; % Dateiname
spreadsheet_name = 'xxx'; % Name des Tabellenblatts
% Import area
spreadsheet_range = 'xxx'; % Zellenbereich
%% Start
%--- COM-Server start
[Excel, File] = Excel_interaction_Start_COM_Server(add_in_path, add_in_filename, file_path, filename);
%% Matlab calculation ongoing
%Write the result in the Excel file
xlswrite1(File,rawData_mod,spreadsheet_name,spreadsheet_range_export);
%% Excel refresh ?????????
  3 件のコメント
Timo
Timo 2024 年 4 月 25 日
編集済み: Timo 2024 年 4 月 25 日
Thanks Sir,
unfortunately it is necessary to update it after each calculation.
What is the advantage to use the readmatrix command instead of xlswrite?
Stephen23
Stephen23 2024 年 4 月 25 日
編集済み: Stephen23 2024 年 4 月 25 日
WRITEMATRIX, WRITECELL, WRITETABLE have more options, better cross-platform support, and are generally more efficient.
XLSWRITE, DLMWRITE, CSVWRITE, etc are not recommended, as the documentation clearly states:

サインインしてコメントする。

回答 (1 件)

Stephen23
Stephen23 2024 年 4 月 25 日
編集済み: Stephen23 2024 年 4 月 25 日
You can use a COM server to trigger Excel to recalculate the worksheets:
A basic outline:
  2 件のコメント
Timo
Timo 2024 年 4 月 25 日
Hi Stephan23,
maybe it is more clear if I provide some more code below. I am also using an external REFPROP Excel AddIn to calculate physical substance data. The Function we are using is: Excel_interaction_Start_COM_Server. The questions are afterwards.
Thank you so much!
disp('Starting COM server to access Excel file...');
tic
Excel = actxserver('Excel.Application');
%Excel.Workbooks.Open('C:\YourAddInFolder\AddInNameWithExtension');
Excel.Workbooks.Open(fullfile(add_in_path,add_in_filename));
%Excel.Workbooks.Item('AddInNameWithExtension').RunAutoMacros(1);
Excel.Workbooks.Item(add_in_filename).RunAutoMacros(1);
%File='C:\YourFileFolder\FileName';
File=fullfile(file_path,filename);
if ~exist(File,'file')
ExcelWorkbook = Excel.Workbooks.Add;
ExcelWorkbook.SaveAs(File,1);
ExcelWorkbook.Close(false);
end
Excel.Workbooks.Open(File);
toc
disp('Done.');
Afterwards you will find the code where we need the Excel Refresh of the full workbook. To calculate the whole workbooks Excel takes aroung 15 seconds. Do you know a function or code line to do this in matlab?
clear
close all
clc
diary Diary_Matlab_Excel
%% User Input
% Angaben zum relevanten Excel Add-in
add_in_path = 'C:\XXX'; % Ordnerpfad
add_in_filename = 'REFPROP.xla'; % Dateiname
%---------- Reale Daten
file_path = 'XXX'; % Ordnerpfad
filename = 'XXXm'; % Dateiname
filename_complete = 'XXX';
spreadsheet_name = 'Kompressor'; % Name des Tabellenblatts
% Import-Bereich spezifizieren
spreadsheet_range = 'A1:N138'; % Zellenbereich, in dem die relevanten Parameter stehen für die Parameterstudie, sprich die zu variierenden Werte und die Ergebnisse (in typischer Excel-Notation, z.B. 'A1:B2')
pause_time = 15;
%% Start
%--- COM-Server starten, der die Interaktion zw. Matlab und Excel ermöglicht
[Excel, File] = Excel_interaction_Start_COM_Server(add_in_path, add_in_filename, file_path, filename);
%% We need at this place an full Excel Workbook Refresh and a save of the excel file (also below, its like a chain)
%----- Einlesen der Daten aus Excel
disp(' ');
disp('Reading excel...');
tic
[~, ~, rawData] = xlsread1(File,spreadsheet_name,spreadsheet_range);
toc
disp('Done.');
%External Matlab Skript to calculate
%The results of the external Matlab script calculation needs to be
%put back into the excel file
rawData_mod = rawData(112,4);
rawData_mod{1,1} = output.shell_T_out;
spreadsheet_range_export = 'D112';
disp('Writing excel...');
xlswrite1(filename_complete,rawData_mod,spreadsheet_name,spreadsheet_range_export);
disp('Done.');
disp('Saving Excel ActiveWorkbook...');
excelObject.Workbooks.Item('XXX').Save;
disp('Done.');
disp_output = sprintf('Pause (%.1i s)...',pause_time);
disp(disp_output);
pause(pause_time);
disp('Done.');
%% We need at this place an full Excel Workbook Refresh and a save of the excel file (also below, its like a chain)
%% Similar Calculation again
clear rawData;
clear rawData_mod;
clear output;
clear input;
%----- Einlesen der Daten aus Excel
disp(' ');
disp('Reading excel...');
tic
[~, ~, rawData] = xlsread1(File,spreadsheet_name,spreadsheet_range);
toc
disp('Done.');
%External Matlab Skript to calculate
%The results of the external Matlab script calculation needs to be
%put back into the excel file
rawData_mod = rawData(112,5);
rawData_mod{1,1} = output.shell_T_out;
spreadsheet_range_export = 'E112';
disp('Writing excel...');
xlswrite1(filename_complete,rawData_mod,spreadsheet_name,spreadsheet_range_export);
disp('Done.');
disp('Saving Excel ActiveWorkbook...');
excelObject.Workbooks.Item('XXX').Save;
disp('Done.');
disp_output = sprintf('Pause (%.1i s)...',pause_time);
disp(disp_output);
pause(pause_time);
disp('Done.');
%% Some more loops to come afterwards within the same scheme
Stephen23
Stephen23 2024 年 4 月 25 日
"Do you know a function or code line to do this in matlab?"
Yes: the ACTXSERVER method RUN, that you use to run a macro that calls the worksheet method CALCULATE. Exactly as per my answer.

サインインしてコメントする。

カテゴリ

Help Center および File ExchangeSpreadsheets についてさらに検索

Community Treasure Hunt

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

Start Hunting!

Translated by