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.