Calling and computing within Excel from Matlab

2025 11 4
1 个回答
回答已采纳
更新时间：2025 11 13
18 次查看（30 天）

0 个投票

Let's say I have an excel worksheet where A1 = 3; A2 = 4; and A3 = A1+A2. I want to feed a new value of A1 and A2 in from Matlab of values 5 and 6. I then want Excel to make the calculations and then I want to pull out the A3 value which will be 11. How can I make code to do that?
I've tried to ask AI for some help and it has produced the following code.
excel = actxserver('Excel.Application');
excel.Visible = false;
workbook = excel.Workbooks.Open('C:\Book1.xlsx');
sheet = workbook.Worksheets.Item(1);
sheet.Cells(1, 1).Value = 'Hello, World!';
workbook.Save;
sheet.Calculate;
value = sheet.Cells(1, 1).Value;
disp(value);
workbook.Close(false);
excel.Quit;
delete(excel);
You would think that code would put in the value "Hello World" in the first cell in the worksheet and then save after calculating. Turns out, it puts "Hello World" in every cell within the worksheet.

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

 采纳的回答

Cris LaPierre
Cris LaPierre 2025-11-5

0 个投票

Something like this works for me
% Specify file name
path = cd;
name = "Book1.xlsx";
file = fullfile(path,name); % This must be full path name
% Open Excel Automation server
Excel = actxserver('Excel.Application');
Workbooks = Excel.Workbooks.Open(file);
excelWB_sheet_1=Workbooks.Sheets.Item(1); %Get first sheet
% Change values of cells A1 and A2
excelWB_sheet_1.Range('A1').Value = 5;
excelWB_sheet_1.Range('A2').Value = 10;
Excel.Calculate; % Force Excel to compute equations
% Extract value from cell A3 and save to a MATLAB variable
valueA3 = excelWB_sheet_1.Range('A3').Value
% Close the workbook and quit Excel application
Workbooks.Close(false); % false to not save changes
Excel.Quit;
delete(Excel); % Release the COM server

8 个评论
显示 6更早的评论 隐藏 6更早的评论

