matlab 将统计年鉴xls中的部分数据重新整理到一个表xlsx中——excel的创建删除、sheet重命名、xls数据读取写入

08/10 09:18
阅读数 66

将统计年鉴xls中的部分数据重新整理到一个表xlsx中

前言

前言:这其中涉及matlab对excel的创建删除,sheet名的修改,从数据源的excel中读取数据,再将其写入新的xlsx中。其中有很多细节需要注意,具体见代码。

代码

该代码的目的是从原来的文件中读取四类作物的播种面积、单产量和总产量三项数据。
原始数据:
在这里插入图片描述
结果文件:
在这里插入图片描述
打开后显示:
在这里插入图片描述





clc;
clear;
t0 = cputime;
%----------------need to change based on your condition--------------------
data_dir = 'E:\you\数据源\';
output_dir = 'E:\you\';
%data_dir为数据源所在的位置
%output_dir为统计结果文件的位置,如2101豆类县级统计数据.xlsx文件所在位置
%--------------------------------------------------------------------------
excelpaths=[string([output_dir,'2101玉米县级统计数据.xlsx']),string([output_dir,'2101豆类县级统计数据.xlsx']), ...
        string([output_dir,'2101小麦县级统计数据.xlsx']),string([output_dir,'2101水稻县级统计数据.xlsx'])];
%如果文件存在则删除
if exist(char(excelpaths(1)),'file') == 2
   delete(char(excelpaths(1))) ;
end
if exist(char(excelpaths(2)),'file') == 2
   delete(char(excelpaths(2))) ;
end
if exist(char(excelpaths(3)),'file') == 2
   delete(char(excelpaths(3))) ;
end
if exist(char(excelpaths(4)),'file') == 2
   delete(char(excelpaths(4))) ;
