Exporting strings to Excel using writetable

15 views (last 30 days)
This is a question related to a previous post about exporting strings to Excel and preventing them from being converted to numbers or dates; I was encouraged to start a new thread. I have a cell array of strings, each of which is a gene name, and I have appended a leading single quote to each string to force Excel to read it as a string:
myCell = {'''Abcd';'''493E07';'''Sep7'};
xlswrite('myCell.xls',myCell)
When I do this, MATLAB correctly exports each string with a single quote, and Excel correctly interprets it when I open the .xls. However, when I am working with tables and attempt the same procedure, MATLAB exports each string with not one, but two leading single quotes for each string, even though using disp() to examine the table contents shows only one leading single quote:
myTable = table(myCell);
disp(myTable.myCell{1});
writetable(myTable,'myTable.xls')
I've attached the output. This problem occurs whether I'm using .xls or .xlsx. I have MATLAB R2017b.

Accepted Answer

Walter Roberson
Walter Roberson on 18 Apr 2018
In R2018a for Mac, it is not possible to write xlswrite() that to .xls or .xlsx because doing so requires ActiveX which is not supported except on Windows. For Mac and Linux, xlswrite() would try to write as .csv but that fails because of the non-numeric values.
In R2018a for Mac, writetable() to either .xls or .xlsx uses internal routines to do the writing, since ActiveX is not supported. For both .xls and .xlsx the result is something that has the leading quote show up in the normal display in MS Word, and when you click on the individual cell, the fx that shows up only has the single leading quote.
I do confirm that the .xls you provided shows up in MS Word (for Mac) with a single leading quote on the display, but when you click on the individual cell, the fx that shows up has two leading quotes.
So... this has something to do with the ActiveX interface to Excel.
  15 Comments
Guillaume
Guillaume on 19 Apr 2018
Bug or not, I would do preformatting in any case. Having a character that is hidden under some circumstances and visible under others is a bad implementation in my opinion.
pmtanx
pmtanx on 19 Apr 2018
Yes, that makes sense. I really wish there were a simple way to specify the format from within MATLAB, though. I suppose the original problem is mainly a consequence of how "intelligent" Excel is in interpreting data types, which is usually desirable...I don't know who thought it was a good idea to give genes names (493E07, Sep7) that look like dates or scientific notation!

Sign in to comment.

More Answers (0)

Community Treasure Hunt

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

Start Hunting!