When exporting to Excel, can the heading be frozen?

February 01, 2007, 08:56 AM

susannah

laure, that is an excel feature, not a webfocus feature, so you're going to have to write a macro in excel. If you're populating a template, you can make your macro part of the host. but you need an excel forum for more help on that.

February 01, 2007, 09:14 AM

Laure

That's what I thought. But we're not exporting to a template so I was hoping someone had already worked out how to format the excel worksheet when it opens at export time.

February 10, 2007, 10:21 AM

susannah

Laure, edit your profile to tell us what your system is, would you?; If you've can populate your spreadsheets with a webquery, then you can set up your sheet 1 time, including formatting and freezing headers, and then refresh the data by using excel's own 'Refresh Data' .
If you're using IE, and have built a SelfServ site (not using servlet but using isapi (webapi.dll)), then you can build a webquery with just a right click export to excel..and let IE build the webquery for you. If not, then you have to build it yourself...its in the manual...not hard. Might be a solution for you, or might be more than you really wanted to know

June 03, 2008, 09:17 AM

RJL

quote:
Originally posted by Laure:
It is possible after opening the excel file that the header lines can be frozen, but is there a way to make this so without intervention by the viewer?
Laure

Hallo Laure,

How did you solve your problem.

Cheers Richard

June 03, 2008, 10:43 AM

Laure

Sorry to say that we didn't.

June 03, 2008, 03:48 PM

Kerry

Hi Laure and all,

FYI, this is a New Feature Request, and here is a technique documented:

SET EMPTYREPORT=ON
SET HTMLCSS=ON
-* THIS GENERATES TITLES
TABLE FILE CAR
"THIS IS TITLE OF REPORT"
SUM DCOST AS 'Dealer Cost'
    RCOST AS 'Retail Cost'
   SALES AS 'Sales'
BY COUNTRY AS 'Country'
WHERE COUNTRY EQ 'USA'
BY CAR AS 'Car'
ON TABLE HOLD AS GARR1 FORMAT HTML
ON TABLE SET PAGE OFF
ON TABLE SET STYLE *
TYPE=REPORT,GRID=OFF, $
TYPE=REPORT, COLUMN=N1, WRAP=1, $
TYPE=REPORT, COLUMN=N2, WRAP=.95, $
TYPE=REPORT, COLUMN=N3, WRAP=.5, $
TYPE=REPORT, COLUMN=N4, WRAP=.5, $
TYPE=REPORT, COLUMN=N5, WRAP=.5, $
TYPE=TITLE,COLOR=BLUE,STYLE=-UNDERLINE,$
ENDSTYLE
END

-* THIS GENERATES DATA
TABLE FILE CAR
PRINT DCOST AS ''
RCOST AS ''
SALES AS ''
BY COUNTRY AS ''
BY CAR AS ''
ON TABLE HOLD AS GARR2 FORMAT EXL2K
ON TABLE SET PAGE OFF
ON TABLE SET STYLE *
TYPE=REPORT,GRID=OFF,$
ENDSTYLE
END
-RUN
-HTMLFORM BEGIN
<html>
<style <type="text/css">
DIV.datscrol2{
    position:absolute; top: 120px; left: 10px; height: 300px;
    width: 750px; overflow: scroll; background-color: white;
    border: ridge firebrick 1px
}
</style>
<body>
!IBI.FIL.GARR1;
<DIV CLASS=datscrol2>
!IBI.FIL.GARR2;
</DIV>
</body>
</html>
-HTMLFORM END

Hope this helps.

Cheers,

Kerry

June 12, 2008, 02:29 PM

Laure

Kerry,
I tried this. The output is HTML window without Excel features. I simply cut your code and place in .fex and executed.

I'm really looking to set the Excel freeze bar after the headings and have the grid functionality of Excel, sorting, filtering etc.

Any suggestions?

June 13, 2008, 09:21 AM

jgelona

Laure,

You may also consider upgrading to Excel 2003. There are a lot of things available but you have to have Excel 2002 or higher.

Another option is to add Active Reports to your installation. I like to refer to it as Excel on steroids. It has the ability to set filters, freeze panes, etc. I'm still trying to convince management to get it.

June 15, 2008, 08:18 AM

AlexU

When your output format is Excel you can omit the Heading (and Footing) entirely with a DM statement:
-IF &FMT EQ 'EXL2K' GOTO NOHEADER;
HEADER
"Report Header"
-NOHEADER

If you need to retain the Header information a more complicated solution would be to place the main report on one worksheet and move the heading to a second worksheet using Compound report functionality.