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

[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

[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.

[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.

[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.

[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;             

[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.

[Maven JJ]

Thank you so much Robert and vasanthz. Will work from here.