Read and write data to an open excel file.

Question

Saidi Chawki 2022-4-13

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1695645-read-and-write-data-to-an-open-excel-file

回答： John Bishop 2024-1-23

Hello everyone, I need to ask how matlab R2017b can read data from an open excel file than write the data to the same file ? Because now it can't run without close the file.

Thank you !

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

请先登录，再进行评论。

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

Answer 1

Emmanuel J Rodriguez 2022-4-13

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1695645-read-and-write-data-to-an-open-excel-file#answer_941990

Hello, take a look at this answer: https://www.mathworks.com/matlabcentral/answers/577870-write-to-an-already-opened-excel-file#answer_478051

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

Emmanuel J Rodriguez 2022-4-26

在 MATLAB Online 中打开

Hello! Below is the code to read/write to an open MS Excel file:

%% Create Excel Automation Server
% Run the Excel application in an Automation server process
% using the 'actxserver' function and the program ID, excel.application
exl = actxserver('excel.application'); % Creates an Excel object
% Use the 'Workbooks' interface to open the Excel file containing the data
exlWkbk = exl.Workbooks;
exlFile = exlWkbk.Open(['C:\Users\ejoaq\OneDrive\1 My_Notebook\3 Engineering\Programming - Logic and Design\MATLAB\Read_and_Write_Data\testReadWrite.xlsx']);
exl.Visible = 1; % Display Excel file by setting the Visible property to 1
% Use the workbook 'Sheets' interface to access the data from a 'Range'
% object
exlSheet1 = exlFile.Sheets.Item('Sheet1');
robj = exlSheet1.Columns.End(4); % Range object, find the end of the column
% For robj to find the end of the column, data must start at row 1, col 1
numrows = robj.row; % And determine what row it is
dat_range = ['A1:F' num2str(numrows)]; % Read the last row
rngObj = exlSheet1.Range(dat_range); % Calls the exlSheet1 object and uses
% the 'Range' method to operate on the data range, 'dat_range'
% The entire data set from the Excel file's 'sheet1' is accessed via
% the range object interface 'rngObj'
exlData = rngObj.Value;
% Convert the numerica data to a double matrix.
exlDataNums = cell2mat(exlData(2,:));
%% Write Spreadsheet Data Using Excel as Automation Server
% Make the first sheet active
% exlSheets = exl.ActiveWorkbook.Sheets;
% exlSheet1 = exlSheets.get('Item',1);
% exlSheet1.Activate
% Assign variables to numeric data
N = exlDataNums(1)
x = exlDataNums(2)
R = exlDataNums(3)
C = exlDataNums(4)
% Logic
xNew = C - R;
A = nan;
if N < R
    A = 12
elseif N > C
    A = 8
else
    A = 10
end
% Put MATLAB data into the worksheet
exlActivesheetRange = get(exl.Activesheet,'Range','E2:E2'); % Get active sheet, assign range
exlActivesheetRange.Value = A; % Write data to (open) Excel file
% Read data back into MATLAB
%exlRange = get(exl.Activesheet,'Range','F1:F3');
exlRange = exlActivesheetRange;
A_readback = exlRange.Value; % Type = cell array
% Save the file
exlFile.Save
% Close Workbook
exlWkbk.Close
% Terminate Excel Automation Process
% Since Excel Automation server runs in a seperate process from MATLAB, you
% must terminate this process explicitly.
exl.Quit
exl.delete % Delete the server object

Emmanuel J Rodriguez 2022-4-26

Also, make sure all MS Excel instances are closed before executing the code.

Saidi Chawki 2022-5-4

Thank you for your help, I have tried it and it works well. There was a problem in my machine.

请先登录，再进行评论。

Answer 2

John Bishop 2024-1-23

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1695645-read-and-write-data-to-an-open-excel-file#answer_1395326

在 MATLAB Online 中打开

A simple workaround method that might work for some is to make a local temporary copy of the open file and work on that, then copy it back to the original when ready. It is also a safe method of working as it would not touch the original data until told to do so.

The 'system' command can run an OS command, e.g. on a MS Windows machine:

originalfilename='Book1.xlsx';
tempfilename='tmp.xlsx';
system(sprintf('xcopy %s %s /Y',originalfilename, tempfilename));

On MS Windows the 'xcopy' command with a /Y option copies the file and overwrites without prompting. Also xcopy is not blocked by the file being open elsewhere whereas a straight 'copy' is blocked. I'm sure there is a similar Linux command.

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

请先登录，再进行评论。

Read and write data to an open excel file.

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

回答（2 个）

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

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

另请参阅

类别

标签

Community Treasure Hunt

Read and write data to an open excel file.

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

回答（2 个）

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

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

另请参阅

类别

标签

Community Treasure Hunt

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

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

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