Solved

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

Posted on 2002-04-30
17
371 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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
 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
Suggested Courses

617 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