Discussions

Web tier: servlets, JSP, Web frameworks: Jsp Read Ms Excel Worksheet Data

  1. Jsp Read Ms Excel Worksheet Data (12 messages)

    Objective :
    Using JSP to read data from a Ms Excel file (.xls),
    and then store the data to a database.

    1. How to read specific worksheet data of a Ms Excel workbook?
    2. Example, from a "test.xls" file, there is a worksheet named "premium",
    and the worksheet contains information as :

    RecNo MemberName Premium(RM)
    1 Rohana 1250
    2 Darliza 2350
    3 David 1999

    3. So, my database record should contains 3 records, which are:
    1 Rohana 1250
    2 Darliza 2350
    3 David 1999

    4. appreciate if anyone knows about that.
  2. I would utilize the Jakarta POI project to read the Excel files. Then simply use JDBC to store the data into the database.

    http://jakarta.apache.org/poi/index.html
  3. Reading and Writing Excel Files with POI

    Best Of Luck.
  4. thank Anil Saldhana and Seinar Overbeck Cook.
    Yup, I have tried out to use the POI lib (HSSF User Model) for the above purpose.
    Anyway I got problem on reading a cell (eg cell A3 as mentioned in the following Eg.) that contains formula value.
    Eg.
    cell A1 value is 5, A2 is 6, A3 is (A1+A2); so, A3 would display as 11
  5. Use JExcel[ Go to top ]

    alternatively,you could make use of the JExcelApi.This is more straightforward than the POI stuff.
  6. thanks Chetan.. I have been trying to use the JExcel API one month already;
    but, I have problem on reading an excel file when opening the excel file on a client's PC, (I have no problem opening it from a PC which acts as the web server(Apache Tomcat4.0).
    I have no problem when opening a file from the web server PC.

    Actually, my program reads an input file from a client, and then would start to process the input file; the code is as below:
        ....
        String inputFile1=request.getParameter("fileupload1");
        .....
        Workbook workbook = Workbook.getWorkbook(new File(inputFile1));
        Sheet sheet = workbook.getSheet(sheetName);
        .....

    The below error occured when the input file is read from a client PC (there is
    a file test2.xls in the D:\tmp\ at the client's PC)

    ******************************************************************************
    Apache Tomcat/4.0.4 - HTTP Status 404 - D:\tmp\test2.xls (The system cannot find the path specified)
     
    type Status report
    message D:\tmp\test2.xls (The system cannot find the path specified)
    description The requested resource (D:\tmp\test2.xls (The system cannot find the path specified)) is not available.


    ******************************************************************************

    appreciate if anyone has solution.
  7. Use JExcel[ Go to top ]

    Hi,

    Where can I get JExcel from..
    The website threebit.net has the link for download broken.
    appreciate response.
  8. Use JExcel[ Go to top ]

    I am using jexcel to write to excel sheet.But i am not able to open it through browser using jsp

    Any ideas
    Please Help

    My code is:


    <%@page language="java" import = "java.io.* , java.sql.* , java.util.* , jxl.* , jxl.write.* , oracle.jdbc.driver.* " %>

    <%@ taglib uri="netui-tags-databinding.tld" prefix="netui-data"%>
    <%@ taglib uri="netui-tags-html.tld" prefix="netui"%>
    <%@ taglib uri="netui-tags-template.tld" prefix="netui-template"%>
    <!DOCTYPE HTML PUBLIC " -//W3C//DTD HTML 4.0 Transitional// EN">

    <HTML><HEAD><TITLE>RATE TABLE </TITLE>
    <META content = "application/vnd.ms-excel ; charset = windows-1252" http-equiv = Content-Type>
    <META content=en-us http-equiv=Content-Language>
    <META content="Microsoft FrontPage 4.0" name=GENERATOR>
    <META content=FrontPage.Editor.Document name=ProgId>
    </HEAD>

        <%
                try{
    String fname = "RateTableRT.xls";
                    File sheetName = new File(fname);
                 response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-disposition","attachment;filename="+fname);
    OutputStream outStream = response.getOutputStream();
    WritableWorkbook workbook = Workbook.createWorkbook(outStream);
    WritableSheet sheet = workbook.createSheet("RateTable", 0);
    // --------------Create a cell format for Arial 10, bold ------------WritableFont arial10font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false);
    WritableCellFormat arial10format = new WritableCellFormat (arial10font);

    //----------------- Create the label, specifying content and format --
    Label label1 = new Label(0, 0, " ^ SOA" , arial10format);
    sheet.addCell(label1);
    Label label2 = new Label(c, r, "Attribute");
    sheet.addCell(label2);
    Label label3 = new Label(c+1,r, "Value");
    sheet.addCell(label3);

    workbook.write();
    workbook.close();
    outStream.close();
    } /// end of try block

    catch(Exception e){
    System.out.println("error: "+e.toString());
              e.printStackTrace();
    } // end of catch block

    %>

    </HTML>
  9. While response generation u have to use either PrintWriter or OutputStream . But not both. U are using the outstream in your code. but the jsp compiler internally makes use of printwriter internally for generating spaces etcc..


    So try to implement the same technique with the servlet it works fine.

    Otherwise u can check the code generated by your jsp compiler. U can very easily sort out the porblem
  10. Please Help Me[ Go to top ]

    Hello Friend,

          I am facing the Same Problem.Iwant to Get the data from Excel into my
          JSP Page.Then I have to Query on that and I have to Store the Result in
          the Table.Could you please help me how to do the task.

          What are the Tools I required.How to set Path. I am using
          XP Proffesional,Excel98.Could you please give me the code.

           Please help me regarding this.

           Thank you very much.

    with regards,
    vnlsk
  11. Please Help Me[ Go to top ]

    Hi,

    I am now having the same issues.
    If you guys have got a work around.
    Please advice, or pointers will be even better.

    Thanks in advance.

    Regards,
    Chandan.
  12. Hai all,

    i have an excel sheet that contains some data.
    i am displaying that sheet on browser whenever the client request it.
    i successfully completed upto this...

    My problem is

    How can i read modified Excel work sheet data which was displayed on client's Browser using JSP???.

    and how can i put submit button on that page to submit the excel sheet???

    Thanks in advance...
  13. I'm also in the similar situation, could you please let me know how did you resolve the problem