end
xlswrite(char(excelpaths(1)),1);
xlswrite(char(excelpaths(2)),1);
xlswrite(char(excelpaths(3)),1);
xlswrite(char(excelpaths(4)),1);
%--------------------------------------------------------------------------
%                           遍历所有播种面积表
%找出玉米、小麦、水稻、豆类 所有地区该年的数据 放入玉米新表的播种面积列中,关闭新表格
%--------------------------------------------------------------------------
dirs=dir([data_dir,'*播种面积*2*.xls']);
dircell=struct2cell(dirs);
bozhong_fns_string = string(dircell(1,:));
[lines,samples]=size(bozhong_fns_string);
%fn=dircell(1,1)
sheet_id=1;%标记excel中sheet顺序,写入数据时需修改sheet名称
for i=2000:2016
    judge_cell = strfind(bozhong_fns_string,string(i));
    %第一个查找的元素为播种面积
    for num = 1:samples
       temp = isempty(judge_cell{num});
       %判断哪个cell不为空,不为空的就是找到的文件名
        if  temp == 0 
            selected_fn = bozhong_fns_string(num);
            break
        else 
            selected_fn = '';
        end
    end
    %以上,找到播种面积文件,下面开始读取相应类别的数据,如果找不到,则赋文件名为空
    if isempty(selected_fn)==1
        continue
    %否则,执行以下步骤: 
    end
    %----------------------------------------------------------------------
    %1豆类2玉米3水稻4小麦
    %打开数据源的表
    [numi,txti,rawi]=xlsread([data_dir,char(selected_fn)]);
    %----------------------------------------------------------------------
    %‘项目’or‘指标’那一行为城市名称,选择读取该行放置到新表的第一列中
    temp = char(rawi{:,1});
    temp_lines = size(temp,1);
    %获取城市名行号,j
    for j=1:temp_lines
        new_temp = temp(j,:);
        new_temp(find(isspace(new_temp)))=[];
        %new_temp(j,:)=strrep(temp(j,:),' ','');
        %strrep返回数组维度上的长度已经改变,不能赋值给原来的变量了,应该设置新变量
        second_temp = strfind(string(new_temp),string('指标'));
        third_temp = isempty(second_temp);
        forth_temp = strfind(string(new_temp),string('项目'));
        fifth_temp = isempty(forth_temp);
        if third_temp==0 || fifth_temp==0
           break 
        end
    end
    
    %获取玉米播种面积行号,k
    for k=1:temp_lines
        new_temp = temp(k,:);
        %new_temp(new_temp==' ')='';
        new_temp(find(isspace(new_temp)))=[];
        second_temp = strfind(string(new_temp),string('玉'));
        third_temp = isempty(second_temp);
        if  third_temp==0
           break 
        end
    end
    %获取豆类合计播种面积行号,mm
    for m=1:temp_lines
        new_temp = temp(m,:);
        %new_temp(new_temp==' ')='';
        new_temp(find(isspace(new_temp)))=[];
        second_temp = strfind(string(new_temp),string('豆类'));
        third_temp = isempty(second_temp);
        forth_temp = strfind(string(new_temp),string('大豆'));
        fifth_temp = isempty(forth_temp);
        if  third_temp==0
            mm=m;
           break 
        elseif fifth_temp==0
            mm=m;
            break
        end
    end
    %获取小麦播种面积行号,有小麦的计小麦,没有小麦的计春小麦,nn
    for n=1:temp_lines
        new_temp = temp(n,:);
        %new_temp(new_temp==' ')='';
        new_temp(find(isspace(new_temp)))=[];
        second_temp = strfind(string(new_temp),string('小麦'));
        third_temp = isempty(second_temp);
        if third_temp==0 && size(new_temp,2)==2
           nn=n;
           break 
        elseif third_temp==0 && size(new_temp,2)==3
           nn=n;
           break
        end
    end
    %获取水稻播种面积行号,有水稻的,计水稻;只有稻谷的,计稻谷,oo
    for o=1:temp_lines
        new_temp = temp(o,:);
        %new_temp(new_temp==' ')='';
        new_temp(find(isspace(new_temp)))=[];
        second_temp = strfind(string(new_temp),string('稻谷'));
        third_temp = isempty(second_temp);
        forth_temp = strfind(string(new_temp),string('水稻'));
        fifth_temp = isempty(forth_temp);
        if third_temp==0
           oo=o;
           break 
        elseif fifth_temp==0 && size(new_temp,2)==2
           oo=o;
           break
        elseif fifth_temp==0 && size(new_temp,2)~=2
            oo=o;
            break
        end
    end
    %----------------------------------------------------------------------
    %将数据写入excel中
    %----------------------------------------------------------------------
    %修改sheet名称
    
    e = actxserver('Excel.Application');
    for p=1:4
        if sheet_id <= 3 
            ewbp = e.Workbooks.Open(char(excelpaths(p)));  
            ewbp.Worksheets.Item(sheet_id).Name = num2str(i);
            ewbp.Save;                              
            ewbp.Close(false);  
        end
    end
    sheet_id = sheet_id+1;
    e.Quit;
    %有多少个城市
    city_num = size(char(txti{j,:}),1);%j为城市的行号
    output = cell(city_num,2);
    %不能一次赋值所有数据到output
    %output{:,1}= txti{j,:};
    %output{:,2}= txti{k,:};
    %城市&玉米
    for q=1:city_num
        if q==1
           output{q,1}='城市'; 
           output{q,2}='播种面积';
        else
           new_temp1 = rawi{j,q};
           new_temp1(find(isspace(new_temp1)))=[];
           new_temp2 = rawi{k,q};
           new_temp2(find(isspace(new_temp2)))=[];
           new_temp2(find(isnan(new_temp2)))=[];
           output{q,1} = new_temp1;
           output{q,2} = str2double(string(new_temp2));
        end     
    end
    xlswrite(char(excelpaths(1)),output,char(string(i)),['A1:B',char(string(city_num))]);
    
    %城市&豆类
    for q=1:city_num
        if q==1
           output{q,1}='城市'; 
           output{q,2}='播种面积';
        else
           new_temp1 = rawi{j,q};
           new_temp1(find(isspace(new_temp1)))=[];
           new_temp2 = rawi{mm,q};
           new_temp2(find(isspace(new_temp2)))=[];
           new_temp2(find(isnan(new_temp2)))=[];
           output{q,1} = new_temp1;
           output{q,2} = str2double(string(new_temp2));
           %disp(output)
        end     
    end
    xlswrite(char(excelpaths(2)),output,char(string(i)),['A1:B',char(string(city_num))]);

    %城市&小麦
    for q=1:city_num
        if q==1
           output{q,1}='城市'; 
           output{q,2}='播种面积';
        else
           new_temp1 = rawi{j,q};
           new_temp1(find(isspace(new_temp1)))=[];
           new_temp2 = rawi{nn,q};
           new_temp2(find(isspace(new_temp2)))=[];
           new_temp2(find(isnan(new_temp2)))=[];
           output{q,1} = new_temp1;
           output{q,2} = str2double(string(new_temp2));
        end     
    end
    xlswrite(char(excelpaths(3)),output,char(string(i)),['A1:B',char(string(city_num))]);
    
    %城市&水稻
    for q=1:city_num
        if q==1
           output{q,1}='城市'; 
           output{q,2}='播种面积';
        else
           new_temp1 = rawi{j,q};
           new_temp1(find(isspace(new_temp1)))=[];
           new_temp2 = rawi{oo,q};
           new_temp2(find(isspace(new_temp2)))=[];
           new_temp2(find(isnan(new_temp2)))=[];
           output{q,1} = new_temp1;
           output{q,2} = str2double(string(new_temp2));
        end     
    end
    xlswrite(char(excelpaths(4)),output,char(string(i)),['A1:B',char(string(city_num))]);   
    
