Clear Filters
Clear Filters

cell2table: Preserve cell type when column contains numbers and strings

24 views (last 30 days)
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).

Accepted Answer

Walter Roberson
Walter Roberson on 31 Jan 2023
Moved: Walter Roberson on 31 Jan 2023
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]}
  1 Comment
FM
FM on 31 Jan 2023
Moved: Walter Roberson on 31 Jan 2023
@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?

Sign in to comment.

More Answers (1)

dpb
dpb on 31 Jan 2023
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.
  3 Comments
dpb
dpb on 31 Jan 2023
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
FM on 31 Jan 2023
Edited: FM on 31 Jan 2023
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.

Sign in to comment.

Categories

Find more on Matrices and Arrays in Help Center and File Exchange

Tags

Products


Release

R2022a

Community Treasure Hunt

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

Start Hunting!