Kbase 21628: Dynamically Load a Table to EXCEL & Format Column Widths
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/15/2008 |
|
Status: Verified
GOAL:
How to dynamically load any Progress Database table, given its name, into a Microsoft Excel worksheet.
FACT(s) (Environment):
Progress 9.X
Microsoft Excel Worksheet
FIX:
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.
/***************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.
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 = TRUE
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 */
hExcel:Application:Workbooks:CLOSE() NO-ERROR.
hExcel:Application:QUIT NO-ERROR.
RELEASE OBJECT hWorksheet.
RELEASE OBJECT hWorkbook.
RELEASE OBJECT hExcel.
DELETE OBJECT hBufferHandle.
DELETE OBJECT hQueryHandle.