how to delete the empty row in the excel with matlab

15 次查看（过去 30 天）
wenchao zhang
wenchao zhang 2024-8-16
评论： wenchao zhang 2024-8-16
hi, as the title description, i want use matlab to delete the empty rows in the excel which has many rows, how to realize.

请先登录，再回答此问题。

采纳的回答

nick
nick 2024-8-16
Hi Wenchao,
You can delete the empty rows from an excel sheet by writing the post-processed data into an .XLSX file. You can identify the missing rows with the help of the following functions :
'ismissing' : it returns a logical array indicating the missing entries.
'all' : it determines if all the elements of the array are nonzero
% Step 1: Read the Excel file into MATLAB
filename = 'filename.xlsx'; % Replace with your file name
sheetName = 'Sheet1'; % Specify the sheet name if necessary
data = readtable(filename, 'Sheet', sheetName);
% Step 2: Identify and remove empty rows
emptyRows = all(ismissing(data), 2);
data_cleaned = data(~emptyRows, :);
% Step 3: Write the cleaned data back to the Excel file
writetable(data_cleaned, 'cleaned_file.xlsx', 'Sheet', sheetName);
You may refer to the following documentation to learn more about the functions 'ismissing' and 'all' :
https://www.mathworks.com/help/matlab/ref/all.html
https://www.mathworks.com/help/matlab/ref/ismissing.html
Hope this helps!
  4 个评论
显示 2更早的评论
wenchao zhang
wenchao zhang 2024-8-16
% Step 1: Read the Excel file into MATLAB
[filename, pathname] = uigetfile( ...
{'*.xlsx';'*.*'}, ...
'Pick a file');
[filepath,name,ext] = fileparts(filename);
sheetName = 'Sheet1'; % Specify the sheet name if necessary
data = readtable(filename, 'Sheet', sheetName);
% Step 2: Identify and remove empty rows
emptyRows = all(ismissing(data), 2);
data_cleaned = data(~emptyRows, :);
data_cleaned = table2cell(data_cleaned);
% Step 3: Write the cleaned data back to the Excel file
xlswrite([name,'_new.xlsx'],data_cleaned);
wenchao zhang
wenchao zhang 2024-8-16
d = dialog('Position',[300 300 250 150],'Name','My Dialog');
txt = uicontrol('Parent',d,...
'Style','text',...
'Position',[20 80 210 40],...
'String','Clear All Value?');
btn = uicontrol('Parent',d,...
'Position',[85 20 70 25],...
'String','Yes',...
'Callback','delete(gcf)');
uiwait(d);
clc;
clear;
[filename, pathname] = uigetfile( ...
{'*.xlsx';'*.*'}, ...
'Pick a file');
[filepath,name,ext] = fileparts(filename);
sheetName = 'Sheet1'; % Specify the sheet name if necessary
data = readtable(filename, 'Sheet', sheetName);
% Step 2: Identify and remove empty rows
emptyRows = all(ismissing(data), 2);
data_cleaned = data(~emptyRows, :);
data_cleaned = table2cell(data_cleaned);
% Step 3: Write the cleaned data back to the Excel file
xlswrite([name,'_new.xlsx'],data_cleaned);

请先登录，再进行评论。

更多回答（2 个）

Sreeram
Sreeram 2024-8-16
Hi Wenchao,
You can make use of the MATLAB function “cellfun” for such operations. You may read about it here:
https://www.mathworks.com/help/matlab/ref/cellfun.html
If you want to delete all empty rows of an excel file, the command will be:
C(all(cellfun('isempty',C),2),:) = [];
where C is the input array which you can get by reading the Excel file.
Hope it helps.
  3 个评论
显示 1更早的评论
Sreeram
Sreeram 2024-8-16
Thanks for informing me, Wenchao.
If you had used the "readTable" function to read the file, it might not have worked because of a mismatch in variable type of C passed to the "cellfun". The "readTable" detects the data type to determine how to import the data. Because the data in dataImport.xlsx was only numbers, the "readTable" might have set empty cells to NaN, and so the "isempty" check was insufficient.
It can be fixed by following the instructions given here to set the type to char. Here is how you can implement it:
opts = detectImportOptions('dataImport.xlsx');
opts = setvartype(opts, 'char');
data = readtable('dataImport.xlsx', opts);
data = table2cell(data);
data(all(cellfun('isempty',data),2),:) = [];
Hope this helps..
wenchao zhang
wenchao zhang 2024-8-16
ok,thanks,Sreeram,i will try it, i think it can works finally.

请先登录，再进行评论。

Romain
Romain 2024-8-16
编辑：Romain 2024-8-16
Hi,
Not sure about what you mean, do you want to:
  1. Delete a row that contain some empty cells (but the row still has non-empty cells) or,
  2. Delete the rows that contain only empty cells ?
Anyway,
In case 1: add MissingRule="omitrow" in the readtable function. Doing that will delete all rows that contain 1 or more empty cell.
In case 2: Should you have row name, add ReadRowNames = true in the readtable function. Otherwise, the readtable function automatically gets rid of empty rows.

类别

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

标签

产品


版本

R2018b

Community Treasure Hunt

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

Start Hunting!

Translated by