How to write and save in an excel file?
120 views (last 30 days)
Show older comments
I want to copy data from a specific column of each sheet from multiple excel sheets in an excel file and write it to an output excel file. out put excel file should look like, the first column contains the data from first sheet, the second contain data from second sheet and so on. each column of out put file contain the sheet name in the first row. also I am having many excel files so the output file shout have the name as the parent file or it can be created in a folder with the name of parent excel file. My code looks like this but does write the output.
clear;
clc;
XLfile = dir();
XLfile([XLfile.isdir]) = [];
number_of_files = length(XLfile);
for index = 1:number_of_files
filename = XLfile(index).name;
[status, sheetnames] = xlsfinfo(filename);
if isempty(status)
warning( sprintf('Skipping non-excel file: "%s"', filename));
continue;
end
fig = figure();
nsheet = min( length(sheetnames), 10 ); %ignore past 10
if nsheet < 10
warning( sprintf('Only %d sheets in file "%s"', nsheet, filename));
end
for i = 1:nsheet
sheetname = sheetnames{i};
y = xlsread(filename, sheetname, 'E2:E10000');
end
xlswrite(filename,A);
end
[EDITED, Jan, Code formatted]
1 Comment
Jan
on 11 Feb 2016
I'd expect an error message, because in xlswrite(filename,A) the variable A has not been defined.
Answers (2)
Adewale Obaro
on 9 Mar 2018
Edited: Adewale Obaro
on 9 Mar 2018
THIS ONE LINE CODE WRITES ARRAY DATA (OR VARIABLE) INTO A SPREADSHEET AND IT WORKS LIKE MAGIC
xlswrite('myData .csv', Gc,'C1:C5','sheet1') OR xlswrite('myData .xlsx', Gc,'C1:C5','sheet1')
NOTE: myData is the name of the file which you intend to name the file
cvs or xlsx is the spreadsheet extension
Gc = [20.4 23.67 11.49 33.17 22.65] % The variable or array which you want to save
C1 is the starting cell on which you want the saving to start from
C5 is the ending cell to which you want the data saving to end
sheet1 is the sheet name you want to save onto, on the spreadsheet
Please like this TO ENCOURAGE ME if it help you because this is MY FIRST CONTRIBUTION ON MATHWORKS
0 Comments
dpb
on 11 Feb 2016
I'd suggest a few changes--
XLfile = dir();
Why not use wildcard to only get .xls? files? Then can get rid of the other logic...
XLfile = dir('*.xls?'); % Or .xls or .xlsx if is specific form
number_of_files = length(XLfile);
for index = 1:number_of_files
filename = XLfile(index).name;
...immaterial to problem lines elided...
A=zeros( sscanf('E10000','E%d')-sscanf('E2','E%d')+1,nsheet); % size of read column by nsheets
for i = 1:nsheet
A(:,i) = xlsread(filename, sheetnames{i}, 'E2:E10000');
end
xlswrite(OutputFileName,A);
end
Need to then create the OutputFileName as desired before beginning the loop.
6 Comments
dpb
on 26 Feb 2016
Did you implement the suggestions I outlined above? If so, where's the updated code and what's the issue?
See Also
Categories
Find more on Data Import from MATLAB in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!