How to Create Excel Sheets from an Application Engine?
Recently one of my friends asked me this question. My initial thought was to either create a .csv file or use object oriented techniques that allow creation of Microsoft Excel as an object from PeopleSoft. Little later, I came to know from one of my colleagues that creation of Excel Sheet is much simpler. Here is how:
By using a comma as the field separator and saving the file as .CSV the purpose would be achieved as the file can be opened in MS-Excel. But the user wants a plain Excel sheet and not .csv and others. From an App Engine, we can open a .XLS file for writing. However, the challenge lies in 'what value' to be used as a field separator instead of comma for a .CSV file. In an excel sheet, after we type data in to one column, to shift to another column we just hit the tab button. So what's actually needed is we need to use 'tab' between fields. The ASCII equivalent of 'tab' is char(9); so just by using char(9) as the separator between two fields we can write data in columns sequentially in a Excel sheet through Peoplecode.
Example:
local string &Filename = "\\folder\abc.XLS";Use / -- for unix server
local file &File = GetFile(&Filename, "w", %FilePath_Absolute);
local string &Field1 = "A";
local string &Field2 = "B";
local string &Separator = char(9);
local string &Data_String = &Field1 | &Separator | &Field2;
&File.writeline(&Data_String);
&File.Close();
By using a comma as the field separator and saving the file as .CSV the purpose would be achieved as the file can be opened in MS-Excel. But the user wants a plain Excel sheet and not .csv and others. From an App Engine, we can open a .XLS file for writing. However, the challenge lies in 'what value' to be used as a field separator instead of comma for a .CSV file. In an excel sheet, after we type data in to one column, to shift to another column we just hit the tab button. So what's actually needed is we need to use 'tab' between fields. The ASCII equivalent of 'tab' is char(9); so just by using char(9) as the separator between two fields we can write data in columns sequentially in a Excel sheet through Peoplecode.
Example:
local string &Filename = "\\folder\abc.XLS";Use / -- for unix server
local file &File = GetFile(&Filename, "w", %FilePath_Absolute);
local string &Field1 = "A";
local string &Field2 = "B";
local string &Separator = char(9);
local string &Data_String = &Field1 | &Separator | &Field2;
&File.writeline(&Data_String);
&File.Close();
Shyam - We can also provide data values in specific cells in excel. Below link for reference.
ReplyDeletehttp://peoplesoft.wikidot.com/creating-excel-files-from-app-engine
@Malar Thanks for the link. When VJ asked me about creation of excel from AE I suggested this same way where we can open Microsoft excel sheet as an object and do stuff. However, one of my colleague said he had done using some ascii and character code. We found that char(9) is the substitution for tab and it worked out. But this way we cannot change fonts, formats etc however using the method you suggested it is possible. Thanks for the info anyway.
ReplyDeleteHi-
DeleteWould you know if it is possible to do excel formatting from App Engine with .XLS output?
Thanks for the info. Look forward for your thoughts on this.
For excel format (xls) output, you can follow the code metntioned in the post below.
Deletehttp://www.peoplesoftjournal.blogspot.com/2013/03/creating-excel-file-from-peoplesoft.html
I reviewed blog but didn't find how to format the cell background and add border lines?
DeleteGood day! Do you turn to get help to some specialized tricks to attract more readers your site on a regular basis? Can't wait to see your answer.
ReplyDeleteHi
ReplyDeleteI have a requirement to read the excel file using Application Engine.
Please let me know if you have any idea
I would suggest to go through peoplebooks and treat xls like any other file
ReplyDeleteHi Shyam thank you so much for posting this code it is working like a charm for me. My boss just asked me to add worksheets to this same excel report and now I am struggling finding an easy way like the one you provided to add worksheets to an excel report. If you or anyone of the folks on the forum could give me a hand I would greatly appreciate it. Thank you.
ReplyDeleteHi Shyam,
ReplyDeleteThe excel file created as per the above code snippets, is actually a flat/CSV file and not an excel workbook.
It is like changing the extension of a txt file to xls. In that case MSExel gives error message and then opens it.
My client does not except this.
Anyway thanks for the approach.
then use below line of code:
DeleteUse
Local string &Separator = Char(9) | Char(160);
instead of
local string &Separator = char(9);
it will work for sure.
--Nagaprasanthi
I have chinese characters hard coded as the file header that is to be written to xls. After the app engine processed, the csv file is attached to an email which the email is sent as notification to users. When email is received and open the xls file directly from the email attachment, the chinese characters are shown as junk character. Any idea how to resolve this?
ReplyDeleteIn the machine, where file is opened, please verify if Chinese font is supported. Else you may have to add Chinese font.
Delete