end






%--------------------------------------------------------------------------
%                             将单产写入excel
%--------------------------------------------------------------------------
dirs=dir([data_dir,'*单产量*2*.xls']);
dircell=struct2cell(dirs);
perout_fns_string = string(dircell(1,:));
[lines,samples]=size(perout_fns_string);
%fn=dircell(1,1)
for i=2000:2016
    judge_cell = strfind(perout_fns_string,string(i));
    %第二个查找的元素为单产量
    for num = 1:samples
       temp = isempty(judge_cell{num});
       %判断哪个cell不为空,不为空的就是找到的文件名
        if  temp == 0 
            selected_fn = perout_fns_string(num);
            break
        else 
            selected_fn = '';
            %如果找不到,则赋文件名为空
        end
    end
    %以上,找到单产量文件,下面开始读取相应类别的数据
    if isempty(selected_fn)==1
        continue
    %否则,执行以下步骤: 
    end
    %----------------------------------------------------------------------
    %1豆类2玉米3水稻4小麦
    %打开数据源的表
    [numi,txti,rawi]=xlsread([data_dir,char(selected_fn)]);
    %----------------------------------------------------------------------
    %‘项目’or‘指标’那一行为城市名称,选择读取该行放置到新表的第一列中
    temp = char(rawi{:,1});
    temp_lines = size(temp,1);
    %获取城市名行号,j
    for j=1:temp_lines
        new_temp = temp(j,:);
        new_temp(find(isspace(new_temp)))=[];
        %new_temp(j,:)=strrep(temp(j,:),' ','');
        %strrep返回数组维度上的长度已经改变,不能赋值给原来的变量了,应该设置新变量
        second_temp = strfind(string(new_temp),string('指标'));
        third_temp = isempty(second_temp);
        forth_temp = strfind(string(new_temp),string('项目'));
        fifth_temp = isempty(forth_temp);
        if third_temp==0 || fifth_temp==0
           break 
        end
    end
    
    %获取玉米单产行号,k
    for k=1:temp_lines
        new_temp = temp(k,:);
        %new_temp(new_temp==' ')='';
        new_temp(find(isspace(new_temp)))=[];
        second_temp = strfind(string(new_temp),string('玉'));
        third_temp = isempty(second_temp);
        if  third_temp==0
           break 
        end
    end
    %获取豆类合计单产行号,mm
    for m=1:temp_lines
        new_temp = temp(m,:);
        %new_temp(new_temp==' ')='';
        new_temp(find(isspace(new_temp)))=[];
        second_temp = strfind(string(new_temp),string('豆类'));
        third_temp = isempty(second_temp);
        forth_temp = strfind(string(new_temp),string('大豆'));
        fifth_temp = isempty(forth_temp);
        if  third_temp==0
            mm=m;
           break 
        elseif fifth_temp==0
            mm=m;
            break
        end
    end
    %获取小麦单产行号,有小麦的计小麦,没有小麦的计春小麦,nn
    for n=1:temp_lines
        new_temp = temp(n,:);
        %new_temp(new_temp==' ')='';
        new_temp(find(isspace(new_temp)))=[];
        second_temp = strfind(string(new_temp),string('小麦'));
        third_temp = isempty(second_temp);
        if third_temp==0 && size(new_temp,2)==2
           nn=n;
           break 
        elseif third_temp==0 && size(new_temp,2)==3
           nn=n;
           break
        end
    end
    %获取水稻单产行号,有水稻的,计水稻;只有稻谷的,计稻谷,oo
    for o=1:temp_lines
        new_temp = temp(o,:);
        %new_temp(new_temp==' ')='';
        new_temp(find(isspace(new_temp)))=[];
        second_temp = strfind(string(new_temp),string('稻谷'));
        third_temp = isempty(second_temp);
        forth_temp = strfind(string(new_temp),string('水稻'));
        fifth_temp = isempty(forth_temp);
        if third_temp==0
           oo=o;
           break 
        elseif fifth_temp==0 && size(new_temp,2)==2
           oo=o;
           break
        elseif fifth_temp==0 && size(new_temp,2)~=2
            oo=o;
            break
        end
    end
    %----------------------------------------------------------------------
    %将数据写入excel中
    %----------------------------------------------------------------------
    %有多少个城市
    city_num = size(char(txti{j,:}),1);%j为城市的行号
    output = cell(city_num,1);
    %不能一次赋值所有数据到output
    %output{:,1}= txti{j,:};
    %output{:,2}= txti{k,:};
    %城市&玉米
    for q=1:city_num
        if q==1
           output{q,1}='单产量'; 
        else
           new_temp2 = rawi{k,q};
           new_temp2(find(isspace(new_temp2)))=[];
           new_temp2(find(isnan(new_temp2)))=[];
           output{q,1} = str2double(string(new_temp2));
        end     
    end
    xlswrite(char(excelpaths(1)),output,char(string(i)),['C1:C',char(string(city_num))]);
    
    %城市&豆类
    for q=1:city_num
        if q==1
           output{q,1}='单产量'; 
        else
           new_temp2 = rawi{mm,q};
           new_temp2(find(isspace(new_temp2)))=[];
           new_temp2(find(isnan(new_temp2)))=[];
           
           output{q,1} = str2double(string(new_temp2));
           %disp(output)
        end     
    end
    xlswrite(char(excelpaths(2)),output,char(string(i)),['C1:C',char(string(city_num))]);

    %城市&小麦
    for q=1:city_num
        if q==1
           output{q,1}='单产量'; 
        else
           
           new_temp2 = rawi{nn,q};
           new_temp2(find(isspace(new_temp2)))=[];
           new_temp2(find(isnan(new_temp2)))=[];
           
           output{q,1} = str2double(string(new_temp2));
        end     
    end
    xlswrite(char(excelpaths(3)),output,char(string(i)),['C1:C',char(string(city_num))]);
    
    %城市&水稻
    for q=1:city_num
        if q==1
           output{q,1}='单产量'; 
        else
           
           new_temp2 = rawi{oo,q};
           new_temp2(find(isspace(new_temp2)))=[];
           new_temp2(find(isnan(new_temp2)))=[];
           
           output{q,1} = str2double(string(new_temp2));
        end     
    end
    xlswrite(char(excelpaths(4)),output,char(string(i)),['C1:C',char(string(city_num))]);   
    
