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
Login         

View Questions and Answers by Category

Advertisements


 
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 7 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
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 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
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
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
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
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
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 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
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
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 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 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
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 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 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 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
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
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
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
Advertisements
 

 

 

DMCA.com