TechTip: Excel on the Fly

[Guest.Visitor]

** This thread discusses the article: TechTip: Excel on the Fly (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=3090) **
What were your findings when comparing RPG against the pure Java version you provided?

[Guest.Visitor]

** This thread discusses the article: TechTip: Excel on the Fly (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=3090) **
Giuseppe, When we run the following statement we get an error (listed under the statement). SQL2XLS SQLSTMT('select baldue from qcustcdt') TOXLS('/home/rshaler/test.xls') Error message below: Cause . . . . . : An incorrect value... parameters: -- Function -- Commitment control -- Date format -- Date separator -- Time format -- Time separator -- Naming option -- DESCRIBE option -- Allow copy data Recovery . . . : Change the incorrect parameter value and try the request again. Refer to the description of the QSQPRCED API for a list of the valid parameter values.

[Guest.Visitor]

** This thread discusses the article: TechTip: Excel on the Fly (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=3090) **
Richard, there might be a limit on the number of bytes that you can pass, so if your file is overly large. You might want to give more selection criteria to your SQL stmt, as it would narrow down the number of hits you get. Just an idea, until Guiseppe gets back to you. We send data to excel spreadsheets all the time and I remember when we were setting up a command, that we ran into this limit. We were do IFS folder processing. If the .csv is too large, it can't import into excel.

[gcostagliola@tin.it]

** This thread discusses the article: TechTip: Excel on the Fly (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=3090) **
Hi Richard, take a look at the parameters in the Function template "sqlp0100". Some of these have been set for Europe. Maybe you should change: "datefmt", "datesep" "timefmt", "timesep", "decpos" according to your locale.

[gcostagliola@tin.it]

** This thread discusses the article: TechTip: Excel on the Fly (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=3090) **
I’ve tested on my iSeries “private” partition (a very little one – where the poi jar compile takes 1hr and 25 minutes !!). The following timings are not meaningful by theirself, but they can be useful for comparison. Maybe you ran the test on a powerful machine and would give us the results. Both db2_classes.jar and POI-2.0.jar have been compiled at 40 before running the test. Best elapsed: ===> SQL2XLS SQLSTMT('SELECT DBXFIL, DBXLIB, DBXTXT, DBXATR FROM QADBXREF') TOXLS('/home/costagliol/dbref.xls') COLHDRS(*FLDNAM) 3.500 rows -> 2’10’’ ===> RUNJVA CLASS(JSQL2XLS) PARM('SELECT DBXFIL, DBXLIB, DBXTXT, DBXATR FROM QSYS.QADBXREF') CLASSPATH('/home/costagliol/:/home/costagliol/db2_classes.jar:/excel/poi-2.0.jar') 3.500 rows -> 1’15’’ The “pure java” version wins, as expected; however I think that most of as/400 programmers feel more confortable with rpg than java, then they would accept to wait a little bit more to get it.

[Guest.Visitor]

** This thread discusses the article: TechTip: Excel on the Fly (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=3090) **
Well, I think if IBM can fix the CPYTOIMPF and have the first line the column headings, it will be very easy to send db2 to Excel format. Tiki

[gcostagliola@tin.it]

** This thread discusses the article: TechTip: Excel on the Fly (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=3090) **
With CPYTOIMPF you create a .csv that's another thing. However there are free utilities in internet that allow you to create .csv with column headings.

[Guest.Visitor]

** This thread discusses the article: TechTip: Excel on the Fly (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=3090) **
One thing I noticed. When trying to move a newly created file with OPS Navigator, I get the message that the file is being used. I can map a drive and open the file with EXCEL. I can also save the file. But, OPS Nav thinks the file is being used.

[gcostagliola@tin.it]

** This thread discusses the article: TechTip: Excel on the Fly (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=3090) **
If you have the folder in share, you can even delete it from windows explorer immediately after it has been created; from wrklink you can also delete it. I think it's only a problem of OPSnav.

[Guest.Visitor]

** This thread discusses the article: TechTip: Excel on the Fly (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=3090) **
Can anyone point me in the right direction for the free utilities to create column headings in CSV files? I really need this....thanks

