File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes EJB and other Java EE Technologies and the fly likes EJB and Excel Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "EJB and Excel" Watch "EJB and Excel" New topic
Author

EJB and Excel

Mahesh Kulkarni
Ranch Hand

Joined: Jul 05, 2001
Posts: 62
Hi,
We are developing a system in which we have to insert the data in a excel sheet(server side) then that sheet should be downloadble at client side.
we have created the connection pool for the excel sheet.The properties file setting in weblogic(5.1) are as follows.
weblogic.jdbc.connectionPool.ExcelPool=\
url=jdbc dbc:JavaExcel,\
driver=sun.jdbc.odbc.JdbcOdbcDriver,\
loginDelaySecs=1,\
initialCapacity=10,\
maxCapacity=50,\
capacityIncrement=10,\
allowShrinking=true,\
shrinkPeriodMins=15,\
refreshMinutes=10,\
#testTable=dual,\
props=user=;password=;server=DEMO
# Add a TXDataSource for the connection pool:
weblogic.jdbc.TXDataSource.weblogic.jdbc.jts.ExcelPool=ExcelPool
#
# Add an ACL for the connection pool:
weblogic.allow.reserve.weblogic.jdbc.connectionPool.ExcelPool=everyone
JavaExcel is the system DSN.
When we start the weblogic it creates 10 pools.
When we run the servlet it retrieves the data from the Oracle 8i database and adds the data to the excel sheet attached to the DSN only if the sheet is open.If the sheet is closed then the data is not inserted in the sheet.Message at the weblogic server is that the connection pool is not available (connection not available).If the sheet is open then it can insert as many records as requests from client.But if the sheet is closed then the connection is lost. Please help me out of this query.Is there any other way to create Excel downloadable file from the data available in the database(Oracle 8i)?.If I create a file DSN will it be helpful?.
Thanx
Mahesh
venkat avunoori
Greenhorn

Joined: Aug 09, 2001
Posts: 2
>>Is there any other way to create Excel downloadable file from the data available in the database(Oracle 8i)?
>>>
We had the same requirement of giving excel downloadable file for web-browser clients, and this is how we did it.
In servlet, (once you get the data from the database), set the content type of response object to "application/vnd.ms-excel":
** response.setContentType("application/vnd.ms-excel");
Then you have to write to the out object of the response in excel format. Following is the simple excel format of 2 records with 4 columns:
****************
ID
;
C;Y1;X2;K"BL"
C;X3;K"Dest"
C;X4;K"Ship_Status"
C;X5;K"Vendor"
;
C;Y3
C;X2;K"6213358"
C;X3;K"VR"
C;X4;K"S-RAR Sent"
C;X5;K"0019066837602A"
;
C;Y4
C;X2;K"6220718"
C;X3;K"VR"
C;X4;K"S-RAR Sent"
C;X5;K"0000170732202B"
;
F;W1 1 5
F;W2 2 12
F;W3 3 9
F;W4 4 18
F;C1;FG0R
;
E
****************
First chunk is the column headings, Second and Third ones are row information and the last one is about the column widths and alignments. If you can build a dynamic string object with data in it (in the above format) and write it to the out object you should be able to get the excel file in the browser.
Let me know if any part of it is not clear
Mahesh Kulkarni
Ranch Hand

Joined: Jul 05, 2001
Posts: 62
Hi Venkat,
Thanx for your reply.We tried your solution it worked for display on client side.
But our requirement is that the Excel sheet should be created at server end on the fly and only file(.xls) to be downloaded from client end.Client doesn't need it in browser. If you could help me out we will be very happy.
Thanx
Mahesh
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 15960
    
  19

You can setup a content-disposition header with the "attachment" option to indicate that the spreadsheet is to be downloaded instead of displayed. However, not all browsers will honor it.


Customer surveys are for companies who didn't pay proper attention to begin with.
venkat avunoori
Greenhorn

Joined: Aug 09, 2001
Posts: 2
Hi Mahesh,
As far as I know, whether to open .xls in browser or offer it as download depends on the browser. Have you tried it in Netscape?
Because Netscape prompts the user with the two options. When it comes to IE, it depends on the client's PC setting.
In windows explorer settings,
view->options->file Types-->"Microsoft Excel work sheet"-->Edit-->"Confirm Open after download" tells the IE browser whether to prompt user or not.
Hope this helps you a bit.
Thanx,
Venkat.
Manjunath Reddy
Ranch Hand

Joined: Jul 26, 2001
Posts: 60
Take a look at the link below..maybe u might get a few ideas to work the solution out. This works on both ie and netscape.
http://www.planet-source-code.com/xq/ASP/txtCodeId.2183/lngWId.2/qx/vb/scripts/ShowCode.htm
Mahesh Kulkarni
Ranch Hand

Joined: Jul 05, 2001
Posts: 62
Thanx venky,Manju,Tim,
For your responses.I hope I'll try your solutions and come back to you.
Thanx,
Mahesh
 
Consider Paul's rocket mass heater.
 
subject: EJB and Excel
 
Similar Threads
Configuring DSN
Cpntainer Managed EJB - MS Access
EJB and Excel Urgent
Writing values to Excel from Applet
How to do connection pooling for cmp?