actxserver move data in entire row few rows down

I am trying to use actxserver to format header row in an excel spreadsheet.

Before I manipulate header row, I would like to shift information in the second row (data row) down few rows to make additional spaces of rows between the header row and the data row.

I am having trouble doing seemingly simple task.

What I currently have is this:

function reformatHeader(excel_filename)
    % Connect to the Excel COM object and open excel_filename
    excel = actxserver('Excel.Application');
    excel_wb = excel.Workbooks.Open(excel_filename);
    sheet = excel_wb.ActiveSheet();
    % Get the size of the sheet; assumes rectangular and not ragged
    nColumns = sheet.Range('A1').End('xlToRight').Column;
    nRows = sheet.Range('A1').End('xlDown').Row;
    % Number of rows to add
    nRowsToAdd = 2;
    % I want to move the second row down by nRowsToAdd here

Basically the content of nRows should be moved to nRows+nRowsToAdd

There seems to be surprising little about of resources.. Is there a proper documentation with all commands?

I am using this at the moment, which isn't sufficient to do such simple operation: https://www.mathworks.com/help/matlab/matlab_external/using-a-matlab-application-as-an-automation-client.html

Thank you in advance.

Answer 1

[Shubham]

Hi Louis,

Here's how you can insert multiple rows at a specified position in an Excel sheet using MATLAB's "actxserver":

% Define the filename and path
filename = "random_data.xlsx";
filepath = fullfile(pwd, filename);

% Start an Excel application and make it visible
excelApp = actxserver('Excel.Application');
excelApp.Visible = 1;

% Open the workbook, access the first sheet, and insert a new row
workbook = excelApp.Workbooks.Open(filepath);
sheet1 = workbook.Sheets.Item(1);

% Insert 5 new rows at the second position
for i = 1:5
    sheet1.Rows.Item(2).Insert();
end

% Save, close the workbook, and quit Excel
workbook.Save();
workbook.Close();
excelApp.Quit();

% Clean up
delete(excelApp);
disp('Excel workbook edited successfully.');

Refer to the following documentation link for more information on "actxserver":

https://mathworks.com/help/releases/R2019b/matlab/ref/actxserver.html

Hope this helps.