[Guest.Visitor]

** This thread discusses the article: TechTip: Excel on the Fly (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=3090) **
Hi, can you give me the link to that free utilities, I know one software from http://www.goering.us call IXLSGEN but you have to buy it. We do have software that you can email DB2 to EXCEL with column heading with file type of .dif. Thanks

[Guest.Visitor]

** This thread discusses the article: TechTip: Excel on the Fly (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=3090) **
We have a couple of fields that have significant spaces before the data. This utility seems to trim both sides. Does anyone know how I could change it to only trimr?

[Guest.Visitor]

** This thread discusses the article: TechTip: Excel on the Fly (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=3090) **
http://www.google.com/search?hl=en&lr=&ie=ISO-8859-1&q=iseries+create+csv+&btnG=Google+Search look for www.as400pro.com (bookmark this one). Inside the links page will be a CSV link. This one works. look for techtarget.com tips. One is about a short program that adds headings, the other is an interesting csv creation using net.data. This approach is interactive (web page) but you could write pretty much whatever you want into the file. The midrange archive seems to have a thread or two regarding this subject as well, but the server is currently down.google search (http://www.google.com/search?hl=en&lr=&ie=ISO-8859-1&q=iseries+create+csv+&btnG=Google+Search)

[Guest.Visitor]

** This thread discusses the article: TechTip: Excel on the Fly (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=3090) **
OK, I'm one of those As/400 programmers who hasn't successfully made Java programs work on the AS/400, so I need a little help. What are the step by step processes for compiling JSQL2XLS so that it runs on the AS/400? (By the way, the "CRTJVAPGM CLSF(/excel/POI-2.0.jar)" took over 2 1/2 hours on my 150!!) Thanks in advance. Steve

[gcostagliola@tin.it]

** This thread discusses the article: TechTip: Excel on the Fly (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=3090) **
I assume that you have the JSQL2XLS.java in /excel. STRQSH export -s CLASSPATH=/excel/:/QIBM/ProdData/OS400/Java400/ext/db2_classes.jar:/excel/POI-2.0.jar: javac /excel/JSQL2XLS.java java JSQL2XLS 'select * from MyFile' From cmdline just run: RUNJVA CLASS(JSQL2XLS) PARM('select * from MyFile') CLASSPATH('/excel/:/QIBM/ProdData/OS400/Java400/ext/db2_classes.jar:/excel/POI-2.0.jar:') Have fun !!

[gcostagliola@tin.it]

** This thread discusses the article: TechTip: Excel on the Fly (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=3090) **
Try adding this code to close the file <hr width=50 align=left>Code ('http://www.mcpressonline.com/mc/showcode@@.6ae84e6d/22')

[gcostagliola@tin.it]

** This thread discusses the article: TechTip: Excel on the Fly (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=3090) **
Try using substring instead of trim. <hr width=50 align=left>Code ('http://www.mcpressonline.com/mc/showcode@@.6ae84e6d/23')

[Guest.Visitor]

** This thread discusses the article: TechTip: Excel on the Fly (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=3090) **
Thanks, that seems to have done it! I did have to use the real name of the field Cell_Alfa instead of CellaAlfa.

[Guest.Visitor]

** This thread discusses the article: TechTip: Excel on the Fly (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=3090) **
Java is magic around our shop. It's so cool when it works, but we can't tell what to do with it when it doesn't. I compiled the java source following your instructions. When run from QSH...ERROR: Authorization failure on distributed database connection attempt. What now? Secondly...when it works...where will the xls be and what will be it's name?

[Guest.Visitor]

** This thread discusses the article: TechTip: Excel on the Fly (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=3090) **
Doh! For you other newbs! Use an editor to find the .java file. Look for the user and password. Change them. Find fileOut and change the directory/filename to where you want the output. Still getting the authorization error...

[gcostagliola@tin.it]

** This thread discusses the article: TechTip: Excel on the Fly (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=3090) **
Try these: See with WRKRDBDIRE that you have the *LOCAL entry. Try to change "localhost" in the connection string with your system real name or ip address. Grant auth. Now I'm at home; next monday if you haven't done, I will make further investigations. Giuseppe.

