public void run() {
    try {
      FileInputStream fis = new FileInputStream(file);
      XSSFWorkbook workbook = new XSSFWorkbook(fis);
      XSSFSheet sheet = workbook.getSheetAt(0);
      Iterator<Row> rowIterator = sheet.iterator();
      Iterator<Row> rowIterator2 = sheet.iterator();

      if (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        String aux = "";
        while (cellIterator.hasNext()) {
          Cell cell = cellIterator.next();
          switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
              aux += cell.getStringCellValue().toUpperCase() + " ";
              break;
          }
        }
        System.out.println("Aux: " + aux);
        if (aux.trim().equals(VALID_HEAD)) {
          textArea.append("EXCEL VALIDO\n\n");
          int size = 0;
          rowIterator2.next();
          while (rowIterator2.hasNext()) {
            rowIterator2.next();
            size++;
          }
          progressBar.setMaximum(size);
          progressBar.setValue(0);
          textArea.append("Clientes en fichero: " + size + "\n\n");
          int num = 0;
          while (rowIterator.hasNext()) {
            num++;
            textArea.append("--- CLIENT NUMBER " + num + " ---\n");
            Row row2 = rowIterator.next();
            processLine(row2.cellIterator());
            progressBar.setValue(num);
            Thread.sleep(1000);
          }
        } else {
          textArea.append(
              "El fichero excel seleccionado no es valido. "
                  + "Selecciona un fichero excel con las columnas: \n"
                  + VALID_HEAD);
        }
      }
      fis.close();
    } catch (Exception ex) {
      ex.printStackTrace();
    }
  }
  public static void main(String args[]) throws Exception {
    File file = new File("d:/delete/createworkbook.xlsx");
    FileInputStream fis = new FileInputStream(file);
    if (file.isFile() && file.exists()) {
      // Get the workbook instance for XLSX file
      XSSFWorkbook workbook = new XSSFWorkbook(fis);
      System.out.println("openworkbook.xlsx file open successfully.");
      // get worksheet, 0 based
      XSSFSheet sheet = workbook.getSheetAt(0);
      // get rows
      Iterator<Row> it = sheet.iterator();
      while (it.hasNext()) {
        Row row = (XSSFRow) it.next();
        // get cells
        Iterator<Cell> cellIt = row.cellIterator();
        while (cellIt.hasNext()) {
          Cell cell = cellIt.next();
          switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
              System.out.print(cell.getNumericCellValue() + " \t\t ");
              break;
            case Cell.CELL_TYPE_STRING:
              System.out.print(cell.getStringCellValue() + " \t\t ");
              break;
          }
        }
      }

    } else {
      System.out.println("Error to open openworkbook.xlsx file.");
    }
  }
  @Override
  public List<Map<String, Object>> parse() throws ReadCensusException {
    XSSFWorkbook listaVotantes = null;
    String fileName = Paths.get(filePath).getFileName().toString();

    try {

      FileInputStream file = new FileInputStream(new File(filePath));
      listaVotantes = new XSSFWorkbook(file);

      XSSFSheet hoja = listaVotantes.getSheetAt(0);
      Iterator<Row> rowIterator = hoja.iterator();
      Row row;

      while (rowIterator.hasNext()) {
        row = rowIterator.next();
        Iterator<Cell> columns = row.cellIterator();

        if (!columns.hasNext()) continue;

        try {
          voter = new HashMap<String, Object>();
          voter.put("name", columns.next().getStringCellValue());
          voter.put("email", columns.next().getStringCellValue());
          voter.put("nif", columns.next().getStringCellValue());
          voter.put("code", String.valueOf((int) columns.next().getNumericCellValue()));
          voter.put("password", PasswordGenerator.generate(8));
          voter.put("file", fileName);
          voter.put("line", row.getRowNum());
        } catch (Exception e) {
          throw new ReadCensusException(
              "[ERROR] ["
                  + fileName
                  + ":"
                  + row.getRowNum()
                  + "] El usuario no tiene el formato correcto");
        }

        voters.add(voter);
      }

    } catch (FileNotFoundException e) {
      throw new ReadCensusException("[ERROR] [" + fileName + "] El fichero no existe");
    } catch (Exception e) {
      throw new ReadCensusException(
          "[ERROR] [" + fileName + "] Fallo inesperado al leer el fichero: " + e.getMessage());
    } finally {
      try {
        if (listaVotantes != null) listaVotantes.close();
      } catch (Exception e) {
        throw new ReadCensusException("[ERROR] [" + fileName + "] I/O Error: " + e.getMessage());
      }
    }

    if (voters.isEmpty()) {
      throw new ReadCensusException("[AVISO] [" + fileName + "] El censo está vacío");
    }

    return voters;
  }
  @Test
  public void dataDrivernTest() throws IOException {
    FileInputStream f =
        new FileInputStream(
            "E:\\July16Batch\\WebdriverProject\\src\\com\\qedge\\excelfiles\\registration.xlsx");

    XSSFWorkbook wb = new XSSFWorkbook(f);
    XSSFSheet ws = wb.getSheet("Sheet1");
    Iterator<Row> row = ws.iterator();
    row.next();

    WelcomeMercuryTours wm = PageFactory.initElements(driver, WelcomeMercuryTours.class);
    RegisterMercuryTours1 rm1 = PageFactory.initElements(driver, RegisterMercuryTours1.class);
    RegisterMercuryTours2 rm2 = PageFactory.initElements(driver, RegisterMercuryTours2.class);
    wm.menu.register();
    while (row.hasNext()) {
      Row r = row.next();
      rm1.contactInformation(r);
      boolean result = rm2.validateRegistration(r.getCell(9).getStringCellValue());
      if (result == true) {
        r.createCell(12).setCellValue("Passed");
      } else {
        r.createCell(12).setCellValue("Failed");
      }
      driver.navigate().back();
    }
    FileOutputStream f1 =
        new FileOutputStream(
            "E:\\July16Batch\\WebdriverProject\\src\\com\\qedge\\resultexcelfiles\\registration.xlsx");
    wb.write(f1);
    f1.close();
    driver.quit();
  }
  private void processSheet(Element container, XSSFSheet sheet) {

    Element table = htmlDocumentFacade.createTable();
    int sIndex = sheet.getWorkbook().getSheetIndex(sheet);
    String sId = "sheet_".concat(String.valueOf(sIndex));
    table.setAttribute("id", sId);
    table.setAttribute("border", "1");
    table.setAttribute("cellpadding", "2");
    table.setAttribute("cellspacing", "0");
    table.setAttribute("style", "border-collapse: collapse;");

    css.append("#")
        .append(sId)
        .append(" tr{height:")
        .append(sheet.getDefaultRowHeightInPoints() / 28.34)
        .append("cm}\n");
    css.append("#")
        .append(sId)
        .append(" td{width:")
        .append(sheet.getDefaultColumnWidth() * 0.21)
        .append("cm}\n");

    // cols
    generateColumns(sheet, table);

    // rows
    Iterator<Row> rows = sheet.iterator();
    while (rows.hasNext()) {
      Row row = rows.next();
      if (row instanceof XSSFRow) processRow(table, (XSSFRow) row, sheet);
    }

    container.appendChild(table);
  }
  public XLSXPlugin(
      String contentFormat, String contentType, String fileInput, String currentWorkingDirectory) {
    this.contentFormat = new ContentFormat(contentFormat, contentType);
    this.fileInput = new File(fileInput);
    this.currentFile = fileInput;
    this.languages = new ArrayList<CMSLanguage>();
    this.cmsBlocks = new ArrayList<CMSBlock>();
    this.currentWorkingDirectory = currentWorkingDirectory;

    this.currentPageName =
        this.currentFile.substring(
            this.currentFile.lastIndexOf("/") + 1, this.currentFile.lastIndexOf("."));

    try {
      this.fileInputStream = new FileInputStream(this.fileInput);
      this.workBook = new XSSFWorkbook(this.fileInputStream);
      this.sheet = this.workBook.getSheetAt(0);
      this.evaluator = new XSSFFormulaEvaluator(this.workBook);
      this.rowIterator = sheet.iterator();
      this.processDataRows();
    } catch (FileNotFoundException fileNotFoundException) {
      fileNotFoundException.printStackTrace();
    } catch (IOException ioException) {
      ioException.printStackTrace();
    }
  }
 @Test
 public void linksTest() throws IOException {
   FileInputStream f =
       new FileInputStream(
           "C:\\Users\\RAHUL\\Downloads\\eclipse-jee-mars-R-win32 (1)\\eclipse\\rahu new\\seleniumprograms\\src\\com\\rahul\\excelfiles\\datadrivenexcel.xlsx");
   XSSFWorkbook wb = new XSSFWorkbook(f);
   XSSFSheet ws = wb.getSheet("sheet1");
   Iterator<Row> row = ws.iterator();
   row.next();
   while (row.hasNext()) {
     Row r = row.next();
     String linkname = r.getCell(0).getStringCellValue();
     try {
       driver.findElement(By.linkText(linkname)).click();
       String acturl = driver.getCurrentUrl();
       r.createCell(2).setCellValue(acturl);
       String expurl = r.getCell(1).getStringCellValue();
       if (acturl.equals(expurl)) {
         r.createCell(3).setCellValue("passsed");
       } else {
         r.createCell(3).setCellValue("failed");
       }
       driver.navigate().back();
     } catch (Exception e) {
       r.createCell(3).setCellValue("links not found");
     }
   }
   FileOutputStream f1 =
       new FileOutputStream(
           "C:\\Users\\RAHUL\\Downloads\\eclipse-jee-mars-R-win32 (1)\\eclipse\\rahu new\\seleniumprograms\\src\\com\\rahul\\resultsexcelfiles\\datadrivenexcel.xlsxlinks.xlsx");
   wb.write(f1);
   f1.close();
   driver.quit();
 }
  public static void parseExcelData(XSSFSheet excelSheet) {
    Iterator<Row> rowIterator = excelSheet.iterator();
    List<Row> rowList = Lists.newArrayList(rowIterator);

    for (int i = 1; i < rowList.size(); i++) {
      Iterator<Cell> cellIterator = rowList.get(i).cellIterator();
      parseCells(cellIterator);
    }

    addObjectsToDb();
    System.out.println(failureClasses.size() + " FailureClasses added to database.");
  }
  /**
   * @param args
   * @throws IOException
   */
  public static void main(String[] args) throws IOException {
    FileInputStream f =
        new FileInputStream(
            "E:\\March23Batch\\SeleniumAutomation\\src\\com\\qedge\\excelfiles\\registration11.xlsx");
    XSSFWorkbook wb = new XSSFWorkbook(f);
    XSSFSheet ws = wb.getSheet("Sheet1");

    Iterator<Row> row = ws.iterator();
    row.next();
    while (row.hasNext()) {
      Row r = row.next();
      System.out.println(r.getCell(1).getStringCellValue());
      /*double d=r.getCell(2).getNumericCellValue();
      long l=(long)d;
      String str=Long.toString(l);*/
      String str = Long.toString((long) r.getCell(2).getNumericCellValue());
      System.out.println(str);
    }
  }
  public static void main(String args[]) {
    try {
      FileInputStream file =
          new FileInputStream(new File("c:\\dev\\temp\\howtodoinjava_demo.xlsx"));

      // Create Workbook instance holding reference to .xlsx file
      XSSFWorkbook workbook = new XSSFWorkbook(file);

      // Get first/desired sheet from the workbook
      XSSFSheet sheet = workbook.getSheetAt(0);

      // Iterate through each rows one by one
      Iterator<Row> rowIterator = sheet.iterator();
      while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        // For each row, iterate through all the columns
        Iterator<Cell> cellIterator = row.cellIterator();

        while (cellIterator.hasNext()) {
          Cell cell = cellIterator.next();
          // Check the cell type and format accordingly
          switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
              System.out.print(cell.getNumericCellValue() + "\t");
              break;
            case Cell.CELL_TYPE_STRING:
              System.out.print(cell.getStringCellValue() + "\t");
              break;
          }
        }
        System.out.println("");
      }
      file.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
