com.larasolution.serverlts.FileUploadHandler.java Source code

Java tutorial

Introduction

Here is the source code for com.larasolution.serverlts.FileUploadHandler.java

Source

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.larasolution.serverlts;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 *
 * @author shan
 */
public class FileUploadHandler extends HttpServlet {

    private static final String UPLOAD_DIRECTORY = "C:/uploads";
    private static final String CSV_FILE = "C:/uploads/data.csv";
    private static String tablename = null;
    private static String lines;

    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        //     tablename=request.getParameter(tablename)
        //process only if its multipart content
        FileOutputStream fos = new FileOutputStream("C:\\uploads\\data.csv");
        String list = "";
        List<List> allData = new ArrayList<List>();

        List<String> parameters = new ArrayList<String>();
        if (ServletFileUpload.isMultipartContent(request)) {

            try {

                StringBuilder data = new StringBuilder();
                List<FileItem> multiparts = new ServletFileUpload(new DiskFileItemFactory()).parseRequest(request);
                System.out.println(multiparts);
                for (FileItem item : multiparts) {
                    if (item.isFormField()) {
                        parameters.add(item.getFieldName());
                        System.out.println(parameters);
                    }
                    if (!item.isFormField()) {
                        String name = new File(item.getName()).getName();

                        item.write(new File(UPLOAD_DIRECTORY + File.separator + name));
                        //System.out.println(File.separator);
                        // Get the workbook object for XLSX file
                        XSSFWorkbook wBook = new XSSFWorkbook(
                                new FileInputStream(UPLOAD_DIRECTORY + File.separator + name));

                        XSSFSheet zz = wBook.getSheetAt(0);
                        FormulaEvaluator formulaEval = wBook.getCreationHelper().createFormulaEvaluator();

                        Row row;
                        Cell cell;

                        // Iterate through each rows from first sheet
                        Iterator<Row> rowIterator = zz.iterator();
                        while (rowIterator.hasNext()) {
                            row = rowIterator.next();

                            // For each row, iterate through each columns
                            Iterator<Cell> cellIterator = row.cellIterator();

                            while (cellIterator.hasNext()) {

                                cell = cellIterator.next();

                                switch (cell.getCellType()) {
                                case Cell.CELL_TYPE_BOOLEAN:
                                    data.append(cell.getBooleanCellValue()).append(",");
                                    break;
                                case Cell.CELL_TYPE_NUMERIC:
                                    if (DateUtil.isCellDateFormatted(cell)) {
                                        data.append(
                                                com.larasolution.modle.getDate.getDate5(cell.getDateCellValue()))
                                                .append(",");
                                    } else {
                                        data.append(cell.getNumericCellValue()).append(",");
                                    }

                                    break;
                                case Cell.CELL_TYPE_STRING:
                                    data.append(cell.getStringCellValue()).append(",");
                                    break;
                                case Cell.CELL_TYPE_BLANK:
                                    data.append("" + ",");
                                    break;
                                case Cell.CELL_TYPE_FORMULA:
                                    Double value = Double.parseDouble(formulaEval.evaluate(cell).formatAsString());

                                    data.append(String.format("%.2f", value)).append(",");
                                    break;
                                default:
                                    data.append(cell).append("");

                                }

                            }
                            data.append("\r\n");
                            //String k = data.substring(0, data.length() - 3);
                            //ls.add(k);

                            // data.setLength(0);
                        }

                        fos.write(data.toString().getBytes());
                        fos.close();

                        //
                    }
                }

                savetosql();
                request.setAttribute("message", "successfully uploaded ");
            } catch (Exception ex) {
                request.setAttribute("message", "File Upload Failed due to " + ex);
            }

        } else {
            request.setAttribute("message", "Sorry this Servlet only handles file upload request");
        }

        request.setAttribute("arrayfile", allData);
        request.setAttribute("names", parameters);
        RequestDispatcher disp = getServletContext().getRequestDispatcher("/FileUploadResult.jsp");
        disp.forward(request, response);

        // System.out.println(allData.size());
        // response.sendRedirect("send.jsp?arrayfile=" + list + "");
        //request.getRequestDispatcher("/send.jsp?arrayfile='"+ls+"'").forward(request, response);
    }

    private static void savetosql() {
        String columns;
        String columnss = "model, avilableqty, reorderqty, minimusl, partCode";
        columns = "Distributor_Code,Distributor_Name,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,Total_Value,Total_Qty";
        columns = "Distributor_Code,Distributor_Name,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,Total_Value,Total_Qty";
        String distAging = "disId, disName, 0to30, 31to60, 61to90, Over90days,@dummy";

        try {

            String JDBC_DRIVER = "com.mysql.jdbc.Driver";
            String myUrl = "jdbc:mysql://localhost/dashbord_ccl";

            try {
                Class.forName(JDBC_DRIVER);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
            Connection conn = DriverManager.getConnection(myUrl, "lara", "lara@1234");

            String query = "LOAD DATA LOCAL INFILE  '" + CSV_FILE + "' INTO TABLE " + tablename
                    + " FIELDS TERMINATED BY ','  ENCLOSED BY '\"' LINES TERMINATED BY '\\n' IGNORE " + lines
                    + " LINES (" + distAging + ") set id=null ;";
            System.out.println(query);
            // create the mysql insert preparedstatement
            PreparedStatement psreparedStmt = conn.prepareStatement(query);

            // execute the preparedstatement
            psreparedStmt.execute();

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    static void xlsx(File inputFile, File outputFile) {
        // For storing data into CSV files
        StringBuffer data = new StringBuffer();
        try {
        } catch (Exception ioe) {
            ioe.printStackTrace();
        }
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP <code>GET</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Handles the HTTP <code>POST</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Returns a short description of the servlet.
     *
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>

}