Exporting strings to Excel using writetable

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.

Answer 1

[Walter Roberson]

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.

[pmtanx]

Thanks, Walter. I'm using R2017b (9.3.0.713579) for Windows 64-bit, and xlswrite() seems to be correctly writing to .xls...it's writetable() which seems to be problematic, as you confirmed when opening the .xls attached.

For right now, I am using a workaround where I simply format the entire empty .xls as text in Excel before running my MATLAB scripts and writing to it.

[Walter Roberson]

Could you attach the myCell.xls that you created with xlswrite() ?

[pmtanx]

Sure, here it is.

[Walter Roberson]

Interesting... for the xlswrite version myCell.xls, the entries do not show any leading quotes on the display, but do show a single quote in fx. The 493E07 entry shows up in word with a shading in the upper left corner. When I click on the cell a yellow caution sign pops up about "The number in this cell is formatted as text or proceeded by an apostrophe sign" .

Excel appears to treat the leading apostrophes specially, but it appears that writetable is emitting them in a way that they become text.

When I look at the .xlsx that is produced by writetable() on Mac, I see that it is making used of references to sharedStrings, and that the shared strings are coded with, for example, '493E07

Could I also trouble you to attach the result of xlswrite() to a .xlsx file? I can analyze the internal .xml contents of .xlsx files but .xls files are pure binary that are a bit of a pain to look through.

[pmtanx]

Yes, that's the same as what I get when opening myCell.xls in Excel, which is the correct behavior. MATLAB exports all three strings with a single leading quote, which Excel is supposed to interpret as indicating a string. That's why Excel shows the initial quote in fx, but not in the display. The bottom line is that MATLAB is exporting correctly with xlswrite(), but not with writetable().

When I use .xlsx instead of .xls, the results stay the same both for xlswrite() and writetable(). I've attached both in the zip file.

[Walter Roberson]

The myCell.xlsx uses sharedStrings as well, but with no ' in the strings.

[pmtanx]

Thanks for the suggestion. I tried it just now but get the following error:

Error using writetable (line 124)
Invalid parameter name: UseExcel.

When I search "help writetable" within MATLAB, the only parameters it lists for writetable() when writing to .xls, .xlsx, .xlsb, .xlsm, .xltx, or .xltm are WriteVariableNames, WriteRowNames, DateLocale, Sheet, and Range.

[Walter Roberson]

useexcel does not appear in any combination of case in @tabular or @table in R2018a

[Jeremy Hughes]

Walter's last comment about ActiveX is on the right track. With both xlswrite and writetable, if Excel is available, MATLAB will launch it and communicate to the running Excel process to write the data. On Mac (or if you don't have Excel) writetable uses a different method to write the excel file.

In the Excel application, writing text into a cell does some things in the background. If you type "9" in a cell, Excel turns that into a number. If you write a date that Excel recognizes, it will format that cell as a date. The same thing is happening when writetable is using Excel. This occurs in the Excel process, so there's nothing MATLAB can do about it. =/

When not using Excel, there's no opportunity for the type to change, so you get the exact thing from the table.

Does the same thing happen with string?

myString = ["'Abcd";"'493E07";"'Sep7"];
myTable = table(myString);
writetable(myTable,'myTable.xls')

[Walter Roberson]

Jeremy, would it be accurate to say that when the ActiveX link to Excel is used, that Excel treats the entries "as-if" you had typed them in interactively in Excel ? (Except perhaps treating navigation characters differently.)

[pmtanx]

Jeremy, thanks for the suggestion to try string. When I execute those commands, it produces the same result as if I generate the table from the cell array—it writes each string to Excel with two leading single quotes instead of one.

[Guillaume]

@Jeremy Hughes,

I'm afraid that the present issue is a bug in matlab, not a problem with excel. Consider the following codes

• using direct actxserver

excel = actxserver('Excel.Application');  %start excel
wb = excel.Workbooks.Add
wb.Worksheets.Item(1).Range('A1').Value = '''aaa';
wb.SaveAs(fullfile(pwd, 'xlactx.xls'))
excel.Quit

• using vbscript (invoked outside matlab via cscript)

Set excel = CreateObject("Excel.Application")
Set wb = excel.Workbooks.Add
wb.Worksheets(1).Range("A1").Value = "'aaa"
wb.SaveAs("C:\users\guillaume\Documents\MATLAB\answers\xlvbs.xls")
excel.Quit

• using writetable

t = table({'''aaa'});
writetable(t, 'xltable.xlsx', 'WriteVariableNames', false)

The last one results in the doubling of the ' in the excel file. The first two don't.

Unfortunately, because the actual workbook handling code is a built-in class of matlab, it's not possible for us to know what is going wrong.

[pmtanx]

Okay, it's good to know that it's a MATLAB bug and not something I'm overlooking. Many thanks to all of you for helping get to the root of this. For right now I'll use the workaround of formatting the cells in my Excel file as text beforehand.

[Guillaume]

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]

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!