You are not logged in.

Dear visitor, welcome to Jabaco - Community. If this is your first visit here, please read the Help. It explains in detail how this page works. To use all features of this page, you should consider registering. Please use the registration form, to register here or read more information about the registration process. If you are already registered, please login here.

A1880

Intermediate

  • "A1880" is male
  • "A1880" started this thread

Posts: 500

Date of registration: Jan 1st 2009

Location: Hanover, Germany

Occupation: Software Engineer

Hobbies: Hilbert Curves

  • Send private message

1

Tuesday, February 3rd 2009, 4:49pm

Access to Excel from Jabaco via JExcel

Hi all,
this is a small (and imcomplete) demo how to create an Excel .xls file out of Jabaco.

Who finds and solves the compile error?

Greetings!

A1880

Source code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
Option Explicit

'
'  Demo for Excel access via JExcel 
'
'  Migrated from Java to Jabaco from 
'  http://www.tutorials.de/forum/java/267257-kleines-beispiel-zur-jexcel-api.html
'
'  JExcel:  http://jexcelapi.sourceforge.net/
'
'  Download JExcel distribution from URL above and add jxl.jar to
'  Jabaco classpath (press F1)


' import java.io.File;
' import java.util.Date;

' import jxl.Workbook;
' import jxl.write.DateFormats;
' import jxl.write.DateTime;
' import jxl.write.Label;
' import jxl.write.Number;
' import jxl.write.NumberFormats;
' import jxl.write.WritableCellFormat;
' import jxl.write.WritableSheet;
' import jxl.write.WritableWorkbook;

 
Public Sub ExcelDemo
   Dim workbook As jxl#write#WritableWorkbook 
   Dim testSheet As jxl#write#WritableSheet 
   Dim file As java#io#File 
   Dim numberFormat As jxl#write#WritableCellFormat 
   Dim dateFormat As jxl#write#WritableCellFormat 
   Dim number As jxl#write#Number 
   Dim dateCell As jxl#write#DateTime 
   
   Dim fileName As String 
   Dim currentColumn As Integer = 0
   Dim currentRow As Integer = 0
   
   fileName = system.getenv("TEMP") & "\text-jexcel1.xls"
   file = New java#io#File(fileName)
   
   workbook = jxl#Workbook.createWorkbook(file)

   testSheet = workbook.createSheet("test", 0)

   numberFormat = New jxl#write#WritableCellFormat(jxl#write#NumberFormats.FLOAT)

   number = New jxl#write#Number(currentColumn, currentRow, 12345.0, numberFormat)
   currentColumn = currentColumn + 1

   testSheet.addCell(number)

   testSheet.addCell(New jxl#write#Label(currentColumn, currentRow, "TEST"))
   currentColumn = currentColumn + 1

   dateFormat = New WritableCellFormat(jxl#write#DateFormats.DEFAULT)

'  the following line leads to a Jabaco compile error
   dateCell = New jxl#write#DateTime(currentColumn, currentRow, New java#util#Date(), dateFormat)
   currentColumn = currentColumn + 1

   testSheet.addCell(dateCell)

   workbook.write

   workbook.close
End Sub

OlimilO

Intermediate

  • "OlimilO" is male

Posts: 277

Date of registration: Jan 18th 2009

Location: Germany

Occupation: software engineer

  • Send private message

2

Tuesday, February 3rd 2009, 6:28pm

Now-bug

Hi A1880

easy going with Date :)

but did you notice this bug?

Jabaco Source

1
2
3
4
5
6
7
Dim myDate1 As Date 
   Dim myDate2 As Date
   
   myDate1 = Now
   myDate2 = "03.02.2009"
   MsgBox myDate1 & "  <-->  " & myDate2
   'output is: '01.07.0008 18:28:25  <--> 03.02.2009 00:00:00


OlimilO

A1880

Intermediate

  • "A1880" is male
  • "A1880" started this thread

Posts: 500

Date of registration: Jan 1st 2009

Location: Hanover, Germany

Occupation: Software Engineer

Hobbies: Hilbert Curves

  • Send private message

3

Tuesday, February 3rd 2009, 8:40pm

Date is not supported error

Hmm,
I also tried "now" or some other VB6 "Date" variable. This circumvents the compile error. But unfortunately, the resulting Excel file does not get a correct DateCell written. It appears that the DateTime() routine of JExcel does expect a java.util.Date rather than a VB6/Jabaco Date.

Any more suggestions?

A1880

p.s.:

There seems to be an error in Conversion.CDate(). "Now" works OK if you assign it to a String variable. If assigned to a Date variable, Now gets converted by Conversion.CDate() before the assignment, and the result ist wrong. The String result of Now has an American date format by default.

This post has been edited 2 times, last edit by "A1880" (Feb 3rd 2009, 9:01pm)


OlimilO

Intermediate

  • "OlimilO" is male

Posts: 277

Date of registration: Jan 18th 2009

Location: Germany

Occupation: software engineer

  • Send private message

4

Tuesday, February 3rd 2009, 11:07pm

Quoted

unfortunately, the resulting Excel file does not get a correct DateCell written

uhh,

on my computer this works fine:

Jabaco Source

1
2
3
Dim myDate As Date
myDate  = "03.02.2009"
dateCell = New jxl#write#DateTime(currentColumn, currentRow, myDate, dateFormat)


but when i use the now-function the cell is in date-format but is has an invalid date-value

maybe the excel version ...

what version of excel do you use?

OlimilO (Excel 2003 SP3)

A1880

Intermediate

  • "A1880" is male
  • "A1880" started this thread

Posts: 500

Date of registration: Jan 1st 2009

Location: Hanover, Germany

Occupation: Software Engineer

Hobbies: Hilbert Curves

  • Send private message

5

Wednesday, February 4th 2009, 8:59am

VB6 Date is translated to java.util.Date

Hi,
OK! Thanks for your hint. It works fine on my system.

When I decompile the lines of code I get something like:

Source code

1
2
3
myDate = Conversion.CDate("03.02.2009");
dateCell = new DateTime(currentColumn, currentRow, myDate, (CellFormat)dateFormat);
 


Jabaco compiles VB6 Date variables to java.util.Date variables. And that's exactly what jxl.write.DateTime() expects.
So the problem melts down to a failure in the implementation of "Now". I analyzed this also via decompilation.
As written in an earlier post: If you assign "Now" to a Date variable, Jabaco generates a Conversion.CDate().
And there seems to be an error, which I haven't been able to spot due to my sleepy eyes ...

Greetings!

A1880

Tobias

Beginner

  • "Tobias" is male

Posts: 12

Date of registration: Mar 31st 2010

Location: Sweden

Occupation: Programmer / Network tech

  • Send private message

6

Friday, April 16th 2010, 10:45pm

Thanks for this great tip! It can be very useful to read/write XL-files!

joda

Beginner

  • "joda" is 39 years old today

Posts: 1

Date of registration: Oct 2nd 2010

  • Send private message

7

Saturday, October 2nd 2010, 6:13pm

Hi!
I'm new here and I'm watching the above code for reading excel files with jabaco.
I have a problem, pls help me.

I copy paste the code above into a "command btn" and try to run but I got some error.

Can somebody help me where and how to copy past the above code into jabaco?

I have experience in VB but no in jabaco... :-)

So I need somebodies hand to make the first steps.


Oh yea!
btw. I got error on this line: Dim workbook As jxl#write#WritableWorkbook
something like " 'clas jxl#write#WritableWorkbook ' not found."
Thanks a lot.

This post has been edited 1 times, last edit by "joda" (Oct 2nd 2010, 7:00pm)


A1880

Intermediate

  • "A1880" is male
  • "A1880" started this thread

Posts: 500

Date of registration: Jan 1st 2009

Location: Hanover, Germany

Occupation: Software Engineer

Hobbies: Hilbert Curves

  • Send private message

8

Sunday, October 3rd 2010, 1:21pm

Hi,
have you read and followed the comment in the code?

Jabaco Source

1
2
'  Download JExcel distribution from URL above and add jxl.jar to
'  Jabaco classpath (press F1)


Your error happens if JExel is not on your system or not included in the project classpath.

Success!

A1880

Rate this thread
WoltLab Burning Board