[Guest.Visitor]

** This thread discusses the article: TechTip: Excel on the Fly (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=3090) **
Thanks for your help. We did have a *local entry, so that wasn't the answer. I started over with a fresh copy of the source and made only the changes to user and password and directory and it worked this time. Must have been something I messed up in the user and password line the first time. Some other observations... The java version kept significant blanks in front of the fields. It required me to pass it the library.filename syntax. The first run of the Java version was much faster (about 30%) than the first run of the RPG version. Even after running the same command (with the same file) several times, RUNJVA still beat the command interface. Hey, Giuseppe, how about making the Java version functionally equivalent (especially passing the output file name and colhdg choices)?

[R.Cozzi]

** This thread discusses the article: TechTip: Excel on the Fly (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=3090) **
Free? No, not that work well. My CPYTOCSV command uses my RPG ToolKit service program to copy an entire file to the IFS in CSV format, with column headings. Or you can call one of the CSV procedures included in the ToolKit directly from RPG IV and write out CSV "records" to the IFS from within RPG. Check it out at www.rpgiv.com/toolkit

[MCWebsite.Staff]

** This thread discusses the article: TechTip: Excel on the Fly (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=3090) **
** This thread discusses the Content article: TechTip: Excel on the Fly (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=3090) **
0

[David Abramowitz]

** This thread discusses the article: TechTip: Excel on the Fly (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=3090) **
You can link a CSV to an <u>MS Access</u> database if it is too large for Excel. Dave

[Guest.Visitor]

Thanks Costagliola, I appreciate you taking the time to help me here. When I follow your instructions, I receive "Invalid class file format" errors, "wrong version: 46, expected 45". Is this due to the version of Java on my machine (1.1.7 on V4R4M0)?? Do I need to recompile/recreate the POI-2.0.jar for this version in order to make this thing work?

[gcostagliola@tin.it]

I have tested both versions (rpg and java) at V5R1 and R2. You can't call java this way on earlier releases. JSQLXLS compiles and runs at 1.2 but I'm not sure that you can run at 1.1.

[gcostagliola@tin.it]

This utility shows how to run dynamic sql and how to call java from rpg. It's a general purpose free utility, that can be useful to create directly from your as400 small/medium spreadsheet (in final .xls format), with headers, formatting, etc. You can easily add colors, borders, footers and formulas. The problem with this RPG version is that is not suitable for very large spreadsheet, because you can easily find out that elapsed is not linear with number of rows: if you write 1,000 rows it takes, say half minute; 5,000 rows 5 minutes, but for 15,000 rows the utility could run for 30-40 minutes. I suppose that this is due to the fact that cells and rows (as java objects) are kept in memory and not reclaimed up to the end of the process. The gc has to inspect a larger number of objects at each interval. Maybe we could try to use a different way of grouping objecs to be cleaned. Following some of the requests I've received, I've prepared a version that has some fixes, including manual start/end of JVM, especially for bath running, but the limitations of rpg/java still remain. The pure java version is a simple program with the only purpose of performance comparison, but can be easily implemented because the Jakarta distribution contains many examples that show howto, that I've used for rpg porting. However, if you want a heavy duty program you can consider BUYING third party tools.

[gcostagliola@tin.it]

In order to fix some errors, please make some patches in the following sections: - Reallocate space for SQLDA if more than 20 fields Because %alloc() does NOT re-initialize storage, even if you set *inlr=*on from a previous call, storage allocated can contain garbage, we should replace this instruction with %realloc. - Create a new WorkBook If you create a Workbook from a template, you could get a Java exception. - Retrieve System Values Miscoding <hr width=50 align=left>Code ('http://www.mcpressonline.com/mc/showcode@@.6ae84e6d/33')

[catalina]

How can you embed JPGS in a generated Excel spreadsheet? I can put in pictures, but when I re-sort the page, the pictures don't move.

[gcostagliola@tin.it]

You can join the POI user list at jakarta.apache.org where you can find some postings that could help using all the features of POI api's.

[Guest.Visitor]

