使用MATLAB将行插入Excel [英] Insert rows into Excel with MATLAB

问题描述

我的Excel文件中的数据应该有争议(第一列中的索引).但是文件中缺少一些数据.例如，#5和6在$ 4和7之间丢失.我的目的是(1)标识丢失数据的文件，以及(2)如果数据丢失，请插入行以使其连续.谁能告诉我如何在现有数据中添加行?使用xlswrite，我只能在文件末尾添加行或替换一些行.

我还有另一组文件的索引不是那么直接.前三列如下所述(如Excel文件所示):

• 第1栏:2003年(在matlab中读取为数字)
• 第2列:日期:1月9日(在matlab中作为文本读取)
• 第3列:时间:1:00(1:00读为0.04167，而2:00读为0.0833，不确定其工作方式)

然后，判断其是否连续的方式将非常复杂，因为年份，月份和天数将有所不同.您能对此提供一些提示吗?

解决方案

基本上，您需要读取整个数据，最好以raw(cell)格式，添加缺失的行(相对于索引)并写回./p>

根据您的问题，此代码可能有效-

 ％注意:我们假设索引从1开始％从输入的Excel文件中读取缺少索引的数据[num，txt，raw] = xlsread('input.xls');错误检查百分比如果(size(num，1)-num(end，1)〜= 0)disp('至少缺少一个索引！');结尾％扩展数据，以便覆盖所有索引.data1 = NaN(num(end，1)，size(raw，2));data1(:，1)= 1:num(end，1);data1 = num2cell(data1);k1 = 1;对于k = 1:num(end，1)if(num(k1,1)== k)data1(k，:)= raw(k1，:);k1 = k1 + 1;结尾结尾％写入数据xlswrite('output.xls'，data1); 

鉴于您的新要求，接下来将添加其他代码.

请注意有关此代码的几件事-

1. 代码将每年添加数据，而不是从特定的月份，日期和时间添加到另一个特定的月份，日期和时间.如果您希望达到此目的，请编辑相关的函数-'create_comp_sheet'.
2. 它将保存一个名为"proper_base_data.xls"的中间文件，该文件可能会在代码末尾删除.

  %%主要代码-CODE1.MINPUT_FILENAME ='input.xls';％缺少年份，日期和时间信息的Excel文件OUTPUT_FILENAME ='output.xls';Excel文件的百分比，其中包含来自输​​入文件的数据以及所有缺少的年份，日期和时间信息%%基础数据start_year = 2003;end_year = 2005;normal_base_data = create_comp_sheet(start_year，end_year);xlswrite('proper_base_data.xls'，proper_base_data);[num，txt，raw] = xlsread('proper_base_data.xls');base_data = cell(size(num，1)，1);对于row_ID = 1:size(num，1)base_data(row_ID)= {strcat(num2str(cell2mat(raw(row_ID，1)))，'-'，cell2mat(raw(row_ID，2))，'-'，num2str(round(24 * cell2mat(raw(row_ID)，3)))))}};结尾％％ 输入数据[num，txt，raw] = xlsread(INPUT_FILENAME);input_data = cell(size(num，1)，1);对于row_ID = 1:size(num，1)input_data(row_ID)= {strcat(num2str(cell2mat(raw(row_ID，1)))，'-'，cell2mat(raw(row_ID，2))，'-'，num2str(round(24 * cell2mat(raw(row_ID)，3)))))}};结尾%%设置最终数据final_data = num2cell(NaN(size(proper_base_data，1)，size(raw，2)));final_data(:，1:3)= proper_base_data;对于k1 = 1:size(input_data，1)对于k2 = 1:size(base_data，1)如果是strcmp(cell2mat(base_data(k2))，cell2mat(input_data(k1)))final_data(k2,4:end)=原始(k1,4:end);结尾结尾结尾%%将最终数据写入Excelxlswrite(OUTPUT_FILENAME，final_data); 

关联功能-

 功能数据1 = create_comp_sheet(start_year，end_year)months_string = {'Jan'，'Feb'，'Mar'，'Apr'，'May'，'Jun'，'Jul'，'Aug'，'Sep'，'Oct'，'Nov'，'Dec'};date_count = [31 28 31 30 31 30 31 31 30 31 30 31]；num_hours = 24;data1 = [];对于year_ID = start_year:end_year对于month_ID = 1:numel(months_string)days_per_month = date_count(month_ID);如果rem(year_ID，4)== 0&&month_ID == 2days_per_month = days_per_month + 1;结尾对于date_ID = 1:days_per_monthyear = repmat({num2str(year_ID)}，[num_hours 1]);date = repmat({strcat(num2str(date_ID)，'-'，char(months_string(month_ID)))}，[num_hours 1]);;time = cell(num_hours，1);对于k = 1:num_hourstime(k)= {strcat(num2str(k)，':00')};结尾data1 = [data1;[年日期时间]]；结尾结尾结尾返回; 

