leading zeros in Excel

Hi
I am exporting data to an excel spreadsheet from an
AbtContainerDetails.
If I have a value of say 000012345 - the leading zeros were being
removed by Excel.
To get around this I appened a '  to the start of the number (before
it gets sent to excel) - giving me '000012345 in my spreadsheet. This
looks a little untidy.
Is there a way for me to get the full number to be displayed in Excel
from Smalltalk?

Thanks for your help!

testmail1222@gmail.com escreveu:
> Hi

You don't give the details about which Smalltalk you're using nor which
version of Excel, so caveat emptor.

> I am exporting data to an excel spreadsheet from an
> AbtContainerDetails.
> If I have a value of say 000012345 - the leading zeros were being
> removed by Excel.

This behavior is standard for Excel. Try to type 000012345 in a cell and
see what happens when you enter.

> To get around this I appened a '  to the start of the number (before
> it gets sent to excel) - giving me '000012345 in my spreadsheet. This
> looks a little untidy.

It depends on the "'" you use. In my Excell 2003 if I cut and paste your
string I get the untidy, however if I type '000012345 (or paste in the
content field (which starts with fx in the toolbar) I get a display of
000012345.

> Is there a way for me to get the full number to be displayed in Excel
> from Smalltalk?

Choose from Excel to format your cells in a custom way.

HTH

Hi
I am using Visual Age 6.0 and I am using Microsoft Office 2007.
I know that this is standard behaviour for Excel but was wondering if
there was a way that I can change this behaviour using Smalltalk.
If I customise from Excel - it is too late then - because the 0's have
alreay been removed.

Cheers


On Apr 1, 9:46=A0am, Cesar Rabak <csra...@yahoo.com.br> wrote:
> testmail1...@gmail.com escreveu:
> 
>
> You don't give the details about which Smalltalk you're using nor which
> version of Excel, so caveat emptor.
> 
>
> This behavior is standard for Excel. Try to type 000012345 in a cell and
> see what happens when you enter.
> 
>
> It depends on the "'" you use. In my Excell 2003 if I cut and paste your
> string I get the untidy, however if I type '000012345 (or paste in the
> content field (which starts with fx in the toolbar) I get a display of
> 000012345.
> 
>
> Choose from Excel to format your cells in a custom way.
>
> HTH

Hi
Sorry about that.
I am using Visual Age 6.0 and Microsoft Office 2007.
Is it possibe for Smalltalk to override this excel behaviour -
customize it on the fly.
If I use the Excel customize option - it is already too late as the
leading zeros have already been removed.

Cheers

On Apr 1, 9:46=A0am, Cesar Rabak <csra...@yahoo.com.br> wrote:
> testmail1...@gmail.com escreveu:
> 
>
> You don't give the details about which Smalltalk you're using nor which
> version of Excel, so caveat emptor.
> 
>
> This behavior is standard for Excel. Try to type 000012345 in a cell and
> see what happens when you enter.
> 
>
> It depends on the "'" you use. In my Excell 2003 if I cut and paste your
> string I get the untidy, however if I type '000012345 (or paste in the
> content field (which starts with fx in the toolbar) I get a display of
> 000012345.
> 
>
> Choose from Excel to format your cells in a custom way.
>
> HTH

Hi testmail1222@gmail.com,

> I am using Visual Age 6.0 and I am using Microsoft Office 2007.
> I know that this is standard behaviour for Excel but was wondering if
> there was a way that I can change this behaviour using Smalltalk.
> If I customise from Excel - it is too late then - because the 0's have
> alreay been removed.

You describe this as if its a Smalltalk problem but you would have this in
any language as its the way that excel works - you basically want to format
the numbers in question as text - e.g. right click on a cell, pick the Forma
t
Cells... option and select Text as the number formatting - that way it will
preserve the leading 0's. (It also mentions there that putting an apostrophe
is the same thing).

So not sure how you are opening excel - but I would suggest that you try
creating a teamplate worksheet that has the cells formatted as text and then
import your numbers in that way.

Or do it like you are and then try the formatting trick afterwards - I notic
e
that if I type 000123 in a cell, with a number formatting you think you have
lost the leading 0's but when you reformat as described they 0's are still
there.

Tim

On 1 Apr., 02:50, testmail1...@gmail.com wrote:
> Is it possibe for Smalltalk to override this excel behaviour -
> customize it on the fly.

What you attempt to do is telling Excel about the display format of a
numeric cell. This is a different kind of information than the cell
value. If I am not mistaken, when you prepend ' to the value, it
indicates that it really is a text value, not a number, which means
that you have changed the value, not the format.

Are you writing data to a comma-separated values file and opening this
file with Excel? In this case, you can't do anything about the format
of the cells. Are you creating and filling the spreadsheet via COM?
Then you can set the cell format by invoking the appropriate functions
from Excel's COM API. Or are you creating a file in Excel file format
from Smalltalk? In this case, you have to write the format information
to the file (I don't know how to do this, but the file format should
be documented somewhere - I'd start by searching MSDN).

> If I use the Excel customize option - it is already too late as the
> leading zeros have already been removed.

Whom do you mean by "I"? Is it your Smalltalk program invoking some
COM function? Or is it yourself using an Excel function after opening
the file?

HTH

On Apr 1, 5:50=A0pm, Joachim Geidel <joachim.gei...@bluecarat.de> wrote:
> On 1 Apr., 02:50, testmail1...@gmail.com wrote:
> 
>
> What you attempt to do is telling Excel about the display format of a
> numeric cell. This is a different kind of information than the cell
> value. If I am not mistaken, when you prepend ' to the value, it
> indicates that it really is a text value, not a number, which means
> that you have changed the value, not the format.
>
> Are you writing data to a comma-separated values file and opening this
> file with Excel? In this case, you can't do anything about the format
> of the cells. Are you creating and filling the spreadsheet via COM?
> Then you can set the cell format by invoking the appropriate functions
> from Excel's COM API. Or are you creating a file in Excel file format
> from Smalltalk? In this case, you have to write the format information
> to the file (I don't know how to do this, but the file format should
> be documented somewhere - I'd start by searching MSDN).
> 
>
> Whom do you mean by "I"? Is it your Smalltalk program invoking some
> COM function? Or is it yourself using an Excel function after opening
> the file?
>
> HTH

Hi
Thanks for your responses. I am creating a CfsReadWriteFileStream and
then opening the associated file after all of the values have been
written to this write stream. It is correct to say that I am changing
the data type from a number to a string when I type a comma before the
first 0.
Is there a way of setting up this cell formatting through Smalltalk -
I am guessing not. I will not be opening an existing excel file - just
writing to a new one. The values in the stream are comma seperated to
seperate them into cells.

Cheers