Re: Create Excel spreadsheet from RPGLE -- RPG400-L

On 6/1/05, Scott Johnson <sjohnson@xxxxxxxxxxxxxxx> wrote:
> 
> Tony,
> 
> Attachments are stripped off. Can you put your subproc in line or put
> it on a website someplace.
> 
> -- Scott
> 

Hmmm... Must be a firewall problem. Well, here's the subprocedure:

D/Copy carollat/qrpglesrc,ifsio_h 

// Display the report in Excel 
P Excelify B 
D Excelify PR 
D Excelify PI 

// Standalones 
D fd S 10I 0 Inz 
D RC S 10I 0 Inz 
D UserName S 10A Inz(*User) 
D Today S D Inz(*Sys) 
D FileName S 43A Varying 
D OpenName S 200A Varying 
D EndOfSheet S 22A Inz('</table></body></html>') 

// Constants 
D Tb1 C '</td><td>' 
D Tb2 C '</td><td bgcolor="#dfdfdf">' 
D FilePrefix C '/home/mcare_appeals_Extracts/' 
D FileSuffix C '.XLS' 
D StartPCO C 'STRPCO' 
D OpenPrefix C 'STRPCCMD PCCMD(''\\10.10.4.15\root\-
D home\mcare_appeals_extracts\' 
D OpenSuffix C '.xls'') PAUSE(*NO)' 

// Structures 
D Title1 DS Qualified 
D Chunk1 221A Inz('<html><head><title>Medicare App-
D eals Extract Listing</title><h2><div-
D style="text-align:center;">MedAmeri-
D ca Billing Services, Inc. -- Medicar-
D e Appeals Extract Listing</div></h2>-
D <h3><div style="text-align:center;co-
D lor:blue">') 

D Title2 DS Qualified 
D Chunk1 368A Inz('</div></h3></head><body><table -
D align="center" border="1" style="wid-
D th:800px;" title="Detail"><tr align=-
D "center" bgcolor="blue" style="color-
D :white;font-weight:bold;"><th>Site</-
D th><th>Account#</th><th>Patient_Name-
D </th><th>Bill_ID</th><th>Ins#</th><t-
D h>1st_Extract</th><th>Code</th><th>L-
D ast_Extract</th><th>Code</th><th>Use-
D r</th><th>Pmts_After</th><th>Pmts_To-
D tal</th></tr>') 

D DSTitle DS Qualified 
D Chunk1 12A Inz('Result Code ') 
D RCChoice 3A Inz 
D Chunk2 4A Inz(' -- ') 
D EarlyDate 10A Inz 

D DSDetail DS Qualified 
D Chunk1 67A Inz('<tr align="center" style="fon- 
D t-weight:bold;"><td bgcolor="#dfdfdf-
D ">') 
D Site 3A Inz 
D Chunk2 9A Inz(Tb1) 
D Account 15A Inz 
D Chunk3 27A Inz(Tb2) 
D Patname 20A Inz 
D Chunk4 9A Inz(Tb1) 
D BillId 2A Inz 
D Chunk5 27A Inz(Tb2) 
D Insur 5A Inz 
D Chunk6 9A Inz(Tb1) 
D FirstXDate 10A Inz 
D Chunk7 27A Inz(Tb2) 
D FirstXCode 3A Inz 
D Chunk8 9A Inz(Tb1) 
D LastXDate 10A Inz 
D Chunk9 27A Inz(Tb2) 
D LastXCode 3A Inz 
D Chunk10 9A Inz(Tb1) 
D UserName 10A Inz 
D Chunk11 27A Inz(Tb2) 
D PmtsAfter 10A Inz 
D Chunk12 9A Inz(Tb1) 
D PmtTot 10A Inz 
D Chunk13 10A Inz('</td></tr>') 

/Free 
FileName=FilePrefix+%Trim(UserName)+FileSuffix; 

// Remove file if it exists first, so we don't overwrite beg file seg 
RC=unlink(FileName); 

// Create file with correct code page, in text mode 
fd=open(Filename:O_CREAT+O_WRONLY+O_CODEPAGE: 
S_IWUSR+S_IRUSR+S_IRGRP+S_IROTH:819); 
RC=close(fd); 
fd=open(FileName:O_WRONLY+O_TEXTDATA); 

// Write header 
RC=write(fd:%addr(Title1):%Size(Title1)); 

DSTitle.RCChoice=DFRes; 
DSTitle.EarlyDate=%Char(Today:*usa); 
RC=write(fd:%addr(DSTitle):%Size(DSTitle)); 

RC=write(fd:%addr(Title2):%Size(Title2)); 

// Write detail lines 
If %Open(MCareTmp); 
Close MCareTmp; 
EndIf; 
Open MCareTmp; 

Read Mctmp Mti; 
Dow Not %EOF(MCareTmp); 
DSDetail.Site=Mti.Tmsite; 

// Prevent Excel from recognizing account numbers as dates 
DSDetail.Account='="' + Mti.Tmacct + '"'; 

DSDetail.Patname=Mti.TMPatName; 
DSDetail.BillId=Mti.TMBillId; 
DSDetail.Insur=Mti.Tmins; 
DSDetail.FirstXDate=Mti.Tmx1d; 
DSDetail.FirstXCode=Mti.TMX1c; 
DSDetail.LastXDate=Mti.Tmxld; 
DSDetail.LastXCode=Mti.Tmxlc; 
DSDetail.UserName=Mti.TMUser; 
DSDetail.PmtsAfter=Mti.Tmaft; 
DSDetail.PmtTot=Mti.Tmtot; 

RC=write(fd:%addr(DSDetail):%Size(DSDetail)); 

Read MCTmp Mti; 
EndDo; 

RC=write(fd:%addr(EndOfSheet):%size(EndOfSheet)); 
RC=close(fd); 

OpenName=OpenPrefix+%Trim(UserName)+OpenSuffix; 
RC=system(StartPCO); 
RC=system(OpenName); 

Return; 
/End-Free 
P Excelify E 

// Determine if the res code relates to medicare appeals------------------
P/Copy carollat/qrpglesrc,isitmca