Hi,
We are running a webspeed program that is on unix and getting it to talk to a program that is on an NT machine through the appserver. This is because we want the program to directly output to an excel spreadsheet on a client's PC.
What's happening is that excel is opening up where the appserver is located. We are trying to get the spreadsheet to open in the main browser where the program is being run from (client's pc). The program also tries to save this excel file on the NT machine.
Below is the code that I am using. I just can't get it to open up on the client's PC.
Also, we have noticed that whenever excel is generated by this means, it tends to save the file under a directory called Local Service or Network Service. These directories do not exist on an of the PCs. So we are a bit confused to why this is happening and how we could bypass it.
Look forward to reading your suggestions / explainations.
Kind regards,
Obi
Junior Progress Person
/* MAIN.P */
DEFINE VARIABLE hAppSrv AS HANDLE NO-UNDO.
DEFINE VARIABLE vFileLength AS INT NO-UNDO.
/* Connect to AppServer and run */
CREATE SERVER hAppSrv.
IF NOT hAppSrv:CONNECT("-AppService asbroker1 -H 6.0.50.130 -S 5162") /* CHANGE THIS */
THEN
DO:
{&OUT} "Could not connect to appserver".
END.
RUN generate.p ON hAppSrv (OUTPUT vFileLength, OUTPUT TABLE ttFile).
IF hAppSrv:TYPE = "SERVER"
THEN
DO:
hAppSrv
ISCONNECT ().
DELETE OBJECT hAppSrv.
END.
/* output to WebBrowser */
{&OUT} "Content-Type: application/vnd.ms-excel" SKIP
"Accept-Ranges: none" SKIP
"Content-Length: " vFileLength FORMAT ">>>>>>9" SKIP (1).
FOR EACH ttFile BY SeqNo:
PUT {&WEBSTREAM} CONTROL ttFile.FileData.
END.
-----------------------------------------------------------------------------------
/* generate.p */
DEFINE TEMP-TABLE ttFile
FIELD SeqNo AS INT
FIELD FileData AS RAW
INDEX ttFileIdx IS PRIMARY SeqNo ASCENDING.
DEFINE OUTPUT PARAMETER vLen AS INTEGER NO-UNDO.
DEFINE OUTPUT PARAMETER TABLE FOR ttFile.
DEFINE VARIABLE vRawData AS RAW NO-UNDO.
DEFINE VARIABLE vSeqNo AS INT NO-UNDO.
DEF VAR vFileName AS CHAR NO-UNDO.
DEFINE VARIABLE chExcelApplication AS COM-HANDLE.
DEFINE VARIABLE chWorkbook AS COM-HANDLE.
DEFINE VARIABLE chWorksheet AS COM-HANDLE.
DEFINE VARIABLE chChart AS COM-HANDLE.
DEFINE VARIABLE chWorksheetRange AS COM-HANDLE.
DEFINE VARIABLE iCount AS INTEGER.
DEFINE VARIABLE iIndex AS INTEGER.
DEFINE VARIABLE iTotalNumberOfOrders AS INTEGER.
DEFINE VARIABLE iMonth AS INTEGER.
DEFINE VARIABLE dAnnualQuota AS DECIMAL.
DEFINE VARIABLE dTotalSalesAmount AS DECIMAL.
DEFINE VARIABLE iColumn AS INTEGER INITIAL 1.
DEFINE VARIABLE cColumn AS CHARACTER.
DEFINE VARIABLE cRange AS CHARACTER.
def var bal-ytd as dec format "->>,>>>,>>9.99".
def var bal-mn as dec format "->>,>>>,>>9.99".
def var v-tot-ytd as dec format "->>,>>>,>>9.99".
def var v-tot-mn as dec format "->>,>>>,>>9.99".
def var cnt as int no-undo.
/*
DEF INPUT PARAM v-month AS INT NO-UNDO.
DEF INPUT PARAM v-year AS INT NO-UNDO.
*/
DEF VAR v-month AS INT NO-UNDO.
DEF VAR v-year AS INT NO-UNDO.
v-month = 7.
v-year = 2003.
DEF VAR v-date AS DATE NO-UNDO.
v-date = DATE(1, 1, v-year).
/* create a new Excel Application object */
CREATE "Excel.Application" chExcelApplication.
/* launch Excel so it is visible to the user */
chExcelApplication:Visible = FALSE.
/* create a new Workbook*/
chWorkbook = chExcelApplication:Workbooks:Add().
chWorksheet = chExcelApplication:Worksheets(1).
/* set the column names for the Worksheet */
chWorkSheet:Columns("A"):ColumnWidth = 15.
chWorkSheet:Columns("B"):ColumnWidth = 18.
chWorkSheet:Columns("C"):ColumnWidth = 30.
chWorkSheet:Columns("D"):ColumnWidth = 15.
chWorkSheet:Columns("E"):ColumnWidth = 15.
chWorkSheet:Columns("F"):ColumnWidth = 15.
chWorkSheet:Range("A1:F1"):Font:Bold = TRUE.
chWorkSheet:Range("A1"):Value = "Account".
chWorkSheet:Range("B1"):Value = "Category".
chWorkSheet:Range("C1"):Value = "Name".
chWorkSheet:Range("D1"):Value = "Balance Sheet".
chWorkSheet:Range("E1"):Value = "Balance Month".
chWorkSheet:Range("F1"):Value = "Balance ytd".
for each
nlacc no-lock break by bal-sheet by nl-acc1 by nl-acc2 by nl-acc3 by nl-acc4 by nl-acc5.
find
nlsum of nlacc where
nlsum.year-start-d = v-date no-lock no-error.
bal-ytd = 0.
bal-mn = 0.
if avail nlsum then do.
bal-mn = actual[v-month].
do cnt = 1 to v-month:
bal-ytd = bal-ytd + actual[cnt].
end.
end.
if bal-ytd = 0 and bal-mn = 0 then next.
find
flextable where
flextable.type = 7 and
flextable.code = int(nlacc.filler) no-lock no-error.
iColumn = iColumn + 1.
cColumn = STRING(iColumn).
cRange = "A" + cColumn.
chWorkSheet:Range(cRange):Value = nlacc.nl-acc1 + " " + nlacc.nl-acc2 + " " + nlacc.nl-acc3 + " " + nlacc.nl-acc4 + " " + nlacc.nl-acc5.
cRange = "B" + cColumn.
IF AVAIL flextable THEN DO:
chWorkSheet:Range(cRange):Value = flextable.codedesc.
cRange = "C" + cColumn.
END.
chWorkSheet:Range(cRange):Value = nlacc.NAME.
cRange = "D" + cColumn.
chWorkSheet:Range(cRange):Value = nlacc.bal-sheet.
cRange = "E" + cColumn.
chWorkSheet:Range(cRange):Value = bal-mn.
cRange = "F" + cColumn.
chWorkSheet:Range(cRange):Value = bal-ytd.
ASSIGN
v-tot-mn = v-tot-mn + bal-mn
v-tot-ytd = v-tot-ytd + bal-ytd.
IF FIRST-OF(nlacc.bal-sheet) THEN DO:
ASSIGN
v-tot-mn = 0
v-tot-ytd = 0.
END.
IF LAST-OF(nlacc.bal-sheet) THEN DO:
iColumn = iColumn + 2.
cColumn = STRING(iColumn).
cRange = "E" + cColumn.
chWorkSheet:Range(cRange):Value = v-tot-mn.
cRange = "F" + cColumn.
chWorkSheet:Range(cRange):Value = v-tot-ytd.
iColumn = iColumn + 1.
END.
END.
vFileName = "test.xls".
chWorkbook:SaveAs (vFileName,,,,,,).
chExcelApplication:QUIT ().
/* READ FILE */
INPUT FROM VALUE (vFileName) BINARY NO-MAP NO-CONVERT.
/* release com-handles */
RELEASE OBJECT chExcelApplication.
RELEASE OBJECT chWorkbook.
RELEASE OBJECT chWorksheet.
RELEASE OBJECT chChart.
RELEASE OBJECT chWorksheetRange.
/* Get the length */
SEEK INPUT TO END.
vLen = SEEK (INPUT).
SEEK INPUT TO 0.
/* Read file */
ASSIGN LENGTH (vRawData) = 8192.
REPEAT:
IMPORT UNFORMATTED vRawData.
CREATE ttFile.
ASSIGN ttFile.SeqNo = vSeqNo
vSeqno = vSeqNo + 1
ttFile.FileData = vRawData.
END.
ASSIGN LENGTH (vRawData) = 0.
INPUT CLOSE.
/* Delete temporary file */
OS-DELETE VALUE (vFileName).
We are running a webspeed program that is on unix and getting it to talk to a program that is on an NT machine through the appserver. This is because we want the program to directly output to an excel spreadsheet on a client's PC.
What's happening is that excel is opening up where the appserver is located. We are trying to get the spreadsheet to open in the main browser where the program is being run from (client's pc). The program also tries to save this excel file on the NT machine.
Below is the code that I am using. I just can't get it to open up on the client's PC.
Also, we have noticed that whenever excel is generated by this means, it tends to save the file under a directory called Local Service or Network Service. These directories do not exist on an of the PCs. So we are a bit confused to why this is happening and how we could bypass it.
Look forward to reading your suggestions / explainations.
Kind regards,
Obi
Junior Progress Person
/* MAIN.P */
DEFINE VARIABLE hAppSrv AS HANDLE NO-UNDO.
DEFINE VARIABLE vFileLength AS INT NO-UNDO.
/* Connect to AppServer and run */
CREATE SERVER hAppSrv.
IF NOT hAppSrv:CONNECT("-AppService asbroker1 -H 6.0.50.130 -S 5162") /* CHANGE THIS */
THEN
DO:
{&OUT} "Could not connect to appserver".
END.
RUN generate.p ON hAppSrv (OUTPUT vFileLength, OUTPUT TABLE ttFile).
IF hAppSrv:TYPE = "SERVER"
THEN
DO:
hAppSrv
ISCONNECT ().DELETE OBJECT hAppSrv.
END.
/* output to WebBrowser */
{&OUT} "Content-Type: application/vnd.ms-excel" SKIP
"Accept-Ranges: none" SKIP
"Content-Length: " vFileLength FORMAT ">>>>>>9" SKIP (1).
FOR EACH ttFile BY SeqNo:
PUT {&WEBSTREAM} CONTROL ttFile.FileData.
END.
-----------------------------------------------------------------------------------
/* generate.p */
DEFINE TEMP-TABLE ttFile
FIELD SeqNo AS INT
FIELD FileData AS RAW
INDEX ttFileIdx IS PRIMARY SeqNo ASCENDING.
DEFINE OUTPUT PARAMETER vLen AS INTEGER NO-UNDO.
DEFINE OUTPUT PARAMETER TABLE FOR ttFile.
DEFINE VARIABLE vRawData AS RAW NO-UNDO.
DEFINE VARIABLE vSeqNo AS INT NO-UNDO.
DEF VAR vFileName AS CHAR NO-UNDO.
DEFINE VARIABLE chExcelApplication AS COM-HANDLE.
DEFINE VARIABLE chWorkbook AS COM-HANDLE.
DEFINE VARIABLE chWorksheet AS COM-HANDLE.
DEFINE VARIABLE chChart AS COM-HANDLE.
DEFINE VARIABLE chWorksheetRange AS COM-HANDLE.
DEFINE VARIABLE iCount AS INTEGER.
DEFINE VARIABLE iIndex AS INTEGER.
DEFINE VARIABLE iTotalNumberOfOrders AS INTEGER.
DEFINE VARIABLE iMonth AS INTEGER.
DEFINE VARIABLE dAnnualQuota AS DECIMAL.
DEFINE VARIABLE dTotalSalesAmount AS DECIMAL.
DEFINE VARIABLE iColumn AS INTEGER INITIAL 1.
DEFINE VARIABLE cColumn AS CHARACTER.
DEFINE VARIABLE cRange AS CHARACTER.
def var bal-ytd as dec format "->>,>>>,>>9.99".
def var bal-mn as dec format "->>,>>>,>>9.99".
def var v-tot-ytd as dec format "->>,>>>,>>9.99".
def var v-tot-mn as dec format "->>,>>>,>>9.99".
def var cnt as int no-undo.
/*
DEF INPUT PARAM v-month AS INT NO-UNDO.
DEF INPUT PARAM v-year AS INT NO-UNDO.
*/
DEF VAR v-month AS INT NO-UNDO.
DEF VAR v-year AS INT NO-UNDO.
v-month = 7.
v-year = 2003.
DEF VAR v-date AS DATE NO-UNDO.
v-date = DATE(1, 1, v-year).
/* create a new Excel Application object */
CREATE "Excel.Application" chExcelApplication.
/* launch Excel so it is visible to the user */
chExcelApplication:Visible = FALSE.
/* create a new Workbook*/
chWorkbook = chExcelApplication:Workbooks:Add().
chWorksheet = chExcelApplication:Worksheets(1).
/* set the column names for the Worksheet */
chWorkSheet:Columns("A"):ColumnWidth = 15.
chWorkSheet:Columns("B"):ColumnWidth = 18.
chWorkSheet:Columns("C"):ColumnWidth = 30.
chWorkSheet:Columns("D"):ColumnWidth = 15.
chWorkSheet:Columns("E"):ColumnWidth = 15.
chWorkSheet:Columns("F"):ColumnWidth = 15.
chWorkSheet:Range("A1:F1"):Font:Bold = TRUE.
chWorkSheet:Range("A1"):Value = "Account".
chWorkSheet:Range("B1"):Value = "Category".
chWorkSheet:Range("C1"):Value = "Name".
chWorkSheet:Range("D1"):Value = "Balance Sheet".
chWorkSheet:Range("E1"):Value = "Balance Month".
chWorkSheet:Range("F1"):Value = "Balance ytd".
for each
nlacc no-lock break by bal-sheet by nl-acc1 by nl-acc2 by nl-acc3 by nl-acc4 by nl-acc5.
find
nlsum of nlacc where
nlsum.year-start-d = v-date no-lock no-error.
bal-ytd = 0.
bal-mn = 0.
if avail nlsum then do.
bal-mn = actual[v-month].
do cnt = 1 to v-month:
bal-ytd = bal-ytd + actual[cnt].
end.
end.
if bal-ytd = 0 and bal-mn = 0 then next.
find
flextable where
flextable.type = 7 and
flextable.code = int(nlacc.filler) no-lock no-error.
iColumn = iColumn + 1.
cColumn = STRING(iColumn).
cRange = "A" + cColumn.
chWorkSheet:Range(cRange):Value = nlacc.nl-acc1 + " " + nlacc.nl-acc2 + " " + nlacc.nl-acc3 + " " + nlacc.nl-acc4 + " " + nlacc.nl-acc5.
cRange = "B" + cColumn.
IF AVAIL flextable THEN DO:
chWorkSheet:Range(cRange):Value = flextable.codedesc.
cRange = "C" + cColumn.
END.
chWorkSheet:Range(cRange):Value = nlacc.NAME.
cRange = "D" + cColumn.
chWorkSheet:Range(cRange):Value = nlacc.bal-sheet.
cRange = "E" + cColumn.
chWorkSheet:Range(cRange):Value = bal-mn.
cRange = "F" + cColumn.
chWorkSheet:Range(cRange):Value = bal-ytd.
ASSIGN
v-tot-mn = v-tot-mn + bal-mn
v-tot-ytd = v-tot-ytd + bal-ytd.
IF FIRST-OF(nlacc.bal-sheet) THEN DO:
ASSIGN
v-tot-mn = 0
v-tot-ytd = 0.
END.
IF LAST-OF(nlacc.bal-sheet) THEN DO:
iColumn = iColumn + 2.
cColumn = STRING(iColumn).
cRange = "E" + cColumn.
chWorkSheet:Range(cRange):Value = v-tot-mn.
cRange = "F" + cColumn.
chWorkSheet:Range(cRange):Value = v-tot-ytd.
iColumn = iColumn + 1.
END.
END.
vFileName = "test.xls".
chWorkbook:SaveAs (vFileName,,,,,,).
chExcelApplication:QUIT ().
/* READ FILE */
INPUT FROM VALUE (vFileName) BINARY NO-MAP NO-CONVERT.
/* release com-handles */
RELEASE OBJECT chExcelApplication.
RELEASE OBJECT chWorkbook.
RELEASE OBJECT chWorksheet.
RELEASE OBJECT chChart.
RELEASE OBJECT chWorksheetRange.
/* Get the length */
SEEK INPUT TO END.
vLen = SEEK (INPUT).
SEEK INPUT TO 0.
/* Read file */
ASSIGN LENGTH (vRawData) = 8192.
REPEAT:
IMPORT UNFORMATTED vRawData.
CREATE ttFile.
ASSIGN ttFile.SeqNo = vSeqNo
vSeqno = vSeqNo + 1
ttFile.FileData = vRawData.
END.
ASSIGN LENGTH (vRawData) = 0.
INPUT CLOSE.
/* Delete temporary file */
OS-DELETE VALUE (vFileName).
