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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
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
Go
Search
Notify
Tools
[SOLVED] WF and Excel Templates
 Login/Join
 
Member
posted
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
Virtuoso
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
Master
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
  Sheets(1).Select
  If Sheets(1).Name = "Sheet1" Then
    Sheets(1).Select
    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
  Range("A8").Select
  Selection.RowHeight = 17.25  
  Range("A9,B9,J9:L9,Q9:S9,U9,X9,Y9,AC9,AD9,AF9,AG9").Select
  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
  Selection.Columns.AutoFit
  Call borders
  Range("A8:C9,D8:E9,F8:G9,H8:U9,V8:X9,Y8:AE9,AF8:AI9,AJ8:AK9,AL9:AN9").Select
  Call thick_borders
  Range(Cells(9, 1), Cells(9, ws_last_col)).Select
  Selection.AutoFilter
  Range("A10").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