Data from excel with operators

Question

Lepakshi Ramkiran 2020-5-11

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/524697-data-from-excel-with-operators

评论： Lepakshi Ramkiran 2020-5-12

Major_and_Minor_Bodies.xlsx

I have a excel sheet with two coloumns. One has a name other has the corresponding mass to it. I have used the correspèonding line to read it and find the position of the name. But when i am trying to fing the mass to the corresponding name as shown below it is not able to store it in the memory. In the excel i have the mass values as 1.989*10^30. this seems to affect the code as the same code works fine when the cells in the excel has just numeric values.

majbod = 'Sun';
minbod = 'Earth';
majbodin = readtable("Major_and_Minor_Bodies.xlsx","Sheet",1);
minbodin = readtable("Major_and_Minor_Bodies.xlsx","Sheet",2);
MAJORBODY = table2array(majbodin(:,"Major_Body"));
MINORBODY = table2array(minbodin(:,"Minor_Body"));
mmaj = table2array(majbodin(:,"Mass"));
mmin = table2array(minbodin(:,"Mass"));
selected_majbody = find(strcmp(MAJORBODY,majbod));
selected_minbody = find(strcmp(MINORBODY,minbod));
M = mmaj(selected_majbody);
m = mmin(selected_minbody);
disp([M ;m])

I have attached the excel file with the code. Please help me with this.

Is there a better way to write the code compared to the way which I wrote?

Thanks.

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

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

Answer 1

Cris LaPierre 2020-5-11

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/524697-data-from-excel-with-operators#answer_431860

编辑：Cris LaPierre 2020-5-11

在 MATLAB Online 中打开

The main issue you are having is that your numbers are not numbers, but text. If possible, update your numbers in Excel to use this notation instead: 1.989E+30

That way, the mass can be read in as a number. Otherwise, convert the text representation of the number to a number.

Is there an easier way? Yes! Use the table to your advantage. Make the object the row name, and then reference it by name.

opts = spreadsheetImportOptions("NumVariables", 2);
% Specify sheet and range
opts.Sheet = "Sheet2";
opts.DataRange = "A2";
% Specify column names and types
opts.VariableNames = ["Minor_Body", "Mass"];
opts.VariableTypes = ["categorical", "string"];
minorBodies = readtable("Major_and_Minor_Bodies.xlsx", opts,"ReadRowNames",true);
%This next line replaces the " *10^" with "e"
minorBodies.Mass=str2double(regexprep(minorBodies.Mass,['(\s*)+\*10\^'],['e']));

Now when you want to reference a mass, use the rowname.

minorBodies.Mass("Saturn")
ans = 5.6830e+26

6 个评论
显示 4更早的评论隐藏 4更早的评论

Lepakshi Ramkiran 2020-5-12

在 MATLAB Online 中打开

Major_and_Minor_Bodies.xlsx

ohh. I have added a third column to the excel, and i have changet the code as follows. But its gives out thr value of the third variable as 0. what is that i have to correct in here? i have attached the new spreadsheet throughg which i have to do that.

minor_bodies = spreadsheetImportOptions("NumVariables", 3);
major_bodies = spreadsheetImportOptions("NumVariables", 2);
major_bodies.Sheet = "Sheet1";
major_bodies.DataRange = "A2";
minor_bodies.Sheet = "Sheet2";
minor_bodies.DataRange = "A2";
major_bodies.VariableNames = ["Major_Body", "Mass", "Radius"];
major_bodies.VariableTypes = ["categorical", "string", "double"];
minor_bodies.VariableNames = ["Minor_Body", "Mass", "Radius"];
minor_bodies.VariableTypes = ["categorical", "string", "double"];
minorBodies = readtable("Major_and_Minor_Bodies.xlsx", minor_bodies,"ReadRowNames",true);
majorBodies = readtable("Major_and_Minor_Bodies.xlsx", major_bodies,"ReadRowNames",true);
minorBodies.Mass=str2double(regexprep(minorBodies.Mass,['(\s*)+\*10\^'],['e']));
majorBodies.Mass=str2double(regexprep(majorBodies.Mass,['(\s*)+\*10\^'],['e']));
M = minorBodies.Mass("Saturn");
m = majorBodies.Mass("Saturn");
rma = majorBodies.Radius("Saturn");
disp([M ;m ;rma])

Cris LaPierre 2020-5-12

在 MATLAB Online 中打开

It's not 0, it's 0.000e+26

The value is being hidden because the magnitude of the other values are so high. Remove the semicolon from the end of the line that assigns a value to rma to see it.

Lepakshi Ramkiran 2020-5-12