Re: XLS plugin - format number with leading zero
Hello,
I found some information in the documentation (header files, I can not stress
this enough).
short answer, the library supports it, the plugin doesn't.
---
from format.h
// good resource for format strings:
http://www.mvps.org/dmcritchie/excel/formula.htm
// Good explanation of custom formats:
http://www.ozgrid.com/Excel/CustomFormats.htm
// MS examples (need Windows):
http://download.microsoft.com/download/excel97win/sample/1.0/WIN98Me/EN-US/Nmbrfrmt.exe
// Google this for MS help: "Create or delete a custom number format"
the plugin uses the format_number_t enumeration in XLS_FORMAT_SET_NUMERIC_FORMAT
i.e.
typedef enum
{
FMT_GENERAL = 0,
FMT_NUMBER1,// 0
FMT_NUMBER2,// 0.00
FMT_NUMBER3,// #,##0
FMT_NUMBER4,// #,##0.00
FMT_CURRENCY1, // "$"#,##0_);("$"#,##0)
FMT_CURRENCY2, // "$"#,##0_);[Red]("$"#,##0)
FMT_CURRENCY3, // "$"#,##0.00_);("$"#,##0.00)
FMT_CURRENCY4, // "$"#,##0.00_);[Red]("$"#,##0.00)
FMT_PERCENT1, // 0%
FMT_PERCENT2, // 0.00%
FMT_SCIENTIFIC1,// 0.00E+00
FMT_FRACTION1, // # ?/?
FMT_FRACTION2, // # ??/??
FMT_DATE1, // M/D/YY
FMT_DATE2, // D-MMM-YY
FMT_DATE3, // D-MMM
FMT_DATE4, // MMM-YY
FMT_TIME1, // h:mm AM/PM
FMT_TIME2, // h:mm:ss AM/PM
FMT_TIME3, // h:mm
FMT_TIME4, // h:mm:ss
FMT_DATETIME, // M/D/YY h:mm
FMT_ACCOUNTING1,// _(#,##0_);(#,##0)
FMT_ACCOUNTING2,// _(#,##0_);[Red](#,##0)
FMT_ACCOUNTING3,// _(#,##0.00_);(#,##0.00)
FMT_ACCOUNTING4,// _(#,##0.00_);[Red](#,##0.00)
FMT_CURRENCY5, // _("$"* #,##0_);_("$"* (#,##0);_("$"* "-"_);_(@_)
FMT_CURRENCY6, // _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)
FMT_CURRENCY7, // _("$"* #,##0.00_);_("$"* (#,##0.00);_("$"*
"-"??_);_(@_)
FMT_CURRENCY8, // _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
FMT_TIME5, // mm:ss
FMT_TIME6, // [h]:mm:ss
FMT_TIME7, // mm:ss.0
FMT_SCIENTIFIC2,// ##0.0E+0
FMT_TEXT// @
} format_number_t;
but custom format based on string requires format_t
> 2019/01/30 16:42、4dialog via 4D_Tech <4d_tech@lists.4d.com>のメール:
>
> The problem is not i 4D, i get the leading zero but only in a text cell.
> In excel you can format manualy a column as number with fixed numbers
> preformated (selfdefined format). In my case, 11 numbers, "000". If
> you put 10 numbers in a cell it gets leading zero.
**
4D Internet Users Group (4D iNUG)
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
**
Re: XLS plugin - format number with leading zero
I don't know anything about the plugin you were attempting to use, but one way to create formatted documents for Excel is to use XML. You specify the type for each cell. I'm sure you could figure out the format option you want by saving a document with Excel and looking at the XML. John DeSoi, Ph.D. > On Jan 30, 2019, at 1:42 AM, 4dialog via 4D_Tech <4d_tech@lists.4d.com> wrote: > > The problem is not i 4D, i get the leading zero but only in a text cell. > In excel you can format manualy a column as number with fixed numbers > preformated (selfdefined format). In my case, 11 numbers, "000". If > you put 10 numbers in a cell it gets leading zero. > > But is there a way to do this formating from 4d? ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
AW: XLS plugin - format number with leading zero
Hi, Some time ago I needed to export data as fixed length strings, including numbers (reals). So I wrote this method: // Uty_FixLengthNum(rToConvert;lLength;lLengthDec;tFillChar;tDecSign;bAlwaysReturnDec) -> tFixLengthNum // // User name (OS): Olivier // Date and time: 16.03.15, 16:00:49 // // Method: Uty_FixLengthNum // Description // Converts a number (real) to a fixed length string // // Parameters: // $1, real, number to convert to a fixed lenght string // $2, longint, total lenght to fix // $3, longint, lenght of decimals to fix // $4, text, fill character, e.g. "0" (zero) // $5, text, decimal separator sign, e.g. "." // $6, boolean, if true, always return string with decimals, even if decimals are zero // if false, return numbers with zero decimal without decimals; // e.g. 100 is returned as 100 when $2 is 7, even if $3 is greater than zero // $0, text, number converted to a fixed length string // ASSERT(Count parameters=6;"Not allowed number of parameter: "+String(Count parameters)) C_REAL($1;$rFixThis;$rInteger;$rDecimal) C_LONGINT($2;$3;$lFixMaxLenght;$lFixDecLenght;$lLenghtInt;$lLenghtDec;$lDifference;$lWhere) C_TEXT($4;$5;$0;$tFillChar;$tDecimalSeparator;$tResult;$tSystemDecimalSeparator;$tInt;$tDec) C_BOOLEAN($6;$bReturnZeroDecimals) $rFixThis:=$1 $lFixMaxLenght:=$2 $lFixDecLenght:=$3 $tFillChar:=$4 $tDecimalSeparator:=$5 $bReturnZeroDecimals:=$6 ASSERT((Length($tFillChar)=1);"Lenght of fill char is not exactly 1: "+$tFillChar) $tResult:=String($rFixThis) GET SYSTEM FORMAT(Decimal separator;$tSystemDecimalSeparator) $lWhere:=Position($tSystemDecimalSeparator;$tResult;*) If ($lWhere>0) $lDifference:=Length($tResult)-$lWhere $tInt:=Substring($tResult;1;$lWhere-1) $tDec:=Substring($tResult;$lWhere+1;$lDifference) $rInteger:=Num($tInt) $rDecimal:=Num($tDec) Else $tInt:=$tResult $tDec:="" $rInteger:=Num($tInt) $rDecimal:=Num($tDec) End if $lLenghtInt:=Length($tInt) $lLenghtDec:=Length($tDec) Case of : ($lFixMaxLenght<=0) $tResult:="" : (($rDecimal=0) & ($bReturnZeroDecimals=False) & ($lLenghtInt=$lFixMaxLenght)) $tResult:=$tInt : (($rDecimal=0) & ($bReturnZeroDecimals=False) & ($lLenghtInt<$lFixMaxLenght)) $lDifference:=($lFixMaxLenght-$lLenghtInt) If ($rInteger>=0) $tResult:=($tFillChar*$lDifference)+$tInt Else $tResult:="-"+($lDifference*$tFillChar)+String(Abs($rInteger)) End if : (($rDecimal=0) & ($bReturnZeroDecimals=False) & ($lLenghtInt>$lFixMaxLenght)) $tResult:="ERROR" Else Case of : ($lFixDecLenght<=0) & ($lLenghtInt=$lFixMaxLenght) $tResult:=$tInt : ($lFixDecLenght<=0) & ($lLenghtInt<$lFixMaxLenght) $lDifference:=$lFixMaxLenght-$lLenghtInt If ($rInteger>=0) $tResult:=($lDifference*$tFillChar)+$tInt Else $tResult:="-"+($lDifference*$tFillChar)+String(Abs($rInteger)) End if : ($lFixDecLenght<=0) & ($lLenghtInt>$lFixMaxLenght) $tResult:="ERROR" Else Case of : ($lLenghtDec>$lFixDecLenght) $tDec:=Substring($tDec;1;$lFixDecLenght) : ($lLenghtDec<$lFixDecLenght) $lDifference:=($lFixDecLenght-$lLenghtDec) $tDec:=$tDec+($lDifference*$tFillChar) End case $tResult:=$tInt+$tDecimalSeparator+$tDec Case of : (Length($tResult)<$lFixMaxLenght) $lDifference:=$lFixMaxLenght-(Length($tResult)) If ($rInteger>=0) $tResult:=($lDifference*$tFillChar)+$tResult Else $tResult:="-"+($lDifference*$tFillChar)+String(Abs($rInteger))+$tDecimalSeparator+$tDec End if : (Length($tResult)>$lFixMaxLenght) $tResult:="ERROR" End case End case End case $0:=$tResult // end of method This method is part of my utility methods component: https://flury-software.ch/a-swiss-army-knife-for-4d/ Best, Olivier -----Ursprüngliche Nachricht----- Von: 4D_Tech <4d_tech-boun...@lists.4d.com> Im Auftrag von 4dialog via 4D_Tech Gesendet: Mittwoch, 30. Januar 2019 08:42 An: 4d_tech@lists.4d.com Cc: 4dialog Betreff: Re: XLS plugin - format number with leading zero Thanks for all answers. The problem is not i 4D, i get the leading zero but only in a text cell. In excel you can format manualy a column as number with fixed numbers preformated (selfdefined format). In my case, 11 numbers, "000". If you put 10 numbers in a cell it gets leading zero. But is there a way to do this formating from 4d? - Hilsen -- Helge Antonsen www.4dialog.no -- Sent from: http://4d.1045681.n5.nabble.com/4D-Tech-f1376241.html ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr
Re: XLS plugin - format number with leading zero
Thanks for all answers. The problem is not i 4D, i get the leading zero but only in a text cell. In excel you can format manualy a column as number with fixed numbers preformated (selfdefined format). In my case, 11 numbers, "000". If you put 10 numbers in a cell it gets leading zero. But is there a way to do this formating from 4d? - Hilsen -- Helge Antonsen www.4dialog.no -- Sent from: http://4d.1045681.n5.nabble.com/4D-Tech-f1376241.html ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: XLS plugin - format number with leading zero
Hilsen: How about putting the logic in 4D code and then branch to the appropriate format. Intelligent formats are handy and elegant but don't have the simplicity, flexibility, or transparency of 4D code. This sort of code is very quick to code and very easy to understand: If(length of date=5) use format with leading 0 Else use standard date format End if -- Douglas von Roeder 949-336-2902 On Tue, Jan 29, 2019 at 3:45 AM 4dialog via 4D_Tech <4d_tech@lists.4d.com> wrote: > I need to build a excel document where born date (6 char) needs to be > number > format with leading zero if only 5 chars. > How can i do this in XLS plugin, what command, what params. > There is many format commands but cant find any documentation on them with > explanation? > > > > - > Hilsen > -- > Helge Antonsen > www.4dialog.no > -- > Sent from: http://4d.1045681.n5.nabble.com/4D-Tech-f1376241.html > ** > 4D Internet Users Group (4D iNUG) > Archive: http://lists.4d.com/archives.html > Options: https://lists.4d.com/mailman/options/4d_tech > Unsub: mailto:4d_tech-unsubscr...@lists.4d.com > ** ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: XLS plugin - format number with leading zero
the plugin has no documentation because it is just a wrapper of an existing free library which has no documentation other than its header files (which is quite normal for a C library) sorry about that. if that's not good enough for you then you might to explore commercial options. https://www.pluggers.nl/product/xl-plugin/ > 2019/01/29 20:43、4dialog via 4D_Tech <4d_tech@lists.4d.com>のメール: > There is many format commands but cant find any documentation on them with > explanation? ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
XLS plugin - format number with leading zero
I need to build a excel document where born date (6 char) needs to be number format with leading zero if only 5 chars. How can i do this in XLS plugin, what command, what params. There is many format commands but cant find any documentation on them with explanation? - Hilsen -- Helge Antonsen www.4dialog.no -- Sent from: http://4d.1045681.n5.nabble.com/4D-Tech-f1376241.html ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