When I try to use SQL2XLS I get a class not found message: Message . . . . : Java exception received when calling Java method. Cause . . . . . : RPG procedure SQL2XLSR in program QGPL/SQL2XLSR received Java exception "java.lang.NoClassDefFoundError: org/apache/poi/hssf/usermodel/HSSFWorkbook" when calling method "<init>" with signature "()V" in class "org.apache.poi.hssf.usermodel.HSSFWorkbook". I've check the classpath, '/excel/POI-2.0.jar' was the only thing there. I've tried changing to '/excel/:/excel/POI-2.0.jar' but I still get the same message. What am I missing? Thanks, Charles

[gcostagliola@tin.it]

Charles, the simplest way to know if the jar is in the right place is to run the following: CRTJVAPGM '/excel/poi-2.0.jar' OPTIMIZE(40) . . and remember that java is case sensitive, then "POI-2.0.jar" is NOT THE SAME as "poi-2.0.jar" !!! Giuseppe.

[Guest.Visitor]

Costagliola, Interestingly enough, I signed on to a different session and it worked successfully. Also, seems to be working from any session this morning; note that I shut down my PC at night. However, I'm having difficulty with one of the SQL statements I'm trying to use. Take a look at the post titled "SQL2XLS fails using a table *UDF" Thanks, Charles

[Guest.Visitor]

Ok, I've got SQL2XLS running with simple SQL statements. However, when I try and use the following: select * from table(cwlibr/PartsGoingOutOfStock()) as X I get the following set of messages: SQL0311 - Length in a varying-length or LOB host variable not valid. SQL0501 - Cursor CURSOR not open. SQL0501 - Cursor CURSOR not open. SQL0501 - Cursor CURSOR not open. The same statement in STRSQL works fine. Also, interestingly the excel spreadsheet is created, but it contains only column headers (*FLDNAM) without any data. I haven't looked at this in any more detail, but I wondered if this was a known limitation of the tool. Thanks, Charles Wilt

[gcostagliola@tin.it]

Charles, I' ve tried to create a simple UDTF as follows: Create Function GetFondi (Tipo varChar(1)) Returns Table (Codice Char(2), Descrizione Char(25)) Language Sql Disallow Parallel Set Option Commit=*None, Datfmt=*Iso Begin Return Select C6B8CD, C6HYTX From Affon00f Where C6COST = Tipo; End Than i ran the utility: SQL2XLS SQLSTMT('select * from table (GetFondi (''O'')) as ListaFondi') TOXLS('/home/costagliol/udtf.xls') and it works fine. No errors. Giuseppe. PS. I've have an updated version of the utility. Write me directly.

[Guest.Visitor]

Giuseppe, I've sent you an email directly. Some additinoal info, I tried creating a very simple UDTF: create function cwlibr/TestUDTF() returns table ( Vendor_Number varchar(6), Vendor_Name varchar(40)) language SQL reads SQL data disallow parallel BEGIN return select vdcode, vdname from meldbf/mpvend; end I get the the same cursor not open messages. Charles

[gcostagliola@tin.it]

Charles, would you try this ? In section *sF* please add the following statements: when SQLTYPE = 448 or SQLTYPE = 449; // .. VarChar SQLLEN += 2; cSQLLEN = SQLLEN; SQLTYPE += 4; This should make your UDTF working properly. Giuseppe.

[Guest.Visitor]

Giuseppe, That fix the problem. It runs without an error messages now. Thanks for your help, Charles

[Guest.Visitor]

With the following select: select RunOutDate as [RunoutDate] from Myfile My Excel spreadsheet has the date in DD/MM/YY format with the correct column heading. Being in the USA, I'd prefer *USA or *MDY. Using SQLLOCVAL(*YES) on the command didn't seem to have an effect. Nor did changing the datefmt d-spec from inz('DMY') to inz('MDY'). Using the following instead: select char(RunOutDate, USA) as [RunoutDate] from myfile Gives me an Excel spreadsheet with a date in MM/DD/YY but the column heading disappears. Any Thoughts before I start looking at this in debug? Thanks, Charles

[gcostagliola@tin.it]

