Kbase P125618: How to Dynamically Generate an EXCEL File from a Table Invisible and Save the File?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
29/10/2008 |
|
Status: Verified
GOAL:
How to Dynamically Generate an EXCEL File from a Table Invisible and Save the File?
GOAL:
How to work with Excel files using WebSpeed?
FACT(s) (Environment):
Progress 9.X
Microsoft Excel Worksheet
Windows
FIX:
WebSpeed agents do not have GUI interface that could be used to display Excel files. There are two steps involved when working with Excel files in WebSpeed:
1. Generate Excel file invisible and save to disk
2. Display the file in web browser.
The solution only covers step 1 - how to generate Excel file invisible and save to disk
The solution can easily be adapted to be a callable internal or external procedure by simply changing the name of the table from a local variable to an input parameter.
The sample code below expands these 9 high-level, Pseudo code steps:
It also auto-formats the resulting worksheet's column widths.
NOTE: If the table contains ARRAY fields (for example, fields with extents) then the logic must be modified to accommodate those fields.
1) Define the necessary variables,
2) Open Excel and initialize variables,
3) Create a dynamic buffer for the table,
4) Output the column headers of the spreadsheet,
5) Create a dynamic query for the table,
6) Populate the spreadsheet with the table data,
7) Calculate the spreadsheet column range,
8) Auto Format (Auto-Format) the width of the calculated range,
9) Clean up.
/*------------------------------------------------------------------------------
Purpose:
Parameters: <none>
Notes:
------------------------------------------------------------------------------*/
/***************Define needed variables************/
DEFINE VARIABLE hExcel AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE hWorkbook AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE hWorksheet AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE hBufferHandle AS HANDLE NO-UNDO.
DEFINE VARIABLE cTableName AS CHARACTER NO-UNDO INITIAL 'customer'.
DEFINE VARIABLE iCounter AS INTEGER NO-UNDO.
DEFINE VARIABLE hFieldHandle AS HANDLE NO-UNDO.
DEFINE VARIABLE hQueryHandle AS HANDLE NO-UNDO.
DEFINE VARIABLE iRowNum AS INTEGER NO-UNDO.
DEFINE VARIABLE ColumnRange AS CHARACTER NO-UNDO.
/********Open Excel and initialize variables********/
CREATE "Excel.Application" hExcel.
ASSIGN
hExcel:VISIBLE = FALSE
hWorkbook = hExcel:Workbooks:Add()
hWorkSheet = hExcel:Sheets:Item(1)
cTableName = "Customer"
iRowNum = 1.
/* Pump field names as EXCEL column headers*/
CREATE BUFFER hBufferHandle FOR TABLE cTableName.
DO iCounter = 1 TO hBufferHandle:NUM-FIELDS:
hFieldHandle = hBufferHandle:BUFFER-FIELD(iCounter).
hExcel:Worksheets("Sheet1"):Cells(iRowNum,iCounter ) =
hFieldHandle:NAME.
END.
/* Create dynamic query for the cTablename */
CREATE QUERY hQueryHandle.
hQueryHandle:SET-BUFFERS(hBufferHandle).
hQueryHandle:QUERY-PREPARE("for each " + cTableName ).
hQueryHandle:QUERY-OPEN.
/* Pump the table data into your worksheet */
REPEAT:
iRowNum = iRowNum + 1.
hQueryHandle:GET-NEXT().
IF hQueryHandle:QUERY-OFF-END THEN LEAVE.
ELSE DO iCounter = 1 TO hBufferHandle:NUM-FIELDS:
hFieldHandle = hBufferHandle:BUFFER-FIELD(iCounter).
hExcel:Worksheets("Sheet1"):Cells(iRowNum,iCounter ) =
hFieldHandle:BUFFER-VALUE.
END.
END.
/* Define populated column range and auto-format width */
/* Hint: First Column is column "A" hence CHR(65) usage*/
ASSIGN
ColumnRange = CHR(65) + ":" + CHR(65 + hBufferHandle:NUM-FIELDS - 1).
hExcel:COLUMNS(ColumnRange):SELECT.
hExcel:SELECTION:COLUMNS:AUTOFIT.
/* Perform housekeeping and cleanup steps */
hworkbook:SaveAs("c:\temp\trash.xls",1,?,?,?,?,?).
hExcel:Application:Workbooks:CLOSE() NO-ERROR.
hExcel:Application:QUIT NO-E.RROR.
RELEASE OBJECT hWorksheet.
RELEASE OBJECT hWorkbook.
RELEASE OBJECT hExcel.
DELETE OBJECT hBufferHandle.
DELETE OBJECT hQueryHandle.
.