Problemas na manipulação de arquivos do MS Excel com .NET

Este artigo ilustra problemas corriqueiros e suas respectivas soluções no cotidiano do desenvolvimento de aplicações que manipulam planilhas do Microsoft Excel.

por Flávio Henrique de Carvalho

Este post tem como objetivo principal elucidar alguns problemas como suas respectivas soluções na manipulação de arquivos do MS Excel via programação em C# ou VB.NET. Os bugs citados seguem uma ordem cronológica tal qual a ordem de surgimento de cada problema no decorrer da implementação de uma aplicação. Portanto,nada mais é que umabreve listagem de tipos de problemas, que embora muitas vezessejam de simples solução, tornam bastante desgastante a busca de uma resolução nas várias fontes de conhecimento de nosso acesso. Sendo assim, este post tem a pretensão de agrupar dentro deste contexto mencionado, alguns dos problemas atuais mais corriqueiros num único artigo.

Utilizando tecnologia e recursoscontemporâneos aoVS2005(antes do lançamento do VS2008) construí uma classe genéricade manipulação deplanilhas do Microsoft Excel. Tal classe foi encapsulada em umprojeto próprio que poderia ser usada como um componente a cada vez que uma aplicaçãonecessitassedeste tipo de funcionalidade, no bome velho estiloPOO.Possuindovários métodos de leitura e gravação utilizando tanto OleDB para a conexão com as planilhas quanto o conceito de Excel Application (worksheets, workbooks)a intenção sempre foi ter um pequeno framework de soluções funcionais neste sentido.Porém houveram algumas mudanças significativas no meu cenário de desenvolvimento, tais como troca por um novo sistema operacional (Windows 7 - 64 bits), novas versões de browser (IE 8 e Mozilla 5.0), também versões novas do Visual Studio .NET (VS2008) e do Office(2007). E mais importante ainda, muitas mudanças de técnicas de desenvolvimento. E quem programa sabe que sempre estamos querendo reescrever nossas aplicações, aperfeiçoando-as em busca da melhor aplicação possível.Diante das novasmudanças alguns dos métodos que antes funcionavam perfeitamente pararam de funcionar ou ficaram “enferrujados” e na tentativa de reescrevê-losme deparei com alguns problemas que descrevo à partir de agora cada um deles.

1) O novo Internet Explorer em sua oitava versão nãoretorna mais o caminho completo de um arquivo lido através do controle FileUpload e suas propriedades, com a alegação de que esta mudança seja por medidas de segurança. O fato é que por segurança ou não, métodos que antes faziam uso da propriedade fileUpload1.postedfile.filename para esta finalidade pararam de funcionar como deviam. E neste novo cenário algo que seria devolvido como ‘C:\Temp\Clientes.xls’, passa agora a ser devolvido como ‘Clientes.xls’. Este tipo de descoberta é de difícil visualização, por ser um controle (ou poderia ser um código qualquer) de utilizaçãocotidiana,nos remete a uma zona de conforto onde existe a ilusão de domínio e de que o mesmo nunca mudará. É mais fácil começar a procurar erros em ConnectionStrings que são sempre problemáticas com relaçãoà alterações, ou nas muitas mudanças de versões de software já mencionadas, do quena verdadeira causa do meu insucesso em conectar-me ao arquivo Excel. Foi necessário algum tempo, testes e depurações, debugando código confiável para resolver o problema. E foi substituído a antiga chamada de fileUpload1.postedfile.filename por System.IO.Path.GetFullPath(fileUpload1.postedfile.filename) recebendo o caminho completo do arquivo. A mensagem de erro ao invocar o método Open() do OledbConnection quando o caminho do arquivo está incorreto é estamencionada abaixo:

“O mecanismo de banco de dados Microsoft Jet não pôde encontrar o objeto "C:\…\Nome_Arquivo.xls". Certifique-se de que o objeto exista e de ter digitado seu nome e o caminho corretamente.”

2) Apesar de estar utilizando o Office 2007 sem ter instalado o Office 2003, consegui realizar a conexão normalmente com planilhas do MS Excel 2003, utilizando OleDb com a Connection String abaixo. E neste ponto me enganei totalmente, pois imaginava que somente com o Office 2003 instalado é que fosse ter o seu drive para realizar a conexão. Mas o que provavelmente acontece é que ou o Office 2007 ou o Framework .NET 2 e 3.5 devem possuir este drive embutidos em suas respectivas instalações.

“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\….\NomeArquivo.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"”

3) A sintaxe deve ser diferenciada para a construção de SELECT´s no MS Excel em relação a tabelas de outras fontes de dados, embora para o Excel o termo mais correto seria aba ou planilha e não tabela. Pois para SELECT´s de planilhas do MS Excel o nome da tabela deverá vir sempre entre colchetes e com o cifrão (ou dólar) após seu nome ("SELECT * FROM [" + VariavelContendoNomePlanilha + "$] WHERE…"). Ao deixar o nome padrão do MS Excel para planilhas Plan1 após o FROM do SQL, que geralmenteocorre por esquecimento ou por uma questão de comodismo em se copiar/colar.Mas neste caso a mensagem de erro retornada é bastante intuitiva:

"O mecanismo de banco de dados Microsoft Jet não pôde encontrar o objeto "SPP". Certifique-se de que o objeto exista e de ter digitado seu nome e o caminho corretamente."

