Page 1 of 1

Add different tabs in the spread sheet and send attachment using IEBGENER.

Posted: Fri Jan 06, 2017 9:08 am
by Maven JJ
Hi All ,

I need to send the data in a single excel but in different tabs using mainframe. As of now, I'm able to send data in different excel sheet but not in tabs of the same sheet.

Is it possible to put data into same excel sheet but in different tabs using IEBGENER or any other utilities ?

Thanks

Re: Add different tabs in the spread sheet and send attachment using IEBGENER.

Posted: Fri Jan 06, 2017 3:58 pm
by nicc
I presume you mean that you have a mainframe data set with the data in CSV format that is sent to a PC where Excel can load it. You now want to send data for multiple sheets in one data set?

Have you tried unloading multiple sheets in Excel to one CSV format file? If not, try it. If that works, can Excel reload it properly? If so, look at the format of the CSV file that was created and do your analysis from there.

Re: Add different tabs in the spread sheet and send attachment using IEBGENER.

Posted: Fri Jan 06, 2017 4:43 pm
by Robert Sample
Is it possible to put data into same excel sheet but in different tabs using IEBGENER or any other utilities ?
NO -- none of the mainframe utilities understand Excel formats. It used to be possible (and presumably still is) to purchase a product that can handle Excel spreadsheets on the mainframe -- which is the ONLY way to do what you want, unless your organization wants you to spend a year (or four) writing code to the Microsoft specification document to handle spreadsheets (the document was over 1,000 pages the last time I looked at it about 4 years ago; I doubt it has shrunk with the new releases of Excel).

Mainframe utilities allow you to generate comma-delimited data sets to transfer to a machine that can run to Excel -- but .CSV files can only be used to generate a single tab in Excel. You could generate multiple comma-delimited data sets and manually combine them into different tabs of an Excel file, but that's not a viable solution for any production workload.

Re: Add different tabs in the spread sheet and send attachment using IEBGENER.

Posted: Sat Jan 07, 2017 3:52 am
by Robert Sample
Update: it is possible to create a multi-tabbed spreadsheet in SAS using the ExcelXP tagset and ODS. If you have SAS (at least base release 9.1 or higher) installed on your mainframe and want to try this, you'll want to reference http://support.sas.com/rnd/base/ods/tem ... VSODS3.pdf for SAS tips and http://www2.sas.com/proceedings/forum2007/229-2007.pdf for some examples.

Re: Add different tabs in the spread sheet and send attachment using IEBGENER.

Posted: Tue Jan 10, 2017 6:11 am
by vasanthz
Below is a sample SAS code I use, which generates separate Excel Sheets for each occurrence of BY variable.

Code: Select all

ODS TAGSETS.EXCELXP FILE = 'MAINFRAME.PS.OUTPUT.FILE' RS=NONE STYLE=NORMAL 
OPTIONS( SHEET_INTERVAL='BYGROUP' );                               
PROC REPORT DATA = SAS.DATASET NOFS;                            
BY SYSTEM;                                                         
.
.
.
.
                                    
RUN;                                 
ODS TAGSETS.EXCELXP CLOSE;             

Re: Add different tabs in the spread sheet and send attachment using IEBGENER.

Posted: Tue Jan 10, 2017 6:49 am
by vasanthz
You want multi-worksheet with IEBGENER??
say no more, fam..

Code: Select all

//SHEETS   EXEC PGM=IEBGENER                                    
//SYSUT1   DD  *                                                
HELO LPARNAME                                                    
MAIL FROM:<HGwells@Wells.COM>                    
RCPT TO:<HGwells@Wells.COM>                     
DATA                                                            
FROM: HGwells@Wells.COM                          
TO: HGwells@Wells.COM                           
SUBJECT: THIS IS A TEST EMAIL                                   
MIME-VERSION: 1.0;                                              
IMPORTANCE: HIGH                                                
CONTENT-TYPE: TEXT/RICH TEXT;                                   
CONTENT-DISPOSITION: ATTACHMENT; FILENAME = GOD.XML         
<?xml version="1.0"?>                                           
<?mso-application progid="Excel.Sheet"?>                        
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"  
 xmlns:o="urn:schemas-microsoft-com:office:office"              
 xmlns:x="urn:schemas-microsoft-com:office:excel"               
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"        
 xmlns:html="http://www.w3.org/TR/REC-html40">                  
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> 
  <ActiveSheet>2</ActiveSheet>                                  
 </ExcelWorkbook>                                               
 <Worksheet ss:Name="Send">                                     
  <Table>                                                       
   <Row>                                                        
    <Cell><Data ss:Type="String">Se</Data></Cell>               
    <Cell><Data ss:Type="String">nd</Data></Cell>               
   </Row>                                                       
   <Row>                                                        
    <Cell><Data ss:Type="String">Nu</Data></Cell>               
    <Cell><Data ss:Type="String">d**</Data></Cell>              
   </Row>                                                       
  </Table>                                                      
    <Cell><Data ss:Type="Number">11</Data></Cell>     
    <Cell><Data ss:Type="Number">22</Data></Cell>     
   </Row>                                             
   <Row>                                              
    <Cell><Data ss:Type="Number">33</Data></Cell>     
    <Cell><Data ss:Type="Number">44</Data></Cell>     
   </Row>                                             
  </Table>                                            
 </Worksheet>                                         
</Workbook>                                           
.                                                     
//SYSIN    DD  DUMMY                                  
//SYSPRINT DD  SYSOUT=*                               
//SYSUT2   DD  SYSOUT=(B,SMTP)                        
//*                                                   
Obviously just a proof of concept.

Re: Add different tabs in the spread sheet and send attachment using IEBGENER.

Posted: Thu Jan 12, 2017 2:43 pm
by Maven JJ
Thank you so much Robert and vasanthz. Will work from here. :)