cell2table: Preserve cell type when column contains numbers and strings

I am using Component Object Model (COM) to export tables to Excel List Objects. I have many layers of m-file functions to do this, but at the bottom level, the assignment is `RangeObject.Value = CellArray`, where `RangeObject` is a COM handle to an Excel range and `CellArray` is a Matlab cell array.

Sometimes, scalar infinity shows up as 65535. At the top level, therefore, I converted my tables to cell arrays and replaced `Inf` with the string "Infinity". When I convert back to a table using `cell2table`, unfortunately, the columns containing strings and numbers get converted entirely to strings:

x={          0    "Infinity"   63.5010
    "Infinity"             0    8.5239
       63.5010        8.5239         0 }

cell2table(x)

   ans = 3×3 table
       x1            x2          x3
   __________    __________    ______
   "0"           "Infinity"    63.501
   "Infinity"    "0"           8.5239
   "63.501"      "8.5239"           0

Is there a streamline way to maintain the cell array nature of these columns? The `cell2table` function doesn't accept name-value pair `Uniform=false`, which normally causes the result to be a cell array.

I know that I can iterate over the columns of the original table, test for the presence of `Inf`, selectively replace those columns with cell arrays, then replace `Inf` values with the string "Infinity". I'm hoping to avoid that because it's worse than living with string representations of numbers (I subjectively decided this for the time being).

Answer 1

[Walter Roberson]

x={          0    'Infinity'   63.5010
    "Infinity"             0    8.5239
       63.5010        8.5239         0 };

T = array2table(x)

T = 3×3 table
          x1               x2             x3     
    ______________    ____________    ___________

    {[         0]}    {'Infinity'}    {[63.5010]}
    {["Infinity"]}    {[       0]}    {[ 8.5239]}
    {[   63.5010]}    {[  8.5239]}    {[      0]}

[FM]

@Walter Roberson: Thanks so much! Who would have guessed that array2table would differ from cell2table in leaving the cell wrapper around each element? As it turns out this works perfectly with the COM assignment RangeHandle=CellArray because the COM interface strips away the Matlab cell wrapper and places the Matlab cell innards within the Excel cells. You don't get all sorts of weirdness from having a Matlab cell-wrapped datum within an Excel cell. Who knows how that would manifest itself.

So for a scalar assignment, SingleExcelCellRangeHandle=MatlabScalar works as expected regardless of whether MatlabScalar is a wrapped in a Matlab cell. However, the assignment will only strip away one layer of Matlab cell wrapping. If I try SingleExcelCellRangeHandle={{pi}}, the Excel cell appears blank. I guess this answers the question of what happens if you leave Matlab cell wrappers around datum that you want to place in an Excel cell. It doesn't get interpretted, and so it appears blank.

For an array assignment MultipleExcelCellRangeHandle=MatlabCellArray, I haven't tried experimenting with the different possibilities, such as replacing MatlabCellArray with a non-cell-array, or nesting cell wrappers for some of the cells in the cell array. The interface developers can only build in so much robustness, and I don't want to spend time investigating all the different ways to break it.

Walter, would you care to post your response as the answer?

Answer 2

[dpb]

The problem outlined in the Q? is owing to that you introduced strings into the cell array -- use

x={          0    'Infinity'   63.5010
    'Infinity'             0    8.5239
       63.5010        8.5239         0 };
cell2table(x)

ans = 3×3 table
         x1              x2           x3  
    ____________    ____________    ______

    {[       0]}    {'Infinity'}    63.501
    {'Infinity'}    {[       0]}    8.5239
    {[ 63.5010]}    {[  8.5239]}         0

However, I'd think you could stick with using inf as numeric; can't imagine can't deal with it "more better" that way rather than klunking around this way. But, we don't have enough details to understand where/why there might be an issue.

[FM]

@dpb: Thanks, that works. But I'm really hoping to find a way to force preservation of cell array types in general when the column contains mixed types. I'm wary of inconsistency when it happens sometimes but not others, depending on the nature of the heterogeneity. Is there some kind of rationale that might explain this difference in using char arrays versus strings? If it's just that strings are new, and TMW is catching up on stamping out inconsistency, then there's nothing left to explain.

Regarding the adherence to numerical Inf, it doesn't provide the desired result, which is 65535 in the Excel spreadsheet.

P.S. I did end up iterating through the columns, converting them to cell arrays, and selectively replacing Inf with "Infinity". But it's a real burden to rejig all places in the code that export tables to Excel via COM. Rather than do that, I went to the bottom level of my stack of function calls and rejigged the m-file function to search all cells being written, check if each is numeric && if it is infinite [`isinf()`], then replacing infinities with string "Infinity".

[dpb]

What about

>> writecell({realmax},'test.xlsx')
>> readcell('test.xlsx')
ans =
  1×1 cell array
    {[Inf]}
>> 

instead, then?

It does show up as the big number in Excel, but seems to get converted back when come the other side back. Otherwise, insert the #DIV/0 indicator;

I'd forgotten about that aberration in Excel; I rarely, if ever use it for numerical work of any sort although have spent last three years working on the financial records of the local college foundation which have been in a "veritable plethora" of Excel workbooks. So, this isn't an issue with those kinds of data.

[FM]

Thanks, dpb, but unfortunately, the usage goes far beyond exporting a data frame. I'm tiling them up, highlighting cells, creating worksheets, etc., all using Matlab code through COM.