Exemple #11
0
  /**
   * This method loads the database table with the information in the mapping file.
   *
   * @throws IOException This exception is thrown if there is a problem accessing the spreadsheet.
   * @throws SQLException
   */
  @Override
  public void loadDatabaseTable() throws IOException, SQLException {

    prepareDatabase();

    XSSFWorkbook oWorkbook = new XSSFWorkbook(this.fisMapFile);

    XSSFSheet oMapSheet = oWorkbook.getSheet(PAGE_NAME);
    if (oMapSheet != null) {
      Iterator<Row> iterDrugRow = oMapSheet.iterator();
      long lRowIndex = 0;
      while (iterDrugRow.hasNext()) {
        Row oRow = iterDrugRow.next();

        if ((lRowIndex > 0) || ((lRowIndex == 0) && (!isTitleRow(oRow)))) {

          RowMapping oRowMapping = new RowMapping(oRow);
          RowProcessingStatus eStatus = null;
          try {
            addRowToTermDatabase(oRowMapping);
            eStatus = RowProcessingStatus.RECORD_WRITTEN;
          } catch (Exception e) {
            outputRowMessage("Exception", e.getMessage(), oRowMapping);
            eStatus = RowProcessingStatus.EXCEPTION_OCCURRED;
          }
          updateStatistics(eStatus);
        }

        lRowIndex++;

        if ((lRowIndex % NUM_ROWS_PER_SECTION) == 0) {
          System.out.println("Total rows processed so far: " + lRowIndex);
        }
      }
    }
  }
