Home     Products      Support      Corporate     Sign In 
Support Knowledge Base, Article 98
Product
ExcelWriter
Version
1.x
Title
HOWTO: Create custom number or date formats and apply them by using "Style"
Problem
The ExcelWriter "Format" and "Style" objects allow you to set the look of a particular cell or set of cells. The "Format.Number" or "Style.Number" properties are used to set how numbers and dates are displayed.

A list of the currently pre-defined ".number" formats are available from the ExcelWriter online documentation on the "Formatting Codes" page.

However, if your desired display is not included in this listing, it is possible to define and apply your own unique custom format.

Solution
ExcelWriter allows you to create custom formats as required. Two examples of this follow below. The first example demonstrates the creation of a custom "TimeStamp" format. The second example demonstrates the creation of a custom "Eurodollar" format.

Example #1: "TimeStamp" date and time format
<%
        Dim TimeStampStyle, RowCount, X

        '--- Create custom "TimeStampStyle" for formatting all rows in a column 1
        '--- to display the full date and time (time stamp)
        set TimeStampStyle = XLW.CreateStyle
        	TimeStampStyle.Number = "mm/dd/yyyy hh:mm:ss AM/PM"

        RowCount = 40
        X = 1

        '--- Use looping to apply "TimeStampStyle" to all rows in column 1
        Do While X <= RowCount
                ws.Cells(X, 1).Style = TimeStampStyle
                '-- Iterate RowCount
                X = X + 1
        loop
%>

Please note: It is very important that the slashes in the custom format are forward slashes "/". The formatting will not work correctly if back slashes "\" are employed.

Example #2: "Eurodollar" currency format
<%
 	Dim EurodollarStyle, RowCount, X
		
        '--- Create custom "EurodollarStyle" for formatting all rows in a column 2
        '--- This custom style will prefix the "€" sign and show all values with two decimal places
        '--- and losses will appear in parenthesis
	Set EurodollarStyle = XLW.CreateStyle
 	     EurodollarStyle.Number = "€ #,##0.00;(€ #,##0.00)"

        '--- Use looping to apply "EurodollarStyle" to all rows in column 2
        Do While X <= RowCount
                ws.Cells(X, 2).Style = EurodollarStyle
                '-- Iterate RowCount
                X = X + 1
        loop
%>
This custom "EurodollarStyle" expands upon the pre-defined currency format (number = 7) by replacing the U.S.A. "$" sign with the Eurodollar "€" symbol while retaining the two decimal places and showing losses in parenthesis.

Created : 12/1/2002 12:00:00 AM (last modified : 5/7/2001 3:18:00 PM)
Rate this article!
 
Comments