cell2table: Preserve cell type when column contains numbers and strings

조회 수: 34 (최근 30일)
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일
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일
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
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 CenterFile Exchange에서 Matrices and Arrays에 대해 자세히 알아보기

태그

제품


릴리스

R2022a

Community Treasure Hunt

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

Start Hunting!

Translated by