cell2table: Preserve cell type when column contains numbers and strings

17 次查看（过去 30 天）
FM
FM 2023-1-31
移动：Walter Roberson 2023-1-31
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).

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

采纳的回答

Walter Roberson
Walter Roberson 2023-1-31
移动：Walter Roberson 2023-1-31
Ran in:
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 个评论
FM
FM 2023-1-31
移动：Walter Roberson 2023-1-31
@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?

请先登录，再进行评论。

更多回答（1 个）

dpb
dpb 2023-1-31
Ran in:
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 个评论
显示 1更早的评论
dpb
dpb 2023-1-31
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 2023-1-31
编辑：FM 2023-1-31
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.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Use COM Objects in MATLAB 的更多信息

标签

产品


版本

R2022a

Community Treasure Hunt

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

Start Hunting!

Translated by