Upload csv or .xlsx file from JSP form to Database Table using servlet

Upload csv or .xlsx file from JSP form to Database Table using servlet

dear sir,

i need the Servlet code that reads the .xlsx or CSV excel file and stores it into the oracle database table.

Sir Its very Urgent I have to Submit My Project

i am using following code which is Working for .xls Excel file

package Daily_Performance;

  import java.io.*;
  import java.sql.*;
  import java.sql.Date;
  import java.util.*;
  import java.util.regex.*;

import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession;

import org.apache.poi.hssf.usermodel.*; import org.apache.poi.poifs.filesystem.POIFSFileSystem;

  public class uploadexcel extends HttpServlet {



      public void doPost(HttpServletRequest request, HttpServletResponse response)
                throws ServletException, IOException {
          response.setContentType("text/html");
          PrintWriter out=response.getWriter();
          String fileName="";

        try {
             fileName=writeintoFile(request);
             System.out.println("path"+fileName);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }             
            Vector dataHolder=read(fileName);
            saveToDatabase(dataHolder, request, response);


      }

      public String writeintoFile(HttpServletRequest request) throws Exception{
          String contentType = request.getContentType();
          String saveFile ="";
            if ((contentType != null) && (contentType.indexOf("multipart/form-data") >= 0)) {
                    DataInputStream in = new DataInputStream(request.getInputStream());
                    //we are taking the length of Content type data
                    int formDataLength = request.getContentLength();
                    byte dataBytes[] = new byte[formDataLength];
                    int byteRead = 0;
                    int totalBytesRead = 0;
                    //this loop converting the uploaded file into byte code
                    while (totalBytesRead < formDataLength) {
                            byteRead = in.read(dataBytes, totalBytesRead, formDataLength);
                            totalBytesRead += byteRead;
                            }
                                            String file = new String(dataBytes);
                    //for saving the file name
                     saveFile = file.substring(file.indexOf("filename=\"") + 10);
                    saveFile = saveFile.substring(0, saveFile.indexOf("\n"));
                    saveFile = saveFile.substring(saveFile.lastIndexOf("\\")
     + 1,saveFile.indexOf("\""));
                    int lastIndex = contentType.lastIndexOf("=");
                    String boundary = contentType.substring(lastIndex + 1,
    contentType.length());
                    int pos;
                    //extracting the index of file 
                    pos = file.indexOf("filename=\"");
                    pos = file.indexOf("\n", pos) + 1;
                    pos = file.indexOf("\n", pos) + 1;
                    pos = file.indexOf("\n", pos) + 1;
                    int boundaryLocation = file.indexOf(boundary, pos) - 4;
                    int startPos = ((file.substring(0, pos)).getBytes()).length;
                    int endPos = ((file.substring(0, boundaryLocation))
    .getBytes()).length;
                    // creating a new file with the same name and writing the content in new file
                    System.out.println("fileNamefileNamefileName : "+saveFile);
                    FileOutputStream fileOut = new FileOutputStream(saveFile);
                    fileOut.write(dataBytes, startPos, (endPos - startPos));
                    fileOut.flush();
                    fileOut.close();
            }
            return "C:\\Documents and Settings\\sajal.singhal\\Desktop\\eclipse\\"+saveFile;
      }

  public static void main( String [] args ) {

}
    public static Vector read(String fileName)    {
    Vector cellVectorHolder = new Vector();

    try{
          FileInputStream myInput = new FileInputStream(fileName);
            POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
            HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
            HSSFSheet mySheet = myWorkBook.getSheetAt(0);
            Iterator rowIter = mySheet.rowIterator(); 
             while(rowIter.hasNext()){
                   HSSFRow myRow = (HSSFRow) rowIter.next();
                   Iterator cellIter = myRow.cellIterator();
                   Vector cellStoreVector=new Vector();
             while(cellIter.hasNext()){
                      HSSFCell myCell = (HSSFCell) cellIter.next();
                      cellStoreVector.addElement(myCell);
              }
              cellVectorHolder.addElement(cellStoreVector);
      }
    }catch (Exception e){e.printStackTrace(); }
    return cellVectorHolder;
}
    private static void saveToDatabase(Vector dataHolder, HttpServletRequest request, HttpServletResponse response) {
    String username="";

    String[] dbValuesArray = new String[40];
            String password="";
            for (int i=1;i<dataHolder.size(); i++){
               Vector cellStoreVector=(Vector)dataHolder.elementAt(i);
               int colIndex = 0;
                    for (int j=0; j < cellStoreVector.size();j++){
                            HSSFCell myCell = (HSSFCell)cellStoreVector.elementAt(j);                                
                            String st = myCell.toString();
                            dbValuesArray[colIndex] = st.substring(0);
                            colIndex++; 


                                                    }




                    try{


                            String url = "jdbc:sqlserver://localhost:1433;DatabaseName=Sajal;";
                            String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
                            Class.forName(driver).newInstance();
                            Connection con = DriverManager.getConnection(url,"sa","password");
                            Statement stat=con.createStatement();

                            String access = request.getSession().getAttribute("access").toString();// temporary

                            String Updateby = request.getSession().getAttribute("username").toString();

                            System.out.println("Access is **** "+access);
                            System.out.println("Update BY is **** "+Updateby);



                            if(access.equals("1"))
                            {                                   
                                stat.executeUpdate("insert into UPLOADDATA(uniq_no,cor_date,cordinator,tse,acc_hol_name,installation,manu_name,store_name,address,state,city,region,contact_per,contact_no,metro,bank_def,key_inj,kwok_req_date,pos_dis_date,pos_rec_date,pod_no,courier,dep_date,dep_month,tat,dep_status,remark,kwok_update,edc_type,appl_no,tin_no,sale_per,fin_appr,trm_no,edc_sr_no,ter_id,old_sr_no,date_rep,store_code,UpdateBy)values("+dbValuesArray[0]+",'"+dbValuesArray[1]+"','"+dbValuesArray[2]+"','"+dbValuesArray[3]+"','"+dbValuesArray[4]+"','"+dbValuesArray[5]+"','"+dbValuesArray[6]+"','"+dbValuesArray[7]+"','"+dbValuesArray[8]+"','"+dbValuesArray[9]+"','"+dbValuesArray[10]+"','"+dbValuesArray[11]+"','"+dbValuesArray[12]+"','"+dbValuesArray[13]+"','"+dbValuesArray[14]+"','"+dbValuesArray[15]+"','"+dbValuesArray[16]+"','"+dbValuesArray[17]+"','"+dbValuesArray[18]+"','"+dbValuesArray[19]+"','"+dbValuesArray[20]+"','"+dbValuesArray[21]+"','"+dbValuesArray[22]+"','"+dbValuesArray[23]+"','"+dbValuesArray[24]+"','"+dbValuesArray[25]+"','"+dbValuesArray[26]+"','"+dbValuesArray[27]+"','"+dbValuesArray[28]+"','"+dbValuesArray[29]+"','"+dbValuesArray[30]+"','"+dbValuesArray[31]+"','"+dbValuesArray[32]+"','"+dbValuesArray[33]+"','"+dbValuesArray[34]+"','"+dbValuesArray[35]+"','"+dbValuesArray[36]+"','"+dbValuesArray[37]+"','"+dbValuesArray[38]+"','')");

                            }



                            }


    stat.close();
    con.close();

    response.sendRedirect("http://192.168.100.127:8080/Deployment_Tracker_System/Deployment.jsp?r=succes");

    }

    catch(Exception e){

    }
    }
    }
  }

