Multiple import/edit of xls files

5 次查看（过去 30 天）
Az
Az 2011-7-19
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 个评论
显示 5更早的评论
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

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by