How to insert cell array into first row of excel worksheet using worksheet and workbook properties (dot notation)?

Hello! Currently, my code opens an existing file in excel, adds a sheet after the last sheet in the excel file with the current date as the name, and then saves and closes excel and all open excel workbooks.

I would like to also insert a cell array into the first row of the newly created sheet, however I get an error when I use xlswrite, writetable, or writecell, that says "Unable to open file 'inventory.xlsx' as a workbook. Check that the file exists, read access is available, and the file is a valid spreadsheet file."

My cell array is:

data = {'apples', 'oranges', 'bananas', 'grapes', 'blueberries'};

Is there a way to add a row to the newly created sheet using worksheet and workbook object properties (dot notation, etc.)? I want to have the cell array in data as my first row in the new excel worksheet. Thanks.

Current code:

Excel = actxserver('Excel.Application'); %Connect to Excel
WB = Excel.Workbooks.Open('inventory.xlsx'); %Get Workbook object
WS = WB.Worksheets; %Get Worksheets object
WS.Add([], WS.Item(WS.Count)); %Add new sheet after the last sheet
WS.Item(sheetnum+1).Name = string(datetime('today')); %Change name of new sheet to today's date
WB.Save(); %Save
Excel.Quit(); %Quit Excel

Answer 1

[Kautuk Raj]

You can add a row to the newly created worksheet using worksheet and workbook object properties in MATLAB. This is an example of how you can modify your code to add a row to the new sheet:

Excel = actxserver('Excel.Application'); %Connect to Excel
WB = Excel.Workbooks.Open('inventory.xlsx'); %Get Workbook object
WS = WB.Worksheets; %Get Worksheets object
newSheet = WS.Add([], WS.Item(WS.Count)); %Add new sheet after the last sheet
newSheet.Name = string(datetime('today')); %Change name of new sheet to today's date
range = newSheet.Range('A1:E1'); %Set range for the new row
data = {'apples', 'oranges', 'bananas', 'grapes', 'blueberries'}; %Create the new row data
range.Value = data; %Write the new row data to the range
WB.Save(); %Save
Excel.Quit(); %Quit Excel

In this example, the Range object is used to set the range for the new row of data. The range is set to the first row (A1:E1) of the new worksheet (newSheet). The Value property of the range is then set to the data cell array, which writes the new row to the worksheet.

Note that this example assumes that the new row of data is the same size and shape as the existing data in the worksheet (i.e., 5 columns wide in this case). If the new row of data has a different size or shape, you may need to adjust the range accordingly.

Answer 2

[Walter Roberson]

For a fair number of releases now, on Windows, xlsread() and xlswrite() leave activex sessions open talking to Excel . This is done for efficiency, to avoid having the overhead of starting up Excel each time.

So if you use xlsread() or xlswrite() on Windows, the Excel you launch is not necessarily going to quit and the worksheet is not necessarily going to be cleanly busy or not busy.

We recommend that you do not talk to Excel directly if you can avoid it, and that you use readtable() and writetable() or readcell() and writecell().

There are some things you can do talking directly to Excel that are not supported by any MATLAB function, so sometimes you do not have much choice... but short of that, best avoid talking to Excel directly.