October 11, 2007, 08:08 AM
trobcompound excel output
I have a few questions regarding a compound excel report. I'm on 762 and I know some can be solved using templates, but I would like a xls file output.
1. Can worksheets be hidden using a compound excel report?
2. Is there a way to freeze cells?
3. Does Dev.Studio not support compound excel reports? If I open in GUI it removes the OPEN portion.
TIA,
-Travis
October 11, 2007, 08:56 AM
GamPTrob,
As far as I know:
1. No, I don't think we can hide a worksheet from wihtin our code, but I'm not 100 % sure.
2. Again, as far as I know, we can't freeze cells.
3. You can certainly do this by issuing the SET COMPOUND=OPEN statement before every single request and SET COMPOUND=CLOSE jusrt before the last. This is fully supported in DS.
October 11, 2007, 09:37 AM
Tony ATrob,
From 7.something, a new compound reporting syntax was introduced so I would expect the GUI to remove bits and pieces of code (one of the reasons that I do not like the GUI
).
You can, of course, still code the COMPOUND OPEN etc. (as per GamP above) when in the text editor.
For information, I resort to using the older syntax when I require portrait and landscape components combined (PDF obviously) as the new syntax does not seem able to cope that well and I do not have the time to mess with it.
T
October 12, 2007, 10:11 AM
PBrightwelltrob,
If you are wanting to freeze cells and hide sheets, you need to create a template. The way I did this was to create my WebFocus report and hold it format EXL2K without any headings. Then create the sheets that you want to display including any formulas and/or frozen cells. Reference the fields in your worksheet to fill the data save the entire thing as format .mht and store it in your eda path. You then change your fex to filedef the .mht file and when you run it save the output from your fex over the worksheet(s). In the final step use ON TABLE PCHOLD FORMAT EXL2K. We covered the rest of the process a few days ago. You may need to hide the worksheets manually, but as long as you didn't overlay your display sheets with your worksheet your frozen cells will be frozen once you save the sheet.
October 12, 2007, 11:59 AM
Tony AOne other thing to remember with FORMAT EXL2K is that it doesn't save it as a
true .xls file, in that it is XML. The trouble with this is that it is not efficient space wise as much as the internal Excel file format, so I tend to have a macro that force saves it as .xls. This can save as much as fifty percent of the original file size, although a normal saving is around thirty percent.
Most of the web archives with macros that I have done for my current Client are primarily to format the sheets in readiness for printing so the users don't have to do it (I'm too kind to them
) and so, once the formating has been achieved on the initial opening of the spreadsheet, that code is no longer required. So I also have a piece of code in the before_save sub that removes all of the macro code so that the user isn't prompted for enabling of macros each time.
T