Multiple import/edit of xls files

Question

Az 2011-7-19

1
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/11907-multiple-import-edit-of-xls-files

Dear All, I have around 1400 xls files with 2 sheets in each file.(with the filenamelike 50005100.xls but without a pattern) Here is what I want to do: 1) delete the first sheet; 2) insert a column with header(ID) and then fill in the column with the file name in the second sheet. then export the file to csv(I know how to do that) I need to do this to all of them one by one. Can anyone help me with this? Million thanks. Az

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Chirag Gupta 2011-7-19

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/11907-multiple-import-edit-of-xls-files#answer_16289

在 MATLAB Online 中打开

This should work! You might need to tweak it slightly!

hExcel = actxserver('Excel.Application');
hExcel.visible = 1; % If you want Excel visible.
hExcel.DisplayAlerts = false; % Avoid excel warning popups
for i:length(filenames) % Assuming filenames contains the 1400 xls files
   Wkbk = hExcel.Workbooks.Open(fullfile(pathToFile, filenames{i})); % Opens Excel file
   Sheets = Wkbk.Sheets; % Get the Sheets
   Sheets.Item(1).Delete; % Deletes the first sheet
   Wkbk.ActiveSheet.Cells.Item(1).EntireRow.Insert; Assuming you want to insert a row and not a column
   Wkbk.ActiveSheet.Cells.Range('A1').Value = filenames{i}; % Insert Filename
   Wkbk.Save; Save xls file
   Wkbk.SaveAs('C:\MatlabWork\Answers\Test.csv', 6); Save as .csv (file format for csv enum is 6)
   Wkbk.Close;

end

hExcel.Quit;
hExcel.delete;

7 个评论
显示 5更早的评论隐藏 5更早的评论

Walter Roberson 2011-7-19

Do you want to work with all of the .xls files in the directory? If so then you can use dir('*.xls') to get file information.

Az 2011-7-19

yes，thanks Walter. I'll try that.

请先登录，再进行评论。

Answer 2

ricco 2011-11-30

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/11907-multiple-import-edit-of-xls-files#answer_29830

在 MATLAB Online 中打开

I have a question which is similar to this. I have a code which loads data from several spreadsheets into excel:

files = dir('*.xls');
%read data from excel into matlab
for i=1:length(files);
    File_Name{i}=files(i,1).name;%Removes the file names from 'files'
    [num{i},txt{i},raw{i}] = xlsread(File_Name{i},'Ble min');
end

Where the loop imports the workbook entitled 'Ble min' from different spreadsheets. How could I use actxserver to do the same i.e. to speed the process up.

thanks