Exemple #12
0
  private void readBOM(File fileItem) throws Exception {
    FileInputStream file = new FileInputStream(fileItem);
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    XSSFSheet sheet = workbook.getSheetAt(0);
    Iterator<Row> rowIterator = sheet.iterator();
    Connection con = Connect.getconnection();
    PreparedStatement ps1 = con.prepareStatement("TRUNCATE TABLE bom");
    ps1.executeUpdate();

    Row nextRow = rowIterator.next();
    while (rowIterator.hasNext()) {
      nextRow = rowIterator.next();
      Iterator<Cell> cellIterator = nextRow.cellIterator();
      PreparedStatement ps =
          con.prepareStatement(
              "INSERT INTO `star`.`bom` (`FGNo`, `Parent`, `Child`, `Qty`, `Decs`, `Unit`, `Mat_grp`,`Mat_grp_desc`,`Mat_sub_grp`,`Mat_sub_grp_desc`,`Stock`) VALUES(?,?,?,?,?,?,?,?,?,?,?)");

      while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();
        switch (cell.getCellType()) {
          case Cell.CELL_TYPE_STRING:
            if (cell.getColumnIndex() == 0) {
              // FG
              ps.setString(1, cell.getStringCellValue());
              // System.out.println(cell.getStringCellValue());
              break;
            } else if (cell.getColumnIndex() == 1) {
              // Parent
              ps.setString(2, cell.getStringCellValue());
              // System.out.println(cell.getStringCellValue());
              break;
            } else if (cell.getColumnIndex() == 2) {
              // Child
              ps.setString(3, cell.getStringCellValue());
              // System.out.println(cell.getStringCellValue());
              break;
            } else if (cell.getColumnIndex() == 4) {
              // Decsription
              ps.setString(5, cell.getStringCellValue());
              // System.out.println(cell.getNumericCellValue());
              break;
            } else if (cell.getColumnIndex() == 5) {
              // Unit
              ps.setString(6, cell.getStringCellValue());
              // System.out.println(cell.getNumericCellValue());
              break;
            } else if (cell.getColumnIndex() == 6) {
              // Mat_grp
              ps.setString(7, cell.getStringCellValue());
              // System.out.println(cell.getNumericCellValue());
              break;
            } else if (cell.getColumnIndex() == 7) {
              // Mat_grp_desc
              ps.setString(8, cell.getStringCellValue());
              // System.out.println(cell.getNumericCellValue());
              break;
            } else if (cell.getColumnIndex() == 8) {
              // Mat_sub_grp
              ps.setString(9, cell.getStringCellValue());
              // System.out.println(cell.getNumericCellValue());
              break;
            } else if (cell.getColumnIndex() == 9) {
              // Mat_sub_grp_desc
              ps.setString(10, cell.getStringCellValue());
              // System.out.println(cell.getNumericCellValue());
              break;
            }
          case Cell.CELL_TYPE_NUMERIC:
            if (cell.getColumnIndex() == 3) {
              // Quantity
              ps.setDouble(4, cell.getNumericCellValue());
              // System.out.println(cell.getNumericCellValue());
              break;
            } else if (cell.getColumnIndex() == 10) {
              // Stock
              ps.setInt(11, (int) cell.getNumericCellValue());
              // System.out.println(cell.getNumericCellValue());
              break;
            }

          case Cell.CELL_TYPE_BLANK:
            if (cell.getColumnIndex() == 0) {
              // FG
              ps.setString(1, " ");
              // System.out.println(cell.getStringCellValue());
              break;
            } else if (cell.getColumnIndex() == 1) {
              // Parent
              ps.setString(2, " ");
              // System.out.println(cell.getStringCellValue());
              break;
            } else if (cell.getColumnIndex() == 2) {
              // Child
              ps.setString(3, " ");
              // System.out.println(cell.getStringCellValue());
              break;
            } else if (cell.getColumnIndex() == 3) {
              // Quantity
              ps.setDouble(4, 0);
              // System.out.println(cell.getNumericCellValue());
              break;
            } else if (cell.getColumnIndex() == 4) {
              // Decsription
              ps.setString(5, " ");
              // System.out.println(cell.getNumericCellValue());
              break;
            } else if (cell.getColumnIndex() == 5) {
              // Unit
              ps.setString(6, " ");
              // System.out.println(cell.getNumericCellValue());
              break;
            } else if (cell.getColumnIndex() == 6) {
              // Mat_grp
              ps.setString(7, " ");
              // System.out.println(cell.getNumericCellValue());
              break;
            } else if (cell.getColumnIndex() == 7) {
              // Mat_grp_desc
              ps.setString(8, " ");
              // System.out.println(cell.getNumericCellValue());
              break;
            } else if (cell.getColumnIndex() == 8) {
              // Mat_sub_grp
              ps.setString(9, " ");
              // System.out.println(cell.getNumericCellValue());
              break;
            } else if (cell.getColumnIndex() == 9) {
              // Mat_sub_grp_desc
              ps.setString(10, " ");
              // System.out.println(cell.getNumericCellValue());
              break;
            } else if (cell.getColumnIndex() == 10) {
              // Stock
              ps.setInt(11, 0);
              // System.out.println(cell.getNumericCellValue());
              break;
            }
          case Cell.CELL_TYPE_ERROR:
            if (cell.getColumnIndex() == 0) {
              // FG
              ps.setString(1, " ");
              // System.out.println(cell.getStringCellValue());
              break;
            } else if (cell.getColumnIndex() == 1) {
              // Parent
              ps.setString(2, " ");
              // System.out.println(cell.getStringCellValue());
              break;
            } else if (cell.getColumnIndex() == 2) {
              // Child
              ps.setString(3, " ");
              // System.out.println(cell.getStringCellValue());
              break;
            } else if (cell.getColumnIndex() == 3) {
              // Quantity
              ps.setDouble(4, 0);
              // System.out.println(cell.getNumericCellValue());
              break;
            } else if (cell.getColumnIndex() == 4) {
              // Decsription
              ps.setString(5, " ");
              // System.out.println(cell.getNumericCellValue());
              break;
            } else if (cell.getColumnIndex() == 5) {
              // Unit
              ps.setString(6, " ");
              // System.out.println(cell.getNumericCellValue());
              break;
            } else if (cell.getColumnIndex() == 6) {
              // Mat_grp
              ps.setString(7, " ");
              // System.out.println(cell.getNumericCellValue());
              break;
            } else if (cell.getColumnIndex() == 7) {
              // Mat_grp_desc
              ps.setString(8, " ");
              // System.out.println(cell.getNumericCellValue());
              break;
            } else if (cell.getColumnIndex() == 8) {
              // Mat_sub_grp
              ps.setString(9, " ");
              // System.out.println(cell.getNumericCellValue());
              break;
            } else if (cell.getColumnIndex() == 9) {
              // Mat_sub_grp_desc
              ps.setString(10, " ");
              // System.out.println(cell.getNumericCellValue());
              break;
            } else if (cell.getColumnIndex() == 10) {
              // Stock
              ps.setInt(11, 0);
              // System.out.println(cell.getNumericCellValue());
              break;
            }
        }
        // out.print("&nbsp;&nbsp;&nbsp;&nbsp");
      }
      ps.executeUpdate();
      // out.println("<br>");

    }
    workbook.close();
    file.close();
    con.close();
  }
