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.

upgundecha

Beginner

  • "upgundecha" started this thread

Posts: 2

Date of registration: Feb 3rd 2009

  • Send private message

1

Tuesday, February 3rd 2009, 1:34pm

Accessing Excel Files

Hi,

I want to access Excel files using Jabaco. One way is using COM object in following way (VB6):

Set objExcel = CreateObject("Excel.Application") or adding reference and using Excel objects.

Can this be done in Jabaco?

Thanks in Advance,

Unmesh :)

A1880

Intermediate

  • "A1880" is male

Posts: 500

Date of registration: Jan 1st 2009

Location: Hanover, Germany

Occupation: Software Engineer

Hobbies: Hilbert Curves

  • Send private message

2

Tuesday, February 3rd 2009, 4:00pm

Ways from Jabaco to Excel

Hi,
the short answer: No! There is not way to access Excel .xls files from Jabaco, unless you are a gifted Java developer.

the longer answer:
The way you're describing is called "ActiveX". The call to "CreateObject()" creates an ActiveX object. This can be accessed from VB6 programs. Currently, Jabaco does not include a direct way to deal with ActiveX. You'd have to use a "Java COM bridge" library to do that. Manuel is looking for volunteers to provide ActiveX support for Jabaco.

Another way would be to use a Java framework specialized on Excel. One example is JExcel.

A third - perhaps a bit cumbersome - option could be to store your Excel data in a non-binary format (txt, csv, xml, ...) and access this with the more traditional file handling routines.

Happy experimenting!

A1880

A1880

Intermediate

  • "A1880" is male

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, 4:52pm

Sample for JExcel

Hi,
you might want to a have a look at my Jabaco/JExcel sample in the "Samples" section.

Greetings!

A1880

Manuel

Administrator

  • "Manuel" is male

Posts: 256

Date of registration: Jul 16th 2008

Location: Erlangen, Germany

Occupation: Software Developer

Hobbies: Jabaco, game theory, text-mining

  • Send private message

4

Tuesday, February 3rd 2009, 7:46pm

Quoted

the short answer: No! There is not way to access Excel .xls files from Jabaco, unless you are a gifted Java developer.
It's possible to solve this problem with Jabaco and it's not necessary to write any Java-classes, or something like that. What A1880 would like to say is that you should know how to read a Java sample to use the librarys with Jabaco. We should and we will write such samples step by step for Jabaco ...

Quoted

you might want to a have a look at my Jabaco/JExcel sample in the "Samples" section.
Cool - another solution could be Apache POI

upgundecha

Beginner

  • "upgundecha" started this thread

Posts: 2

Date of registration: Feb 3rd 2009

  • Send private message

5

Wednesday, February 4th 2009, 6:11am

Thanks Guys for your help.

Last night I tried using Database control to read the contents of an Excel file into JBGrid control and it worked.

Jabaco Source

 
1
2
3
4
5
6
7
8
9

Dim myRes As ResultSet 
Dim myResMetaData As java#sql#ResultSetMetaData 
DataBase1.Connect("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\WebDemo.xls;READONLY=true}") 
myRes = DataBase1.ExecuteStatement("SELECT * FROM [Login$]") 
myResMetaData = myRes.getMetaData 
JBGrid1.Cols = myResMetaData.getColumnCount 
For intCnt = 1 To myResMetaData.getColumnCount 
JBGrid1.Header(intCnt-1) = myResMetaData.getColumnName(intCnt) 
Next intCnt


However I observed problem with JBGrid control. There are more than 20 columns and when we load column information in JBGrid's header, JBGrid displays the column in rather weird way (See attachment).

Please advice.

Thanks,

UPG
upgundecha has attached the following image:
  • JBGrid.png

JCato

Beginner

  • "JCato" is male

Posts: 4

Date of registration: Jan 3rd 2009

Location: Meridian, MS USA

Occupation: Manager

Hobbies: Martial Arts

  • Send private message

6

Wednesday, February 18th 2009, 2:46am

Excel to Jabaco using the java excel api

Here's how I got this to work.... It doesn't work very well yet.



Create a form with a jbgrid named jbgrid1 and a command button named command1.

Jabaco Source

 
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

Option Explicit


Public Sub Command1_Click()
Dim cmdX As New CommonDialog
Dim strFileName As String 
strFileName = ""
cmdX.DialogTitle("Open Excell Spreadsheet")
cmdX.ShowOpen

strFileName = cmdX.FileName
OpenExcell(strFileName)

End Sub

Private Sub gridrefresh
Dim rowcounter As Integer
Dim colcounter As Integer

For rowcounter = 0 To jbgrid1.Rows
For colcounter = 0 To jbgrid1.Cols
jbgrid1.TextMatrix(rowcounter, colcounter) = ""
Next colcounter
Next rowcounter

' Refresh the grid.
jbgrid1.Rows = 0
jbgrid1.Cols = 0
jbgrid1.Refresh

End Sub


Public Function OpenExcell(fileName As String) As Boolean

Dim workbook As jxl#Workbook
Dim testSheet As jxl#sheet
Dim file As java#io#File
Dim rowcounter As Integer
Dim colcounter As Integer

Call gridrefresh

If fileName = "" Then
OpenExcell = False
Else
file = New #java#io#file(fileName)
workbook = jxl#Workbook.getWorkbook(file)
testSheet = workbook.getSheet(0)
jbgrid1.Rows = testsheet.getRows
jbgrid1.Cols = testsheet.getColumns 
For colcounter = 0 To (testsheet.getcolumns - 1)
For rowcounter = 0 To (testsheet.getrows - 1)
If testsheet.getCell(rowcounter, colcounter).getContents.
If testsheet.getCell(rowcounter, colcounter).getContents.length < 1 Then
jbgrid1.textmatrix((colcounter,rowcounter) = ""
Else
jbgrid1.textmatrix((colcounter,rowcounter) = testsheet.getCell(rowcounter, colcounter).getContents
End If
Next rowcounter
Next colcounter


workbook.close
OpenExcell = True 
End If

End Function

Similar threads

Rate this thread
WoltLab Burning Board