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

Ads
 

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

Ads









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
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
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
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
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
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
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
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
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
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 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 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 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 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
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 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
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
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
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
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
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
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
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
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
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
Data needs to be gathered in XML file from the database (MySql) using JSP
data regarding particular id from the database table. Data needs to be gathered in XML file from the database (MySql) using appropriate JSP/Java Bean functions...Data needs to be gathered in XML file from the database (MySql) using JSP 

Ads