Multiple import/edit of xls files

Az
2011 7 19
2 个回答
回答已采纳
3 次查看（30 天）

1 个投票

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

1 个投票

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更早的评论

Az
Az 2011-7-19
First, many thanks to you,Chirag Gupta
This is like the second time I work with Matlab.
Apologies if my questions are dumb.
Is this code for Matlab?
1)How can I deal with the filenames? they are like 43423232.xls,5485747.xls with NO pattern.
2) in addtion, I want to delete the 1,2,5,7 columns in the second sheet as well how do I do that?
Thank you so much!!!!!!
Az
Az 2011-7-19
one more, I want to save the converted file with the same filename as of the xls file How should I tweak that?
Thanks!!!
Chirag Gupta
Chirag Gupta 2011-7-19
This is MATLAB code!
I am assuming that you have a list of filenames in a cell array. The code above is quite complex for a beginner especially if you are not familiar with VBA.
filenames = {'79802.xls','682038.xls',.... and so on};
Also to delete particular Columns, you will need to look on the MSDN website.
But it is typically:
Wkbk.ActiveSheet.Cells.Range('C1').EntireColumn.Delete;
Remember after you delete column C, column D will automatically become column C. Take that into account when deleting columns successively
Chirag Gupta
Chirag Gupta 2011-7-19
To save it with the same name:
You need to change the SaveAs command and replace it filename with the name you want
Az
Az 2011-7-19
Thanks Chirag,
So basically, I need to type in all the filenames manually?
Many thanks!
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

0 个投票

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

类别

在 帮助中心 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by