4) O nome do arquivo segue as próprias regras de nomenclatura do Windows, ou seja, passando pelas restrições do sistema operacional, nada impedirá de realizar uma conexão via OleDb. Inclusive foi testado com sucesso conexões com arquivo contendo espaços entre as palavras. Já para as colunas de composição do cabeçalho (primeira linha de uma planilha)NÃO deve haver a presença de caracteres especiais na composição de seu nome. Pois muito embora a primeira linha seja entendida como um cabeçalho de campos, quando inserimos a seguinte cláusula em nossa connection string, HDR=Yes, denotando uma visão de tabela. Para o Excel internamenteà planilha, células são células e qualquer conteúdo é permitido, podendo para ele ser inserido qualquer símbolo juntamente a outros caracteres. Cabe entãoeste tipo de controle ao desenvolvedor do sistema, que deve fazer uso das regras de normalização para nomenclaturas de campos e tabelas. No exemplo abaixo, por mais incrível que pareça, num processo automatizado por um dispostivo móvel um campo chamado Dist# V1-V4(L2), que gerou a seguinte mensangem:

{"Erro de sintaxe na data na expressão de consulta "Dist# V1-V4 (L2)"."}

5) Ocorreu o seguinte erro ao realizar um UPDATE na planilha utilizando a sintaxe, “UPDATE [NomePlanilha$] SET Campo1=? WHERE Campo2=?”:

“A operação deve usar uma consulta atualizável.”

E aqui quero frisar várias coisas, pois são muitas as possibilidades de problemas:

5.1) Em primeiro lugar, a sintaxe deste UPDATE está correta, igual a um UPDATE qualquer que usaríamos numa tabela do SQL Server, Oracle, etc.

5.2) A princípio achei que por ter aberto dois OleDbDataReader´s conectando a mesma planilha poderia estar dando este problema.Imaginando que o primeiro OleDbDataReader que abria um SELECT de leitura de informação na planilha (realmente necessário para o cruzamento de informações com outra planilha), estava bloqueando o OleDbDataReader que chamava o UPDATE. Na prática eu percebi que isto não ocorre, pois após verificar a resolução, este fato não interferiu em nada.

5.3) Um segundo fato muito observado em fóruns para este tipo de problema é ausência de permissões que o usuário do ASP.NET pode estar sofrendo na pasta onde está gravada a planilha a ser alterada. No meu caso, o usuário não teve dificuldade alguma em realizar a leitura via SELECT sem que eu desse permissões, mas por via das dúvidas fui até a pasta e dei permissões globais a TODOS os usuários na pasta. O que nada adiantou !!!

5.4) Eu vinha realizando uma prática preguiçosa, que além de perigosa já me trouxe muitos problemas, que é a substituição dos valores de campos diretamente na string de consulta SQL. Como por exemplo, utilizar o SQL, UPDATE [NomePlanilha$] SET Campo1=100 WHERE Campo2=’Carlos’, sem o uso devido de parâmetros (OleDbParameter) UPDATE [NomePlanilha$] SET Campo1=? WHERE Campo2=?. Tem determinadas regras de boas práticas que muitas vezes ignoramos por termos um caminho mais rápido escrevendo código que já dominamos ou que achamos menores. Mas esquecendo todo o falatório sobresegurança no código (não sou contrário as regras de segurança, mas seus defensores são muito maçantes), o uso de parâmetros é muito menos oneroso do que seu desuso, pelo simples fato dele fazer a validação de tipos. Ou seja, ele nos tira a responsabilidade de prever se um campo é do tipo string, numérico, ou outro tipo qualquer. Pois como sabemosna montagemmanual de uma cláusula WHEREdevemos prever seuscaracteres de string, ou de datas, o que pode mudar de banco para banco.E além disso o Excel não realiza uma análise rigorosa para que capturemos o tipo de uma coluna, pois segundo documentação do próprio fabricante ele usa apenas oito linhas para verificar qual tipo prevalece. Troquei a lógica antiga (e porca, diga-se de passagem) por uma lógica que utilizasse OleDbParameters o que também não resolveu meu problema.

5.5) Mas o meu UPDATE passou a ser gravado perfeitamente no banco de dados quando tive a feliz informação de que o “IMEX=1” de uma Connection String pode causar problema. Esta cláusula IMEX foi criada pararealizar leitura e gravação de dados mixtos dentro de uma coluna, onde há dados de várias formas, inclusives nulos, conforme pode ser melhor esclarecido no site desuporte da própria Microsoft,http://support.microsoft.com/kb/257819/pt#Connect. Para o meu caso, eu tinha apenas números em minha consulta, não tendo a preocupação de ter outro tipo de valor. Sendo assim, resolvieste problema e meus métodos voltaram a funcionar corretamente.

É claro que existem muito mais problemas neste contexto do que estes poucos que foram mencionados aqui. Mas uma das coisas que venho experimentando na prática é que se esperarmos termos todos os dados, nunca começamos. Por isto, aconselho que cada um vá criando seu próprio “manual” de problemas e acertos, não só para estes que envolvem o Excel, mas para todos os nosso problemas que ocorrem desenvolvendo código. O que aconteceria naturalmentedentro denossas cabeças, não fosse a velocidade de tantas mudanças.