Hi, is there a way to add trailing spaces to an excel formatted as EXL07? I want the result set to look like below where the + are actual spaces. Any suggestions?

COUNTRY
-------
ENGLAND
++++++++++++JAPAN
++ITALY
+++++++W GERMANY
++++++++++++++++++++++FRANCE

This message has been edited. Last edited by: FP Mod Chuck,

My question should have said LEADING spaces, not trailing.

Hello,

To better understand the request why exactly are the amount of spaces for each country so random? Not sure the best method to do what you're trying to do. If you were trying to standardize the length of the column and then right justify them so they all line up it would make sense, but when the amount of spaces seem to be random it's hard to understand the usage here.

Ditto with above, more info on the requirements would help.

Searching the forum I found this nugget, it's for trailing spaces but perhaps you can use it for leading...thing is though they're adding a specific amount of spaces to each so again if you're looking for more random spacing this may not be of help...

Instead of using spaces use HEXBYT(160,'A1') for each space that you require - which is a "special" space character. If your string already exists, consider using STRREP on the spaces - HEXBYT(32,'A1').

T

Have you tried ON TABLE SET SHOWBLANKS=ON? Works for me.

SHOWBLANKS
The SHOWBLANKS parameter preserves leading and internal blanks in HTML and EXL2K report output. 

The syntax is:

SET SHOWBLANKS = {OFF|ON}
where:

OFF
Removes leading and internal blanks in HTML and EXL2K report output. OFF is the default value.

ON 
Preserves leading and internal blanks in HTML and EXL2K report output

I know it says EXL2K but it works in XLSX as well. They probably need to update the documentation.

This message has been edited. Last edited by: jgelona,

I should have added--the amount of spaces would be fixed. The end goal is to the have the Country column indented based on which Country is present. If it's England, no indentation. If it's Italy, 2 spaces, etc.

I'm using 7705 and tried SET SHOWBLANKS = {OFF|ON}, but that doesn't seem to work for me.

I'm using WebFocus 7705.

I used Tony's suggestion and came up with a solution--

TABLE FILE CAR
PRINT
COUNTRY
COMPUTE INDENTED_COUNTRY/A500 = IF COUNTRY EQ 'ENGLAND' THEN COUNTRY
ELSE IF COUNTRY EQ 'JAPAN' THEN HEXBYT(160, 'A12') | COUNTRY
ELSE IF COUNTRY EQ 'ITALY' THEN HEXBYT(160, 'A2') | COUNTRY
ELSE IF COUNTRY EQ 'W GERMANY' THEN HEXBYT(160, 'A7') | COUNTRY
ELSE IF COUNTRY EQ 'FRANCE' THEN HEXBYT(160, 'A23') | COUNTRY
ELSE COUNTRY;
ON TABLE PCHOLD FORMAT EXL07
END
-RUN

I've been using SET SHOWBLANKS ON successfully with XLSX ever since IBI supported XLSX.

Try this:

TABLE FILE CAR
PRINT COUNTRY
      COMPUTE BLANKS/A24=HEXBYT(160,'A1');
      COMPUTE INDENTED_COUNTRY/A500 =
	IF COUNTRY EQ 'JAPAN' THEN EDIT(BLANKS,'9999999999999') | COUNTRY ELSE
	IF COUNTRY EQ 'ITALY' THEN EDIT(BLANKS,'999') | COUNTRY ELSE
	IF COUNTRY EQ 'W GERMANY' THEN EDIT(BLANKS,'99999999') | COUNTRY ELSE
	IF COUNTRY EQ 'FRANCE' THEN BLANKS | COUNTRY ELSE COUNTRY;
ON TABLE SET SHOWBLANKS ON
ON TABLE PCHOLD FORMAT EXL07
END