Formatting Values Within Cells in XLSX Report Output
WebFOCUS formats defined in Master Files or within a FOCEXEC will be represented in the resulting cells in an Excel XLSX worksheet. Where possible, the WebFOCUS formats are translated to custom Excel formats and applied to values passed as raw data. Each data value passed to a cell in Excel is defined with a value and a format mask pair. The data format is associated with the cell rather than embedded in the value. This technique provides enhanced support for editing worksheets generated by WebFOCUS. New values entered into existing cells will retain the cell formats and continue to display in the style defined for the column within the report.
The following types of data can be passed to Excel:
- Numeric. Where corresponding Excel format masks can be defined, numeric values are passed as raw values with associated format masks. In instances where an equivalent format mask cannot be defined, the numeric value is passed as a text string.
- Alphanumeric. Alphanumeric formats are passed to Excel as text strings, with General format defined. By default, General format presents all text fields as left-justified. Alignment and other styling attributes can be applied to these cells to override the default.
- Date formats. Data that contain sufficient elements to define a valid Excel date format are passed as raw date values with the WebFOCUS formats translated to Excel date format masks. In WebFOCUS formats that do not contain sufficient information to create valid Excel date values, the dates are converted to text strings.
- Date-Time formats. Date-time values are passed as raw date-time values with WebFOCUS formats translated to Excel date-time format masks using Custom formats.
- Text. Text values are passed as strings with General Format defined (as with alphanumeric data).
Note: This behavior is a change from EXL2K format, where cells containing dates and more complex numeric formats were passed as formatted text.
Displaying Formatted Numeric Values in XLSX Report Output
Each numeric WebFOCUS format is translated to a custom numeric Excel format. The numeric value is displayed in the Excel formula bar for the selected cell. Within the actual cell, the value with the format mask applied displays.
The WebFOCUS formats for the following numeric data types are translated into Excel XLSX format masks supporting full editing within the resulting workbook:
- Data types: E, F, D, I, P
- Comma edit option (C)
- Zero suppression (S)
- Leading zero (L)
- Floating currency symbol (M)
- Comma suppression (c)
- Right-side minus sign (-)
- Credit negative (CR)
- Bracket negative (B)
- Fixed extended currency symbol (!d, !e, !l, !y)
- Floating extended currency symbol (!D, !E, !L, !Y)
- Percent (%)
Example: Passing Numeric Formats to XLSX Report Output
In the following example, the DOLLARS field is assigned different numeric formats to demonstrate different available options. The column titles have been edited to display the WebFOCUS format options that have been applied:
TABLE FILE GGSALES
SUM DOLLARS/D12.2 AS 'D12.2'
DOLLARS/D12C AS 'D12C'
DOLLARS/D12CM AS 'D12CM'
BY REGION
BY CATEGORY
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET BYDISPLAY ON
END
In the resulting worksheet, notice that cell C2 containing the DOLLAR value for Midwest Coffee presents the value with the WebFOCUS format D12.2, which presents the comma (,) and two decimal places. On the formula bar, the actual value is presented without any formatting. Examine each of the DOLLAR values in each row to see that the value as displayed in the formula bar remains the same, and only the display values presented in each cell change.
Also notice that with SET BYDISPLAY ON, the BY field values are repeated for every row on the worksheet. This creates fully qualified data rows that can be used with various data sorting, filtering, and table features in Excel without losing valuable information. This setting is recommended as a best practice for all worksheets.
The following example uses Fixed Dollar (N) format, as well as multiple combined format options. Each WebFOCUS format option is translated to the appropriate Excel XLSX format mask and applied to the cell value:
TABLE FILE GGSALES
SUM BUDDOLLARS/D12N
DOLLARS/D12M
COMPUTE OVERBUDGET/D12BMc = BUDDOLLARS-DOLLARS; AS 'Over Budget'
BY REGION
BY CATEGORY
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET BYDISPLAY ON
ENDNotice the fixed numeric format defined for the BUDDOLLARS column (Column C) presents the local currency symbol in a fixed position within each cell, regardless of the size of the data value. On the formula bar, the values in the Over Budget calculated field is passed as a negative value where appropriate. In the actual cells, the bracketed styling is applied to the negative values as part of the custom Excel XLSX format mask.
Using Numeric Formats in Report Headings and Footings
By default, headings and footings are passed to Excel as a single character string. Spot markers are not supported for positioning within each line. Numeric fields and dates passed in headings and footings are passed as text strings within the overall heading or footing contents.
To display numeric fields and dates within headings and footings as numeric or date values, use HEADALIGN=BODY in the StyleSheet to define each of the items in the heading as an individual cell. Each cell containing numeric or date values will then be passed as the appropriate value with the associated format mask.
Using Numeric Format Punctuation in Headings and Footings
For data columns, all currency formats are translated using the Excel XLSX format masks that use the punctuation rules defined by the regional settings of the desktop.
In languages that use Continental Decimal Notation, the currency definitions designate that a comma (,) is used as the decimal separator, and a period (.) is used as the thousands separator, so D12.2CM may present the value as $ 9.999,99 rather than the English (United States) value $ 9,999.99. In headings and footings, you can designate that punctuation should be converted to Continental Decimal notation by issuing the SET CDN=ON command. With this setting in effect, the data embedded within heading and footing text strings will be formatted using the converted punctuation. Specify HEADALIGN=BODY to delineate items as individual cells and to retain the numeric formatting within the field, which will follow the same rules as the report data within the data columns.
Reference: Usage Note for Sorting an XSLX Report That Contains a Footing
In XLSX format, the report footer is included as a part of the data table in Excel. This is not the same behavior in EXL2K. In EXL2K format, the footer is not included as a part of the table.
For example, if you run the following procedure and sort the data table, the report footer is part of the data table, as shown in the image below the request:
TABLE FILE WF_RETAIL_LITE PRINT COUNTRY_NAME AS Country STATE_PROV_NAME AS State PRODUCT_CATEGORY AS Category WHERE RECORDLIMIT EQ 10 FOOTING "" "TEST FOOTING TEST FOOTING" ON TABLE PCHOLD FORMAT XLSX ON TABLE SET STYLE * ENDSTYLE END
The output is:
The workaround is to add a named data range to the procedure, as shown in the following procedure:
TABLE FILE WF_RETAIL_LITE PRINT COUNTRY_NAME AS Country STATE_PROV_NAME AS State PRODUCT_CATEGORY AS Category WHERE RECORDLIMIT EQ 10 FOOTING "" "TEST FOOTING TEST FOOTING" ON TABLE PCHOLD FORMAT XLSX ON TABLE SET STYLE * TYPE=DATA, IN-RANGES=DATA, $ TYPE=TITLE, IN-RANGES=DATA, $ ENDSTYLE END
The report footer is not part of the data table, as shown in the following image:
Passing Dates to XLSX Report Output
Most translated and smart dates can be sent to Excel as standard date values with format masks, enabling Excel to use them in functions, formulas, and sort sequences.
Excel 2007 only supports mixed-case date text strings so all month and day names are displayed in mixed-case, regardless of how the case has been specified in the WebFOCUS format. For example, the WebFOCUS date format WRYMTRD presents the date text information in uppercase in all non-Excel formats. Excel transforms this value to mixed-case automatically.
In HTML, the date format displays as:
In XLSX, the date format displays as:
Example: Translating WebFOCUS Dates to Excel XLSX Dates
The following request against the GGSALES data source creates the date January 1, 2010 and converts it to four date formats with translated text:
DEFINE FILE GGSALES NEWDATE/MDYY = '01/01/2010'; WRMtrDY/WRMtrDY = NEWDATE; wDMTY/wDMTY = NEWDATE; wrDMTRY/wrDMTRY = NEWDATE; wrYMtrD/wrYMtrD = NEWDATE; END TABLE FILE GGSALES SUM DATE NOPRINT NEWDATE WRMtrDY wDMTY wrDMTRY wrYMtrD ON TABLE PCHOLD FORMAT XLSX END
The following table shows how the dates should appear.
|
WebFOCUS Format |
WebFOCUS Display |
XLSX Display |
XLSX Value |
|---|---|---|---|
|
WRMtrDY |
FRIDAY, January 1 10 |
Friday, January 1 10 |
1/1/2010 |
|
wDMTY |
Fri, 1 JAN 10 |
Fri, 1 Jan 10 |
1/1/2010 |
|
wrDMTY |
Friday, 1 JANUARY 10 |
Friday, 1 January 10 |
1/1/2010 |
|
wrYMtrD |
FRIDAY, 10 JANUARY 1 |
Friday, 10 January 1 |
1/1/2010 |
In Excel 2007/2010, all of the cells have a date value with format masks, and all month and day names are in mixed-case, regardless of how the case has been specified in the WebFOCUS format. The output is:
Passing Dates Without a Day Component
Date formats that do not specify the day value explicitly are defined as the date value of the first day of the month. Therefore, the value placed in the cell may be different from the day component value in the source data field and may produce unexpected results when used for sorting or date calculations in an Excel formula.
The following table shows how WebFOCUS date formats are represented in XLSX. The table shows how the value is preserved in the cell and how the display is generated using the format mask that corresponds to the WebFOCUS date format.
DATEFLD/MDYY = '01/02/2010'
|
WebFOCUS Format |
XLSX Display |
XLSX Value |
|---|---|---|
|
DMYY |
02/01/2010 |
1/2/2010 |
|
MY |
01/10 |
1/1/2010 |
|
MTY |
Jan, 10 |
1/1/2010 |
|
MTDY |
Jan 2, 10 |
1/2/2010 |
Example: Passing WebFOCUS Dates With and Without a Day Component to XLSX Report Output
The following request against the GGSALES data source creates the date January 2, 2010 and passes it to Excel with formats MDYY, DMYY, MY, and MTDY:
DEFINE FILE GGSALES
NEWDATE/MDYY = '01/02/2010';
END
TABLE FILE GGSALES
SUM DATE NOPRINT
NEWDATE AS 'MDYY' NEWDATE/DMYY AS 'DMYY' NEWDATE/MY AS 'MY'
NEWDATE/MTY AS 'MTY' NEWDATE/MTDY AS 'MTDY'
ON TABLE PCHOLD FORMAT XLSX
ENDColumns D and E have actual date values with format masks, displayed by Excel 2007/2010 in mixed-case. Since the MTY format does not have a day component, the date value stored is the first of January 2010 (1/1/2010), not the second of January 2010 (1/2/2010):
Passing Date Components for Use in Excel Formulas
Dates formatted as individual components (for example, D, Y, M, W) are passed to Excel as numeric values that can be used as parameters to Excel date functions. The values are passed as General format that are recognized by Excel as numbers.
Example: Passing Numeric Date Components to XLSX Report Output
The following request against the GGSALES data source creates the date January 1, 2010 and extracts numeric date components, passing them to Excel 2007/2010:
DEFINE FILE GGSALES NEWDATE/MDYY = '01/01/2010'; D/D = NEWDATE; Y/Y = NEWDATE; W/W = NEWDATE; w/w = NEWDATE; M/M = NEWDATE; YY/YY = NEWDATE; END TABLE FILE GGSALES SUM DATE NOPRINT NEWDATE D Y W w M YY ON TABLE PCHOLD FORMAT XLSX END
The output is:
Passing Quarter Formats
Date formats that contain a Quarter component are always passed to Excel as text strings since Excel does not support Quarter formats.
Example: Passing Dates With a Quarter Component to XLSX Report Output
The following request against the GGSALES data source creates the date January 1, 2010 and converts it to date formats that contain a Quarter component:
DEFINE FILE GGSALES NEWDATE/MDYY = '01/01/2010'; Q/Q = NEWDATE; QY/QY = NEWDATE; YBQ/YBQ = NEWDATE; END TABLE FILE GGSALES SUM DATE NOPRINT NEWDATE Q QY YBQ ON TABLE PCHOLD FORMAT XLSX END
In XLSX, the cells containing dates with Quarter components have General format. To see this, open the Format Cells dialog box.
The output is:
Passing Date Components Defined as Translated Text
Date formats that do not contain sufficient information to present a valid date result in Excel are not translated to a value, including formats that do not contain year and/or month information. These dates will be sent to Excel as text. In the absence of complete information, the year defaults to the current year, so the value sent would be incorrect if this type of format was passed as a date value. The following formats will not be sent as values:
- MT, MTR, Mt, Mtr
- W, w, WR, wr
When date formats are passed to XLSX with format masks, all month and day names are in mixed-case, regardless of how the case has been specified in the WebFOCUS format. However, since the values in this example are always sent as text, the casing defined in the WebFOCUS format is applied in the resulting cell.
Example: Passing Date Components Defined as Translated Text to XLSX Report Output
The following request against the GGSALES data source creates the date January 1, 2010 and converts it to date formats that are defined as either month name or day name:
DEFINE FILE GGSALES NEWDATE/MDYY = '01/01/2010'; MT/MT = NEWDATE; MTR/MTR = NEWDATE; Mtr/Mtr = NEWDATE; WR/WR = NEWDATE; wr/wr = NEWDATE; END TABLE FILE GGSALES SUM DATE NOPRINT NEWDATE MT MTR Mtr WR wr ON TABLE PCHOLD FORMAT XLSX END
In Excel 2007 or 2010, the cells containing the days have General format. To see this, open the Format Cells dialog box.
The output is:
Reference: Usage Notes for Date Values in XLSX Report Output
- The following date formats are not supported in XLSX. They will
translate into Excel General format and possibly produce unpredictable
results:
- JUL, YYJUL, and I2MT.
- Dates stored as a packed or alphanumeric field with date display options.
Passing Date-Time to XLSX
Most WebFOCUS date-time formats can be sent to XLSX as standard date/time values with format masks, enabling Excel to use them in functions, formulas, and sort sequences.
As with the Date formats, Excel only supports mixed-case to date-time fields, so if the date-time format contains text and is supported by Excel, the text will be in mixed-case, regardless of the casing defined within the WebFOCUS format.
Example: Passing Date-Time to XLSX
The following request shows an example against the GGSALES data source.
DEFINE FILE GGSALES DT1/HYYMDm WITH REGION = DT(20100506 16:17:01.993876); DPT1/HDMTYYm = DT1; ALPHA_DATE1/A30 = HCNVRT(DT1,'(HYYMDm)',30,'A30'); END TABLE FILE GGSALES PRINT ALPHA_DATE1 DT1 AS 'HYYMDm' DPT1 AS 'HDMTYYm' DT1/HdMTYYBS AS 'HdMTYYBS' DT1/HdMTYYBs AS 'HdMTYYBs' ON TABLE SET SPACES 1 IF RECORDLIMIT EQ 1 ON TABLE PCHOLD FORMAT XLSX END
The output is:
Note: Minutes by themselves are not supported in Excel and will be sent as an integer to XLSX with a Custom format.
Also, Excel time formats only support to the milliseconds. WebFOCUS formats that display microseconds will send the value to Excel, but the value will be rounded to milliseconds within the worksheet if the cell is edited.
The following table shows how the date-time values appear.
|
WebFOCUS Format |
XLSX Displays |
XLSX Value |
|---|---|---|
|
HYYMDm |
2010/05/06 16:17:01.993 |
5/6/2010 4:17:02 PM |
|
HDMTYYm |
06 May 2010 16:17:01.993 |
5/6/2010 4:17:02 PM |
|
HdMTYYBS |
6 May 2010 16:17:01 |
5/6/2010 4:17:01 PM |
|
HdMTYYBs |
6 May 2010 16:17:01.993 |
5/6/2010 4:17:02 PM |
