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.

New TIBCO Community Coming Soon
In early summer, TIBCO plans to launch a new community—with a new user experience, enhanced search, and expanded capabilities for member engagement with answers and discussions! In advance of that, the current myibi community will be retired on April 30. We will continue to provide updates here on both the retirement of myibi and the new community launch.

What You Need to Know about Our New Community
We value the wealth of knowledge and engagement shared by community members and hope the new community will continue cultivating networking, knowledge sharing, and discussion.

During the transition period, from April 20th until the new community is launched this summer, myibi users should access the TIBCO WebFOCUS page to engage.

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] WF and Excel Templates

Read-Only Read-Only Topic
[SOLVED] WF and Excel Templates
I've seen many questions on this and answered very many different ways but nothing definitive.

Has anyone found a way to actually write to an existing sheet in a template rather than WF replacing the sheet?

Before realizing that WF replaces the sheet I made a sheet with the perfect headers, froze top row and all columns filtered.

I'd like to predefine column widths etc. I just can't seem to get it the way I want in WF styling. I saw the mention of mht but no one I know has a copy of Excel 2003 to be able to export it for that to work.

I tried using the EXL2K option with HFREEZE but when it opens in Excel, you have to manually scroll the page. For whatever reason, Excel ignores the scroll wheel on the mouse.

This message has been edited. Last edited by: FP Mod Chuck,
Posts: 5 | Registered: October 11, 2018Report This Post
posted Hide Post
As far as I know, you will need to use an Excel Macro Template where the macro will generates and format the Excel file as you wish once the data will be loaded (exported to Excel) by WF : auto run macro.

WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Gold member
posted Hide Post
I believe MartinY is correct. We use a lot of excel templates and what has always worked is to just do a data dump to the first sheet and then have the template vlookup the data to populate the template and have a macro run upon opening to get the formatting correct.

Version: 8.2.03M, OS/Platform: Windows 7 & 10, Output: Excel, pdf, html
Posts: 63 | Location: Liberty Lake, WA - USA | Registered: June 23, 2016Report This Post
posted Hide Post
HFREEZE is only for HTML output, that is documented (at least in our current version).

Whenever WebFOCUS writes to a sheet, EVERYTHING is replaced. It has always worked that way.

When I do a report, it is almost always in Excel. You can count on 1 hand the number of HTML and PDF reports we have. In Excel, we use templates with an Auto_Open macro. I start with a report and have WebFOCUS do what it can do easily. Then I use Excel to finish the formatting. Below is an example of one of the simpler macros. I use Excel to rotate column heading 90 degrees, auto fit column widths, freeze panes, turn on filters, draw borders and group borders. I even have a technique for wrapping column headings and doing vertical center. Instead of using a comma in the AS phrase, I use a colon. Then in the macro I change the colon to a Line Feed. The trick is finding out how many rows and columns there are and using the RANGE(Cells(r,c),Cells(r,c)).SELECT format of the RANGE command.
Sub Auto_Open()
  Application.ScreenUpdating = False
  Application.PrintCommunication = False
  If Sheets(1).Name = "Sheet1" Then
    Sheets(1).Name = "Pending Completion"
    Call format_ws
  End If
  Application.PrintCommunication = True
  Application.ScreenUpdating = True
End Sub
Public Sub format_ws()
  ws_last_col = Cells.SpecialCells(xlLastCell).Column
  ws_last_row = Cells.SpecialCells(xlLastCell).Row
  Range(Cells(1, 1), Cells(ws_last_row, ws_last_col)).Select
  Selection.VerticalAlignment = xlCenter
  Selection.RowHeight = 17.25  
  Selection.RowHeight = 89.25
  Selection.Orientation = 90
  Range(Cells(10, 1), Cells(ws_last_row, ws_last_col)).Select
  Selection.WrapText = False
  Range(Cells(9, 1), Cells(9, ws_last_col)).Select
  Selection.Replace What:=":", Replacement:="" & Chr(10) & ""
  Range(Cells(9, 1), Cells(ws_last_row, ws_last_col)).Select
  Call borders
  Call thick_borders
  Range(Cells(9, 1), Cells(9, ws_last_col)).Select
  ActiveWindow.FreezePanes = True
End Sub
Public Sub borders()
  Selection.borders(xlDiagonalDown).LineStyle = xlNone
  Selection.borders(xlDiagonalUp).LineStyle = xlNone
  Selection.borders(xlEdgeLeft).Weight = xlThin
  Selection.borders(xlEdgeTop).Weight = xlThin
  Selection.borders(xlEdgeBottom).Weight = xlThin
  Selection.borders(xlEdgeRight).Weight = xlThin
  Selection.borders(xlInsideVertical).Weight = xlThin
  Selection.borders(xlInsideHorizontal).Weight = xlThin
End Sub
Sub thick_borders()
  Selection.borders(xlDiagonalDown).LineStyle = xlNone
  Selection.borders(xlDiagonalUp).LineStyle = xlNone
  Selection.borders(xlEdgeLeft).Weight = xlMedium
  Selection.borders(xlEdgeTop).Weight = xlMedium
  Selection.borders(xlEdgeBottom).Weight = xlMedium
  Selection.borders(xlEdgeRight).Weight = xlMedium
End Sub

Remember, once you have your report in Excel, almost everything you can do in Excel, you can put in a macro. The only limitation is your imagination.

In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report 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     [SOLVED] WF and Excel Templates

Copyright © 1996-2020 Information Builders