Change one line in Excel file

6 次查看（过去 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 Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

产品


版本

R2019a

Community Treasure Hunt

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

Start Hunting!

Translated by