xlswrite - clear cells and write multiple values

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!

[Andy]

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.

[Prajwal Bhat]

By calculations I mean cells with formulas in them. My sheet has some calculations at the top few rows. The data has a definite number of columns, but varying number of rows; and this data has to be written below the cells containing the formulas. Hope this clears..

Answer 1

[Oleg Komarov]

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):

http://www.mathworks.com/matlabcentral/answers/642-how-do-i-clear-the-contents-of-excel-by-sheet

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)

[Prajwal Bhat]

Not an option, since my excel sheet has a lot of formatting and some calculations that need to be visible.

[Oleg Komarov]

Then try the other option described in the link. First backup your file as I am not sure at the moment what happens to formatting. Gonna test in a min

[Oleg Komarov]

Use the following cellfun line to ClearContent only:

cellfun(@(x) Excel.ActiveWorkBook.Sheets.Item(x).Cells.ClearContents, sheetNames);

[Prajwal Bhat]

Thanks for the reply. I am not very sure of the terms here. Lets say I have a file at C:\Users\Documents\abc.xls and I want to clear the contents in cells B2:C4 in sheet named "sheet1". Could you please tell me how the code should look?

[Oleg Komarov]

See edit

[Harry Commin]

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]);

[stephen brewis]

try this simple and it works

delete 'filename.xlsx'