31 jan 2014
(Parte 2) Importando planilhas do Excel para o MySQL usando PHP com PDO
Vamos dar continuidade na segunda parte do artigo para importação de planilhas do excel para o MySQL usando PHP, no primeiro artigo foi demonstrado a classe abstrata que vai servir como base para classe especifica que será exibida neste artigo.
Abaixo segue o script das 2 tabelas (TAB_IMPORTACAO, TAB_GASTO) para o banco de dados DB_BLOG:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE DATABASE DB_BLOG; CREATE TABLE `TAB_IMPORTACAO` ( `IMP_ID` int(11) NOT NULL AUTO_INCREMENT, `IMP_USUARIO` varchar(50) NOT NULL, `IMP_TIPO_IMPORTACAO` varchar(30) NOT NULL, `IMP_STATUS` varchar(20) NOT NULL, `IMP_DATA` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `IMP_ERRO` varchar(500) DEFAULT NULL, `IMP_REGISTRO` int(11) DEFAULT NULL, PRIMARY KEY (`IMP_ID`) ) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8; CREATE TABLE `TAB_GASTO` ( `GAS_ID` int(11) NOT NULL AUTO_INCREMENT, `GAS_DEPARTAMENTO` varchar(45) DEFAULT NULL, `GAS_VALOR` varchar(45) DEFAULT NULL, `GAS_ANO` char(4) DEFAULT NULL, `GAS_STATUS` char(1) DEFAULT NULL, PRIMARY KEY (`GAS_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
Para esse exemplo criei uma planilha com o nome gastos.xls com os mesmos campos da tabela TAB_GASTO e na mesma disposição(ordem). Gravei na pasta planilhas que está no diretório raiz da aplicação, segue imagem da planilha.
Abaixo segue a classe específica para importar os dados da tabela planilha gastos.xls, essa classe descende da classe importacaoBase.class.php postada na primeira parte desse artigo. Nesse caso estamos reaproveitando todos os métodos e atributos declarados como private e public na classe importacaoBase.class.php além disso serão implementados os métodos abstratos na classe específica.
importacaoGasto.class.php
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 |
<?php session_start(); header('Content-type: text/html; charset=utf-8'); require_once "importacaoBase.class.php"; class importacaoGasto extends importacaoBase{ const qtde_colunas = 4; /* * Método construtor executando construtor da classe pai */ public function __construct($conexao, $file, $tabela){ parent::__construct($conexao, $file, $tabela); if ($this->getColunas() != self::qtde_colunas): echo 'Quantidades de colunas da planilha está incorreta!'; exit(); endif; } /* * Método que retorna dados da planilha * @return = Linhas e colunas da planilha */ public function getDados(){ $retorno = ''; $retorno .= 'Total de linhas: ' . $this->getLinhas() . ""; $retorno .= '*****************************************************************************' . ""; for ($linha = 1; $linha <= $this->dataSheet->rowcount(); $linha++) { $retorno .= $this->prepareString($this->dataSheet->val($linha, 1)) . " - "; $retorno .= trim($this->dataSheet->val($linha, 2)) . " - "; $retorno .= trim($this->dataSheet->val($linha, 3)) . " - "; $retorno .= trim($this->dataSheet->val($linha, 4)) . ""; $retorno .= '*****************************************************************************' . ""; } return $retorno; } /* * Método para inserção dos dados no banco * @return = TRUE se não houver erros e FALSE caso ocorre erros */ public function insertDados(){ try{ if ($this->dataSheet && $this->limpaTabela()): $sql = "INSERT INTO TAB_GASTO (GAS_DEPARTAMENTO, GAS_VALOR, GAS_ANO, GAS_STATUS) VALUES (?, ?, ?, ?)"; $stm = $this->pdo->prepare($sql); for ($linha = 2; $linha <= $this->dataSheet->rowcount(); $linha++): $departamento = $this->prepareString($this->dataSheet->val($linha, 1)); $valor = str_replace(',', '', trim($this->dataSheet->val($linha, 2))); $ano = $this->prepareString($this->dataSheet->val($linha, 3)); $status = $this->prepareString($this->dataSheet->val($linha, 4)); $stm->bindValue(1, $departamento); $stm->bindValue(2, $valor); $stm->bindValue(3, $ano); $stm->bindValue(4, $status); $stm->execute(); endfor; endif; //unlink($this->name_file); return $this->verificaOp('PLANILHA DE GASTOS'); } catch (PDOException $e){ $this->msgErro .= "Script: " . $e->getFile() . " - Linha: " . $e->getLine() . " - Mensagem: " . $e->getMessage(); $this->verificaOp('PLANILHA DE GASTOS'); } } /* * Método que confronta as quantidades de linhas inseridas * e a quantidade de linhas na planilha * @param $tabela = Nome da tabela para contar os registros */ protected function verificaOp($tipo=NULL){ try{ $sql = "SELECT COUNT(*) AS CONTADOR FROM " . $this->tabela; $stm = $this->pdo->prepare($sql); $stm->execute(); $reg = $stm->fetch(PDO::FETCH_OBJ); } catch (PDOException $e){ $this->msgErro .= "Script: " . $e->getFile() . " - Linha: " . $e->getLine() . " - Mensagem: " . $e->getMessage(); } if($this->getLinhas() == ($reg->CONTADOR + 1)): $this->insertLog('William', 'SUCESSO', $tipo, $reg->CONTADOR); return TRUE; else: $this->insertLog('William', 'ERRO', $tipo); return FALSE; endif; } } |
Na classe acima existe uma constante $qtde_colunas, ela deve guardar a quantidade de colunas existente na planilha gastos.xls para que seja comparado esse valor com a quantidade de colunas retornada pelo método getColunas(). Essa comparação é importante para não haver discrepância no momento da importação, por isso essa verificação é feita no construtor da classe e se houver erro é finalizado o script com uma mensagem e o comando exit.
Além do construtor existem mais 3 métodos nessa classe:
- getDados(): Método serve para exibir os dados da planilha no navegador, essa prática auxilia para visualizar a formatação dos dados que serão inseridos na tabela, principalmente valores monetários.
- insertDados(): Método que realmente inseri os dados no banco de dados através de um loop, observem que antes de inserir limpo totalmente a tabela chamando o método $this->limpaTabela(), caso não seja necessário pode ser retirar esse método. Outro ponto importante é na preparação da instrução SQL, notem que a mesma é preparada apenas uma vez e posteriormente os valores são passados via parâmetro dentro do loop, isso aumenta a performance do script. Imaginem planilhas com mais de 1000 registros preparando instruções SQL na mesma quantidade.
- verificaOp(): Método tem a função de verificar se quantidade de registros inseridas é a mesma que a quantidade de linhas na planilha, após a verificações esse método chama o método insertLog() para inserir os dados de SUCESSO ou ERRO na tabela TAB_IMPORTACAO, para esse exemplo estou passando como parâmetro $user o meu nome, mas esse valor pode vir de uma $_SESSION contendo o usuário logado. Um detalhe importante é que a tabela TAB_IMPORTACAO tem utilidade para o programador ou analista de sistema da empresa, pois as mensagens de erro que serão gravadas não são das mais bonitas e intuitivas.
Agora vamos aos testes, inicialmente vamos exibir os dados da planilha no navegador.
index.php
|
1 2 3 4 5 6 7 8 9 10 11 12 |
<?php header('Content-type: text/html; charset=utf-8'); // Require nos scripts require_once "conexao.php"; require_once "importacaoGasto.class.php"; // Instância um objeto importacaoGasto passando os parâmetros necessários $importGasto = new importacaoGasto(Conexao::getInstance(), 'planilha/gastos.xls', 'TAB_GASTO'); // Chama o método getDados() e imprime o retorno echo $importGasto->getDados(); |
Resultado no navegador:
Agora vamos testar a inserção dos dados no banco de dados chamando o método insertDados():
index.php
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
<?php header('Content-type: text/html; charset=utf-8'); // Require nos scripts require_once "conexao.php"; require_once "importacaoGasto.class.php"; // Instância um objeto importacaoGasto passando os parâmetros necessários $importGasto = new importacaoGasto(Conexao::getInstance(), 'planilha/gastos.xls', 'TAB_GASTO'); // Chama o método insertDados() e atribui o retorno $retorno = $importGasto->insertDados(); // Verifica o retorno e dispara a mensagem if($retorno == TRUE): echo "<script>alert('Importação realizada com sucesso!')</script>"; else: echo "<script>alert('Importação não foi realizada!')</script>"; endif; |
Mensagem de retorno confirmando o sucesso da importação:
Como a mensagem indica sucesso na importação, segue abaixo imagem do dados na tabela TAB_GASTO:
Para finalizar segue imagem da tabela TAB_IMPORTACAO, para ilustrar melhor essa tabela contém registros de importações anteriores:
Bom pessoal chegamos ao final dessa série de 2 artigos, espero que os exemplos compartilhados aqui possam ser de grande valia. Essas classes podem ser customizadas e infinitamente melhoradas, elas foram construídas de acordo com a necessidade do meu cliente mas cada caso é um caso.
Até a próxima …
link: 1º Parte
