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();

10 comments:

  1. Shyam - We can also provide data values in specific cells in excel. Below link for reference.

    http://peoplesoft.wikidot.com/creating-excel-files-from-app-engine

    ReplyDelete
  2. @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.

    ReplyDelete
    Replies
    1. Hi-
      Would 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.

      Delete
    2. For excel format (xls) output, you can follow the code metntioned in the post below.

      http://www.peoplesoftjournal.blogspot.com/2013/03/creating-excel-file-from-peoplesoft.html

      Delete
    3. I reviewed blog but didn't find how to format the cell background and add border lines?

      Delete
  3. Good 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.

    ReplyDelete
  4. Hi

    I have a requirement to read the excel file using Application Engine.

    Please let me know if you have any idea

    ReplyDelete
  5. I would suggest to go through peoplebooks and treat xls like any other file

    ReplyDelete
  6. Hi 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.

    ReplyDelete
  7. Hi Shyam,

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

    ReplyDelete