end





%--------------------------------------------------------------------------
%                             将总产写入excel
%--------------------------------------------------------------------------
dirs=dir([data_dir,'*总产量*2*.xls']);
dircell=struct2cell(dirs);
totalout_fns_string = string(dircell(1,:));
[lines,samples]=size(totalout_fns_string);
%fn=dircell(1,1)
for i=2000:2016
    judge_cell = strfind(totalout_fns_string,string(i));
    %第三个查找的元素为总产量
    for num = 1:samples
       temp = isempty(judge_cell{num});
       %判断哪个cell不为空,不为空的就是找到的文件名
        if  temp == 0 
            selected_fn = totalout_fns_string(num);
            break
        else 
            selected_fn = '';
            %如果找不到,则赋文件名为空
        end
    end
    %以上,找到总产量文件,下面开始读取相应类别的数据
    if isempty(selected_fn)==1
        continue
    %否则,执行以下步骤: 
    end
    %----------------------------------------------------------------------
    %1豆类2玉米3水稻4小麦
    %打开数据源的表
    [numi,txti,rawi]=xlsread([data_dir,char(selected_fn)]);
    %----------------------------------------------------------------------
    %‘项目’or‘指标’那一行为城市名称,选择读取该行放置到新表的第一列中
    temp = char(rawi{:,1});
    temp_lines = size(temp,1);
    %获取城市名行号,j
    for j=1:temp_lines
        new_temp = temp(j,:);
        new_temp(find(isspace(new_temp)))=[];
        %new_temp(j,:)=strrep(temp(j,:),' ','');
        %strrep返回数组维度上的长度已经改变,不能赋值给原来的变量了,应该设置新变量
        second_temp = strfind(string(new_temp),string('指标'));
        third_temp = isempty(second_temp);
        forth_temp = strfind(string(new_temp),string('项目'));
        fifth_temp = isempty(forth_temp);
        if third_temp==0 || fifth_temp==0
           break 
        end
    end
    
    %获取玉米总产行号,k
    for k=1:temp_lines
        new_temp = temp(k,:);
        %new_temp(new_temp==' ')='';
        new_temp(find(isspace(new_temp)))=[];
        second_temp = strfind(string(new_temp),string('玉'));
        third_temp = isempty(second_temp);
        if  third_temp==0
           break 
        end
    end
    %获取豆类合计总产行号,mm
    for m=1:temp_lines
        new_temp = temp(m,:);
        %new_temp(new_temp==' ')='';
        new_temp(find(isspace(new_temp)))=[];
        second_temp = strfind(string(new_temp),string('豆类'));
        third_temp = isempty(second_temp);
        forth_temp = strfind(string(new_temp),string('大豆'));
        fifth_temp = isempty(forth_temp);
        if  third_temp==0
            mm=m;
           break 
        elseif fifth_temp==0
            mm=m;
            break
        end
    end
    %获取小麦总产行号,有小麦的计小麦,没有小麦的计春小麦,nn
    for n=1:temp_lines
        new_temp = temp(n,:);
        %new_temp(new_temp==' ')='';
        new_temp(find(isspace(new_temp)))=[];
        second_temp = strfind(string(new_temp),string('小麦'));
        third_temp = isempty(second_temp);
        if third_temp==0 && size(new_temp,2)==2
           nn=n;
           break 
        elseif third_temp==0 && size(new_temp,2)==3
           nn=n;
           break
        end
    end
    %获取水稻总产行号,有水稻的,计水稻;只有稻谷的,计稻谷,oo
    for o=1:temp_lines
        new_temp = temp(o,:);
        %new_temp(new_temp==' ')='';
        new_temp(find(isspace(new_temp)))=[];
        second_temp = strfind(string(new_temp),string('稻谷'));
        third_temp = isempty(second_temp);
        forth_temp = strfind(string(new_temp),string('水稻'));
        fifth_temp = isempty(forth_temp);
        if third_temp==0
           oo=o;
           break 
        elseif fifth_temp==0 && size(new_temp,2)==2
           oo=o;
           break
        elseif fifth_temp==0 && size(new_temp,2)~=2
            oo=o;
            break
        end
    end
    %----------------------------------------------------------------------
    %将数据写入excel中
    %----------------------------------------------------------------------
    %有多少个城市
    city_num = size(char(txti{j,:}),1);%j为城市的行号
    output = cell(city_num,1);
    %不能一次赋值所有数据到output
    %output{:,1}= txti{j,:};
    %output{:,2}= txti{k,:};
    %城市&玉米
    for q=1:city_num
        if q==1
           output{q,1}='总产量'; 
        else
           new_temp2 = rawi{k,q};
           new_temp2(find(isspace(new_temp2)))=[];
           new_temp2(find(isnan(new_temp2)))=[];
           output{q,1} = str2double(string(new_temp2));
        end     
    end
    xlswrite(char(excelpaths(1)),output,char(string(i)),['D1:D',char(string(city_num))]);
    
    %城市&豆类
    for q=1:city_num
        if q==1
           output{q,1}='总产量'; 
        else
           new_temp2 = rawi{mm,q};
           new_temp2(find(isspace(new_temp2)))=[];
           new_temp2(find(isnan(new_temp2)))=[];
           
           output{q,1} = str2double(string(new_temp2));
           %disp(output)
        end     
    end
    xlswrite(char(excelpaths(2)),output,char(string(i)),['D1:D',char(string(city_num))]);

    %城市&小麦
    for q=1:city_num
        if q==1
           output{q,1}='总产量'; 
        else
           
           new_temp2 = rawi{nn,q};
           new_temp2(find(isspace(new_temp2)))=[];
           new_temp2(find(isnan(new_temp2)))=[];
           
           output{q,1} = str2double(string(new_temp2));
        end     
    end
    xlswrite(char(excelpaths(3)),output,char(string(i)),['D1:D',char(string(city_num))]);
    
    %城市&水稻
    for q=1:city_num
        if q==1
           output{q,1}='总产量'; 
        else
           
           new_temp2 = rawi{oo,q};
           new_temp2(find(isspace(new_temp2)))=[];
           new_temp2(find(isnan(new_temp2)))=[];
           
           output{q,1} = str2double(string(new_temp2));
        end     
    end
    xlswrite(char(excelpaths(4)),output,char(string(i)),['D1:D',char(string(city_num))]);   
    
end
disp('统计完成,结束运行!');
t1 = cputime;
during = t1 - t0;
disp('耗时:');
disp(during);
展开阅读全文
打赏
1
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
1
分享
OSCHINA
登录后可查看更多优质内容
返回顶部
顶部