Latest Tutorials| Questions and Answers|Ask Questions?|Site Map



Home Answers Viewqa JSP-Servlet Upload csv or .xlsx file from JSP form to Database Table using servlet

Have Programming Question? Ask it here!
 
 
 


Sajal Singhal
Upload csv or .xlsx file from JSP form to Database Table using servlet
1 Answer(s)      a year and 5 months ago
Posted in : JSP-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


Advertisement
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
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
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 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 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 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 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 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
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
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
php csv file uploding into mysql database table.
php csv file uploding into mysql database table.  hai friends, i... fields. i have one mysql database table with two files fields, when ever i am uploding csv file, i want to fetch the datas in corresponding fields in table. can
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
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... file  from database. All CSV file will show as hyperlink. 2
Export Database table to CSV File
Export Database table to CSV File In this tutorial, you will learn how to retrieve data from database and save it to CSV File. The Comma-separated values... as writing into a CSV file. Here is a database table is to be extracted: Example
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: http://roseindia.net/jsp/servlet-jsp-data-list.shtml Thanks
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
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
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
upload a file into database and progrm should support excel and text and csv file formats
upload a file into database and progrm should support excel and text and csv file formats  Hai all, I need a program to upload a file into database table... and the program should support .excel ,.txt ,.csv file formats. can
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
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 to save form data to a csv file using jquery or ajax
How to save form data to a csv file using jquery or ajax  Please let...=data.responseText; Now the problem is ,i should write form data to a csv file using ajax... this. i am able to read the csv file using this code if (window.XMLHttpRequest
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
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
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
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
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
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
PHP code for csv file upload into mysql
PHP code for csv file upload into mysql  I want to upload a file from my pc to mysql server using upload option. while i m selecting the csv file it ll be upload the mysql database. can u send me the coding? i have tried
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
Export data into CSV File using Servlet
Export data into CSV File using Servlet  ... to Export data into  CSV file using Servlet. We have created  file "JdbcCsvFile.java" to export data from this .java file.. Brief
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
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
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
java, upload csv file - Development process
java, upload csv file  hi, i have a csv file having 29 colums, i want to upload that csv file to database. i need the code,, can u please send me... link: http://www.roseindia.net/jsp/upload-insert-csv.shtml Thanks
Java xlsx - JSP-Servlet
Java xlsx  Dear Sir's / Madam's i want to read/write XLSX file... but not xlsx file.   Hi Friend, 1)Write .xlsx file: import java.io....); } } 2)Read .xlsx File: import java.io.*; import java.util.*; import
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  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
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
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
reading a csv file from a particular row
reading a csv file from a particular row  how to read a csv file from a particular row and storing data to sql server by using jsp servlet
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
export value in csv from jsp
export value in csv from jsp  Hi! i have creted a html form using javascript. i hv taken the array values in jsp. nw i want to pass these array values from jsp to csv file. so please help me how to send
Expport data into CSV File using JDBC in Servlet
into CSV File using JDBC in Servlet   ... into CSV file using JDBC in Servlet. We have created  file "JdbcCsvFile.java" to export data from database in to CSV file. Brief
Upload and download file - JSP-Servlet
the word document file: 1)page.jsp Display file upload form... from a database but before downloading a file, server should ask me...Upload and download file  What is JSP code to upload and download
How to upload file using JSP?
How to upload file using JSP?   Hi all, I m the beginner in JSP, I want to upload file on server in specific folder.   1)page.jsp... file upload form to the user</TITLE></HEAD> <
Advertisements
 

 

 

DMCA.com