OQueFax?.java

private boolean extrairDados(HSSFSheet worksheet, String tabela,
			Integer tipoInsercao, StringBuffer sbLog, String fileName) {

		StringBuffer queryInsert = new StringBuffer();
		StringBuffer queryUpdate = new StringBuffer();
		StringBuffer queryFinal = new StringBuffer();

		List<String> camposUpdate = new ArrayList<String>();

		List<String> queriesPorLinha = new ArrayList<String>();
		List queriesGeral = new ArrayList();

		Integer totalFields = 0;

		// Se a op��o for de excluir a tabela, a primeira query incluida �
		// a de truncate
		if (tipoInsercao == 3) {
			queryFinal.append("Truncate table ");
			queryFinal.append(tabela);
			queriesPorLinha.add(queryFinal.toString());
			queriesGeral.add(queriesPorLinha);
		}

		try {
			for (Row row : worksheet) {

				if (row.getRowNum() == 0) {

					// Prepara texto b�sico para query de Update
					queryUpdate.append("Update ");
					queryUpdate.append(tabela);
					queryUpdate.append(" set ");

					// Prepara texto b�sico para query de Insert
					queryInsert.append("Insert into ");
					queryInsert.append(tabela);
					queryInsert.append(" (");

					// Define a quantidade de campos por linha serao lidos
					totalFields = row.getPhysicalNumberOfCells();

					for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {

						row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
						queryInsert.append(row.getCell(i).getStringCellValue());

						if ((i + 1) < row.getPhysicalNumberOfCells())
							queryInsert.append(",");

						camposUpdate.add(row.getCell(i).getStringCellValue());
					}

					queryInsert.append(") values ");

				}

				if (row.getRowNum() >= 1) {

					queriesPorLinha = new ArrayList<String>();

					// Gera query de Insert
					queryFinal = new StringBuffer();

					queryFinal.append(queryInsert.toString());
					queryFinal.append("(");

					for (int i = 0; i < totalFields; i++) {

						if (row.getCell(i) == null 	|| row.getCell(i).toString().trim().equals("")) {
							queryFinal.append("null");
						} else if (row.getCell(i).getCellType() == Cell.CELL_TYPE_STRING) {
							row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
							queryFinal.append("'");
							queryFinal.append(String.valueOf(row.getCell(i)
									.getStringCellValue().replaceAll("'", "")));
							queryFinal.append("'");
						} else if (DateUtil.isCellDateFormatted(row.getCell(i))) {
							queryFinal.append("'");
							queryFinal.append(String.valueOf(row.getCell(i)
									.getDateCellValue()));
							queryFinal.append("'");
						} else {
							row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
							queryFinal.append("'");
							queryFinal.append(String.valueOf(row.getCell(i)
									.getStringCellValue().replaceAll("'", "")));
							queryFinal.append("'");
						}

						if ((i + 1) < totalFields)
							queryFinal.append(",");
					}

					queryFinal.append(")");
					queriesPorLinha.add(queryFinal.toString());

					// Gera query para teste de registro em banco
					queryFinal = new StringBuffer();

					queryFinal.append("Select * from ");
					queryFinal.append(tabela);
					queryFinal.append(" where ");
					queryFinal.append(camposUpdate.get(0));
					queryFinal.append(" = ");

					row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
					queryFinal.append("'");
					queryFinal.append(String.valueOf(row.getCell(0)
							.getStringCellValue()));
					queryFinal.append("'");
					queriesPorLinha.add(queryFinal.toString());

					// Gera query de Update
					queryFinal = new StringBuffer();

					queryFinal.append(queryUpdate.toString());

					for (int i = 0; i < totalFields; i++) {

						queryFinal.append(camposUpdate.get(i));
						queryFinal.append(" = ");

						if (row.getCell(i) == null 	|| row.getCell(i).toString().trim().equals("")) {
							queryFinal.append("null");
						} else if (row.getCell(i).getCellType() == Cell.CELL_TYPE_STRING) {
							row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
							queryFinal.append("'");
							queryFinal.append(String.valueOf(row.getCell(i)
									.getStringCellValue().replaceAll("'", "")));
							queryFinal.append("'");
						} else if (DateUtil.isCellDateFormatted(row.getCell(i))) {
							queryFinal.append("'");
							queryFinal.append(String.valueOf(row.getCell(i)
									.getDateCellValue()));
							queryFinal.append("'");
						} else {
							row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
							queryFinal.append("'");
							queryFinal.append(String.valueOf(row.getCell(i)
									.getStringCellValue().replaceAll("'", "")));
							queryFinal.append("'");
						}

						if ((i + 1) < totalFields)
							queryFinal.append(",");
					}

					queryFinal.append(" where ");
					queryFinal.append(camposUpdate.get(0));
					queryFinal.append(" = ");
					row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
					queryFinal.append("'");
					queryFinal.append(String.valueOf(row.getCell(0)
							.getStringCellValue()));
					queryFinal.append("'");

					queriesPorLinha.add(queryFinal.toString());

					queriesGeral.add(queriesPorLinha);
				}
			}

			if (tipoInsercao == 3) {
				sbLog.append("Excluindo dados da tabela ");
				sbLog.append(tabela);
				sbLog.append(System.getProperty("line.separator"));
			}

			tabelaSistemaDao.execQuery(sbLog, queriesGeral, tipoInsercao);
			FacesMessage msg = new FacesMessage("Succesful", "Arquivo "
					+ fileName + " carregado com sucesso");
			FacesContext.getCurrentInstance().addMessage(null, msg);
			return true;

		} catch (Exception e) {

			addMessage(FacesMessage.SEVERITY_ERROR,
					"Ocorreu um erro ao carregar o arquivo " + fileName);

			if (e.toString().contains("NullPointerException")) {
				sbLog.append("Falha: Houve a tentativa de ler uma celula vazia");
				sbLog.append(System.getProperty("line.separator"));
			} else {
				sbLog.append(e.getMessage());
				sbLog.append(System.getProperty("line.separator"));
			}

			sbLog.append(System.getProperty("line.separator"));
			sbLog.append("Todas as alterações serão desfeitas");
			sbLog.append(System.getProperty("line.separator"));

			return false;
		}

	}