Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [WORKAROUND]Borders in EXL2K format

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[WORKAROUND]Borders in EXL2K format
 Login/Join
 
Silver Member
posted
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,


Thanks and Regards,

WF 7611
XFOCUS/FOCUS DB
Win XP
EXL2K/HTML
 
Posts: 37 | Location: India | Registered: September 25, 2008Report This Post
Member
posted Hide Post
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,


WF 7.6.2 , iWay 5.5 , Sun Unix , Windows XP , .NET , Oracle , SQL Server and more
 
Posts: 24 | Registered: December 05, 2007Report This Post
Virtuoso
posted Hide Post
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  


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
 
Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004Report This Post
<JG>
posted
Might as well just use FORMAT EXL97 then you do not need the HTMLFORM
 
Report This Post
Silver Member
posted Hide Post
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,


Thanks and Regards,

WF 7611
XFOCUS/FOCUS DB
Win XP
EXL2K/HTML
 
Posts: 37 | Location: India | Registered: September 25, 2008Report This Post
Virtuoso
posted Hide Post
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.


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
 
Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004Report This Post
Member
posted Hide Post
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,


WF 7.6.2 , iWay 5.5 , Sun Unix , Windows XP , .NET , Oracle , SQL Server and more
 
Posts: 24 | Registered: December 05, 2007Report This Post
Expert
posted Hide Post
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:


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
<JG>
posted
Use a template / macro or raise another case about the same problem with IBI and wait.
 
Report This Post
Member
posted Hide Post
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,


WF 7.6.2 , iWay 5.5 , Sun Unix , Windows XP , .NET , Oracle , SQL Server and more
 
Posts: 24 | Registered: December 05, 2007Report This Post
Expert
posted Hide Post
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.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
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


WF 7.6.2 , iWay 5.5 , Sun Unix , Windows XP , .NET , Oracle , SQL Server and more
 
Posts: 24 | Registered: December 05, 2007Report This Post
Silver Member
posted Hide Post
Thanks All Smiler

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.


Thanks and Regards,

WF 7611
XFOCUS/FOCUS DB
Win XP
EXL2K/HTML
 
Posts: 37 | Location: India | Registered: September 25, 2008Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [WORKAROUND]Borders in EXL2K format

Copyright © 1996-2020 Information Builders