Java tutorial
/* * 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> }