希望这可以节省您的所有麻烦！

The data in my Excel files is supposed to be contentious (index in the first column). But some data is missing in the file. For example, # 5, and 6 are missing between $ 4 and 7. My purpose are (1) identify the file with missing data and (2) if data is missing insert rows to make it continuous. Can anyone tell me how to add in rows in the existing data? Using xlswrite I can only add in rows at the end of the file or replace some rows.

EDIT 1:

I have another set of file in which the index is not so direct. The first 3 columns are described below (as shown in the Excel file):

• Column 1：Year: 2003 (read as number in matlab)
• Column 2：Date: 1-Sep (read as text in matlab)
• Column 3：Time: 1:00 (1:00 read as number 0.04167 and 2:00 read as 0.0833, not sure how it works)

Then the way to tell if it is continuous will be quite complicate since there will be different years, months, and days. Could you give some hint on this?

解决方案

Basically you need to read the entire data, preferably in raw(cell) format, add the missing rows(with respect to the indices) and write back.

Based on your question, this code might work -

% NOTE: We are assuming that the indexing starts with 1

% Read data from input excel file with missing indices
[num,txt,raw] = xlsread('input.xls');

% Error-checking
if (size(num,1)-num(end,1)~=0)
    disp('At least one index is  missing!');
end

% Expand data such that all indices are covered.
data1=NaN(num(end,1),size(raw,2));
data1(:,1) = 1:num(end,1);
data1=num2cell(data1);

k1=1;
for k = 1:num(end,1)
    if(num(k1,1)==k)
        data1(k,:)= raw(k1,:);
        k1 = k1+1;
    end
end

% Write data
xlswrite('output.xls',data1);

EDIT 1: In view of your new requirements, additional code is added next.

Please note few things about this code -

1. The code adds data for every year and not from a specific month, date and time to another specific month, date and time. If you wish to achieve that, please edit the associated function - 'create_comp_sheet'.
2. It saves an intermediate file named - 'proper_base_data.xls', which maybe deleted at the end of the code.

%% MAIN CODE - CODE1.M
INPUT_FILENAME = 'input.xls'; % Excel file that has some missing year,date and time info
OUTPUT_FILENAME = 'output.xls'; % Excel file that has data from the input file along with all the missing year,date and time info

%% Base data
start_year=2003;
end_year=2005;
proper_base_data = create_comp_sheet(start_year,end_year);
xlswrite('proper_base_data.xls',proper_base_data);

[num,txt,raw] = xlsread('proper_base_data.xls');
base_data=cell(size(num,1),1);
for row_ID = 1:size(num,1)
    base_data(row_ID) = {strcat(num2str(cell2mat(raw(row_ID,1))),'-', cell2mat(raw(row_ID,2)),'-',num2str(round(24*cell2mat(raw(row_ID,3)))))};
end

%% Input data
[num,txt,raw] = xlsread(INPUT_FILENAME);
input_data=cell(size(num,1),1);
for row_ID = 1:size(num,1)
    input_data(row_ID) = {strcat(num2str(cell2mat(raw(row_ID,1))),'-', cell2mat(raw(row_ID,2)),'-',num2str(round(24*cell2mat(raw(row_ID,3)))))};
end

%% Setup final data
final_data = num2cell(NaN(size(proper_base_data,1),size(raw,2)));
final_data(:,1:3) = proper_base_data;

for k1=1:size(input_data,1)
    for k2=1:size(base_data,1)
        if strcmp(cell2mat(base_data(k2)),cell2mat(input_data(k1)))
            final_data(k2,4:end) = raw(k1,4:end);
        end
    end
end

%% Write final data to excel 
xlswrite(OUTPUT_FILENAME,final_data);

Associated function -

function data1 = create_comp_sheet(start_year,end_year)

months_string = {'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'};
date_count = [31 28 31 30 31 30 31 31 30 31 30 31];
num_hours = 24;

data1=[];
for year_ID = start_year:end_year
    for month_ID = 1:numel(months_string)

        days_per_month = date_count(month_ID);
        if rem(year_ID,4)==0 && month_ID ==2
            days_per_month = days_per_month+1;
        end

        for date_ID = 1:days_per_month

            year = repmat({num2str(year_ID)},[num_hours 1]);
            date = repmat({strcat(num2str(date_ID),'-',char(months_string(month_ID)))},[num_hours 1]);
            time=cell(num_hours,1);

            for k = 1:num_hours
                time(k) = {strcat(num2str(k),':00')};
            end

            data1 = [data1 ; [year date time]];
        end
    end

end

return;

Hope this saves all your troubles!

这篇关于使用MATLAB将行插入Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！