Adding data to existing excel

Hi, I want to ask a question I want a program where I can add more row data every time I execute this program, but when i execute this code, only row 2 got added, not 3, 4, etc. I want it to stack, every time I run the program

here is the code :

tgl=4;
Jumlah=1;
nama='adawd';
ntgl=3;
nJumlah=3;
nnama='dddd';

A = {'Tanggal','Jumlah Laki-Laki','Nama Video'; tgl,Jumlah,nama};
baru = {ntgl,nJumlah,nnama};
A(end+1,:) = baru;

xlswrite('Masukkan nama file.xls',A);

Answer 1

[Image Analyst]

You need to use the xlsread() first to determine what the last row of your existing data it. Then use xlswrite's 3rd and 4th arguments to make sure you're writing additional data to the end of the existing data.

[syahdan edy murad]

Like this? please help me which line should i edit

xlsread('Masukkan nama file.xls');

tgl=4;
Jumlah=1;
nama='adawd';
ntgl=3;
nJumlah=3;
nnama='dddd';

A = {'Tanggal','Jumlah Laki-Laki','Nama Video'; tgl,Jumlah,nama};
baru = {ntgl,nJumlah,nnama};
A(end+1,:) = baru;

xlswrite('Masukkan nama file.xls',A);

[Image Analyst]

No. You did not read your data into any variable and determine the size like I instructed. Try this:

filename = fullfile(pwd, 'Masukkan nama file.xls')
[numbers, strings, raw] = xlsread(filename);
lastRow = size(raw, 1)
% Specify next place to write to.
xlRange = sprintf('A%d', lastRow+1);
xlswrite(filename, A, xlRange);

[syahdan edy murad]

like this ?

filename = fullfile(pwd, 'Masukkan nama file.xls')
[numbers, strings, raw] = xlsread(filename);
lastRow = size(raw, 1)
% Specify next place to write to.
xlRange = sprintf('A%d', lastRow+1);

baru = {13,13,13};
A = {'Tanggal','Jumlah Laki-Laki','Nama Video'; 14,14,14};
A=[A;baru];

xlswrite(filename, A, xlRange);

[Image Analyst]

You'd know if you tried it. Did you try it? Did it work? Looks like it should work.

[syahdan edy murad]

still not working, it just adding the row 3 everytime i run the code

[Image Analyst]

Evidently if you use the range without a sheet name, you can't just give the upper left corner like you can if you don't give the sheet name. So do this, which will work as long as A is not wider than 26 columns.

filename = fullfile(pwd, 'Masukkan nama file.xls')
[numbers, strings, raw] = xlsread(filename);
lastRow = size(raw, 1)

% Create new data.
baru = {13,13,13}
A = {'Tanggal','Jumlah Laki-Laki','Nama Video'; 14,14,14}
A=[A;baru]
% Get the size of this new data
[rows, columns] = size(A)
lastColumn = 'A' + columns - 1

% Specify next place to write to.
xlRange = sprintf('A%d:%s%d', lastRow+1, lastColumn, lastRow + rows)

xlswrite(filename, A, xlRange);

[syahdan edy murad]

after i try this, it start from a3 and start to making a 2 row at the same time, so the second execution i need to change the code to this

filename = fullfile(pwd, 'Masukkan nama file.xls')
[numbers, strings, raw] = xlsread(filename);
lastRow = size(raw, 1)
% Create new data.
baru = {13,13,13}
A = {14,14,14}
A=[A;baru]
% Get the size of this new data
[rows, columns] = size(A)
lastColumn = 'A' + columns - 1
% Specify next place to write to.
xlRange = sprintf('A%d:%s%d', lastRow+1, lastColumn, lastRow + rows)
xlswrite(filename, baru, xlRange);

is there any way, that the column name like tanggal, jumlah laki laki and nama video change it to be added in the first execution, not each execution? because if i still add it, the column name overwrite the second row the data in the first execution

[Image Analyst]

It sounds like those words might be column headers. If so, just make up a spreadsheet with nothing in it but those column headers. If you do this a bunch of times with different filenames, then make a master workbook called 'Excel Results Template.xlsx' and use copyfile() to copy it to some new name.