Exemple #13
0
  private void readExcel() throws IOException, URISyntaxException {
    URL inputStream = this.getClass().getResource("ReleaseNotes-002.xlsx");
    File file = new File(inputStream.toURI());

    FileInputStream fIP = new FileInputStream(file);

    XSSFRow row;
    // Get the workbook instance for XLSX file
    XSSFWorkbook workbook = new XSSFWorkbook(fIP);
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
      XSSFSheet spreadsheet = workbook.getSheetAt(i);
      //            System.out.println(spreadsheet.getSheetName());
      if (!spreadsheet.getSheetName().equals("EAR SAI")) {
        continue;
      }
      StringBuilder sb = new StringBuilder();
      Iterator<Row> rowIterator = spreadsheet.iterator();
      while (rowIterator.hasNext()) {
        row = (XSSFRow) rowIterator.next();
        System.out.println(row.getRowNum() + " " + row.getRowStyle());
        if (row.getRowStyle() != null) {
          System.out.println(row.getRowNum() + " " + row.getRowStyle().getBorderBottom());
        }
        Iterator<org.apache.poi.ss.usermodel.Cell> cellIterator = row.cellIterator();
        if (row.getRowNum() < 4) {
          if (row.getRowNum() == 3) analyzeTable(row);
          continue;
        }
        while (cellIterator.hasNext()) {
          org.apache.poi.ss.usermodel.Cell cell = cellIterator.next();
          if (cell.getColumnIndex() == NAME_COLUMN) {
            if (!(sb.lastIndexOf("\n") == sb.length() - 1)) sb.append("\n");
          }
          if (cell.getColumnIndex() < VALUE_COLUMN) {
            sb.append(readCells(cell));
            continue;
          }
          if (cell.getColumnIndex() == VALUE_COLUMN) {
            sb.append(" -> " + readCells(cell));
            if (!cellIterator.hasNext()) {
              sb.append("\n");
            }
          }
          if (cell.getColumnIndex() == STATUS_COLUMN) {
            sb.append(" => " + readCells(cell));
            if (!cellIterator.hasNext()) {
              sb.append("\n");
            }
          }
          //                    if (!cellIterator.hasNext()) {
          //                        sb.append("\n");
          //                    }

        }

        //                while (cellIterator.hasNext()) {
        //                    Cell cell = cellIterator.next();
        //                    sb.append(readCells(cell));
        //                    if (cellIterator.hasNext()) {
        //
        //                        if (cell.getColumnIndex() == 5 && cell.getCellType() != 3) {
        //                            sb.append("\t->\t");
        //                            sb.append(readCells(cell));
        //                            if (!cellIterator.hasNext()) {
        //                                sb.append("\t=>\tstate\n");
        //                                continue;
        //                            }
        //                            cell = cellIterator.next();
        //                            if (cell.getCellType() != 3) {
        //                                sb.append("\t=>\t");
        //                                sb.append(readCells(cell));
        //                                sb.append("\n");
        //                            } else {
        //                                sb.append("\t=>\tstate\n");
        //                            }
        //                            continue;
        //                        }
        //                        sb.append(readCells(cell));
        //                    } else {
        //                        sb.append("\\");
        //                    }
        //                }
      }
      //                System.out.println();
      System.out.println(sb);
    }

    if (file.isFile() && file.exists()) {
      System.out.println("openworkbook.xlsx file open successfully.");
    } else {
      System.out.println("Error to open openworkbook.xlsx file.");
    }
  }
  /** @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */
  protected void doPost(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {
    // TODO Auto-generated method stub
    String filepath = null;
    String id = null,
        name = null,
        email = null,
        mobile = null,
        doj = null,
        password = null,
        man1 = null,
        man2 = null,
        check = "success";
    int level = 0, layer = 0;

    /* filepath=request.getParameter("file");
     */
    String message = "";
    // out.print("<html><body><h3>"+filepath+"</h3></body></html>");
    boolean isMultiPart = ServletFileUpload.isMultipartContent(request);
    HttpSession session = request.getSession();
    if (isMultiPart) {
      ServletFileUpload upload = new ServletFileUpload();

      try {
        FileItemIterator itr = upload.getItemIterator(request);
        while (itr.hasNext()) {
          FileItemStream item = itr.next();
          if (!item.isFormField()) {

            String path = getServletContext().getInitParameter("file-upload");
            request.setAttribute("path", path);

            if (FileUpload.processFile(path, item)) {
              filepath =
                  "C:\\Users\\shagayaraj\\Documents\\Tina\\webtechlab\\Ideation\\WebContent"
                      + "\\"
                      + item.getName();

              String[] ext = filepath.split("\\.");
              String ext1 = ext[0];
              String ext2 = ext[1];
              if (ext2.equals("xlsx")) {
                int rc = 0;
                FileInputStream fis = new FileInputStream(new File(filepath));

                XSSFWorkbook wb = new XSSFWorkbook(fis);
                XSSFSheet spreadsheet = wb.getSheetAt(0);

                // System.out.println(list);
                Iterator<Row> rowIterator = spreadsheet.iterator();
                while (rowIterator.hasNext()) {
                  check = "success";
                  row = (XSSFRow) rowIterator.next();
                  rc++;
                  Iterator<Cell> cellIterator = row.cellIterator();
                  while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    // out.print("<h3>"+cell.getCellType()+"</h3><br>");
                    switch (cell.getCellType()) {
                      case Cell.CELL_TYPE_NUMERIC:
                        if (cell.getColumnIndex() == 4) {
                          DataFormatter df = new DataFormatter();
                          if ((df.formatCellValue(cell))
                              .matches("^([0-9]{4})-([0-9]{2})-([0-9]{2})$"))
                            doj = df.formatCellValue(cell);
                          else {
                            message += "<p>Invalid Date format at row:" + rc + " and Column:5</p>";
                            check = "error";
                          }
                          // out.print( "<h3>"+cvd+"</h3><br>");
                        } else if (cell.getColumnIndex() == 7) {
                          cell.setCellType(Cell.CELL_TYPE_STRING);
                          if ((cell.getStringCellValue()).matches("[0-9]{10}"))
                            mobile = cell.getStringCellValue();
                          else {
                            message +=
                                "<p>Invalid Mobile Number format at row:"
                                    + rc
                                    + " and Column:8</p>";
                            check = "error";
                          }
                        } else if (cell.getColumnIndex() == 5) {
                          cell.setCellType(Cell.CELL_TYPE_STRING);
                          if ((cell.getStringCellValue()).matches("[0-9]+"))
                            level = Integer.parseInt(cell.getStringCellValue());
                          else {
                            message += "<p>Invalid Level at row:" + rc + " and Column:6</p>";
                            check = "error";
                          }
                        } else if (cell.getColumnIndex() == 6) {
                          cell.setCellType(Cell.CELL_TYPE_STRING);
                          if ((cell.getStringCellValue()).matches("[0-9]+"))
                            layer = Integer.parseInt(cell.getStringCellValue());
                          else {
                            message += "<p>Invalid Layer at row:" + rc + " and Column:7</p>";
                            check = "error";
                          }
                        }
                        break;
                      case Cell.CELL_TYPE_STRING:
                        if (cell.getColumnIndex() == 0) {
                          id = cell.getStringCellValue();
                          String test;
                          if (id.isEmpty()) test = "invalid";
                          else {
                            userdao userd = new userdao();
                            userbean user = userd.getUserbyId(id);
                            if (user == null) {
                              test = "valid";
                            } else test = "invalid";
                          }
                          if (test.equals("invalid")) {
                            message +=
                                "<p>User Id is either null or already exists at row:"
                                    + rc
                                    + " and Column:1</p>";
                            check = "error";
                          }
                        }
                        if (cell.getColumnIndex() == 8) {
                          man1 = cell.getStringCellValue();
                          String test;
                          if (man1.isEmpty()) test = "valid";
                          else {
                            userdao userd = new userdao();
                            userbean user = userd.getUserbyId(man1);
                            if (user == null) {
                              test = "invalid";
                            } else test = "valid";
                          }
                          if (test.equals("invalid")) {
                            message += "<p>Invalid Manager Id at row:" + rc + " and Column:9</p>";
                            check = "error";
                          }
                        }
                        if (cell.getColumnIndex() == 9) {
                          man2 = cell.getStringCellValue();
                          String test;
                          if (man2.isEmpty()) test = "valid";
                          else {
                            userdao userd = new userdao();
                            userbean user = userd.getUserbyId(man2);
                            if (user == null) {
                              test = "invalid";
                            } else test = "valid";
                          }
                          if (test.equals("invalid")) {
                            message += "<p>Invalid Manager Id at row:" + rc + " and Column:10</p>";
                            check = "error";
                          }
                        }

                        if (cell.getColumnIndex() == 1) {
                          if ((cell.getStringCellValue()).matches("^[a-zA-Z\\s]+"))
                            name = cell.getStringCellValue();
                          else {
                            message += "<p>Invalid name format at row:" + rc + " and Column:2</p>";
                            check = "error";
                          }
                        }

                        if (cell.getColumnIndex() == 2) {
                          if ((cell.getStringCellValue())
                              .matches("^\\w+([\\.-]?\\w+)*@\\w+([\\.-]?\\w+)*(\\.\\w{2,3})+$"))
                            email = cell.getStringCellValue();
                          else {
                            message += "<p>Invalid email format at row:" + rc + " and Column:3</p>";
                            check = "error";
                          }
                        }

                        if (cell.getColumnIndex() == 3) password = cell.getStringCellValue();

                        break;
                    }
                  }
                  if (check.equals("success")) {
                    java.sql.Date date_joined = java.sql.Date.valueOf(doj);
                    userdao userd = new userdao();
                    userd.setUser(
                        id,
                        name,
                        email,
                        password,
                        date_joined,
                        level,
                        layer,
                        0,
                        mobile,
                        man1,
                        man2,
                        false,
                        true);
                    user_roledao user_roled = new user_roledao();
                    user_roled.setUserRolebyUId(id, "3");
                  }
                }
                fis.close();
              } // if closed
              else if (ext2.equals("xls")) {
                int rc = 0;
                File file = new File(filepath);
                InputStream input = new BufferedInputStream(new FileInputStream(file));

                POIFSFileSystem fs = new POIFSFileSystem(input);
                HSSFWorkbook wb = new HSSFWorkbook(fs);
                HSSFSheet sheet = wb.getSheetAt(0);
                Iterator rows = sheet.rowIterator();
                while (rows.hasNext()) {
                  HSSFRow row = (HSSFRow) rows.next();
                  rc++;
                  check = "success";
                  Iterator cells = row.cellIterator();
                  while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell) cells.next();
                    if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                      if (cell.getColumnIndex() == 4) {
                        DataFormatter df = new DataFormatter();
                        if ((df.formatCellValue(cell))
                            .matches("^([0-9]{4})-([0-9]{2})-([0-9]{2})$"))
                          doj = df.formatCellValue(cell);
                        else {
                          message += "<p>Invalid Date format at row:" + rc + " and Column:5</p>";
                          check = "error";
                        }
                        // out.print( "<h3>"+cvd+"</h3><br>");
                      } else if (cell.getColumnIndex() == 7) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        if ((cell.getStringCellValue()).matches("[0-9]{10}"))
                          mobile = cell.getStringCellValue();
                        else {
                          message +=
                              "<p>Invalid Mobile Number format at row:" + rc + " and Column:8</p>";
                          check = "error";
                        }
                      } else if (cell.getColumnIndex() == 5) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        if ((cell.getStringCellValue()).matches("[0-9]+"))
                          level = Integer.parseInt(cell.getStringCellValue());
                        else {
                          message += "<p>Invalid Level at row:" + rc + " and Column:6</p>";
                          check = "error";
                        }
                      } else if (cell.getColumnIndex() == 6) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        if ((cell.getStringCellValue()).matches("[0-9]+"))
                          layer = Integer.parseInt(cell.getStringCellValue());
                        else {
                          message += "<p>Invalid Layer at row:" + rc + " and Column:7</p>";
                          check = "error";
                        }
                      }

                    } else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
                      if (cell.getColumnIndex() == 0) {
                        id = cell.getStringCellValue();
                        String test;
                        if (id.isEmpty()) test = "invalid";
                        else {
                          userdao userd = new userdao();
                          userbean user = userd.getUserbyId(id);
                          if (user == null) {
                            test = "valid";
                          } else test = "invalid";
                        }
                        if (test.equals("invalid")) {
                          message +=
                              "<p>User Id is either null or already exists at row:"
                                  + rc
                                  + " and Column:1</p>";
                          check = "error";
                        }
                      }
                      if (cell.getColumnIndex() == 8) {
                        man1 = cell.getStringCellValue();
                        String test;
                        if (man1.isEmpty()) test = "valid";
                        else {
                          userdao userd = new userdao();
                          userbean user = userd.getUserbyId(man1);
                          if (user == null) {
                            test = "invalid";
                          } else test = "valid";
                        }
                        if (test.equals("invalid")) {
                          message += "<p>Invalid Manager Id at row:" + rc + " and Column:9</p>";
                          check = "error";
                        }
                      }
                      if (cell.getColumnIndex() == 9) {
                        man2 = cell.getStringCellValue();
                        String test;
                        if (man2.isEmpty()) test = "valid";
                        else {
                          userdao userd = new userdao();
                          userbean user = userd.getUserbyId(man2);
                          if (user == null) {
                            test = "invalid";
                          } else test = "valid";
                        }
                        if (test.equals("invalid")) {
                          message += "<p>Invalid Manager Id at row:" + rc + " and Column:10</p>";
                          check = "error";
                        }
                      }

                      if (cell.getColumnIndex() == 1) {
                        if ((cell.getStringCellValue()).matches("^[a-zA-Z\\s]+"))
                          name = cell.getStringCellValue();
                        else {
                          message += "<p>Invalid name format at row:" + rc + " and Column:2</p>";
                          check = "error";
                        }
                      }

                      if (cell.getColumnIndex() == 2) {
                        if ((cell.getStringCellValue())
                            .matches("^\\w+([\\.-]?\\w+)*@\\w+([\\.-]?\\w+)*(\\.\\w{2,3})+$"))
                          email = cell.getStringCellValue();
                        else {
                          message += "<p>Invalid email format at row:" + rc + " and Column:3</p>";
                          check = "error";
                        }
                      }

                      if (cell.getColumnIndex() == 3) password = cell.getStringCellValue();
                    }
                  }
                  if (check.equals("success")) {
                    java.sql.Date date_joined = java.sql.Date.valueOf(doj);
                    userdao userd = new userdao();
                    userd.setUser(
                        id,
                        name,
                        email,
                        password,
                        date_joined,
                        level,
                        layer,
                        0,
                        mobile,
                        man1,
                        man2,
                        false,
                        true);
                    user_roledao user_roled = new user_roledao();
                    user_roled.setUserRolebyUId(id, "3");
                  }
                }
              } else {
                message += "Unsupported File format!Choose files of format .xls or .xlsx only!";
              }
              if (message == null) {
                message = "<p>Users added successfully</p>";
              }
            } else {
              message += "File upload unsuccessful";
            }
          }
        }

        request.setAttribute("errormsg", message);
        RequestDispatcher rd = request.getRequestDispatcher("exceltodbmessage.jsp");
        rd.forward(request, response);
      } catch (FileNotFoundException e) {
        message += "The given file cannot be found!";
        request.setAttribute("errormsg", message);
        RequestDispatcher rd = request.getRequestDispatcher("exceltodbmessage.jsp");
        rd.forward(request, response);
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
  }
  public static void main(String[] args) {

    int i, j = 0, col = 0, row1 = 0, col1 = 0, cluster;

    try {
      int itr = 0;
      ArrayList<Double> al = new ArrayList<Double>();
      ArrayList<Integer> index = new ArrayList<Integer>();
      ArrayList<Double> al4 = new ArrayList<Double>();

      FileInputStream fileInputStream = new FileInputStream(new File("E:\\NewExcelFile.xls"));
      XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
      XSSFSheet worksheet = workbook.getSheet("FirstSheet");
      Scanner in = new Scanner(System.in);
      Iterator<Row> rowIterator = worksheet.iterator();

      // FOR GETTING ROWS AND COLUMNS FROM A FILE
      while (rowIterator.hasNext()) {
        row1++;
        Row row = rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
          Cell cell = cellIterator.next();
          double a1Val = cell.getNumericCellValue();
          al.add(a1Val);
          col++;
        }
        col1 = col;
        col = 0;
      }
      al.clear();

      // for getting the ELEMENTS IN MATRIX B
      double b[][] = new double[row1][col1];
      Iterator<Row> rowIterator1 = worksheet.iterator();
      i = 0;
      j = 0;
      while (rowIterator1.hasNext()) {
        Row row = rowIterator1.next();
        Iterator<Cell> cellIterator1 = row.cellIterator();
        j = 0;
        while (cellIterator1.hasNext()) {
          Cell cell = cellIterator1.next();
          double a1Val = cell.getNumericCellValue();
          b[i][j] = a1Val;
          j++;
        }
        i++;
      }
      //	double result[][]=new double[row1][col+1];
      // PRINTING ELEMENTS OF 2-D ARRAY DATA
      System.out.println("Printing The Content Of The File...\n");
      for (i = 0; i < row1; i++) {
        for (j = 0; j < col1; j++) {
          System.out.print(b[i][j] + "  ");
        }
        System.out.print("\n");
      }

      System.out.println("Enter The Number Of Clusters  -  ");
      cluster = in.nextInt();

      // Randomly Select
      int z = 0;

      Random rand = new Random();
      int indexArr[] = new int[cluster];
      while (z != cluster) {
        int randIndex = rand.nextInt(row1);
        if (!index.contains(randIndex)) {
          index.add(randIndex);
          indexArr[z] = randIndex;
          z++;
        }
      }

      // First Cenroid Initialization
      double centroid[][] = new double[cluster][col1];
      for (i = 0; i < cluster; i++) {
        for (j = 0; j < col1; j++) {
          centroid[i][j] = b[indexArr[i]][j];
        }
      }
      System.out.println("\n\nPrinting Initial Centroids at iteration " + itr);
      for (i = 0; i < cluster; i++) {
        for (j = 0; j < col1; j++) {
          System.out.print(" " + centroid[i][j]);
        }
        System.out.println();
      }

      // distance calculation
      double dist_mat[][] = new double[row1][cluster];
      int k;
      double dist = 0, res = 0;
      for (i = 0; i < cluster; i++) {
        for (j = 0; j < row1; j++) {
          for (k = 0; k < col1; k++) {
            dist = dist + Math.pow(centroid[i][k] - b[j][k], 2);
          }
          res = Math.round(Math.sqrt(dist) * 100.0) / 100.0;
          al.add(res);
          dist = 0;
        }
      }
      k = 0;
      for (j = 0; j < cluster; j++) {
        for (i = 0; i < row1; i++) {
          dist_mat[i][j] = al.get(k++);
        }
      }
      System.out.println("Displaying Initial Distance Matrix for iteration " + itr + "  is");
      for (i = 0; i < row1; i++) {
        for (j = 0; j < cluster; j++) {
          System.out.print(+dist_mat[i][j] + "\t\t");
        }
        System.out.println();
      }

      // Finding the minimum frm distance matrix..
      double dist_cost = 0;
      int mat_pos[] = new int[row1];
      for (i = 0; i < row1; i++) {
        double min = dist_mat[i][0];
        for (j = 0; j < cluster; j++) {
          if (dist_mat[i][j] < min) {
            min = dist_mat[i][j];

            mat_pos[i] = j;
          }
        }

        // System.out.println("The minimum value in row"+i+" is "+min);
        dist_cost += min;
      }
      System.out.print(
          "\n\nThe Initial Total cost calculated for iteration " + itr + " is \n" + dist_cost);
      // Printing minimum position array..
      for (i = 0; i < row1; i++) {
        System.out.print(" " + mat_pos[i]);
      }
      // Saving the mat_pos and totalcost in temp arry mat_pos1 totalcost1
      int mat_pos1[] = new int[row1];
      for (i = 0; i < row1; i++) {
        mat_pos1[i] = mat_pos[i];
      }
      double total_cost = dist_cost;

      int randIndex = rand.nextInt(row1);
      // BIG LOOP
      itr = 0;
      int s = 0;
      while (s < (row1 - cluster)) {

        // System.out.println("S = :"+s);
        randIndex = rand.nextInt(row1);
        if (!index.contains(randIndex)) {
          index.add(randIndex);
          indexArr[cluster - 1] = randIndex;

          for (i = 0; i < cluster; i++) {
            for (j = 0; j < col1; j++) {
              centroid[i][j] = b[indexArr[i]][j];
            }
          }
          itr++;
          System.out.println(
              "\n\n---------------------------------------------------------------------------");
          System.out.println("\nPrinting Centroids In  Iteration... " + itr);
          for (i = 0; i < cluster; i++) {
            for (j = 0; j < col1; j++) {
              System.out.print(" " + centroid[i][j]);
            }
            System.out.println();
          }

          al.clear();
          dist = 0;
          res = 0;
          for (i = 0; i < cluster; i++) {
            for (j = 0; j < row1; j++) {
              for (k = 0; k < col1; k++) {
                dist = dist + Math.pow(centroid[i][k] - b[j][k], 2);
              }
              res = Math.round(Math.sqrt(dist) * 100.0) / 100.0;
              al.add(res);
              dist = 0;
            }
          }
          k = 0;
          for (j = 0; j < cluster; j++) {
            for (i = 0; i < row1; i++) {
              dist_mat[i][j] = al.get(k++);
            }
          }
          System.out.println("\n\nDisplaying Distance Matrix In Iteration..." + itr + "\n");
          for (i = 0; i < row1; i++) {
            for (j = 0; j < cluster; j++) {
              System.out.print(+dist_mat[i][j] + "\t\t");
            }
            System.out.println();
          }

          // Finding the minimum frm distance matrix..
          dist_cost = 0;

          for (i = 0; i < row1; i++) {
            double min = dist_mat[i][0];
            for (j = 0; j < cluster; j++) {
              if (dist_mat[i][j] < min) {
                min = dist_mat[i][j];

                mat_pos[i] = j;
              }
            }

            // System.out.println("The minimum value in row"+i+" is "+min);
            dist_cost += min;
          }
          System.out.print("\n\nThe Total Cost In Iteration " + itr + " Is " + dist_cost + "\n");

          // Printing minimum position array..
          System.out.println("\n\nPosition Of Objects In Cluster In Iteration " + itr + "\n");
          for (i = 0; i < row1; i++) {
            System.out.print(" " + mat_pos[i]);
          }
          if (dist_cost < total_cost) {
            total_cost = dist_cost;
            for (i = 0; i < row1; i++) {
              mat_pos1[i] = mat_pos[i];
            }
          }
          s++;
        }
      }
      System.out.println(
          "\n\n---------------------------------------------------------------------------");
      System.out.println("\n..........FINAL RESULT.............");

      System.out.println("Mat Position....");
      for (i = 0; i < row1; i++) {
        System.out.print(" " + mat_pos1[i]);
      }
      System.out.println("\n\nValue Of Minimum Cost Is " + total_cost);

      double result[][] = new double[row1][col1 + 1];
      for (i = 0; i < row1; i++) {
        for (j = 0; j < col1; j++) {
          result[i][j] = b[i][j];
        }
        result[i][col1] = mat_pos1[i];
      }

      // PRINTING THE FINAL RESULT
      System.out.println("\n\nTHE FINAL RESULT WHICH IS TO BE SHOWN IS");

      for (i = 0; i < cluster; i++) {
        for (j = 0; j < row1; j++) {
          if (mat_pos1[j] == i) {
            for (k = 0; k < col1; k++) {
              al4.add(b[j][k]);
            }
          }
        }
        // System.out.println("AL4 SIZE : "+al4.size());

        System.out.println("the value of cluster " + (i + 1) + " is ");
        System.out.print("{");

        for (k = 0; k < al4.size(); k++) {
          System.out.print("(");
          for (j = 0; j < col1; j++) {

            System.out.print(al4.get(0) + ",");
            al4.remove(0);
          }
          System.out.print(")");
        }
        System.out.println("}");
      }

      /*	System.out.println("\n\nTHE FINAL RESULT WHICH IS TO BE SHOWN IS");

      				for(i=0;i<row1;i++)
      				{
      					for(j=0;j<col1;j++)
      					{
      						System.out.print((result[i][j])+"\t");
      					}
      					System.out.print((int)(result[i][j]));
      					System.out.println();

      				}
      */
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    }
  }
  /**
   * @param planilha objeto do tipo Planilha que contém todos os dados necessários para facilitar a
   *     operação na planilha
   */
  public void processaPlanilha(Planilha planilha) throws NegocioException {
    List<Desembolso> dadosParaSalvar = new ArrayList();
    int linha = 0;
    Double d;
    try {

      // cria um workbook = planilha toda com todas as abas
      XSSFWorkbook workbook = new XSSFWorkbook(planilha.getFile());

      // recuperamos apenas a aba mandada
      XSSFSheet sheet = workbook.getSheetAt(planilha.getNumeroDaPlanilha() - 1);

      // retorna todas as linhas da planilha selecioada
      Iterator<Row> rowIterator = sheet.iterator();

      // varre todas as linhas da planilha selecionada
      while (rowIterator.hasNext() && linha < planilha.getUltimaLinha()) {
        linha++;

        // recebe cada linha da planilha
        Row row = rowIterator.next();

        // andar as linhas que serão ignoradas no início
        if (row.getRowNum() < planilha.getUltimaLinha()) {
          continue;
        }

        // pegamos todas as celulas desta linha
        Iterator<Cell> cellIterator = row.iterator();

        // responsavel por definir qual coluna esta sendo trabalhada no intante
        int coluna = 1;

        Cell cell;

        Desembolso desembolso = new Desembolso();
        // varremos todas as celulas da linha atual
        while (cellIterator.hasNext()) {
          // criamos uma celula
          cell = cellIterator.next();

          // TODO O CÓDIGO DE PERSISTENCIA AQUI!!

          switch (coluna) {
            case ColunasDesembolso.GRUPO:
              desembolso.setGrupo(cell.getStringCellValue());
              break;
            case ColunasDesembolso.PREFIXO_SUPER_INTENDENCIA:
              cell.setCellType(Cell.CELL_TYPE_STRING);
              desembolso.setPrefixoSuperintendencia(cell.getStringCellValue());
              break;
            case ColunasDesembolso.NOME_SUPER_INTENDENCIA:
              cell.setCellType(Cell.CELL_TYPE_STRING);
              desembolso.setNomeSuperintendencia(cell.getStringCellValue());
              break;
            case ColunasDesembolso.PREFIXO_REGIONAL:
              cell.setCellType(Cell.CELL_TYPE_STRING);
              desembolso.setPrefixoRegional(cell.getStringCellValue());
              break;
            case ColunasDesembolso.NOME_AGENCIA:
              cell.setCellType(Cell.CELL_TYPE_STRING);
              desembolso.setNomeAgencia(cell.getStringCellValue());
              break;
            case ColunasDesembolso.ORCAMENTO_PROPOSTO_ACUMULADO:
              desembolso.setOrcamentoPropostoAcumulado(
                  this.doubleToBigDecimal(cell.getNumericCellValue()));
              break;
            case ColunasDesembolso.REALIZADO_ATUAL:
              desembolso.setRealizadoAtual(this.doubleToBigDecimal(cell.getNumericCellValue()));
              break;
            case ColunasDesembolso.PERCENTUAL_ATINGIMENTO_UM:
              desembolso.setPercentualAtingimentoUm(
                  this.doubleToBigDecimal(cell.getNumericCellValue()));
              break;
            case ColunasDesembolso.REALIZADO_D_MENOS_UM:
              desembolso.setRealizadoDmenosUm(this.doubleToBigDecimal(cell.getNumericCellValue()));
              break;
            case ColunasDesembolso.NECESSIDADE_DIA_MENOS_UM:
              cell.setCellType(Cell.CELL_TYPE_NUMERIC);
              desembolso.setNecessidadeDiaDmenosUm(
                  this.doubleToBigDecimal(cell.getNumericCellValue()));
              break;
            case ColunasDesembolso.META_CONTATOS_ACUMULADA:
              cell.setCellType(Cell.CELL_TYPE_NUMERIC);
              desembolso.setMetaContatosAcumulada(
                  this.doubleToBigDecimal(cell.getNumericCellValue()));
              break;
            case ColunasDesembolso.REALIZADO_CONTATOS_MES:
              cell.setCellType(cell.CELL_TYPE_NUMERIC);
              desembolso.setRealizadoContatosMes(
                  this.doubleToBigDecimal(cell.getNumericCellValue()));
              break;
            case ColunasDesembolso.PERCENTUAL_ATINGIMENTO_CONTATOS:
              cell.setCellType(cell.CELL_TYPE_NUMERIC);
              desembolso.setPercentualAtingimentoContatos(
                  this.doubleToBigDecimal(cell.getNumericCellValue()));
              break;
            case ColunasDesembolso.CONTATOS_D_MENOS_UM:
              cell.setCellType(cell.CELL_TYPE_NUMERIC);
              desembolso.setContatosDmenosUm(this.doubleToBigDecimal(cell.getNumericCellValue()));
              break;
            case ColunasDesembolso.CONTATOS_D_MENOS_DOIS:
              cell.setCellType(cell.CELL_TYPE_NUMERIC);
              desembolso.setContatosDmenosDois(this.doubleToBigDecimal(cell.getNumericCellValue()));
              break;
            case ColunasDesembolso.PREFIXO_REPETE:
              cell.setCellType(Cell.CELL_TYPE_STRING);
              desembolso.setPrefixoRepete(cell.getStringCellValue());
              break;
            case ColunasDesembolso.AGENCIA_REPETE:
              cell.setCellType(Cell.CELL_TYPE_STRING);
              desembolso.setAgenciaRepete(cell.getStringCellValue());
              break;
            case ColunasDesembolso.CODIDGO_CARTEIRA:
              cell.setCellType(Cell.CELL_TYPE_STRING);
              desembolso.setCodigoCarteira(cell.getStringCellValue());
              break;
            case ColunasDesembolso.CARTEIRA:
              cell.setCellType(Cell.CELL_TYPE_STRING);
              desembolso.setCarteira(cell.getStringCellValue());
              break;
            case ColunasDesembolso.ORCAMENTO_PROPORCIONAL_ACUMULADO_DOIS:
              cell.setCellType(cell.CELL_TYPE_NUMERIC);
              desembolso.setOrcamentoProporcionalAcumuladoDois(
                  this.doubleToBigDecimal(cell.getNumericCellValue()));
              break;
            case ColunasDesembolso.REALIZADO_ATUAL_DOIS:
              cell.setCellType(cell.CELL_TYPE_NUMERIC);
              desembolso.setRealizadoAtualDois(this.doubleToBigDecimal(cell.getNumericCellValue()));
              break;
            case ColunasDesembolso.PERCENTUAL_ATINGIMENTO_DOIS:
              cell.setCellType(cell.CELL_TYPE_NUMERIC);
              desembolso.setPercentualAgintimentoDois(
                  this.doubleToBigDecimal(cell.getNumericCellValue()));
              break;
            case ColunasDesembolso.META_CONTATOS_ACUMULADA_DOIS:
              cell.setCellType(cell.CELL_TYPE_NUMERIC);
              desembolso.setMetaContatosAcumuladaDois(
                  this.doubleToBigDecimal(cell.getNumericCellValue()));
              break;
            case ColunasDesembolso.REALIZADO_CONTATOS_MES_DOIS:
              cell.setCellType(cell.CELL_TYPE_NUMERIC);
              desembolso.setRealizadoContatosMesDois(
                  this.doubleToBigDecimal(cell.getNumericCellValue()));
              break;
            case ColunasDesembolso.PERCENTUAL_ATINGIMENTO_CONTATOS_DOIS:
              cell.setCellType(cell.CELL_TYPE_NUMERIC);
              desembolso.setPercentualAtingimentoContatosDois(
                  this.doubleToBigDecimal(cell.getNumericCellValue()));
              break;
          }

          System.out.println("valor = " + cell.toString());

          coluna++;
        }
        dadosParaSalvar.add(desembolso);
      }
      this.salvar(dadosParaSalvar);

    } catch (FileNotFoundException ex) {
      throw new NegocioException("Arquivo com Erro Tente novamente!!");
    } catch (IOException ex) {
      throw new NegocioException("Arquivo com Erro Tente novamente!!");
    }
  }