30 jan 2014
(Parte 1) Importando planilhas do Excel para o MySQL usando PHP com PDO
Olá pessoal, hoje vamos dar sequência na série de artigos sobre PDO mas com um exemplo prático e tenho certeza que muito útil, pelo menos para mim foi. Esse artigo será dividido em 2 partes, na primeira parte será passado uma breve introdução do objetivo, indicação de onde baixar a classe excel_reader2.php e como construir uma classe base abstrata para as importações. Na segunda parte será exibido o script do banco de dados, planilha do excel a ser importada e será construída uma classe específica para essa importação. Algum tempo atrás um cliente me solicitou uma funcionalidade de importar dados de uma planilha no Excel para um sistema em PHP com MySQL, essa planilha continha mais de 3.000 registros e poderia ser importada várias vezes ao dia, sendo que os registros anteriores deveriam ser excluídos para serem gravados os novos valores. Pesquisei bastante sobre o assunto e achei vários fragmentos de código na internet, um pouquinho de cada coisa juntei tudo e construí uma classe de importação que está em uso até hoje.
A idéia é seguir o seguinte fluxo de informação:
Com o processo de importação passando pelo PHP antes de serem gravados os dados no MySQL poderemos ter um controle maior sobre a validação e formatação dos dados. Mas para podermos usar o PHP nessa tarefa será necessário baixar uma classe específica para esse tipo de importação, é a excel_reader2.php, a partir dela construiremos nossa classe base de importação.
Com essa classe excel_reader2.php podemos ter acesso a diretrizes da planilha tais como, quantidade de linhas e colunas preenchidas (as vezes falha..), além de poder acessar o valor das células em forma de matriz bidimensional, ou seja, valor[linha, coluna].
A classe abstrata vai servir como base, terá como objetivo auxiliar as classes descendentes com métodos de validações, formatação, retorno de dados como quantidade de linhas e colunas e 2 métodos abstratos que serão implementados em classes descendentes.
importacaoBase.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 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 |
<?php require_once "excel_reader2.php"; error_reporting(E_ALL ^ E_NOTICE); abstract class importacaoBase{ // Atributos da classe protected $name_file = null; protected $dataSheet = null; protected $pdo = null; protected $extensao = 'xls'; protected $tabela = null; protected $msgErro = null; /* * Método construtor da classe * @param $conexao = Conexão com o banco de dados * @param $file = Endereço e nome da planilha * @param $tabela = Tabela que vai receber os dados */ public function __construct($conexao, $file, $tabela){ if ($conexao != null) $this->pdo = $conexao; if ($tabela != null) $this->tabela = $tabela; if ($file != null && $this->existsExtensao($file)): $this->name_file = $file; $this->dataSheet = new Spreadsheet_Excel_Reader($file); else: echo "Arquivo não encontrado ou extensão inválida!"; exit(); endif; } abstract public function getDados(); abstract public function insertDados(); /* * Método para setar o atributo extensão * @param = extensão do arquivo (Planilha) */ public function setExtensao($extensao){ $this->extensao = $extensao; } /* * Método que retorna a extensão aceita * @return = string com a extensão */ public function getExtensao(){ return $this->extensao; } /* * Método para verificar extensão do arquivo * @param $file = Nome do arquivo para ser verificado a extensão * @return = TRUE para extensão aceita e FALSE para não aceita */ public function existsExtensao($file){ $retorno = false; $str = substr($file, -3); if ($str == $this->extensao) $retorno = true; return $retorno; } /* * Método que retorna o número de linhas na planilha * @return = Quantidade de linhas da planilha */ public function getLinhas(){ return $this->dataSheet->rowcount(); } /* * Método que retorna o número de colunas na planilha * @return = Quantidade de colunas da planilha */ public function getColunas(){ return $this->dataSheet->colcount(); } /* * Método para substituir somente o primeira ocorrência de um valor * @param $search = Carácter a ser procurado * @param $replace = Carácter a ser substituído * @param $subject = String a ser modificada * @return = String modificada */ private function str_replace_once($search, $replace, $subject){ if(($pos = strpos($subject, $search)) !== false): $ret = substr($subject, , $pos).$replace.substr($subject, $pos + strlen($search)); else: $ret = $subject; endif; return($ret); } /* * Método para verifcar existência de um registro no banco * @param $tabela = Tabela a ser pesquisada * @param $campo = Campo da condição WHERE * @param $valor = Valor do campo na condição WHERE * @return = Quantidade de registros encontrados */ private function VerificaRegistro($tabela, $campo, $valor) { try{ $sql = "SELECT * FROM $tabela WHERE $campo = ?"; $stm = $this->pdo->prepare($sql); $stm->bindValue(1, $valor); $stm->execute(); $retorno = $stm->rowCount(); } catch (Exception $e){ $this->msgErro .= "Script: " . $e->getFile() . " - Linha: " . $e->getLine() . " - Mensagem: " . $e->getMessage(); } return $retorno; } /* * Método para alterar acentução * @param $valor = String a ser alterada * @return = String modificada */ private function AlteraAcento($valor) { $valor = str_replace('á', 'Á', $valor); $valor = str_replace('ã', 'Ã', $valor); $valor = str_replace('à', 'À', $valor); $valor = str_replace('â', 'Â', $valor); $valor = str_replace('é', 'É', $valor); $valor = str_replace('ê', 'Ê', $valor); $valor = str_replace('í', 'Í', $valor); $valor = str_replace('ó', 'Ó', $valor); $valor = str_replace('ô', 'Ô', $valor); $valor = str_replace('õ', 'Õ', $valor); $valor = str_replace('ú', 'Ú', $valor); $valor = str_replace('ü', 'Ü', $valor); $valor = str_replace('ç', 'Ç', $valor); return $valor; } /* * Método para inserir o log de importação * @param $user = Nome do usuário que executou a importação * @param $status = Status da importatação ('SUCESSO' ou 'ERRO') * @param $tipo = Tipo de Importação * @param $registros = Quantidade de registros inseridas */ public function insertLog($user=null, $status=null, $tipo=null, $registros=null){ try{ $sql = "INSERT INTO TAB_IMPORTACAO (IMP_USUARIO, IMP_TIPO_IMPORTACAO, IMP_STATUS, IMP_ERRO, IMP_REGISTRO) VALUES (?, ?, ?, ?, ?)"; $stm = $this->pdo->prepare($sql); $stm->bindValue(1, $user); $stm->bindValue(2, $tipo); $stm->bindValue(3, $status); $stm->bindValue(4, $this->msgErro); $stm->bindValue(5, $registros); $stm->execute(); } catch (Exception $e){ $this->msgErro .= "Script: " . $e->getFile() . " - Linha: " . $e->getLine() . " - Mensagem: " . $e->getMessage(); } } /* * Método para limpar a tabela antes da inserção dos dados */ protected function limpaTabela(){ try{ $sql = "DELETE FROM " . $this->tabela; $stm = $this->pdo->prepare($sql); $retorno = $stm->execute(); } catch (Exception $e){ $this->msgErro .= "Script: " . $e->getFile() . " - Linha: " . $e->getLine() . " - Mensagem: " . $e->getMessage(); $this->insertLog('William', 'ERRO', 'PLANILHA DE GASTOS'); } return $retorno; } /* * Método para preparar string, retirando espaços e codificando em UTF-8 * @param $string = String a ser preparada * @param $encode = TRUE utiliza a função utf8_encode() FALSE não usa * @return = String sem espações e codificada */ protected function prepareString($string, $encode=TRUE){ return ($encode) ? utf8_encode(trim($string)) : trim($string); } } |
Observem que a classe acima apenas instância um objeto Spreadsheet_Excel_Reader(), passando como parâmetro o caminho da planilha, o restante dos métodos são auxiliares e serão herdados pelas classes descendentes evitando replicação de códigos. É importante mencionar que a classe excel_reader2.php infelizmente só aceita planilhas com a extensão (*.xls), realizei alguns testes com extensões (*.xlsx) que são geradas por versões mais recentes do excel e não houve sucesso, por esse motivo existe um atributo $extensao com o valor pré-definido, um método getExtensao() para retornar a extensão do arquivo passado como parâmetro e um método existsExtensao() para verificar se a extensão do arquivo é a mesma do atributo $extensao.
Um ponto importante dessa classe é o método insertLog(), ele tem como objetivo gravar detalhes da importação realizada, esses dados serão gravados em uma tabela TAB_IMPORTACAO. Este método recebe 4 parâmetros;
* $user – Usuário logado no sistema que disparou a importação.
* $status – Status final da importação, se todos os registros foram importados é gravado SUCESSO senão é gravado ERRO.
* $tipo – Quando estamos importando diferentes planilhas, podemos identificar nesse campo qual o tipo que está sendo importado.
* $registros – Quantidade total de registros inseridos.
Além desses parâmetros mais uma informação é gravada com o atributo $this->msgErro, caso ocorra um erro será gravado a mensagem disparada pelo objeto PDOException. A classe está bem comentada com informações de parâmetros de entrada e saída.
Bom pessoal vamos finalizando, conforme o descrito no início do artigo na segunda será finalizado com os scripts do banco de dados, planilha e classe especifica de importação.
Até a próxima ….
