Multiple import/edit of xls files

5 次查看(过去 30 天)
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

采纳的回答

Chirag Gupta
Chirag Gupta 2011-7-19
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 个评论
Walter Roberson
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
Az 2011-7-19
yes,thanks Walter. I'll try that.

请先登录,再进行评论。

更多回答(1 个)

ricco
ricco 2011-11-30
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

Community Treasure Hunt

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

Start Hunting!

Translated by