I've been a little busy lately, so I asked my friend and fellow PeopleSoft expert David Vandiver if he would like to contribute an article to the blog. He came back with a very interesting post about generating Excel reports from PeopleCode using ISCripts (see below).
Incidentally, if you have a great PeopleSoft approach or tip you'd like to share but don't want to start your own blog, feel free to send it to me ( This e-mail address is being protected from spambots. You need JavaScript enabled to view it ) and I'll do my best to get it posted.
And now, here's David's article:
Want to add some cool features to PeopleSoft while you wait for Fusion? Want to start producing Excel documents well before XML Publisher is available? Then this blog entry is for you.
I have built a free PeopleCode library that allows custom Excel reports to be built via a PeopleSoft web page. The end result allows an end-user to click a link or button and a separate PeopleSoft window will be launched giving an Excel document. And not just any plain Excel format, but a rich Excel document with fonts, color, formulas, and multiple worksheets. The code is part of an open source project at http://sourceforge.net/projects/sqr2xml, which also has a solution for producing Excel documents from SQR.
How it works: PeopleSoft has a function called iScript (starting in Tools 8.1) that allows a developer to launch a new web window and build the HTML. It is a great tool to build a quick report (Purchase Order or Voucher Coversheet). Instead of creating HTML, I built XML data that Excel expects. Once the new window opens, it sees the XML schema in the file and will launch Excel.
Any developer can code the XML, just like the HTML, using iScript. But to simplify the process, I created a library of functions the developer can call to write the XML. By using the library (which is in PeopleCode as well), the developer doesn't have to write XML or even understand what XML is. All the XML writing is done by the library of functions.
I also wrote a manual that accompanies the free code. In the manual, you'll find more info on how this works, as well as all the functions you can call and their syntax. Some of the features are:
This code is already in use in several companies and universities in America and abroad, including the University of Houston (my current client). My hope is that the code can help customers get more out of PeopleSoft as they wait for Fusion. Feel free to write me if you want more information or want to share your successes.
Code location: http://sourceforge.net/projects/sqr2xml
Enjoy
David Vandiver
This e-mail address is being protected from spambots. You need JavaScript enabled to view it
VandiverHouse Consulting Inc.
Great!
written by Achal, May 23, 2008
...
written by jonathan, August 12, 2008
Business Systems Analyst
written by Eric, January 22, 2009
...
written by kalyan, July 30, 2011
Incidentally, if you have a great PeopleSoft approach or tip you'd like to share but don't want to start your own blog, feel free to send it to me ( This e-mail address is being protected from spambots. You need JavaScript enabled to view it ) and I'll do my best to get it posted.
And now, here's David's article:
Want to add some cool features to PeopleSoft while you wait for Fusion? Want to start producing Excel documents well before XML Publisher is available? Then this blog entry is for you.
I have built a free PeopleCode library that allows custom Excel reports to be built via a PeopleSoft web page. The end result allows an end-user to click a link or button and a separate PeopleSoft window will be launched giving an Excel document. And not just any plain Excel format, but a rich Excel document with fonts, color, formulas, and multiple worksheets. The code is part of an open source project at http://sourceforge.net/projects/sqr2xml, which also has a solution for producing Excel documents from SQR.
How it works: PeopleSoft has a function called iScript (starting in Tools 8.1) that allows a developer to launch a new web window and build the HTML. It is a great tool to build a quick report (Purchase Order or Voucher Coversheet). Instead of creating HTML, I built XML data that Excel expects. Once the new window opens, it sees the XML schema in the file and will launch Excel.
Any developer can code the XML, just like the HTML, using iScript. But to simplify the process, I created a library of functions the developer can call to write the XML. By using the library (which is in PeopleCode as well), the developer doesn't have to write XML or even understand what XML is. All the XML writing is done by the library of functions.
I also wrote a manual that accompanies the free code. In the manual, you'll find more info on how this works, as well as all the functions you can call and their syntax. Some of the features are:
- Cell Styles (font, color, number format, alignment)
- Page formatting (margins, header/footer)
- Row styles (hidden, height)
- Column styles (hidden, width)
- Cell types (character, number, dates, etc)
- Formulas (simple to complex)
- Multiple Worksheets
- Page Breaks and Auto Filter
This code is already in use in several companies and universities in America and abroad, including the University of Houston (my current client). My hope is that the code can help customers get more out of PeopleSoft as they wait for Fusion. Feel free to write me if you want more information or want to share your successes.
Code location: http://sourceforge.net/projects/sqr2xml
Enjoy
David Vandiver
This e-mail address is being protected from spambots. You need JavaScript enabled to view it
VandiverHouse Consulting Inc.
Trackback(0)
TrackBack URI for this entry
Comments (5)
Subscribe to this comment's feed
written by Achal, May 23, 2008
Hey Dave,
i didn't know you had a version of the library in PCode also. Nice!
BTW: I started a blog to write about PS related "good stuff" - mentioned SQL2XML on one of the entries:
http://rowset.organizzi.com/2008/05/20/writing-excel-files-from-sqr-programs/
i didn't know you had a version of the library in PCode also. Nice!
BTW: I started a blog to write about PS related "good stuff" - mentioned SQL2XML on one of the entries:
http://rowset.organizzi.com/2008/05/20/writing-excel-files-from-sqr-programs/
-
- +0
-
-
written by jonathan, August 12, 2008
hi david,
i really like your blog and want to use the stuffs you introduced. however, the link you provided just didnt work. if its not too much for you, please send me a copy of it instead.
Thank you in advance!
i really like your blog and want to use the stuffs you introduced. however, the link you provided just didnt work. if its not too much for you, please send me a copy of it instead.
Thank you in advance!
-
- +0
-
-
written by Eric, January 22, 2009
Any thoughts on how to handle Data Groupings using the PeopleCode to XML methodology? According to [urlhttp://msdn.microsoft.com/en-us/library/aa140062.aspx on XML Spreadsheet (XMLSS) does not include Data Grouping. Any further ideas on this would be most helpful.
FYI I have successfully used your library to create an XML Spreadsheet for formatting and such and it has proven to be very useful. Thank you.
FYI I have successfully used your library to create an XML Spreadsheet for formatting and such and it has proven to be very useful. Thank you.
-
- +0
-
-
written by kalyan, July 30, 2011
Hi . Good to see that you have come up with list of options that are possible in excel sheet created by people code. I have seen the same technique being used by Peoplesoft in some of the delivered functionalities to generate the Excel file. Readers can also refer to the code written in sourcing module in Psoft finance . It generates an XML based excel file through people code. Howw eever this post makes the job easy to find all the properties that can be applied to a cell and other features..
The problem what we have seen in this approach is , a performance issue when generating an excel file with huge data or formattings .
I some how feel ,its beetter to generate excel file through Java methods if any .Your thoughts would add much value now. Thanks .Kalyan.D
The problem what we have seen in this approach is , a performance issue when generating an excel file with huge data or formattings .
I some how feel ,its beetter to generate excel file through Java methods if any .Your thoughts would add much value now. Thanks .Kalyan.D
-
- +0
-
-
Write comment
| Next > |
|---|
Last Updated on Thursday, 30 March 2006 17:37.
Thx.