If the colum headers disappear, try using COLHDRS(*ANY). To use a *MDY you can change the "datefmt" subfield in "sqlp0100". The original version has a bug in SQLLOCVAL. See the previous postings for a fix. Giuseppe.

[Guest.Visitor]

As I mentioned in my first post, I had already tried changing the "datefmt" subfield in "sqlp0100". It had no effect, dates still coming out DMY. I'll look for more info about the SQLLOCVAL bug, maybe it is related. Charles.

[Guest.Visitor]

When I changed the pSQLDA = %alloc in section sB line 731 to use %realloc. I received a "CEE0810 - The starting address for reallocation is not valid." error. I changed it back to %alloc and it continues to work. Did I miss something? I don't understand the reason for this change. According to the RPG manual neither %alloc or %realloc initialize storage. Thanks, Charles Wilt

[gcostagliola@tin.it]

As long as date format and other environvent parameters are stored in *sqlpkg, that is created only once in QTEMP, you should delete it or re-signon, and then run the utility again to let your changes take effect. Giuseppe.

[dsenger@erico.com]

I would like to use the full 60 character labels (3 20-byte segments) for the spreadsheet column headings, however the SQLDA documentation says that only the first 20 bytes are returned in the SQLVAR. I'm looking for any ideas or suggestions as to the best method to do this. Thanks, Dave Senger

[gwilburn@hobbyhouse.com]

I'm a descent RPG programmer that is completely out of my element. I have litte to no experience wiht JAVA. I have tried to follow the instructions here, but i cannot find the file POI-2.0.jar on the Apache Mirror sites. Instead, I locate subfolders (at the locations indicated) containg ZIP files... so I downloaded "poi-bin-2.5.1-final-20040804.zip". This huge file contained all kinds of PDF and HTML files along with "poi-2.5.1-final-20040804.zip". I renamed this file dropping the final & CCYYMMDD and placed it on my IFS in /excel. I ran the CRTJVAPGM, downloaded and compiled the source. When I tried to run the SQL2XLS command I received the following message in my job log: SEE CODE BELOW I tried changing the RPGLE pgm to point to my JAR file and received the same error. I am in the process of CRTJVAPGM using the "poi-2.0.jar" I found in your ZIP file. I'm going to try that. I am running v5r2 on a model 270. FYI - the CRTJVAPGM on the file I downloaded took over 2 hours. <hr width=50 align=left>Code ('http://www.mcpressonline.com/mc/showcode@@.6ae84e6d/50')

[gcostagliola@tin.it]

Hi, you can do one of the following - download the 2.5 final jar from apache and rename it to poi-2.0.jar - download the 2.5 final jar and rename to poi-2.5.jar and change the references in the classpath into the rpg program - download the poi-2.0.jar from MC article of november The 2.5 had a big bug with images. Probably it has been fixed with 2.5.1 but I'm not sure. Use 2.0 and you are right. Very soon I will present another implementation in pure java with a different set of api's especially suitable for large xls creation at hi-speed. Don't miss it. CRTJVAPGM takes a long time. Submit in batch and be patient.

[gwilburn@hobbyhouse.com]

Thank you... I simply downloaded the 2.0 jar and things seem to be working. (I had already tried using the 2.5.1 and changing the RPG code - I had no luck with this). Several things I've noticed when using this. 1. If the excel file already exists in the target folder, it does not simply over-write with new data. 2. I have several excel templates (i currently use with iSeries Data Transfer) that contain excel macros. If I use these in the "From XLS" parameter of SQL2XLS, it fails to create the spreadsheet. Other templates w/out macros seem to work fine. (note: These macros simply subtotal several columns). Thanks again for your help.

[gwilburn@hobbyhouse.com]

<hr width=50 align=left>Code ('http://www.mcpressonline.com/mc/showcode@@.6ae84e6d/54')

[joel@gmdsolutions.com]

I have SQL2XLS working on my test box and would like to install it on other client systems. Is it possible to transfer the java "class" files by copying specific objects or stream files or is it required that you run the CRTJAVA command on each box you want to install this utility on. Joel

[gcostagliola@tin.it]