templateFullFileName = fullfile(folder, 'Excel Results Template.xlsx');
thisFullFileName = fullfile(folder, 'Masukkan nama file.xls'); % or wherever you want.
copyfile(templateFullFileName, thisFullFileName);

where you change the output/destination folder name to be different than the master template workbook.

Then call xlswrite() with thisFullFileName workbook.

Then at each iteration all your new data will be appended but the column headers won't be.

[syahdan edy murad]

Thank you for your help, the code is work

[Oman Wisni]

hi syahdan, do you have full code for this? would you attached your full code here? thanks

[halawati cm]

yes its work..tqvm

my sample codes inside export button:

templateFullFileName = fullfile('D:\', 'Excel Results Template.xlsx'); %create new file in selected folder

thisFullFileName = fullfile('C:\', 'New Report.xlsx'); % place the new file for desired output

copyfile(templateFullFileName, thisFullFileName);

allResults = zeros(6, 4);

for k = 1 : size(allResults, 1) % k is row number

theseResults = baru; %baru is a set of data as syahdan write

allResults(k, :) = theseResults;

xlswrite(thisFullFileName, allResults); %write the output the excel file 'New Report.xlsx'

end

[Image Analyst]

Looks like you'll be overwriting all prior data since your call to xlswrite() does not specify a cell reference so everything will go into cell A1. Thus you're calling xlswrite() way more than is needed. You can call it just once, after the loop where you've stuffed everything into allResults. Something like

            templateFullFileName = fullfile('D:\', 'Excel Results Template.xlsx');  %create new file in selected folder
            thisFullFileName = fullfile('C:\', 'New Report.xlsx'); % place the new file for desired output
            if isfile(templateFullFileName)
                % Copy over template, but only if it exists (so we don't throw an error).
                copyfile(templateFullFileName, thisFullFileName);
            end
            allResults = zeros(6, 4); % Preallocate space for all results.
            xlRow = 1;
            for k = 1 : size(allResults, 1) % k is row number
                % Get new results (a 4 element row vector) by calling the baru() function.
                theseResults = baru; % baru is a set of data as syahdan write
                % Stuff these 4 numbers into row k of allResults.
                allResults(k, :) = theseResults;
            end
            % Now that allResults has been built up, we can export it to 
            % an Excel workbook file called 'New Report.xlsx' into cell A1 of 'Sheet1'.
            xlswrite(thisFullFileName, allResults);

I'd only put it inside the loop if the computation takes a long time (hours) and you want to make sure you at least have something in the workbook in case your program crashes.

[halawati cm]

ok, if i have different set of data to write into excel file like this:

data1= [0.1, 0.2, 0.3, 0.4];

data2= [0.5, 0.6, 0.7, 0.8];

data3=[0.9, 0.10, 0.11, 0.12];

the question is:

1-how to write it to the excel file with different set of data?

2-after close the application and run again, how to append the new set of data into the existing excel file without missing the existing data in the file?

tqvm for your response.

[Image Analyst]

One way to do it would be to first read it in and see what the last row is. Then write to the row below it.

[numbers, strings, raw] = xlsread(filename);
lastRow = size(raw, 1);
nextRow = lastRow + 1;
cellReference = sprintf('A%d', nextRow);
xlswrite(excelFullFileName, data1, 'Sheet1', cellReference);
nextRow = nextRow + 1;
cellReference = sprintf('A%d', nextRow);
xlswrite(excelFullFileName, data2, 'Sheet1', cellReference);
nextRow = nextRow + 1;
cellReference = sprintf('A%d', nextRow);
xlswrite(excelFullFileName, data3, 'Sheet1', cellReference);

Answer 2

[Hazem Kamel]

Check this code. It may help to write and append data in an existing Excel sheet:

A=magic(5);

header={'Hazem', 'Gigi', 'Rita', 'Karim', 'Viola'}

xlswrite('test.xlsx',header);

[number, strings, row] = xlsread('test.xlsx');

lastRow = size(row,1)

nextRow = lastRow+1;

cellReference = sprintf('A%d', nextRow);

xlswrite('test.xlsx', A, 'Sheet1', cellReference);

winopen('test.xlsx');