actxserver move data in entire row few rows down

Question

Louis 2020-12-4

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/678508-actxserver-move-data-in-entire-row-few-rows-down

回答： Shubham 2024-9-6，7:21

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.

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Shubham 2024-9-6，7:21

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/678508-actxserver-move-data-in-entire-row-few-rows-down#answer_1511954

在 MATLAB Online 中打开

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.');