Change one line in Excel file

조회 수: 10 (최근 30일)
Douglas Anderson
Douglas Anderson 2021년 6월 10일
편집: dpb 2021년 6월 11일
Hello!
I want to change one line (the header) in many Excel files for consistency (so I can merge them using readtable() ). Individuals created the Excel files themselves, and have minor variations in the header that muck up readtable(). I have a loop, below, to fix the Excel files, but have tried xlsread/xlswrite, cellread/cellwrite, and have issues with each. Suggestions?
addpath(fileDir);
fileNames = dir(fileDir);
fileNames = {fileNames.name};
fileNames = fileNames(cellfun(@(f)contains(f,'.xls'),fileNames));
for f = 1:numel(fileNames)
flist = xlsread(fileNames{f});
% flist_orig = flist;
flist{1,1} = 'Topic Number';
flist{1,2} = 'First Name';
flist{1,3} = 'Last Name';
flist{1,4} = 'Mentoring Topic';
flist{1,5} = 'Years Experience';
flist{1,6} = 'Location';
flist{1,7} = 'Email Address';
xlswrite(filenames{f},flist); %cell(flist,'junkofix.xlsx'); %fileNames{f});
end
I set "fileDir" before this runs, of course!
Thanks.
Doug Anderson

답변 (2개)

dpb
dpb 2021년 6월 10일
I'd avoid xlswrite and use writecell instead. Use the 'Range' parameter to put in the right place and not disturb anything else, of course.
NB: I have a bunch of these must deal with all the time and rather than trying to fight the losing battle of the users not continuing to create new versions from same old versions as had before even after fixing them, I instead just rename the variables in the table to match the ones I want instead.

Image Analyst
Image Analyst 2021년 6월 10일
Do you have Windows? If so, I'd use ActiveX. The advantage of that over writecell() is that you won't destroy any formatting (like bolding, cell colors, borders, etc.) like writecell() does.
Alternatively you can just reassign the headers after they've been inported into MATLAB.
  댓글 수: 1
dpb
dpb 2021년 6월 10일
편집: dpb 2021년 6월 11일
I'm 100% in agreement to the latter alternative as I noted as well; don't even bother to try to fix the originals; the hired help will just create new ones with the same problem in their place, anyways...and, yeah, go ahead and ask me how I know that! :)
writecell(...,'UseExcel',true,'AutoFitWidth',false,'PreserveFormat',true);
handles(*) the above without the grief of having to do the connection and low-level stuff directly.
If one is simply writing one or two records, the overhead isn't bad; I agree if need to update a bunch of different ranges separately, then the overhead of opening/closing the connection every write is a killer.
There's a FileExchange submission xlswriteEx that creates a persistent connection object to get around this that works a champ; I highly recommend it for a little higher-level interface but that will not grind to a snail's pace or hang when writing many rows in one-at-a-time fashion where it's difficult to build the full sheet content at one time.
(*) I'm not sure which release introduced these; I believe it was in R2019b I first became aware of them.

댓글을 달려면 로그인하십시오.

카테고리

Help CenterFile Exchange에서 Switches and Breakers에 대해 자세히 알아보기

제품


릴리스

R2019a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by