matlab将每一行从不同的excel文件连接到新的excel文件 [英] matlab concatenate each row from different excel file to new excel file
问题描述
将每个excel文件的第一页的每一行连接成新的excel文件，名为'final'
，每第二行连续将每个excel文件的第一张作为新的excel文件，名为'final'
，将每个excel文件的第1页的第3行连接成新的名为'final'的新excel文件的sheet1。
然后
将每个excel文件的第二张表中的第一行连接成新的excel文件中的新的sheet2，名为'final'
将每个excel文件的第二页的每第二行连接成新的excel文件中的新的excel文件，名为'final'
将每个excel文件的第二张表格中的第3行连接成新的excel文件中的最终'....
重复...为所有5张... ...
示例：
excel文件1，sheet1
30 4 1.6 1.2 1.2 1.0
35 16 0.9 0.9 1.5 1.0
40 62 0.9 0.9 1.6 1.2
45 3 0.9 0.9 0.9 0.9
50 1 1.5 1.5 0.8 0.8
excel文件2，sheet1
10 1 0.8 0.9 0.9 0.9
15 31 0.9 0.9 1.2 1.6
20 2 0.9 0.9 0.9 0.9
25 3 0.9 0.9 0.9 0.9
30 18 0.9 0.9 0.9 0.9
excel文件3，sheet1到excel文件10，表1等...
结果我想要得到
final.xls，sheet1
30 4 1.6 1.2 1.2 1.0％excel文件中的第一行sheet1
10 1 0.8 0.9 0.9 0.9％excel文件中的第一行sheet1
...％重复excel文件中的第一行sheet1到10
35 16 0.9 0.9 1.5 1.0％第2行sheet1 in excel文件1
15 31 0.9 0.9 1.2 1.6％excel文件中的第2行sheet1
...％重复Excel文件中第2行sheet1到10
final.xls，sheet2
％类似于sheet1只是从sheet2读取的数据。
有没有人可以帮助我？
我创建了10个code> .xls 文件进行测试，每张5张。所有片材都有5×6个随机数的细胞。这是我的第一个解决方案：
％＃获取输入XLS文件
dName = uigetdir（'。'文件夹包含Excel XLS文件'）;
如果dName == 0，错误（'没有选择文件夹'）; end
files = dir（fullfile（dName，'*。xls'））;
files = strcat（dName，filesep，{files.name}'）; ％'
％＃准备输出XLS文件
[fName dName] = uiputfile（{'*。xls''Excel（* .xls）'}，'输出文件'的.xls'）;
如果dName == 0，错误（'无文件选择'）; end
fOut = fullfile（dName，fName）;
％＃process
NUM_SHEETS = 5; ％＃每个文件的页数
s = 1：NUM_SHEETS
％＃从所有文件中提取相同表单的内容
numData = cell（numel（files），1）;
for f = 1：numel（files）
numData {f} = xlsread（files {f}，s）;
end
％＃重排数据
numData = cat（3，numData {：}）;
numData = reshape（permute（numData，[3 1 2]），[]，size（numData，2））;
％＃将数据写入相应的输出表XLS文件
xlswrite（fOut，numData，s）;
end
这很慢。它花了3分钟完成...原因是一个连接到Excel自动化服务器，然后在每次调用 XLSREAD / XLSWRITE 。在这方面，这两个功能可以隐藏许多与Excel进行交互，并显示一个易于使用的界面。
在我的第二个解决方案中，我手动调用 Excel COM API 。优点是我们只启动一次，一旦完成就拆掉它，消除了很多开销。实际上，这段代码执行的时间少于 4秒！
％＃获取输入XLS文件
dName = uigetdir（'。'，'选择包含Excel XLS文件的文件夹'）;
如果dName == 0，错误（'没有选择文件夹'）; end
files = dir（fullfile（dName，'*。xls'））;
files = strcat（dName，filesep，{files.name}'）; ％'
％＃获取输出XLS文件
[fName dName] = uiputfile（{'*。xls''Excel（* .xls）'}，'输出文件'的.xls'）;
如果dName == 0，错误（'无文件选择'）; end
fOut = fullfile（dName，fName）;
％＃打开Excel COM Server
Excel = actxserver（'Excel.Application'）;
Excel.DisplayAlerts = 0;
％＃准备输出
如果〜存在（fOut，'file'）
％＃创建如果不存在
wb = Excel.workbooks.Add;
wb.SaveAs（fOut，1）;
wb.Close（false）;
else
％＃删除现有文件
delete（fOut）;
end
％＃提取输入文件的内容
NUM_SHEETS = 5;
data = cell（numel（files），NUM_SHEETS）;
for f = 1：numel（files）
wb = Excel.Workbooks.Open（files {f}，0，true）; ％＃打开XLS文件读
assert（wb.sheets.Count == NUM_SHEETS）;
s = 1：NUM_SHEETS％＃循环所有表
％＃激活表，并提取整个内容
Excel.sheets.get（'item'，s）.Activate（）;
Excel.Range（'A1'）。Activate（）;
data {f，s} = cell2num（Excel.ActiveSheet.UsedRange.Value）;
end
wb.Close（false）; ％＃关闭XLS文件
end
％＃重排数据
D =单元格（NUM_SHEETS，1）;
for s = 1：NUM_SHEETS
x = cat（3，data {：，s}）;
D {s} = reshape（permute（x，[3 1 2]），[]，size（x，2））;
end
％＃将数据写入XLS文件的输出文件
wb = Excel.Workbooks.Open（fOut，0，false）; ％＃打开XLS文件写
，而Excel.Sheets.Count< NUM_SHEETS％＃根据需要创建工作表
Excel.Sheets.Add（[]，Excel.Sheets.Item（Excel.Sheets.Count））;
end
for s = 1：NUM_SHEETS％＃写入每个表单的圆心
cellRange = sprintf（'A1：％s％d'，'A'+ size（D {s} 2）-1，size（D {s}，1））;
wb.sheets.get（'item'，s）.Activate（）;
Excel.Range（cellRange）.Select（）;
set（Excel.selection，'Value'，num2cell（D {s}））;
end
wb.Save（）;
wb.Close（false）; ％＃关闭XLS文件
％＃cleanup
Excel.Quit（）;
Excel.delete（）;
清除Excel;
我相信已经有提交的 执行类似操作的FEX ...
I have a folder include 10 excel files. Each of the excel file contain 5 sheets. I would like to
concatenate every 1st row in 1st sheet of each excel file into new sheet1 in new excel file named 'final' concatenate every 2nd row in 1st sheet of each excel file into new sheet1 in new excel file named 'final' concatenate every 3rd row in 1st sheet of each excel file into new sheet1 in new excel file named 'final' ....
then
concatenate every 1st row in 2nd sheet of each excel file into new sheet2 in new excel file named 'final' concatenate every 2nd row in 2nd sheet of each excel file into new sheet2 in new excel file named 'final' concatenate every 3rd row in 2nd sheet of each excel file into new sheet2 in new excel file named 'final' ....
repeatedly...do for all 5 sheets...
example:
excel file 1, sheet1
30 4 1.6 1.2 1.2 1.0
35 16 0.9 0.9 1.5 1.0
40 62 0.9 0.9 1.6 1.2
45 3 0.9 0.9 0.9 0.9
50 1 1.5 1.5 0.8 0.8
excel file 2, sheet1
10 1 0.8 0.9 0.9 0.9
15 31 0.9 0.9 1.2 1.6
20 2 0.9 0.9 0.9 0.9
25 3 0.9 0.9 0.9 0.9
30 18 0.9 0.9 0.9 0.9
excel file 3, sheet1 to excel file 10, sheet 1 etc...
the result i would like to get
final.xls, sheet1
30 4 1.6 1.2 1.2 1.0 %1st row of sheet1 in excel file 1
10 1 0.8 0.9 0.9 0.9 %1st row of sheet1 in excel file 2
... %repeated 1st row of sheet1 in excel file 3 to 10
35 16 0.9 0.9 1.5 1.0 %2nd row of sheet1 in excel file 1
15 31 0.9 0.9 1.2 1.6 %2nd row of sheet1 in excel file 2
... %repeated 2nd row of sheet1 in excel file 3 to 10
final.xls, sheet2
%similar to sheet1 just the data read from sheet2..
Does anyone can help me?
I created 10 .xls files for testing with 5 sheets each. All sheets have 5x6 cells of random numbers. Here is my first solution:
%# get input XLS files
dName = uigetdir('.', 'Select folder containing Excel XLS files');
if dName==0, error('No folder selected'); end
files = dir( fullfile(dName,'*.xls') );
files = strcat(dName, filesep, {files.name}'); %'
%# prepare output XLS file
[fName dName] = uiputfile({'*.xls' 'Excel (*.xls)'}, 'Output File', 'final.xls');
if dName==0, error('No file selected'); end
fOut = fullfile(dName,fName);
%# process
NUM_SHEETS = 5; %# number of sheets per file
for s=1:NUM_SHEETS
%# extract contents of same sheet from all files
numData = cell(numel(files),1);
for f=1:numel(files)
numData{f} = xlsread(files{f}, s);
end
%# rearrange data
numData = cat(3,numData{:});
numData = reshape(permute(numData,[3 1 2]), [], size(numData,2));
%# write data to corresponding sheet of output XLS file
xlswrite(fOut, numData, s);
end
This was quite slow. It took around 3 minutes to finish... The reason is that a connection to Excel automation server is created then destroyed repeatedly in each call to XLSREAD/XLSWRITE. On the plus side, these two functions hide away a lot of the dirty work needed to interact with Excel, and expose an easy-to-use interface.
In my second solution, I manually call the Excel COM API. The advantage is that we initiate it only one time, and tear it down once we are finished, eliminating a lot of overhead. In fact, this code executes in less than 4 seconds!:
%# get input XLS files
dName = uigetdir('.', 'Select folder containing Excel XLS files');
if dName==0, error('No folder selected'); end
files = dir( fullfile(dName,'*.xls') );
files = strcat(dName, filesep, {files.name}'); %'
%# get output XLS file
[fName dName] = uiputfile({'*.xls' 'Excel (*.xls)'},'Output File','final.xls');
if dName==0, error('No file selected'); end
fOut = fullfile(dName,fName);
%# open Excel COM Server
Excel = actxserver('Excel.Application');
Excel.DisplayAlerts = 0;
%# prepare output
if ~exist(fOut, 'file')
%# create if doesnt exist
wb = Excel.workbooks.Add;
wb.SaveAs(fOut,1);
wb.Close(false);
else
%# delete existing file
delete(fOut);
end
%# extract contents of input files
NUM_SHEETS = 5;
data = cell(numel(files),NUM_SHEETS);
for f=1:numel(files)
wb = Excel.Workbooks.Open(files{f}, 0, true); %# open XLS file for reading
assert( wb.sheets.Count == NUM_SHEETS );
for s=1:NUM_SHEETS %# loop over all sheets
%# activate sheet, and extract entire content
Excel.sheets.get('item',s).Activate();
Excel.Range('A1').Activate();
data{f,s} = cell2num( Excel.ActiveSheet.UsedRange.Value );
end
wb.Close(false); %# close XLS file
end
%# rearrange data
D = cell(NUM_SHEETS,1);
for s=1:NUM_SHEETS
x = cat(3,data{:,s});
D{s} = reshape(permute(x,[3 1 2]), [], size(x,2));
end
%# write data to sheets of output XLS file
wb = Excel.Workbooks.Open(fOut, 0, false); %# open XLS file for writing
while Excel.Sheets.Count < NUM_SHEETS %# create sheets as required
Excel.Sheets.Add([], Excel.Sheets.Item(Excel.Sheets.Count));
end
for s=1:NUM_SHEETS %# write conents to each sheet
cellRange = sprintf('A1:%s%d', 'A'+size(D{s},2)-1, size(D{s},1));
wb.sheets.get('item',s).Activate();
Excel.Range(cellRange).Select();
set(Excel.selection, 'Value',num2cell(D{s}));
end
wb.Save();
wb.Close(false); %# close XLS file
%# cleanup
Excel.Quit();
Excel.delete();
clear Excel;
I believe there are already submissions on the FEX that do a similar thing...
这篇关于matlab将每一行从不同的excel文件连接到新的excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
