Solved

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

Posted on 2002-04-30
17
323 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
Comment Utility
You can - but you must use JNI
0
 
LVL 10

Expert Comment

by:Nushi
Comment Utility
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
Comment Utility
basiclly you will have to use VBScript and not Java Script.

Nushi.
0
 
LVL 10

Expert Comment

by:Nushi
Comment Utility
basiclly you will have to use VBScript and not Java Script.

Nushi.
0
 

Author Comment

by:marcoamador
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 19

Expert Comment

by:Jim Cakalic
Comment Utility
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
Comment Utility
<%@ 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
Comment Utility
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
Comment Utility
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
Comment Utility
>>
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
Comment Utility
OK. If it works with Excel then I stand corrected :-)
0
 
LVL 86

Expert Comment

by:CEHJ
Comment Utility
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
Comment Utility
 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
parentbit challenge 3 50
Starting to learn JAVA, 7 46
bitbucket vs gitbucket 3 28
what is a "java.lang.System Property"   ? 20 27
For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
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…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…

763 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

6 Experts available now in Live!

Get 1:1 Help Now