POI should preserve macros. I suggest to download the latest jar, put into /excel ifs, and rename to poi-2.5.jar, change the statement that adds the classpath into the rpg program, and recompile: // set CLASSPATH environment rc = putenv('CLASSPATH=/excel/POI-2.5.jar'); When you try the new version don't forget to signoff and signon again !! Pretty soon I will make available a pure java version and you can try this one. You can join the POI-Jakarta forum to get additional informations on this api usage. Giuseppe.

[gcostagliola@tin.it]

Joel, other system you mean another as400 ? Just create the ifs /excel and copy the poi jar into it. the crtjvapgm is suggested to get better performances but the Jit will create the program on the fly the first time you run it (and it will take some time).

[Guest.Visitor]

I am trying to compile the source for the command SQL2XLS on my Iseries it does compile... although when I run the command it gets an error saying "Pointer not set for location referenced."! A more complete message then comes up that says "Pointer or parameter error (C G D F).". It seems that the command processing program SQL2XLSR is not receiving one of its parameters from the execution of the command! Do you know what might cause this to happen?

[gcostagliola@tin.it]

The cmd SQL2XLS from August article has some more parameters compared to the first version of November. Maybe you are using the new command with the old rpg or viceversa.

[Guest.Visitor]

My RPGLE has 15 parameters and my command has 11 parameters. How do I get the most recent version. Do you have a web site that I can go to.

[dacust]

