Change one line in Excel file

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

Answer 1

[dpb]

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.

Answer 2

[Image Analyst]

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.

[dpb]

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.