Adding column headers when saving/exporting as an Excel file
in [Matlab]
|
From: Thomas on 23 Feb 2010 11:48 Hi, I have an array of numbers (lets say a 4x4 array), and I wish to export this to an Excel file - I've been using xlswrite and this works fine. However, I also want to have column headers in this array, so something like: Col1 Col2 Col3 Col4 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 I'm struggling getting the text headers into the Excel file as well as the numeric data - can anyone provide assistance? Thanks for the help.
From: Cokelid on 23 Feb 2010 12:03 On Feb 23, 11:48 am, "Thomas " <tpickha...(a)yahoo.co.uk> wrote: > I have an array of numbers (lets say a 4x4 array), and I wish to export this to an Excel file - I've been using xlswrite and this works fine. However, I also want to have column headers in this array, so something like: > > Col1 Col2 Col3 Col4 > 1 2 3 4 > 5 6 7 8 > 9 10 11 12 > 13 14 15 16 > > I'm struggling getting the text headers into the Excel file as well as the numeric data - can anyone provide assistance? To mix text and numbers you need to use a cell array with xlswrite. Try converting your numeric array to a cell array with num2cell() and then add text column headings with vertcat(), like: vertcat({'Col1', 'Col2', 'Col3', 'Col4'}, mynum2cellarray) Justin
From: ade77 on 23 Feb 2010 12:31 Cokelid <cokelid(a)googlemail.com> wrote in message <a886b1f3-93fc-4956-b846-fcadf28dbadf(a)o16g2000vbf.googlegroups.com>... > On Feb 23, 11:48 am, "Thomas " <tpickha...(a)yahoo.co.uk> wrote: > > > I have an array of numbers (lets say a 4x4 array), and I wish to export this to an Excel file - I've been using xlswrite and this works fine. However, I also want to have column headers in this array, so something like: > > > > Col1 Col2 Col3 Col4 > > 1 2 3 4 > > 5 6 7 8 > > 9 10 11 12 > > 13 14 15 16 > > > > I'm struggling getting the text headers into the Excel file as well as the numeric data - can anyone provide assistance? > > To mix text and numbers you need to use a cell array with xlswrite. > Try converting your numeric array to a cell array with num2cell() and > then add text column headings with vertcat(), like: vertcat({'Col1', > 'Col2', 'Col3', 'Col4'}, mynum2cellarray) > > Justin Justin is correct, but to make it simpler for you: header = {'col1' 'col2' 'col3' 'col4'} %cell array of 1 by 4 xlswrite('yourfilename', header, 'sheetname') % by defualt starts from A1 xlswrite('yourfilename', 4x4 array, 'sheetname','A2') % array under the header.
From: Thomas on 24 Feb 2010 05:38 Thanks for the replies. Cokelid/Justin: I tried your method, and then did xlswrite('test.xls',array) (where array was formed using vertcat() as you described), but got the following error message: "Warning: Could not start Excel server for export. XLSWRITE attempts to write file in CSV format. > In xlswrite at 163 ??? Error using ==> xlswrite at 180 An error occurred on data export in CSV format. Error using ==> dlmwrite at 99 The input cell array cannot be converted to a matrix" ade77: Unfortunately your method doesn't work etiher. Each time I run xlswrite I get the following message: "Warning: Could not start Excel server for export. XLSWRITE attempts to write file in CSV format. > In xlswrite at 163" The file saves as a .csv file. Additoinally, when you run the command the second time, writing the 4x4 array into the file, it overwrites the column headers already written, starting at cell A1. Incidentally, when writing the column headers to the file this way, each individual character is in a different cell. I think this may be caused because I'm using the Mac version of Matlab, and the COM server functionality is only available on Windows. Is there any alternative way to add headers to some kind of delimited file (be it tab or comma) that Excel can then read and import? If not, I may have to resort to setting up an Automator action to do this outside of Matlab, but I really don't have to want to resort to that since it will mean my script/package is not cross-platform. Thanks for the help :)
From: Cokelid on 24 Feb 2010 09:42
On Feb 24, 5:38 am, "Thomas " <tpickha...(a)yahoo.co.uk> wrote: > "Warning: Could not start Excel server for export. > > "Warning: Could not start Excel server for export. > > I think this may be caused because I'm using the Mac version of Matlab, and the COM server functionality is only available on Windows. Is there any alternative way to add headers to some kind of delimited file (be it tab or comma) that Excel can then read and import? I don't have a Mac, but sounds like that could be an issue. Given this, I would suggest a creating a csv file using fprintf() to write your text and numerics to a file opened with fopen() (and finally closed with fclose()). You'll have to understand C-style formatting codes for fprintf(), but it's worth the effort in the long run. This won't be a one or two liner like with xlswrite(), but will give you full control over your text output. HTH, Justin |