Tuesday, April 04, 2006

Run PeopleSoft Query from Excel or to Excel

I got inspired from this post in PeopleSoft Corner.

I found following issues with the above solution.
  • You need to use third party XML PeopleCode Library

  • XML files generated by this process can be viewed only in Microsoft Excel 2003

  • XML Link is buggy in PeopleTools 8.43. It does not support special characters (#,$) etc. in password field. So if your site has implemented password controls which requires special characters, XML Link servelet will fail in 8.43 with error message Error: The return content is not xmllink data, please check log file.

  • Also XML Link does not use configuration.properties parameters defaultXMLLinkUSERID and defaultXMLLinkPWD. It defaults to userid and pwd supplied in the url



You can use following URL in IE or open it from inside Excel File Open dialog box. Remove the breaks from the URL before pasting it in ie or excel. Also change the portal registry (EMPLOYEE) and Portal Node (EMPL) and Site ID (ps) for your site.

http://hostname:port/psc/ps/EMPLOYEE/EMPL/s/>
WEBLIB_XXX.QRYRUN.FieldFormula.IScript_ToExcel?
userid=yyy&pwd=zzz&disconnect=y&type=public&
qryname=PT_SEC_PTLS_USERS&
MENUNAME=DATA_MOVER




You will need to create this IScript Function inside a Web Library Record (WEBLIB_XXX) as follows. You also need to assign the ISCript to appropriate permission list

Download Source Code

4 comments:

Philippe said...

Hi Ketan

I had a look at your interesting Post about "Run PeopleSoft Query from Excel or to Excel".

It seems the code and the link are lost. Do you have a possibility to get the information back into your wonderful blog? Thanks a lot.

Regards, Philippe

prateek said...

"Run PeopleSoft Query from Excel or to Excel" links are broken. Could you please update the links and can we use the same code/link to get the data in xml format rather then excel format.

thanks in advance

travelingwilly said...

What Happened to code ????

Manish Sharma said...

Local string &xquery = "Testing Apache";
Local string &xquery1 = "Testing sheet";
Local JavaObject &XSSFWorkbook = CreateJavaObject("org.apache.poi.xssf.usermodel.XSSFWorkbook");
Local JavaObject &jClass = GetJavaClass("java.lang.Class");
Local JavaObject &jCompileArgTypes = CreateJavaObject("java.lang.Class[]", &jClass.forName("java.lang.String"));
Local JavaObject &jCompileMethod = &XSSFWorkbook.getClass().getDeclaredMethod("createSheet", &jCompileArgTypes);

Local JavaObject &sheet1 = &jCompileMethod.invoke(&XSSFWorkbook, CreateJavaObject("java.lang.Object[]", &xquery));
Local JavaObject &sheet2 = &jCompileMethod.invoke(&XSSFWorkbook, CreateJavaObject("java.lang.Object[]", &xquery1));

Local JavaObject &int = GetJavaClass("java.lang.Integer");
Local JavaObject &IntType = GetJavaClass("java.lang.Integer").TYPE;
Local JavaObject &jIntArgTypes = CreateJavaObject("java.lang.Class[]", &IntType);


Local JavaObject &jcreateRow = &sheet1.getClass().getDeclaredMethod("createRow", &jIntArgTypes);


Local number &i, &k;
&k = 1;
For &i = 1 To 40000
Local JavaObject &Row = &jcreateRow.invoke(&sheet1, CreateJavaObject("java.lang.Object[]", &i));
Local JavaObject &jcreatecell = &Row.getClass().getDeclaredMethod("createCell", &jIntArgTypes);
Local JavaObject &cell = &jcreatecell.invoke(&Row, CreateJavaObject("java.lang.Object[]", &k));
Local JavaObject &setCellValue = &cell.getClass().getDeclaredMethod("setCellValue", &jCompileArgTypes);
Local JavaObject &cellvalue = &setCellValue.invoke(&cell, CreateJavaObject("java.lang.Object[]", "Interesting this is nice"));
End-For;
rem Local JavaObject &autoSizeColumn = &sheet1.getClass().getDeclaredMethod("autoSizeColumn", &jIntArgTypes);
rem Local JavaObject &autoSize = &autoSizeColumn.invoke(&sheet1, CreateJavaObject("java.lang.Object[]", 0));

Local JavaObject &out = CreateJavaObject("java.io.FileOutputStream", "/psoft/fs9devt/UserUpload/ap/comments12.xlsx", True);
&XSSFWorkbook.write(&out);
&out.close();

getting below Error while executing the above code through Application Engine but it worked for 1 line printing, but when doing it in Loop it throws this error.
Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke.