Veja como funciona a função FiltroXML Excel.
No artigo nós mostramos duas formas de a utilizar, para junto com a função serviçoweb retornar dados da web e também com um exemplo de como fazer “split”, texto para colunas, dividir dados de uma célula separados por delimitadores em várias células.
FiltroXML e ServiçoWEB Consultar WebService no Excel
No Excel há uma função chamada ServiçoWeb, ela permite que sejam retornados dados de um webservice de uma forma muito simples.
O webservice no nosso exemplo é de um site chamado http://republicavirtual.com.br/web_cep.php?cep=89201280, mas poderia ser também o viacep.com.br/ws/01001000/xml/.
Estes links acima retornam os dados de um endereço a partir do CEP informado.
Este retorno vem em formato XML.
Podemos usar a função serviçoweb do Excel então para retornar, veja um exemplo:
=SERVIÇOWEB(“http://republicavirtual.com.br/web_cep.php?cep=89201280”)
No exemplo acima a fórmula faz uma consulta direta do cep 89201280 no republicavirtual.
Note que é passado apenas o endereço do site e o CEP.
O retorno que se tem então é:
| <?xml version=”1.0″ encoding=”iso-8859-1″ ?> <webservicecep> <resultado>1</resultado> <resultado_txt>sucesso – cep completo</resultado_txt> <uf>SC</uf> <cidade>Joinville</cidade> <bairro>Centro</bairro> <tipo_logradouro>Travessa</tipo_logradouro> <logradouro>Sergipe</logradouro> </webservicecep> |
No Excel ele parecerá estar em uma linha só.
Como pode notar os dados estão ali separados por tags, como por exemplo <uf> e </uf>.
Isso significa que a informação entre estas duas tags é a UF, a de cidade está em <cidade> e <cidade> e assim por diante.
Para separar estes dados utilizamos então a função chamada FiltroXML.
Ela permite que realizemos a extração dos dados do XML que estão entre as tags.
Veja o exemplo de aplicação da ServiçoWeb e da FiltroXML em conjunto:
=FILTROXML(SERVIÇOWEB(“http://republicavirtual.com.br/web_cep.php?cep=89201280″);”//uf”)
Perceba que ela tem dois parâmetros:
=FILTROXML(xml;xpath)
No primeiro passamos o XML, que é a informação do retorno do webservice e em xpath passamos usando // e a tag que desejamos, por exemplo //uf.
Importante, tem que ser escrito da mesma forma o nome da tag, minúsculo e maiúsculo porque é case sensitive esta função.
Por fim, montamos então a fórmula seguinte:
=FILTROXML(SERVIÇOWEB(“http://republicavirtual.com.br/web_cep.php?cep=”&$B8);”//”&SUBSTITUIR(MINÚSCULA(C$7);” “;”_”))
Nela usamos também a célula do cabeçalho aonde tem o nome do campo que desejamos retornar e usamos o substituir para trocar os espaços por _ conforme o retorno que temos do xml.
Ao aplicar a fórmula em todas as colunas temos então:
FiltroXML para Dividir Texto Separado por Vírgulas
No outro exemplo temos então a separação dos dados por ponto e vírgula em uma célula e queremos que estes dados sejam separados em outras células.
Veja que na coluna Lista temos dados de texto separados por ponto e vírgula, aonde temos nomes.
Queremos que estes nomes venham para as colunas 1, 2, 3, 4, 5 da tabela.
Para isso podemos fazer uso da função FiltroXML, veja o exemplo.
O FiltroXML só funciona com tags, como vimos antes, por exemplo <a> e </a> e <b> e </b>.
Por isso o nosso objetivo inicial é converter o texto abaixo:
marcos;henrique;thiago;josé
em:
<a><b>marcos</b><b>henrique</b><b>thiago</b><b>josé</b></a>
Porque com os dados neste formato conseguiremos extrair as informações.
Então para isso usamos a função abaixo inicialmente:
“<a><b>&SUBSTITUIR($B8;”;”;”</b><b>”)&”</b></a>“
Com isso temos então a conversão dos dados para o formato que desejamos.
E após isso usamos a função FiltroXML para separar os dados como fizemos antes, separando por //b.
E também usamos o ÍNDICE parar retornar a posição desejada:
=SEERRO(ÍNDICE(FILTROXML(“<a><b>“&SUBSTITUIR($B8;”;”;”</b><b>”)&”</b></a>”;”//b”);C$7);””)
A função índice permite então que retornemos o primeiro tag, segundo, terceiro, quarto, etc.
Pegamos então a célula C7, C8, C9… que tem estes números e aplicamos na função acima.
Download
Clique no botão abaixo para realizar o download da planilha de filtroxml Excel, com exemplo de dados:
