RPG Program Sample for Creating a New Spreadsheet Output File and populating the spreadsheet
From Support
The enclosed RPG sample copies a blank spreadsheet template file and then populates the file with data.
*********************************************************************
* Program Name: SQLTEST06R
* Purpose:
* 1) Connects to a RPGSQL server using specified IP address
* 2) Creates a New MS Excel Spreadsheet via SQL_PCFileCopy
* 3) Creates a New Sheet (Sheet1) in the spreadsheet file via ADO
* 4) Inserts 10 duplicate records into Sheet1
* 5) Closes ADO connection.
* 6) Closes RPGSQL server connection.
*
* Note: This sample does no error checking. In your own code you
* will need to check the return codes and last error by
* using the last error return info.
*
*********************************************************************
/COPY SOURCE,RPGSQLH
D quot S 1 INZ(')
D ExcelFile S 255A
D ExcelTemplate S 255A
D DefaultDir S 255A
*-----------------------------------------------------------------------------
* Main Program Processing
*-----------------------------------------------------------------------------
C *ENTRY PLIST
C PARM IPADDR 100
*-----------------------------------------------------------------------------
* Connect to RPG/SQL Server
*-----------------------------------------------------------------------------
C* ** Connect to RPG SQL Server
C Eval SQL_Socket = SQL_Connect(%TRIM(IPADDR))
C* ** Exit with Error Return - TCP Server Connect
C If SQL_Socket = -999
C Eval Rtn = -1
C Eval *INLR = *On
C Return
C Endif
*-----------------------------------------------------------------------------
* Open ADO SQL database connection
* Create new MS Excel spreadsheet file named c:\irpgsql.xls
* using the spreadsheet file template and the Excel OLEDB driver.
* **NOTE**
* Make sure you have a blank spreadsheet template named: c:\irpgsql.xls
* or this sample may not work correctly.
*-----------------------------------------------------------------------------
C Eval ExcelTemplate = 'c:\irpgsql.xls'
C Eval ExcelFile = 'c:\irpgsqlnew.xls'
C Eval DefaultDir = 'c:\'
* ** Make sure old spreadsheet does not exist
C Eval Rtn = SQL_PCFileDel(SQL_Socket:
C Excelfile)
* ** Copy spreadsheet template to new file name
C Eval Rtn = SQL_PCFileCopy(SQL_Socket:
C ExcelTemplate:Excelfile)
* ** Open spreadsheet connection
C Eval Rtn = SQL_DBOpenConn(SQL_Socket:
C 'Driver={Microsoft Excel Driver ' +
C '(*.xls)}; ' +
C 'DriverID=790; ' +
C 'Dbq=' + %trimr(ExcelFile) + '; ' +
C 'Defaultdir=' + %trimr(DefaultDir) + '; ' +
C 'ReadOnly=False')
*-----------------------------------------------------------------------------
* Create Spreadsheet Sheet - Sheet1 in Excel File c:\irpgsql.xls
* You can insert data into multiple sheets if required and they
* will be created automatically within the Excel file.
*-----------------------------------------------------------------------------
C Eval Rtn = SQL_RunSQLExec(SQL_Socket:
C 'create table Sheet1' +
C '(First char(100),' +
C 'Last char(100),' +
C 'Address1 char(100),' +
C 'Address2 char(100),' +
C 'City char(100),' +
C 'State char(100),' +
C 'Zip char(100),' +
C 'Phone char(100),' +
C 'Fax char(100),' +
C 'Email char(100),' +
C 'Date1 date,' +
C 'Dollars double)')
*-----------------------------------------------------------------------------
* Loop and insert multiple records
*-----------------------------------------------------------------------------
C Z-add 0 ct 5 0
C Dow ct < 10
*-----------------------------------------------------------------------------
* Run Query to Insert Records into Sheet1
*-----------------------------------------------------------------------------
C Eval Rtn = SQL_RunSQLExec(SQL_Socket:
C 'insert into Sheet1' +
C '(First,' +
C 'Last,' +
C 'Address1,' +
C 'Address2,' +
C 'City,' +
C 'State,' +
C 'Zip,' +
C 'Phone,' +
C 'Fax,' +
C 'Email,' +
C 'Date1,' +
C 'Dollars) ' +
C 'VALUES(' +
C quot + 'James' + quot + ',' +
C quot + 'Johnson' + quot + ',' +
C quot + '111 Main Street' + quot + ',' +
C quot + 'Apt 5' + quot + ',' +
C quot + 'Mpls' + quot + ',' +
C quot + 'MN' + quot + ',' +
C quot + '55555' + quot + ',' +
C quot + '111-111-1111' + quot + ',' +
C quot + '222-222-2222' + quot + ',' +
C quot + 'james@johnson.com' + quot + ',' +
C quot + '12/25/2002' + quot + ',' +
C '123456.78' +
C ')')
C Eval ct = ct + 1
C Enddo
*-----------------------------------------------------------------------------
* Close ADO Database Conection
*-----------------------------------------------------------------------------
C callp SQL_DBCloseConn(SQL_Socket)
*-----------------------------------------------------------------------------
C* Disconnect from RPGSQL server
*-----------------------------------------------------------------------------
C callp SQL_Disconnect(SQL_Socket)
C SETON LR
