Excel files: how to open, edit, and then close file without causing file to become "Read-Only" in MATLAB
225 次查看(过去 30 天)
显示 更早的评论
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
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
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 个评论
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
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
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.
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'})
0 个评论
另请参阅
类别
在 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!