Matt
Matt 2025-11-5
移动：Walter Roberson 2025-11-5
That did the trick. Thanks so much!
Matt
Matt 2025-11-6
The Workbooks.Close(false) errors out for some reason in my script. I can't figure it out. It doesn't matter much, but does anyone know why it throws an error?
tic
data_csv = readtable('C:\Users\matt\Test3\diagnostics.csv');
toc;
% Create an Excel object
path = 'C:\Users\matt\';
name = "Fusing Algorithm.xlsx";
excelFilePath = fullfile(path,name); % This must be full path name
ExcelObj = actxserver('Excel.Application');
WorkBooks = ExcelObj.Workbooks.Open(excelFilePath);
% Set visibility to false (optional)
excelWB_sheet_1=WorkBooks.Sheets.Item(1); %Get first sheet
total_rows = height(data_csv);%*0+10;
clear Outputs
tic
Outputs{total_rows,3} = 0;
found_something = 1;
for j = 1:total_rows
% rand_row = ceil(height(data_csv)*rand())*0+j; %Randomly sample results
rand_row = j;
for i = 1:8
char_data{i} = string(data_csv{rand_row,2+2*i});
conf_data{i} = data_csv{rand_row,3+2*i};
end
% Write the matrix to the Excel file
matrix = [char_data; conf_data];
excelWB_sheet_1.Range('b3:i4').Value = matrix;
ExcelObj.Calculate; % Force Excel to compute equations
% xlswrite(excelFilePath,matrix,1,'b3');
% pause(.05)
if (mod(j,1000)==0)
fprintf('Char %d - %0.1f min on %s\n',rand_row,toc/60,datetime)
end
Old_prediction = data_csv.Predicted(rand_row);
Old_confidence = data_csv.OverallConfidence(rand_row);
% readValue = readcell(excelFilePath, 'Range','b20:b21');
readValue = excelWB_sheet_1.Range('b20:b21').Value;
Outputs{j,1} = str2double(string(readValue(2)));
Outputs{j,2} = string(readValue(1));
Outputs{j,3} = rand_row;
if or(strcmp(Old_prediction,Outputs{j,2})==0, abs(Outputs{j,1}-Old_confidence)>.01)
fprintf('Previous character %d was %c @ %0.2f\n',j, string(Old_prediction),Old_confidence)
fprintf('New character %d is %c @ %0.2f\n',j, Outputs{j,2},Outputs{j,1})
Notable_output{found_something, 1} = j;
Notable_output{found_something, 2} = Old_prediction;
Notable_output{found_something, 3} = Old_confidence;
Notable_output{found_something, 4} = Outputs{j,2};
Notable_output{found_something, 5} = Outputs{j,1};
found_something = found_something + 1;
end
end
WorkBooks.Close(false); % false to not save changes
ExcelObj.Quit;
delete(ExcelObj); % Release the COM server
Walter Roberson
Walter Roberson 2025-11-6
What is the error message, if any?
Cris LaPierre
Cris LaPierre 2025-11-6
I borrowed that part from your original code. However, in the thread I linked you to in my comment, they just use the following (replace Workbook with Workbooks to work with my Answer). Decide if you want to save the changes in the Excel file or not, and adapt the code accordingly.
% Save file
invoke(Workbook,'Save')
% Close Excel and clean up
invoke(Excel,'Quit');
delete(Excel);
clear Excel;
Matt
Matt 2025-11-10
编辑：Walter Roberson 2025-11-13
I'm still getting a "Unrecognized function or variable 'Workbooks'." error. The script you posted works great for me. I modified relevant portions and it is not allowing me to save or close. Not sure what I'm doing wrong.
path = 'C:\Users\matt.pittard\Documents\test\';
name = "test.xlsx";
excelFilePath = fullfile(path,name); % This must be full path name
ExcelObj = actxserver('Excel.Application');
WorkBooks = ExcelObj.Workbooks.Open(excelFilePath);
excelWB_sheet_1=WorkBooks.Sheets.Item(1); %Get first sheet
invoke(Workbooks,'Save')
% Close Excel and clean up
invoke(ExcelObj,'Quit');
delete(ExcelObj);
clear ExcelObj;
Walter Roberson
Walter Roberson 2025-11-10
WorkBooks = ExcelObj.Workbooks.Open(excelFilePath);
You are assigning to WorkBooks with a capital B.
invoke(Workbooks,'Save')
You are using Workbooks with a lower-case b
Matt
Matt 2025-11-10
Sometimes it takes a second look. :-) Thanks for the help. The original code works now too.
Matt
Matt 2025-11-12
编辑：Cris LaPierre 2025-11-13
I'm writing to and reading from this Excel file almost 700,000 times. One thing I am noticing is that the execution time per iteration doubled between the first iteration and the final iteration. I re-wrote the functions to write and read from Excel to be inline with the first document posted in the responses and it sped up the time considerably, but it still increases with each execution call. Not sure what causes the issue, but it would be good to figure out.
tic
data_csv = readtable('C:\Users\matt\Test3\diagnostics.csv');
toc;
% Create an Excel object
path = 'C:\Users\matt\';
name = "Fusing Algorithm.xlsx";
Excel = actxserver('Excel.Application');
WorkBooks = Excel.Workbooks.Open(excelFilePath);
% Excel.Visible=1;
% Make the first sheet active
Sheets = Excel.ActiveWorkBook.Sheets;
sheet1 = get(Sheets, 'Item', sheetnum);
invoke(sheet1, 'Activate');
excelWB_sheet_2 = Excel.Activesheet;
total_rows = height(data_csv);
clear Outputs
tic
Outputs{3} = 0;
found_something = 1;
use_excel = 1;
char_data{8} = 0;
conf_data{8} = 0;
for j = 1:total_rows
% rand_row = ceil(height(data_csv)*rand())*0+j;
rand_row = j;
for i = 1:8
char_data{i} = string(data_csv{rand_row,2+2*i});
conf_data{i} = data_csv{rand_row,3+2*i};
end
% Write the matrix to the Excel file
matrix = [char_data; conf_data];
if (mod(j,1000)==0)
fprintf('Char %d - %0.1f min on %s\n',rand_row,toc/60,datetime)
end
Old_prediction = data_csv.Predicted(rand_row);
Old_confidence = data_csv.OverallConfidence(rand_row);
if use_excel
ActivesheetRange = get(excelWB_sheet_2,'Range','b3:i4');
set(ActivesheetRange, 'Value', matrix)
readValue = get(excelWB_sheet_2,'Range','b20:b21').value;
else
readValue(1)=Old_prediction;
readValue(2)=num2cell(Old_confidence);
end
Outputs{1} = str2double(string(readValue(2)));
Outputs{2} = string(readValue(1));
Outputs{3} = rand_row;
if or(strcmp(Old_prediction,Outputs{2})==0, abs(Outputs{1}-Old_confidence)>.01)
fprintf('Previous character %d was %c @ %0.2f\n',j, string(Old_prediction),Old_confidence)
fprintf('New character %d is %c @ %0.2f\n',j, Outputs{2},Outputs{1})
Notable_output{found_something, 1} = j;
Notable_output{found_something, 2} = Old_prediction;
Notable_output{found_something, 3} = Old_confidence;
Notable_output{found_something, 4} = Outputs{2};
Notable_output{found_something, 5} = Outputs{1};
found_something = found_something + 1;
end
end
WorkBooks.Close(false); % false to not save changes
% Close Excel and clean up
invoke(Excel,'Quit');
delete(Excel);
clear Excel;

请先登录，再进行评论。

更多回答（0 个）

类别

在 帮助中心 和 File Exchange 中查找有关 Spreadsheets 的更多信息

产品

版本

R2024b

标签

Community Treasure Hunt

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

Start Hunting!

Translated by