Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 397
  • Last Modified:

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

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
marcoamador
Asked:
marcoamador
  • 3
  • 3
  • 3
  • +6
1 Solution
 
Venci75Commented:
You can - but you must use JNI
0
 
NushiCommented:
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
 
NushiCommented:
basiclly you will have to use VBScript and not Java Script.

Nushi.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
NushiCommented:
basiclly you will have to use VBScript and not Java Script.

Nushi.
0
 
marcoamadorAuthor Commented:
Sorry, but I realy need the code in Java, because I will use it as a component called in JSP.
Thanks anyway.
0
 
CEHJCommented:
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
 
shyamkumarreddyCommented:
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
 
jerelwCommented:
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
 
Jim CakalicSenior Developer/ArchitectCommented:
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
 
raid999Commented:
<%@ 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
 
girionisCommented:
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
 
Jim CakalicSenior Developer/ArchitectCommented:
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
 
CEHJCommented:
>>
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
 
Jim CakalicSenior Developer/ArchitectCommented:
OK. If it works with Excel then I stand corrected :-)
0
 
CEHJCommented:
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
 
girionisCommented:
 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 3
  • +6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now