Export a Char String to Excel

Question

Karl Laenger 2020-12-12

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/691900-export-a-char-string-to-excel

评论： dpb 2020-12-13

采纳的回答： dpb

在 MATLAB Online 中打开

Greetings,

in my MATLAB Code (R2017a) i got a variable Matrix ('res') with the typ Char.

For example for one switch it looks like this (8x20):

Carreau-Parameter:  
a = 7521.1446 Pa*s  
b = 0.10637 s       
c = 0.78114         
Aktivierungsenergie:
E = 40281.5361 J    
resnorm:            
0.030751

The rows and columns of 'res' are variable and as you can see it is a String with variables in it. Therfore i would like to export every text and variable of a row (without the spaces) to a seperate column in exel. Here is an example for the last row:

        A1        A2
    __________    __
    '0.030751'    ''

First i build something like this and it worked for just one row:

dname = uigetdir('');
fileName = '\Kapillarrheometer.xlsx';
s = strcat(dname,fileName);
sheet=1;
res = ['Carreau-Parameter:  ';'a = 7521.1446 Pa*s  ']; % and so on...
[r,c] = size(res);
for i = 1:r
    A=strsplit(res(i,:));
end
alltableres=array2table(A);
disp(alltableres);
writetable(alltableres,s,'Sheet',Sheet,'Range','C5','WriteVariableNames',0);

After this i tried to create a dynamik Matrix and it worked but isn't realy a good solution because of the varible size of 'res':

dname = uigetdir('');
fileName = '\Kapillarrheometer.xlsx';
s = strcat(dname,fileName);
sheet=1;
res = ['Carreau-Parameter:  ';'a = 7521.1446 Pa*s  ']; % and so on...
[r,c] = size(res);
%for i = 1:r
    A=strsplit(res(1,:));
    [Ar,Ac] = size(A);
        if Ac<5
            A=[A nan nan nan];
            else
            A=A;
        end
    B=strsplit(res(2,:)); % and so on...
    allres=[A;B];
%end
alltableres=array2table(allres);
writetable(alltableres,s,'Sheet',Sheet,'Range','C5','WriteVariableNames',0);
disp(alltableres);

Here are the results of 'alltableres':

          allres1           allres2      allres3      allres4    allres5
    ____________________    _______    ___________    _______    _______
    'Carreau-Parameter:'    ''         [NaN]    [ NaN]     [NaN]  
    'a'                     '='        '7521.1446'    'Pa*s'     ''     

I tried to use the cellfun/arrayfun command but it didn't worked quite well. After one week of work i am very tired of this problem...

I am open for every help you can offer!

(This is my first post at this forum. Please tell me if i did something wrong with my inputs!)

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

Image Analyst 2020-12-12

Error using vertcat

Dimensions of arrays being concatenated are not consistent.

Error in test (line 9)

res = ['Carreau-Parameter: ';'a = 7521.1446 Pa*s ';'b = 0.10637 s ';'c = 0.78114 ']; % and so on...

Also, what is s? The filename. I assume I can just pick anything, but it would shorten the time to solution if you just gave us code that ran right out of the box.

Karl Laenger 2020-12-12

编辑：Karl Laenger 2020-12-12

i'm sry :/ think i missmatched something with the dimension of res. i changed it in the orginal post and the code should be working now...

yes s is the filename i add it to the orginal post...

请先登录，再进行评论。

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

Answer 1

dpb 2020-12-12

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/691900-export-a-char-string-to-excel#answer_573540

在 MATLAB Online 中打开

The example doesn't work as is, exactly...

>> res = ['Carreau-Parameter:  ';'a = 7521.1446 Pa*s  ';'b = 0.10637 s    ';'c = 0.78114   ']; % and so on...
Error using vertcat
Dimensions of arrays being concatenated are not consistent. 
>>

Think you'll get along a lot better if you would use cellstr instead of char() array --

>> res=strtrim([{'Carreau-Parameter:  '};{'a = 7521.1446 Pa*s  '};{'b = 0.10637 s    '};{'c = 0.78114   '}]);
>> cellfun(@strsplit,res,'UniformOutput',false)
ans =
  4×1 cell array
    {1×2 cell}
    {1×5 cell}
    {1×5 cell}
    {1×4 cell}
>> ans{:}
ans =
  1×2 cell array
    {'Carreau-Parameter:'}    {0×0 char}
ans =
  1×5 cell array
    {'a'}    {'='}    {'7521.1446'}    {'Pa*s'}    {0×0 char}
ans =
  1×5 cell array
    {'b'}    {'='}    {'0.10637'}    {'s'}    {0×0 char}
ans =
  1×4 cell array
    {'c'}    {'='}    {'0.78114'}    {0×0 char}
>> 

Looks to produce what you asked for. You could eliminate the trailing null char artifact from strsplit.

To put into a regular, rectangular cell array would require augmenting shorter rows to length of the maximum.

Alternatively, if there aren't too many, you could just loop over the array to wrtie altho that will be inefficient.

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

dpb 2020-12-12

在 MATLAB Online 中打开

That's what was pointing out -- to be able to use this as anything but a cell array you'll have to augment the shorter cells to the size of the longest similar to what you had done above.

Or, you will have to loop through the rows writing each in turn. This may or may not be a major issue depending on how big an array you might have.

It's really not as bad to do the augmentation as sounds -- using the result from my example above where presumed save the result of the arrayfun also into variable res

>> nmax=max(cellfun(@numel,res);            % return longest cell in array
>> cell2table(cellfun(@(c) [c repmat({''},1,nmax-numel(c))],out,'UniformOutput',false))
ans =
  4×1 table
                                          Var1                                       
    _________________________________________________________________________________
    {'Carreau-Parameter:'}    {0×0 char}    {0×0 char   }    {0×0 char}    {0×0 char}
    {'a'                 }    {'='     }    {'7521.1446'}    {'Pa*s'  }    {0×0 char}
    {'b'                 }    {'='     }    {'0.10637'  }    {'s'     }    {0×0 char}
    {'c'                 }    {'='     }    {'0.78114'  }    {0×0 char}    {0×0 char}
>> 

Knowing what the max size is, it's easy enough to code to add however many empty cells are needed.

Karl Laenger 2020-12-13

在 MATLAB Online 中打开

Awesome now it workes how it should be!

dname = uigetdir('');
fileName = '\Kapillarrheometer.xlsx';
s = strcat(dname,fileName);
Sheet=1;
res=strtrim([{'Carreau-Parameter:  '};{'a = 7521.1446 Pa*s  '}]);
res1=cellstr(res);
res2=strtrim(res1);
res3=cellfun(@strsplit,res2,'UniformOutput',false);
nmax=max(cellfun(@numel,res3));
res4=cell2table(cellfun(@(c) [c repmat({''},1,nmax-numel(c))],res3,'UniformOutput',false));
writetable(res4,s,'Sheet',Sheet,'Range','C5','WriteVariableNames',0);

Thank you Sir for all your patience and i wish a nice weekend!

dpb 2020-12-13

No problem; glad to help. Remember for a slight efficiency gain to eliminate that trailing null character cell column. Wish there were an option in strfind to tell it not to do that--never followed why TMW built it that way to begin with.

请先登录，再进行评论。

Export a Char String to Excel

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

采纳的回答

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

更多回答（0 个）

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

Export a Char String to Excel

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

采纳的回答

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

更多回答（0 个）

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

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

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