Opening XLSX file and setting the variable type to double
9 次查看(过去 30 天)
显示 更早的评论
I am working with .xlsx file and trying to get the values from it and multiply by another value(for tests, I do +1 to every cell, but it is not working as well).
This is the code I am using
Input1 = readtable('OutputFile.xlsx','sheet',2);
WorkData = Input1(17:end,[7,23:end]);
i=[1,14,27,40,53,66,79];
ValueMatrix = table2array(Input1(18:end,23:end));
GWPValues = ValueMatrix(:,i);
GWPValuesMoney = cellfun(@(x) x+1, GWPValues, 'UniformOutput', false);
But when I am trying to add 1 to every cell, it treats the cells as string rather then numeric value.
In ValueMatrix I have only numeric values and NULL
![CaptureMatlab.PNG](https://www.mathworks.com/matlabcentral/answers/uploaded_files/239481/CaptureMatlab.png)
Maybe due to NULL I cannot do mathematical operations with ValueMatrix. If this is the reason how can I substitute all NULLs to 0?
0 个评论
采纳的回答
Guillaume
2019-9-24
编辑:Guillaume
2019-9-24
You never need to use table2array to operate on a table. You can work directly on the table, it's often simpler.
If you look at the table once it's loaded, you'll see that it loads all the header data as table data. Since all that header is text, matlab automatically sets the variable type to text. Since you don't want that header data, you either need to tell matlab to ignore it by giving a 'Range' to readtable, or you need to use a function that's better at detecting the data format.
detectImportOptions, at least on R2019b, does a very good job of detecting and skipping the header.
opt = detectImportOptions('OutputFile.xlsx', 'Sheet', 2); %check that opt.VariableNamesRange is A18. if it is, it worked
workData = readtable('OutputFile.xlsx', opt);
However, it still imports some columns as text because some of them contain the text NULL. The best thing would be to fix your spreadhseet so you don't have mixed text and numbers in numeric columns. Note that even excel formulas would fail on such columns, so you can't blame matlab. Nonetheless, with detectImporOptions we can tell matlab to treat these variables as numbers, so the code becomes:
opt = detectImportOptions('OutputFile.xlsx', 'Sheet', 2);
opt = opt.setvartype(23:numel(opt.VariableNames), 'double'); %override data type for columns 23 to end
workData = readtable('OutputFile.xlsx', opt);
Now the file has been imported properly.
It appears you want to add one to all GWP* variables, in which case:
toadd = startsWith(workData.Properties.VariableNames, 'GWP');
workData{:, toadd} = workData{:, toadd} + 1;
更多回答(1 个)
Ankit
2019-9-24
Hello Konstantin,
I have a question why don't you change the excel sheet value from NULL to 0?
Could you please give a try to below code and let me know if its work for you.
Input1 = readtable('OutputFile.xlsx','sheet',2);
WorkData = Input1(17:end,[7,23:end]);
i=[1,14,27,40,53,66,79];
ValueMatrix = table2array(Input1(18:end,23:end));
GWPValues = ValueMatrix(:,i);
tf = strcmp(GWPValues,'NULL');
GWPValues(tf) = {0} ;
GWPValuesMoney = cellfun(@(x) x+1, GWPValues, 'UniformOutput', false);
Thank you
Ankit
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Cell Arrays 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!