Progress读取Excel文件

记录下Progress读取Excel文件的办法。
更详细的请看 http://rich.uchytil.com/progress/excel.html

readexcel.p

DEF VAR vchExcel AS COM-HANDLE NO-UNDO.
DEF VAR vchWorkBook AS COM-HANDLE NO-UNDO.
DEF VAR vchWorkSheet AS COM-HANDLE NO-UNDO.
DEF VAR vRow AS INT NO-UNDO.
def var vError as logical.

def temp-table xypo
    field xypo_nbr like po_nbr
    field xypo_part like pt_part
    field xypo_qty like pod_qty_ord
    field xypo_ord_date as date
    index xypo is primary xypo_nbr ascending.

for each xypo:
    delete xypo.
end.

CREATE "Excel.Application":U vchExcel.
ASSIGN vchExcel:VISIBLE = FALSE
    vchWorkBook = vchExcel:WorkBooks:OPEN("d:/tmp/test.xls")
    vchWorkSheet = vchExcel:Sheets:ITEM(1).



GetExcel-BLK:
REPEAT TRANSACTION:
    ASSIGN vError = FALSE
           vRow = vRow + 1.
    /** QUIT WHEN REACH A BLANK LINE **/
    IF vchWorkSheet:RANGE("A":U + STRING(vRow)):TEXT EQ "" THEN LEAVE GetExcel-BLK.
    CREATE xypo.
    /* IMPORT FROM EXCEL */
    ASSIGN xypo.xypo_nbr = vchWorkSheet:Range("A":U + STRING(vRow)):VALUE
           xypo.xypo_part = vchWorkSheet:Range("B":U + STRING(vRow)):VALUE
           xypo.xypo_qty = vchWorkSheet:Range("C":U + STRING(vRow)):VALUE
           xypo.xypo_ord_date = vchWorkSheet:Range("D":U + STRING(vRow)):VALUE
           NO-ERROR.
    /** WAS THERE AN ERROR READING THE SPREADSHEET? **/
    IF ERROR-STATUS:ERROR THEN DO:
        ASSIGN vError = YES.
        MESSAGE ERROR-STATUS:GET-MESSAGE(1) VIEW-AS ALERT-BOX.
    END.
END. /** END OF REPEAT TRANSACTION - GetExcel-BLK **/


vchWorkBook:CLOSE.
RELEASE OBJECT vchWorkSheet.
RELEASE OBJECT vchWorkBook.
vchExcel:QUIT.
RELEASE OBJECT vchExcel.

for each xypo:
    display
        xypo.
end.