Solved

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

Posted on 2002-04-30
17
345 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…

776 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