Creating Excel Files From App Engine

With SQR, you were always limited to writing CSV files, unless you had some sort of overpriced third party app you could call.

Well, with App Engine (and more specifically using a PeopleCode action in App Engine) you can create Excel files via the Microsoft Excel COM object, as long as the process scheduler is running on Windows.

The following is an example of doing so, from a template file called template.xls, which needs to sit on the server somewhere. The template file exists, because most of the time you don't want to be doing everything from PeopleCode. Mine usually contains images like company letterhead layout.

/* Set up the Excel COM objects and open the template file */Local object &oWorkApp, &oWorkBook;&oWorkApp = CreateObject("COM", "Excel.Application");&oWorkApp.DisplayAlerts = "False";&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");&oWorkBook.Open("C:\some_path_to\template.xls");&oWorkSheet = &oWorkApp.Worksheets("Sheet1");&oWorkApp.ActiveWorkBook.SaveAs("C:\your_output_file.xls");/* then go crazy with your worksheet object *//* eg */&oWorkSheet.Cells(1, 1).Value = "I'm adding stuff to be bolded";&oWorkSheet.Cells(1, 1).Font.Bold = True; /* Save Excel file and quit */&oWorkApp.ActiveWorkBook.Save();&oWorkApp.ActiveWorkBook.Close();&oWorkApp.DisplayAlerts = "True";&oWorkApp.Quit();

And that's it. And of course with COM objects, all the properties and methods available for use in VBA are available here. What I usually do is record a macro, and transfer over the code, but using PeopleSoft variable notation of course.

Please note I did this work a long time ago, and may have googled for some of the code. If it looks like yours, I'm happy to add a reference :)