FiltroXML Excel

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: