How to overwrite labels and data in an excel file?

If I have an Excel file having labels in columns A1,B1,C1,D1,E1,F1,G1,H1,I1 and J1. Below the labels are numeric data. Now if I want to overwrite the labels in B1,E1,G1. Also if I want to overwrite the numeric data below E1 and G1, how will I do that?

Answer 1

[Ameer Hamza]

All of the following functions for writing to excel sheet support 'range' option for writing to excel file

writetable(): https://www.mathworks.com/help/matlab/ref/writetable.html

writematrix(): https://www.mathworks.com/help/matlab/ref/writematrix.html

writecell(): https://www.mathworks.com/help/matlab/ref/writecell.html

You can specify the range like B1:B100 to replace the first 100 cells of column B in the excel sheet.

[Sadiq Akbar]

Thank you very much Ameer Hamza for your consistant help. I visited the site but again it seems too technical for me. I am attaching an excel file.

Suppose I want to overwrite the label fmin in column C with functionminumum. Likewise I want to overwrite Error in Amplitude1 with EA1, Amplitudes MSE with MSE of Amplitudes.

Further, I want to overwrite data in Column C, F,M with any random data.So how will I do that via Matlab code?

[Ameer Hamza]

Here is an easy method for your excel file

data = readtable('myfile2sn01.xlsx', 'PreserveVariableName', true);

data.Properties.VariableNames{3} = 'functionminumum';
data.Properties.VariableNames{6} = 'EA1';
data.Properties.VariableNames{13} = 'MSE of Amplitudes';

data{:, 3} = rand(size(data,1), 1);
data{:, 6} = rand(size(data,1), 1);
data{:, 13} = rand(size(data,1), 1);

[Sadiq Akbar]

Thank you very much Ameer Hamza for your devoted support and help. I ran the above program, but it gives me the following Error:

>> Excel_Overwrite

Error using readtable (line 197)

Invalid parameter name: PreserveVariableName.

Error in Excel_Overwrite (line 1)

data = readtable('myfile2sn01.xlsx', 'PreserveVariableName', true);

>>

[Ameer Hamza]

Which MATLAB release are you using?

[Sadiq Akbar]

Matlab2016b

[Walter Roberson]

Leave out 'PreserveVariableName', true in your release.

Also the line

data.Properties.VariableNames{13} = 'MSE of Amplitudes';

will have to be changed to assign something that is a valid MATLAB variable name.

If you need 'MSE of Amplitudes' complete with space to be written into the file then we will need to take a bit different approach.

[Sadiq Akbar]

Thank you very much Walter Roberson for your tip. I changed the above program. This time it ran but the desired labels and data were not overwritten. Further Matlab gave me a warning message as:

>> Excel_Overwrite

Warning: Variable names were modified to make them valid MATLAB identifiers. The original

names are saved in the VariableDescriptions property.

>>

The chnaged program is as below:

data = readtable('myfile2sn01.xlsx');
data.Properties.VariableNames{3} = 'functionminumum';
data.Properties.VariableNames{6} = 'EA1';
data.Properties.VariableNames{13} = 'MSE_of_Amplitudes';

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% Overwrite Data in Columns 3,6 and 13
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
data{:, 3} = rand(size(data,1), 1);
data{:, 6} = rand(size(data,1), 1);
data{:, 13} = rand(size(data,1), 1);

[Walter Roberson]

Modified variable name warning is just a warning.

You are not writing the data variable to a file.

[Sadiq Akbar]

Thank you very much Walter Roberson. I am not too much technical. How will we write it to the file, "myfile2sn01.xlsx" , I have given in the attachment? Can you guide me further?

[Ameer Hamza]

@Sadiq, you can use writetable()

writetable(data, 'filename.xlsx')

[Sadiq Akbar]

Thank you very much dear Ameer Hamza for your consistant help. Yes, it worked for the labels. But it didn't work for the numerical data. Further, it filled column "K" data from row33 till end with hashes i.e. ####.

I am attaching the file for your observation.

[Ameer Hamza]

I am not sure about the issues. These commands work fine in R2020b. The output file I get is attached.

[Sadiq Akbar]

Thank you very much dear Ameer Hamza. Indeed you are a true helper. I am very happy from you. Ok I will try to arrange Matlab 2020b if I could. But currently I am not having it. Its expensive too. Thank you very much once again. May you live long to keep helping the needy like me.

[Sadiq Akbar]

https://www.mathworks.com/matlabcentral/answers/617363-how-to-get-a-cdf-plot-of-my-data