xlswrite - clear cells and write multiple values
25 views (last 30 days)
Show older comments
Hi,
I have a Matlab code write on to an existing excel file. I need to do this in order to perform certain calculations and formatting for easy printing.
Each time I run the code, the size of the array may vary. I need to clear the cells before I write, in order to avoid values from previous run to stay in the sheet. How can I do this? Filling it with spaces [' '] is not an option as it messes with the print settings(will print blank pages).
Also, currently I have the code use xlswrite function multiple times. This is slow because it opens, writes and closes the file each time. Is there a way to write several variables on to specific ranges in the excel sheet faster?
Thanks!
2 Comments
Andy
on 18 May 2011
You said in a comment that your Excel sheet has formatting that needs to be preserved and calculations (cells with formulas in them?) that needs to be visible. If this is the case, I think you must have some constraint on the size of the data you're writing that you haven't shared with us. (E.g., how do you know you aren't overwriting the cells that contain your calculations? And how do the cells which contain your calculations know what range of data to look at?) I think we need a bit more information.
Answers (1)
Oleg Komarov
on 18 May 2011
The easiest would be to write to a new sheet (see xlswrite options)
Or using activex as explained in this post (clears all sheets for specified xlsx):
EDIT
% Name of the excel file
filename = 'C:\Users\<yourUser>\Documents\abc.xls';
% Open Excel as a COM Automation server
Excel = actxserver('Excel.Application');
% Open Excel workbook
Workbook = Excel.Workbooks.Open(filename);
% Clear the content of the sheet
Workbook.Worksheets.Item('Sheet1').Range('B2:C4').ClearContents
% Now save/close/quit/delete
Workbook.Save;
Excel.Workbook.Close;
invoke(Excel, 'Quit');
delete(Excel)
7 Comments
Harry Commin
on 12 Jan 2012
If the xls file is in the current directory, I found that xlsinfo() will locate it by filename alone, but Excel.Workbooks.Open() needs the full path:
Workbook = Excel.Workbooks.Open([pwd '\' filename]);
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!