Thanks

View Answers

March 16, 2013 at 1:51 PM

Hi, use this code in servlet or jsp and modifiy this code as your requirement

public void read(File inputWorkbook) throws IOException {
        Workbook workbook;
        try {
        workbook = Workbook.getWorkbook(inputWorkbook);
        Sheet sheet = workbook.getSheet(0);
        // System.out.println("No of Columns :: "+sheet.getColumns());
        for (int j = 0; j < sheet.getRows(); j++) {
        for (int i = 0; i < sheet.getColumns(); i++) {
        Cell cell = sheet.getCell(i, j);
        CellType type = cell.getType();
        if (cell.getType() == CellType.LABEL) { System.out.print(cell.getContents() + " "); }
        else if (cell.getType() == CellType.NUMBER) {System.out.print(cell.getContents() + " "); }
        else { System.out.print(cell.getContents() + " "); }
        }
        System.out.println("\n"); }
        } catch (BiffException e) { e.printStackTrace(); }
}

Note :set the classpath to jxl.jar file









Related Tutorials/Questions & Answers:
Upload csv or .xlsx file from JSP form to Database Table using servlet
Upload csv or .xlsx file from JSP form to Database Table using servlet  dear sir, i need the Servlet code that reads the .xlsx or CSV excel file and stores it into the oracle database table. Sir Its very Urgent I have
Upload CSV File into Columns of sql table using servlets and jsp
Upload CSV File into Columns of sql table using servlets and jsp  Hello sir, plz give me the code to upload csv file data into respective columns of table
Advertisements
Upload Excel into Database Table Using SERVLET - JSP-Servlet
Upload Excel into Database Table Using SERVLET  Hi i hava sitution that upload .xls file into database table which has same coloumn names... in database table are NAME,ID,SAL. please help me to upload that excel using
Retrieve database from the table dynamically in jsp from oracle using servlet
Retrieve database from the table dynamically in jsp from oracle using servlet  Sir, I have created a table in oracle using eclipse, and added few... using java servlet from the database in the jsp page
how to upload an image from a jsp page to a mysql database table using jsp
how to upload an image from a jsp page to a mysql database table using jsp  how to upload an image from a jsp page to a mysql database table using jspstrong text
upload a file and write it in JSP using servlet
upload a file and write it in JSP using servlet  Hello, I'm facing a problem here. I want to upload a file through abc.jsp and write the contents of file using a servlet in xyz.jsp. It is supposed to be a excel file which
upload file and insert other details to database using a single form
upload file and insert other details to database using a single form   hi.. I have problem with uploading a file and insert other user datas together which I retrieved from another jsp/html page. Here i was able to upload file
how to display a table from database using servlet
how to display a table from database using servlet  how to display a table with values from servletpage   Hi Friend, Please go through the following link:ADS_TO_REPLACE_1 http://roseindia.net/jsp/servlet-jsp-data
reading the records from a .xlsx file and storing those records in database table
reading the records from a .xlsx file and storing those records in database table  Here is my requirement, I want to read the records from a .xlsx file and store that records in database table. I tried like this public class
Image upload in mysql database using jsp servlet
Image upload in mysql database using jsp servlet  Hello, I need code to insert image in mysql database, I have seen the code which is already in your... different format of file to upload into mysql db like pdf and doc file
file upload and insert name into database - JSP-Servlet
file upload and insert name into database  Hi, I just joined as a fresher and was given task to upload a file and insert its name into database...  HIread for more information,http://www.roseindia.net/jsp/file_upload
How I Upload File and Store that file name in Database using JSP
How I Upload File and Store that file name in Database using JSP  Hi All, I m the beginner in JSP and I want to upload the file and store that file and some other form data in MySQL database. Ex. There is one employee detail
upload csv to mysql db using jsp upload
upload csv to mysql db using jsp upload  Hello all, Please give me the code to uplad .csv from jsp page and insert values into MySQl db. I have a table with 8 cloumns.(MDN--varchar(30),Otafdate date,crt varchar(30),dmdn
How i upload file and save that record in database using JSP?
How i upload file and save that record in database using JSP?  Hi All, I m the beginner in JSP and I want to upload the file and store that file and some other form data in MySQL database. Ex. There is one employee detail form
To Upload and insert the CSV file into Database
to upload a CSV file through JSP and insert it into the database. For this, we have... To Upload and insert the CSV file into Database... on upload_page.jsp. Before proceeding further, we need table in database. We created
upload to database - JSP-Servlet
to upload a pdf file into database(sqlserver2000) using jsp. In roseindia some examples... into database whenever i want that uploaded pdf file i have to retrieve it from database please help me friends thanks in advance it is very urgent
Read code from excel sheet and upload into database using JSP
Read code from excel sheet and upload into database using JSP  I want to upload data to database from an excel worksheet using jsp ...Please help
How to Upload a file directly to Oracle database using JSP or Servlets?
How to Upload a file directly to Oracle database using JSP or Servlets?  Hi I want to upload a file(csv or excel) to Oracle 10g Database using JSP...;Upload File to Oracle Database</h2> <form id="form1" enctype
how do i upload a file by using servlet or jsp?
how do i upload a file by using servlet or jsp?  hi plz tell me the write java code
how to save html form data into .csv file using only jsp.
how to save html form data into .csv file using only jsp.  Dear all, I am developing a website only using jsp and html. i need to save the form data into a .csv file using jsp. can anyone give me any sample solution or tutorial
Upload Image and save in database using jsp-servlet mvc
Upload Image and save in database using jsp-servlet mvc  Here is my code.. In jsp ... <form name=frm method="post" action="Device"> <table> <tr><TD ><B>Upload Image</B><
Upload Image and save in database using jsp-servlet mvc
Upload Image and save in database using jsp-servlet mvc  Here is my code.. In jsp ... <form name=frm method="post" action="Device">...;TD ><B>Upload Image</B></TD> <td><input type="file
How display a image on servlet from file upload - JSP-Servlet
How display a image on servlet from file upload   Dear Sir, My Question is: How display a image on servlet from file upload Your Answer: Hi... of file. like any .jpg, .txt. Servlet page dispaly empty in each cases
how to use String tokenizer on table that is retrieved from database using jsp servlet
how to use String tokenizer on table that is retrieved from database using jsp servlet  Query:Table---- mysql> select pid,medicinename,dose,day,qty from medicinedetails2 where pid=15
How display a image on servlet from file upload - JSP-Servlet
How display a image on servlet from file upload  Dear Sir, My issue is: How display a image on servlet from file upload I receive your answer today....jar and set that on classpath.I keep the servlet file in javacode package. First
How display a Image on servlet from file upload - JSP-Servlet
How display a Image on servlet from file upload  Dear Sir, I were ask a question that How display the Image on servlet through file upload. Today I get your answer. But Sir, It code not display the image on servlet
file upload using JSP
file upload using JSP  I have created a form to upload a file in a html page, now i want to get the path of the file in a jsp page so what code...="java" %> <HTML> <HEAD><TITLE>Display file upload form
How display a Image on Servlet from file upload - JSP-Servlet
How display a Image on Servlet from file upload  Dear Sir, My requirement is I want to display a Image on Servlet from File Upload. But It not display on servlet,Its appear a downloaded form and downloaded on disk when click
File upload - JSP-Servlet
in the problem part]   Hi friend, Display file upload form to the user File Uload Using JSP   Choose the file To Upload...File upload  Hello Friends, In my JSP project i want
Image upload file - JSP-Servlet
Image upload file  I want a code for image upload jsp or servlet.  Hi friend, For image upload jsp or servlet visit to : http://www.roseindia.net/jsp/file_upload/employee_upload_profile_image.shtml http
File upload - JSP-Servlet
----------------------------------------------------------------------------- Display file upload form to the user <% // for uploading the file we used Encrypt type of multipart/form-data and input...File upload  I am trying to do a file upload program. But, it shows
fetch record from oracle database using jsp-servlet?
fetch record from oracle database using jsp-servlet?  how can i fetch data from oracle database by using jsp-servlet. i'm using eclipse, tomcat server and oracle database and creating jsp pages and also using servlet
How to browse excel file and stored the contents into the database using jsp/servlet?
How to browse excel file and stored the contents into the database using jsp/servlet?  Hi.. I want to browse excel file and stored the file data into the My-sql database using jsp/servlet
How to display image in jsp from database using Servlet?
How to display image in jsp from database using Servlet?  Hi, How to display image in jsp from database using Servlet? Thanks   Hi, You will find code and example program at Retrieve image from database using Servlet
Download CSV File from Database in JSP
Download CSV File from Database in JSP  ... to download CSV  file from database in JSP.  In this example, we have... and download the CSV file  from database. All CSV file will show
How to retrieve image from database using jsp and servlet?
How to retrieve image from database using jsp and servlet?  Hi, I am trying to find code for displaying the image from database in a JSP page. How to retrieve image from database using jsp and servlet
how to display values from database into table using jsp
how to display values from database into table using jsp  I want to display values from database into table based on condition in query, how... the values from database based on the bookname or authorname entered must be display
Read data from excel file and update database using jsp
Read data from excel file and update database using jsp  read data from excel file and update database using jsp Hi, I am using a MySQL database... upload excel file and update database using JSP ? Thanks in Advance
Populate dropdown menu from database using jsp and servlet
Populate dropdown menu from database using jsp and servlet  please i need code to populate dropdown menu from mysql database using jsp and servlet. thanks
form based file upload using servlets
form based file upload using servlets  Hai all, I wrote a program to upload a file into specified directory and store the file path and username... file path it uploaded successfully but in database table it gives null value
File Upload - JSP-Servlet
File Upload  Hi everyone, I am facing file uploading problem.the multiple file upload code of roseindia is working on localhost but the same code is not working on server.i think the package commons-fileupload-1.2.jar
file upload error - JSP-Servlet
file upload error  Iam doing jsp project. File uploading is one part... ----------- file upload example..., I think jar problem. so Please add servlet-api.jar file in lib folder
how to connect the database using hibernet through servlet/jsp through form
how to connect the database using hibernet through servlet/jsp through form  plz give me the reply   Hi Friend, Please visit the following link:ADS_TO_REPLACE_1 http://www.roseindia.net/jsf/myfacesspring
retrive the employee details with image from mysql database using jsp servlet
retrive the employee details with image from mysql database using jsp servlet  im doing the web project to retrive the employee profile which i stored in the database using jsp servlet then want to show the result in the next jsp
image upload with jsp from form
image upload with jsp from form  hi i used the code specified in your following post http://www.roseindia.net/answers/viewqa/JSP-Servlet/9749-image...) org.apache.jsp.upload_jsp._jspService(upload_jsp.java:85
File Upload in J2ee on solaris machine using sftp - JSP-Servlet
File Upload in J2ee on solaris machine using sftp  Hi, Currently we are using FTP to upload the file from our J2EE web application... the application in such a way that file upload can be done using SFTP on remote Solaris
Retrieve image from database using servlet and display in JSP
Retrieve image from database using servlet and display in JSP  Hi, I... to use MySQL Database from JSP page. How to retrieve image from database using servlet and display in JSP? Thanks
file upload error - JSP-Servlet
file upload error  Hello friends, In my project when i am trying to upload a file i used the roseindia.net coding from URL: http://www.roseindia.net/jsp/file_upload/employee_upload_profile_image.shtml
Exporting data from mysql into csv using jsp
Exporting data from mysql into csv using jsp  Hi friends.... I want to export the data from mysql to csv file using... i am having 30 columns in my... want csv file with 10 rows.. i want to do in jsp... pls help me soon..its very
How to get the data from the database using Servlet or JSP program
How to get the data from the database using Servlet or JSP program  ... the problem   Get data from database using servlet Retrieve data from database using JSP Get data from database using JSP

Ads