Solved

How can I open an Excel file in JSP and process it (parse it) ?

Posted on 2002-04-30
17
338 Views
Last Modified: 2012-08-14
How can I open an Excel file in JAVA and process it (parse it) ?
I know that I can do this with JDBC.
But as the name of the excel file is a parameter, I can't do it with ODBC system dsn.
0
Comment
Question by:marcoamador
  • 3
  • 3
  • 3
  • +6
17 Comments
 
LVL 9

Expert Comment

by:Venci75
ID: 6980122
You can - but you must use JNI
0
 
LVL 10

Expert Comment

by:Nushi
ID: 6980142
use this script to open an excel file.
it will generat sample file for you.
after the file is opened you will have to close it in order to return tho the page

Nushi.

<HTML>
<HEAD>
</HEAD>
<BODY>
Press the button to load Excel
<FORM NAME="Form1">
   <INPUT TYPE="Button" NAME="Button1" VALUE="Click">
   <SCRIPT FOR="Button1" EVENT="onClick" LANGUAGE="VBScript">
     Dim excel
     Set excel = CreateObject("Excel.Application")
     Set book = excel.Workbooks.Add
        Set sheet = excel.Worksheets(1)
     sheet.Range("A1:C1").Value = Array("Header1", " Header2", " Header3")
     'Create an array with 3 columns and 100 rows
     Dim counter, cell
     For counter = 1 To 100
          sheet.Range("A" & (counter + 1)).Value = "Cell A" & (counter + 1)
          sheet.Range("B" & (counter + 1)).Value = "Cell B" & (counter + 1)
          sheet.Range("C" & (counter + 1)).Value = "Cell C" & (counter + 1)
     Next
        'Transfer the array to the worksheet starting at cell A2
     excel.Visible = True
     excel.WindowState = 3
        MsgBox "created spreadsheet - check it out and press OK when ready to close"
     book.SaveAs "C:\Book1.xls"
     excel.Quit
     excel=""
   </SCRIPT>
</FORM>
</BODY>
</HTML>
0
 
LVL 10

Expert Comment

by:Nushi
ID: 6980143
basiclly you will have to use VBScript and not Java Script.

Nushi.
0
 
LVL 10

Expert Comment

by:Nushi
ID: 6980149
basiclly you will have to use VBScript and not Java Script.

Nushi.
0
 

Author Comment

by:marcoamador
ID: 6980150
Sorry, but I realy need the code in Java, because I will use it as a component called in JSP.
Thanks anyway.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 6980321
You can open the Excel file using an ODBC driver and then read it with that. See http://www.javaworld.com/javaqa/2001-06/04-qa-0629-excel_p.html.
0
 
LVL 3

Expert Comment

by:shyamkumarreddy
ID: 6980449
macro
You can use JDBC to do this function. But U need to create DSN for it, because driver are been provided with the OS itself then you can query like a database to fetch the value. Other i don't know whether how to get such drivers from outside to query it as database.

Shyam
0
 
LVL 3

Expert Comment

by:jerelw
ID: 6983984
If you download Formula 1, you can open up excel files an do what you want with them.

http://www.actuate.com/forms/template2.asp?formid=385&seminarid=&pid=583&pcode=zenginee&res=
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 19

Expert Comment

by:Jim Cakalic
ID: 6987973
Have you tried the Jakarta open source project POI? It is a 100% Java implementation that can read/write OLE2 Compound Document Format -- Microsoft Excel and Word files. Specifically, the HSSF (Horrible SpreadSheet Format) subproject will read/write Excel 92(-2002) files.
    http://jakarta.apache.org/poi/index.html

While POI was great for writing, the earlier versions weren't as good for reading. We had problems with columns containing numeric values that we couldn't retrieve as Strings. So until that part becomes more robust, we're also using XLRD for reading.
    http://www.andykhan.com/

Best regards,
Jim Cakalic
0
 
LVL 1

Expert Comment

by:raid999
ID: 6988254
<%@ page contentType="application/vnd.ms-excel" %>

<%-- Note that there are tabs, not spaces, between columns. --%>

1997  1998  1999  2000  2001 (Anticipated)

12.3  13.4  14.5  15.6  16.7


if you are using JSP then you can use that. or if you are using servlets then you have to out put the same thing to Excel

response.setContentType("application/vnd.ms-excel");
PrintWriter out = res.getWriter();
out.println("1997\t1998\t1999\t2000\t2001\n");
out.println("12.3\t13.4\t14.5\t15.6\t16.7\n");
0
 
LVL 35

Expert Comment

by:girionis
ID: 8682688
No comment has been added lately, so it's time to clean up this TA.

I will leave a recommendation in the Cleanup topic area that this question is:

- points to CEHJ

Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

girionis
Cleanup Volunteer
0
 
LVL 19

Expert Comment

by:Jim Cakalic
ID: 8682959
Hmm. Hi, girionis. In this case, I have to dissent. The comment provided by CEHJ was to use ODBC. But this solution had already been excluded by the original question when it was stated, "as the name of the excel file is a parameter, I can't do it with ODBC". So I don't think that is an appropriate answer.

Venci75 claimed it could only be done in JNI which is not true. Nushi provided an ASP answer which seems inappropriate for the context. jerelw suggested using a product which, last I checked, had a 6 figure license cost. raid999 provided, best I can tell, a way to display the file but you cannot use this mechanism to 'process' the excel file. That leaves ...

:-o
0
 
LVL 86

Accepted Solution

by:
CEHJ earned 50 total points
ID: 8683166
>>
The comment provided by CEHJ was to use ODBC. But this solution had already been excluded by the original question when it was stated, "as the name of the excel file is a parameter, I can't do it with ODBC". So I don't think that is an appropriate answer.
>>

AFAIK, you don't need a DSN. See http://beta.experts-exchange.com/Programming/Programming_Languages/Java/Q_20176445.html
0
 
LVL 19

Expert Comment

by:Jim Cakalic
ID: 8683201
OK. If it works with Excel then I stand corrected :-)
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 8683245
Here's another link - i was reluctant to post it as it evidently didn't work! It may well be just a question of syntax though:
http://www.rgagnon.com/javadetails/java-0345.html
0
 
LVL 35

Expert Comment

by:girionis
ID: 8684456
 Hello Jim, I know what the asker said but, as CEHJ also said, I am pretty sure it can be done DSN-less.

  Anyway I am sure a moderator will consider both your comments and will allocate points to the right person(s) :-)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Application launch issue with Apache Tomcat 5 21
Unable to open debugger port in Intellij idea 6 143
groupNoAdj 7 85
base64 decode encode 12 119
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
The viewer will learn how to implement Singleton Design Pattern in Java.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now