TechTip: Excel on the Fly

[gcostagliola@tin.it]

TechTip: Excel on the Fly

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

[Roman Moser]

TechTip: Excel on the Fly

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

[dlee@swbc.com]

TechTip: Excel on the Fly

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]

TechTip: Excel on the Fly

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]

TechTip: Excel on the Fly

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 "" with signature "(Ljava.io.InputStreamV" in class "org.apache.poi.poifs.filesystem.POIFSFileSystem". Can anyone help me with this? Thanks.

[Guest.Visitor]

TechTip: Excel on the Fly

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

[mass8]

Re:TechTip: Excel on the Fly

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?

[MCWebsite.Staff]

TechTip: Excel on the Fly

** This thread discusses the article: TechTip: Excel on the Fly **
** This thread discusses the Content article: TechTip: Excel on the Fly **
0

[Guest.Visitor]

TechTip: Excel on the Fly

** This thread discusses the article: TechTip: Excel on the Fly **
What were your findings when comparing RPG against the pure Java version you provided?

[Guest.Visitor]

TechTip: Excel on the Fly

** This thread discusses the article: TechTip: Excel on the Fly **
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]

TechTip: Excel on the Fly

** This thread discusses the article: TechTip: Excel on the Fly **
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]

TechTip: Excel on the Fly

** This thread discusses the article: TechTip: Excel on the Fly **
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.

[David Abramowitz]

TechTip: Excel on the Fly

** This thread discusses the article: TechTip: Excel on the Fly **
You can link a CSV to an MS Access database if it is too large for Excel. Dave

[gcostagliola@tin.it]

TechTip: Excel on the Fly

** This thread discusses the article: TechTip: Excel on the Fly **
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]

TechTip: Excel on the Fly

** This thread discusses the article: TechTip: Excel on the Fly **
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