Excel files: how to open, edit, and then close file without causing file to become "Read-Only" in MATLAB

220 次查看（过去 30 天）
Keilan
Keilan 2014-12-9
编辑： Walter Roberson 2020-4-13
Hi all,
I am having difficulties with editing an Excel file in MATLAB. My ultimate goal is to edit the formatting in the file, particularly the cell borders. When I run this script, it creates the borders that I want, but the Excel file becomes "Read-Only" and I cannot save the changes I've made. I am not sure what is causing the file to become read-only. Any help or suggestions would be greatly appreciated.
Thanks, K
% Link to Excel
Excel = actxserver('Excel.Application');
Excel.Workbooks.Open('C:\Users\Keilan\Documents\MATLAB Expirements\2014\MATLAB to Excel test.xlsm');
double = get(Excel.ActiveWorkBook.Sheets,'Item',3); % Want to edit 3rd sheet in workbook
% Number associated with each border: left, right, top, bottom
lt = 1;
rt = 2;
% tp = 3;
bt = 4;
% Add in the periodic borders required
for kk = 1:ct
% Row numbers of interest
rn1 = 1 + 3*kk; % R1, A(rn1):E(rn2). R2, BG(rn1):BK(rn2)
rn2 = 3 + 3*kk; % R3, A(rn2):BK(rn6)
% Ranges of interest
R1 = sprintf('A%d:E%d',rn1,rn2); %'A4:E6';
R2 = sprintf('BG%d:BK%d',rn1,rn2); % 'BG4:BK6';
R3 = sprintf('A%d:BK%d',rn2,rn2); % 'A6:BK6';
Range1 = Excel.Range(R1);
Range2 = Excel.Range(R2);
Range3 = Excel.Range(R3);
% Create solid borders in desired locations
set(get((Range1.borders),'item',lt),'linestyle',1);
set(get((Range1.borders),'item',rt),'linestyle',1);
set(get((Range2.borders),'item',rt),'linestyle',1);
set(get((Range3.borders),'item',bt),'linestyle',1);
end
Excel.Visible = 1; % Open the Excel spreadsheet
% Excel.ActiveWorkbook.Save; % Tried this, didn't work either
delete(Excel); % Close the activex server
  1 个评论
matt dash
matt dash 2014-12-9
I have 0 experience with the excel activexserver, but quick idea: Do you also have the workbook open in an actual Excel instance? I think that would lock it.

请先登录，再进行评论。

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

采纳的回答

Guillaume
Guillaume 2014-12-9
If you don't save the workbook and don't quit excel, the workbook will remain open in excel and be left read-only by excel, thus before closing the activex server:
Excel.ActiveWorkbook.Save;
Excel.Quit;
Side Note: Rather than using ActiveWorkbook, I would use the workbook object returned by the Workbooks.Open method:
workboox = Excel.Workbooks.Open('C:\Users\Keilan\Documents\MATLAB Expirements\2014\MATLAB to Excel test.xlsm');
sheet = workbook.Sheets.Item(3);
%...
workbook.Save;
Excel.Quit;
And certainly don't use double as a variable name!
  4 个评论
显示 2更早的评论
Image Analyst
Image Analyst 2016-9-27
Try this snippet:
[taskstate, taskmsg] = system('tasklist|findstr "EXCEL.EXE"');
if ~isempty(taskmsg)
status = system('taskkill /F /IM EXCEL.EXE');
% If it gets here, shutting down worked, (I think) unless it asked them to save any unchanged work and they said to cancel shutdown.
end
Monty
Monty 2018-4-6
编辑：Walter Roberson 2020-4-13
Try
xlApp = actxserver('Excel.Application');
xlWorkbook =xlApp.workbooks.Open(fullfile([PathName,FileName]));
xlWorkbook.Save;
xlWorkbook.Close;
xlApp.Quit;
xlApp.delete;
Using delete property closes the com for me.

请先登录，再进行评论。

更多回答（2 个）

Image Analyst
Image Analyst 2014-12-10
Look at my attached Excel class. See the methods in there like FormatRightBorder(). (Sorry, it's a work in progress and there are a few things done in different ways, but it works.)
Also, see my attached Excel demo - it doesn't make the file read only.
  1 个评论
Keilan
Keilan 2014-12-18
Thank you for your help and for passing along your Excel class files. I have not read them cover to cover yet but thus far they look very interesting and useful. I appreciate it!

请先登录，再进行评论。

Pruthvi G
Pruthvi G 2020-4-13
Download Link :: https://in.mathworks.com/matlabcentral/fileexchange/71329-delete-excel-workbook-sheets-sheet-1-delete-using-matlab
%%**********************************************************************************************************
% Name : Delete_sheets_Excel
% Author : Pruthvi Raj G :: (9677066394 :: www.prudhvy.com )
% Version : Version 1.0 - 2011b Compactible
% Description : Deleting Excel Sheets required after writing data to Excel.
% Input : File_Name with path included , Sheet_name / Sheet_names.
% Date : 22-April-2019
%
% Examples : Delete_sheets_Excel('D:\Pruthvi\Test_file.xls',{'Sheet1','Sheet2'}) %To delete 2 sheets
% Delete_sheets_Excel('D:\Pruthvi\Test_file.xls','Sheet1')
% Delete_sheets_Excel('D:\Pruthvi\Test_file.xls') % Takes 'Sheet1' as Default
%************************************************************************************************************
Use the Below Lines of Code ::
Delete_sheets_Excel('D:\Pruthvi\Test_file.xls',{'Sheet1','Sheet2'})

类别

在 Help Center 和 File Exchange 中查找有关 Use COM Objects in MATLAB 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by