How to add arbitrary rows of zeroes in matrices/ex

Question

This question is an exact duplicate of:

• How to add rows of zeroes in matrices/excel spreadsheet with code (MATLAB)? 1 answer

I have a XLS file with 3 columns and 212 rows of data. Is there any way to insert 3 columns of zeroes with x number of rows in the middle, so there is a total of 365 rows?

Answer 1

OK yes, so one easy way is to go to the first row where you need the 0's to appear.

Type a '0' in each cell. Then select all three cells, right-click and hold it down while moving the mouse down for the 152 rows.

I'l ladd a pic.

full-pic

Answer 2

I'm a Linux man when possible, so my Microsoft skills are a bit lacking, ie there might be a better way to solve the problem than this, but the following should work:

%# Set parameters
FN = 'YourExcelWorkbookPath.xlsx';
r = 3; %#Row to start inserting zeros
n = 2; %#Number of rows of zeros to insert

%# Import the excel data
[~, ~, Raw] = xlsread(FN);

%# Insert the zeros
RawOut = [Raw(1:r-1, :); num2cell(zeros(n, 3)); Raw(r:end, :)];

%# Write the output back to excel workbook
xlswrite(FN, RawOut);

Sorry, am at work, so don't have time to give a long explanation. Essentially, I read the workbook into a cell array in matlab, insert a cell array of zeros in the middle, and then write the whole thing back to workbook. Not elegant, but it worked for me on a test workbook.

EDIT: Of course, if your workbook contains lots of other information besides the 3 columns of data, you'll need to hack around with this approach to get the insert happening in the right place. Because of this, I'd go with the ActiveX approach suggested by @grantnz (+1). Much more Microsoft-y.

Answer 3

Another way you can do this is via the excel activex control.

The following assume you have an excel spread c:\temp\zeros.xlsx.

It inserts 5 rows each with 3 columns of zeros starting at row 3.

Obviously, you can adjust the values assigned to insertRow and insertRowCount to meet your specific requirements.

Alternatively, you could use copy/insert instead but this should be good enough.

% Create Excel ActiveX control (and therefore application) and make it visible
excel = actxserver('Excel.Application');
excel.visible = 1;

% Open the the spreadsheet
workbook = excel.Workbooks.Open('c:\temp\zeros.xlsx');
sheet = workbook.ActiveSheet;

% Insert rows

insertRow = 3;    
insertRowCount = 5;
range = get(sheet,'Range', sprintf('A%d:C%d', insertRow, insertRow ) );
for r=1:insertRowCount
    range.Insert()
end


% Set cell values on inserted rows
cellValue = 0;

for r=1:insertRowCount
    for c=1:3
        set(get(sheet,'Cells',r+insertRow-1,c),'Value',cellValue);     
    end
end