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

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