Try here: Latest SQL2XLS Article (http://www.mcpressonline.com/mc?14@232.1KNKfHX1eQT.17@.6aed3f76) And here is a link to his Latest Articles and Posts (http://www.mcpressonline.com/mc/224@232.1KNKfHX1eQT.17@3f81cdc1@1@232.1KNKfHX1eQT. 17@.6ae8269f)

[Guest.Visitor]

I'm using these classes from within an ILE RPG application. If I run the RPG program normally, I receive the following error; RPG procedure XXXXXXX in program LIBRARY/XXXXXXX received Java exception "java.io.IOException:" when calling method "<init>" with signature "(Ljava.io.InputStream;)V" in class "org.apache.poi.poifs.filesystem.POIFSFileSystem". Here's the odd thing. When I run the ILE RPG program in debug, the error doesn't occur. Has anyone else experienced this? Can anyone give me any guidance on this?

[gcostagliola@tin.it]

Does this error occur only when you import an existing xls, or in any case ? Check the authorities to the directory you are reading from.

[Guest.Visitor]

It happens when importing an existing XLS. The odd thing though is it's fine when I run it in debug mode. Otherwise I get the error.

[Guest.Visitor]

Problem solved. Downloaded poi-2.5.1 and tried with that and now all is well.

[Guest.Visitor]

spoke too soon... first time I ran it, it worked like a charm. Every time since then I get the error. Interactive or batch I get the same result

[gcostagliola@tin.it]

Mike, - make sure you are not using QDLS - check all the auth's - try with a brand new folder Incidentally are you using client access or another tool ? Do you run it locally or remotely ?

[Guest.Visitor]

Not using QDLS. Using Client Access. Running locally.

[todd_johnson@cu.net]

I'm trying to use this new tool but having problems with the sql statement. The code is below with the error. Any suggestions. <hr width=50 align=left>Code ('http://www.mcpressonline.com/mc/showcode@@.6ae84e6d/69')

[gcostagliola@tin.it]

Check your sql syntax. For example the comma before the from clause, the join in not formulated correctly. I suggest you open a sql session (strsql) and you prcatice there. When you got your stmt running, copy/paste into sql2xls.

[todd_johnson@cu.net]

Thank you, I've been backing off commands to learn both SQL and the ticker logic. Friday I finally got past sql and ticker logic but got nailed on the java signature error and gave up. But I'm back at it again today. As you can tell, I'm a novice using rpgle. Any suggestions on the following: Additional Message Information Message ID . . . . . . : RNX0301 Severity . . . . . . . : 50 Message type . . . . . : Escape Date sent . . . . . . : 06/06/05 Time sent . . . . . . : 07:48:32 Message . . . . : Java exception received when calling Java method. Cause . . . . . : RPG procedure SQL2XLSR in program NS87849/SQL2XLSR received Java exception "java.lang.NoClassDefFoundError: org/apache/poi/hssf/usermodel/HSSFWorkbook" when calling method "<init>" with signature "()V" in class "org.apache.poi.hssf.usermodel.HSSFWorkbook". Recovery . . . : Contact the person responsible for program maintenance to determine the cause of the problem. Technical description . . . . . . . . : If the exception indicates that the Java class was not found, ensure the class for the method is in the class path. If the exception indicates that the Java method was not found, check the method name and signature. If the signature is not correct, change the RPG prototype for the method, or change the Java method, so that the return type and parameter types match. You can determine the signatures for all the methods in class XYZ using command QSH CMD('javap -s XYZ'). More... Press Enter to continue. F3=Exit F6=Print F9=Display message details F12=Cancel F21=Select assistance level Thank you Todd Johnson MTSPUDGUN (Montana USA SPUDGUN)

[gcostagliola@tin.it]

When you get the error java.lang.NoClassDefFoundError: org/apache/poi/hssf/usermodel/HSSFWorkbook this means that you installed the poi .jar classes in the wrong directory. Before running the utility follow the setup steps including the crtjvapgm. Btw you should use the updated version of the command available with SQL2CSV and SQL2XML.

[Guest.Visitor]

I'm getting the below error message when I try to use a template (from) xls with the SQL2XLS command in batch. Note that I can use a template xls interactively just fine. I only get the error in batch. <hr width=50 align=left>Code ('http://www.mcpressonline.com/mc/showcode@@.6ae84e6d/73')

[gcostagliola@tin.it]

Use the updated version that comes with the SQL2CSV and SQL2XML utilities. Or - better - use the java version SQL2JXl or SQL2POI.

[Roman Moser]

Hi, I use the SQL2JXL on a V5R2 machine and it works very fine. Now, I have moved it to a V5R3 machine and always get "Error running SQL2JXL". I tried to find what's the problem, but the only thing I could find was the error message "ERROR: INTERNAL ERROR: Creation of DB2Driver object for registering with DriverManager failed." in the java-shell. The classpath mentioned in the RUNJVA-command is: CLASSPATH('/QIBM/ProdData/OS400/jt400/lib/jt400.jar:/excel:/excel/jxl.jar') Any idea what's wrong? Thank's for any help, Roman

[gcostagliola@tin.it]

The CCSID of your job mybe is 65535. Try changing it with the CCSID of your language.

[Roman Moser]

Thanks Giuseppe! That's it. After changing the CCSID it works correct!!

[dlee@swbc.com]

I'm using hssf POI to create excel. I'm trying to find a solution to my problem where when creating more than one sheet in excel, the 2nd sheet loses the data formating. Formulas and cell structure are all intact, just the data formating is lost. This happens when I create a workbook and sheet1 in program1 then save the workbook. then in program2 I open the workbook and add another sheet2, then save the workbook again. Sheet2 will lose the data formating. Anyone had this problem, suggestions appreciated. Darrell dlee2319@aol.com

[gcostagliola@tin.it]

If you are using SQL2XLS make sure you run the latest version (available in the article "TechTip: SQL2CSV and SQL2XML"). However I suggest you using SQL2JXL or SQL2POI.

[Guest.Visitor]

Hi, I trie your utility from command line and it works GREAT!!, but when I call it from a CGI program I get the following error: Java exception received when calling Java method. RPG procedure SQL2XLSR in program SQL2XLS/SQL2XLSR received Java exception "java.lang.NoClassDefFoundError: org/apache/poi/poifs/filesystem/POIFSFileSystem" when calling method "<init>" with signature "(Ljava.io.InputStream;)V" in class "org.apache.poi.poifs.filesystem.POIFSFileSystem". Can anyone help me with this? Thanks.

[Guest.Visitor]

add following instruction before call SQL2XLS rc = putenv('CLASSPATH=/excel/POI-2.0.jar');

[mass8]

Hi, when I create the file on a ifs folder, and try to open form pc with exccel, excel notify me that the file are in use by iSeries User, but the job on iSeries are ended. Why?