TechTip: Excel Flies Higher with JExcelApi

[dsenger@erico.com]

TechTip: Excel Flies Higher with JExcelApi

** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
I'm using the SQL2POI utility and have a requirement to format numbers using comma for the decimal symbol and a period for the thousands separator. I tried changing the code to: style2d.setDataFormat(df.getFormat("#.##0,00")); but the resulting numeric cell value of 2 is being formated as 2.00000 instead of 2,00 Do I need to set a locale somewhere ? Any other ideas ? Thanks, Dave Senger

TechTip: Excel Flies Higher with JExcelApi

** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
You can use SQL2JXL to get data from a SqlServer and put them directly into an .xls in your iSeries. First, download a suitable driver like jTDS, an open source 100% pure Java (type 4) JDBC 3.0 driver for Microsoft SQL Server (6.5, 7, 2000 and 2005) from http://jtds.sourceforge.net/ and put it into the “/excel” folder of your iSeries. Then modify the SQL2JXL* as outlined below, recompile the SQL2JXL CMD, CLLE and the class and run the command: SQL2JXL SQLSTMT('select * from Northwind.DBO.Customers') TOXLS(…….) COLHDRS(*FLDNAM) DRIVER(*JTDS) RMTSYS(SQLServer) USER('…………') PASSWORD(‘…………..’) JDEBUG(*YES)

---

Code

TechTip: Excel Flies Higher with JExcelApi

** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
Maybe this can help: http://www-912.ibm.com/s_dir/slkbase...6?OpenDocument

TechTip: Excel Flies Higher with JExcelApi

** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
When i enter the command: SQL2JXL SQLSTMT('select * from books') TOXLS('/xlsfiles/books.xls') COLHDRS(*FLDNAM) JDEBUG(*YES) I get this in the Java Shell Display: Connecting to LOCALHOST with "com.ibm.db2.jdbc.app.DB2Driver" ERROR: INTERNAL ERROR: Creation of DB2Driver object for registering with DriverManager failed. Java program completed with exit code 1 Not sure what I am missing here.

[buffcessna]

TechTip: Excel Flies Higher with JExcelApi

** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
OK, that's cool...I changed the CL to use the SQL2XLS and it works fine...So, I guess I'll just use that one...Thanx for all the input guys... Shane

[buffcessna]

TechTip: Excel Flies Higher with JExcelApi

** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
Refer to Dan Stevens post...

[dacust]

TechTip: Excel Flies Higher with JExcelApi

** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
Many people put a MONMSG CPF0000 to catch any extraneous errors from crashing a CL. The proper way would be to have a standard error routine to handle the situation, like MONMSG MSGID(CPF0000 CPA0000) EXEC(GOTO CMDLBL(ERROR)). To see examples of this, do a search on this site for STANDARD ERROR HANDLING. On the search results page look over to the right in the block "Results From the Forums". Funny thing is, the first post shown when I did that was one of mine. -dan

[R.Daugherty]

TechTip: Excel Flies Higher with JExcelApi

** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
The question was supposed to follow Shane's posting of CL code which has a MONMSG MSG(CPF0000) in it. I was asking why, but if everything is working just disregard. rd

TechTip: Excel Flies Higher with JExcelApi

** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
What do you mean ?

TechTip: Excel Flies Higher with JExcelApi

** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
They use a different set of Api's. SQL2XLS and SQL2POI use Jakarta/POI api's that are more powerful while SQL2JXL uses Andy Khan's JExcelApi that is faster. SQL2XLS uses RPG mixed with Java. This is excellent for small xls but cannot be used for large xls. For these ones, the pure Java SQL2POI and SQL2JXL are the best solution.

[R.Daugherty]

TechTip: Excel Flies Higher with JExcelApi

** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
I have a question. Why do you MONMSG CPF0000 in your CL? rd

[buffcessna]

TechTip: Excel Flies Higher with JExcelApi

** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
I changed my CL to use the SQL2XLS utility instead of the SQL2JXL utility...and it works...is there a difference between the two? Shane

[buffcessna]

TechTip: Excel Flies Higher with JExcelApi

** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
Ok, I enabled it and ran the program in debug...again, it works fine when I run the command...however, when a user runs the command, it comes up with the following error in their job log: 3500 - SQL2JXL SQLSTMT('select rundated, runmach, runtime, runuser from casprun') TOXLS('/excel/casprun.xls') SHEETNAME('Run Time') COLHDRS(*LABEL) TITLE('Run Time') TITLEALIGN(*CENTER) JDEBUG(*YES) 4500 - CALL PGM(SQL2JXLR) /* The CALL command contains parameters */ Error CrtSQLstmt : open() failed. 3401 : Permission denied.. 12200 - SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('Error running SQL2JXL') MSGTYPE(*ESCAPE) Error running SQL2JXL. CPF9898 received by CASQAQCC at 3500. (C D I R) What must I do in order to let regular users use this utility in a CL program? Shane

TechTip: Excel Flies Higher with JExcelApi

** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
write SQL2JXL into cmdline and press F4 and then F10. Scroll up to the end. The last parameter is JDEBUG. Put the value *YES.

[buffcessna]

TechTip: Excel Flies Higher with JExcelApi

** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
How do I do that? Shane