MATLAB Central Newsreader - Writing numbers as text to Excel from Matlab

Writing numbers as text to Excel from Matlab

Conrad — Thu, 08 Oct 2009 17:23:03 -0400

Hi,

I have a cell array containing text and numbers. Among the numbers, some have decimals and others don't. To keep things simple, I have all the numbers as text and I am trying to send this cell array to an Excel file. XLSWRITE converts all the '1.0' data to '1'. Having looked around, I thought PutCellValue (by Michael Robbins) would do it:

  sheet1=Excel.Worksheets.get('Item', 'Sheet1');
  range=get(sheet1,'Range', 'A1');
  PutCellValue(range,'1.0',1);

but I still get the value of 1 on the Excel Sheet. Any ideas?
Thanks

Re: Writing numbers as text to Excel from Matlab

Rune Allnor — Thu, 08 Oct 2009 18:04:10 -0400

On 8 Okt, 19:23, "Conrad " <conr...@gmx.net> wrote:
> Hi,
>
> I have a cell array containing text and numbers. Among the numbers, some have decimals and others don't. To keep things simple, I have all the numbers as text and I am trying to send this cell array to an Excel file. XLSWRITE converts all the '1.0' data to '1'. Having looked around, I thought PutCellValue (by Michael Robbins) would do it:
>
> sheet1=Excel.Worksheets.get('Item', 'Sheet1');
> range=get(sheet1,'Range', 'A1');
> PutCellValue(range,'1.0',1);
>
> but I still get the value of 1 on the Excel Sheet. Any ideas?
> Thanks

This is just as much a problem with display otions in Excel
as with the numerical values. Record a macro in excel where
you select the display format you want. Then open the macro
in the editor, and look at the commands that were used to
set the display options. Last, insert the same commands
at the matlab side when you write the excel file.

It should be something like

range.set('numberformat','0.00');

Rune

Re: Writing numbers as text to Excel from Matlab

Conrad — Thu, 08 Oct 2009 20:40:23 -0400

Thanks for your help Rune. In the meantime i found a way to do it! Using a for loop, I converted the numbers as follows
excel_table(a,b)=strcat('''',old_table(a,b));
I then used xlswrite with excel_table and that worked.

Rune Allnor <allnor@tele.ntnu.no> wrote in message <c7fedc7c-e966-487d-8d5f-8c2880a3e879@h30g2000vbr.googlegroups.com>...
> On 8 Okt, 19:23, "Conrad " <conr...@gmx.net> wrote:
> > Hi,
> >
> > I have a cell array containing text and numbers. Among the numbers, some have decimals and others don't. To keep things simple, I have all the numbers as text and I am trying to send this cell array to an Excel file. XLSWRITE converts all the '1.0' data to '1'. Having looked around, I thought PutCellValue (by Michael Robbins) would do it:
> >
> > ? sheet1=Excel.Worksheets.get('Item', 'Sheet1');
> > ? range=get(sheet1,'Range', 'A1');
> > ? PutCellValue(range,'1.0',1);
> >
> > but I still get the value of 1 on the Excel Sheet. Any ideas?
> > Thanks
>
> This is just as much a problem with display otions in Excel
> as with the numerical values. Record a macro in excel where
> you select the display format you want. Then open the macro
> in the editor, and look at the commands that were used to
> set the display options. Last, insert the same commands
> at the matlab side when you write the excel file.
>
> It should be something like
>
> range.set('numberformat','0.00');
>
> Rune