Hi All,

Got stuck in this issue. Its that I want an EXL2K format report with no dividing outline in the cells of a particular column(see image..FIELD3 in below url)



But when I tried it by making backcolor white it appeared like this(see below url..borders for that column 'FIELD3' cells are missing):-



Searched on the forum about relevant material, but found that borders specification doesn't apply in EXL2K format.
So, I am wondering is there any workaround for this.?
Any help appreciated.

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

Hi,
I had the some issue , the single way to fix it was to write an excel macro to put the borders .

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

This isn't perfect, but you get the idea .... Play with the html format until it is perfect the do a HOLD FORMAT HTML, and add the last 3 lines.

-* File z_focalpoint_excelformat.fex
TABLE FILE CAR
SUM
     DEALER_COST
     RETAIL_COST
     SALES
BY COUNTRY
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     BORDER-TOP=LIGHT,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=LIGHT,
     BORDER-RIGHT=LIGHT,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=DATA,
     COLUMN=N3,
     BORDER-TOP=OFF,
     BORDER-BOTTOM=OFF,
     BORDER-LEFT=LIGHT,
     BORDER-RIGHT=LIGHT,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     BORDER-TOP=OFF,
     BORDER-BOTTOM=OFF,
     BORDER-LEFT=OFF,
     BORDER-RIGHT=OFF,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     BORDER-TOP=OFF,
     BORDER-BOTTOM=OFF,
     BORDER-LEFT=OFF,
     BORDER-RIGHT=OFF,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
ENDSTYLE
END
-RUN
SET HTMLFORMTYPE=XLS
-HTMLFORM HOLD  

Might as well just use FORMAT EXL97 then you do not need the HTMLFORM

Thanks Mtguy, Dhagen, JG for ur suggestions.

Is there a way to write or include excel macro in a FEX file bcoz i am running an on-demand online report(dynamically), so how will i associate the particular macro with the FEX file?
Secondly, the report has thousands of records, so will it not take much time to generate the report(if i write a macro)?

Dhagen, I tried with ur suggestion, but its not coming perfectly as i want.(bottom-most border for that column is missing).

JG, I tried using EXL97 also but same problem its not coming perfectly(bottom-most border for that column is missing)

Is there any other way around.??

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

If you use CSS and CLASS setting in the report, you can get exactly what you are looking for. I'm just not the best at CSS, so you will have to research the appropriate options on your own. The technique I posted will support CSS.

Hi pink,
I did a solution how combine webfocus with excel templates and macros . The way to do it is to create an excel template ( this is a file with mht extension , be carefull in this file you need to have at least 2 sheets) this will be the place for your macro , upload the file under the server in the webfocus path and call it from your webfocus procedure .
Example :
-* start WF code
TABLE FILE CAR
PRINT *
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE 'the_template_file_name' SHEETNUMBER 1
END
-* end WF code
This code will open an excel file(the macro is in) with your report under the Sheet1 ,
The formating job will be do by macro .
Private Sub Workbook_Open()
vba macro code
for formating is here ....
.......
End Sub

Hope this help .

MtGuy

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

Using CSS doesn't appear to make things any easier:

-*-- exl2k_style1.fex

SET PAGE=NOLEAD
-RUN

TABLE FILE CAR
PRINT
COUNTRY AS 'FIELD 1'
CAR AS 'FIELD 2'
MODEL AS 'FIELD 3'
BODYTYPE AS 'FIELD 4'

HEADING
"Report Heading"
" "

ON TABLE SUBFOOT
"Report Footing"

ON TABLE HOLD AS H001 FORMAT HTMTABLE

ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, CLASS=reportBody, $
TYPE=HEADING, CLASS=reportPageHeading, $
TYPE=FOOTING, CLASS=reportPageFooting, $
TYPE=TABFOOTING, CLASS=reportPageFooting, $
TYPE=TITLE, CLASS=reportColumnTitle, $
TYPE=DATA, CLASS=reportData, $
TYPE=DATA, COLUMN=MODEL, CLASS=reportData1, $
ENDSTYLE
END
-RUN

SET HTMLFORMTYPE=XLS
-RUN

-HTMLFORM BEGIN
<style type="text/css">

table { border-collapse: collapse; }

U { text-decoration: none; } /* Remove underline from Column Title */

.reportPageHeading
{
font-family: Arial, sans-serif;
font-size: 14px;
font-weight: bold;
color: brown;
}

.reportPageFooting
{
color: orange;
border-top: 1px solid black;
}

.reportColumnTitle
{
font-family: Arial, sans-serif;
font-size: 12px;
font-weight: bold;
color: blue;
border: 1px solid black;
}

.reportData
{
font-family: Arial, sans-serif;
font-size: 12px;
color: red;
border: 1px solid black;
}

.reportData1
{
font-family: Arial, sans-serif;
font-size: 12px;
color: red;
}

</style>
!IBI.FIL.H001;
-HTMLFORM END

Results in:



If you comment out the HTMLFORMTYPE command to view the report in HTML, it appears as expected, but there are problems in Excel: The borders are thicker than expected. All the cells have a white background. The footing does not have the top border.

HTML version:

Use a template / macro or raise another case about the same problem with IBI and wait.

Hello ,
This solution using css should fix forever your pb .

SET CSSURL=http://localhost:8080/approot/baseapp/my.css
SET PAGE=NOLEAD
SET GRID=OFF
TABLE FILE CAR
 PRINT 
   MODEL 
   CAR 
   COUNTRY
ON TABLE SET HTMLCSS ON
ON TABLE HOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=DATA,CLASS=xlsbord,$
ENDSTYLE
END
-RUN
SET HTMLFORMTYPE=XLS
-HTMLFORM HOLD

my.css content will follow

.xlsbord 
{
font-size:11pt;
text-align:right;
border-style:solid solid ;
}

The report will look like this

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

mtguy, unfortunately, the example you provide will not leave the borders off for a selected column, which is what pink is looking for.

Also, unfortunately, you cannot include images in this forum that reside on your C drive (c:\xlsfile.GIF). The image has to be somewhere in the world wide web. I upload my images to tinypic.com which provides a link to the uploaded image.

Good news,

First to respect the rules I uploaded my images under tinypic.com .
The second I fixed the pb using css.

SET CSSURL=http://localhost:8080/approot/baseapp/my.css
SET PAGE=NOLEAD
SET GRID=OFF
TABLE FILE CAR
 PRINT
   MODEL
   CAR
   COUNTRY
ON TABLE SET HTMLCSS ON
ON TABLE HOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=REPORT, COLUMN=MODEL, CLASS=xlsbord,$
TYPE=REPORT, COLUMN=CAR, CLASS=xlsnobord,$
TYPE=REPORT, COLUMN=COUNTRY, CLASS=xlsbord,$
TYPE=TITLE,CLASS=xlsbord,$
TYPE=FOOTING,CLASS=xlsbord,$
ENDSTYLE
END
-RUN
SET HTMLFORMTYPE=XLS
-HTMLFORM HOLD

my.css file content

.xlsbord 
{
font-size:11pt;
text-align:right;
border-style:solid solid ;
}
.xlsnobord 
{
font-size:11pt;
text-align:right;
border-style:none;
}

Cheers,
MtGuy

Thanks All

Thank you very much for ur valuable suggestions.

I am out of office, thats y can't chk those suggestions till monday.

I hope mtguy's solution will work for me and will revert back in case of any issue.