Transformando DataSet para Excel através de XSL

Desde a época que comecei a trabalhar com desenvolvimento (e lá se vão mais de 5 anos ) transformar informações carregadas do banco de dados em Excel é uma tarefa presente. Naquela época o sistema era feito em Delphi 5 e utilizávamos uma classe que encapsulava o objeto COM do próprio Excel. O programador que quisesse aquela funcionalidade em sua tela de exportação, com seu próprio layout de arquivo, tinha que realizar loops aninhados e desbravar o mundo dos objetos não tipados (tipo var no Delphi) pra conseguir setar cores, dados, fontes, formato de número e tudo mais.

Já não desenvolvo mais em Delphi há algum tempo e provavelmente hoje exista algo mais simples pra criar arquivos Excel a partir de DataSets ou algo que o valha na linguagem (talvez até já existisse aquela época e eu que não conhecesse), mas a questão é que a demanda permanece e, mesmo no mundo .NET, as soluções utilizadas, em sua maioria, não são exatamente simples. Com esse post pretendo mostrar como criar arquivos Excel a partir de DataSets com ASP.NET de uma maneira bastante simples e direta.

Antes de por a mão na massa vamos entender o conceito da solução. Primeiro fato: DataSets podem ser tratados como arquivos XML. É claro que um DataSet extrapola em muito o simples conceito de um agrupado de propriedades tipadas, mas o fato é que eles podem ser serializados e desserializados para e de arquivos XML, e isto é o que nos importa para esta solução. Um DataSet serializado, sem suas informações de XSD, fica mais ou menos assim:

1 <?xml version=“1.0“ encoding=“utf-8“ ?>

2 <MeuDS>

3 <MeuDT>

4 <ID>1</ID>

5 <Name>João</Name>

6 </MeuDT>

7 <MeuDT2>

8 <ID>1</ID>

9 <Name>João</Name>

10 </MeuDT2>

11 </MeuDS>

Segundo fato: um arquivo Excel também pode ser gravado como XML. Desde a versão 2003 do Excel é possível gravar um arquivo Excel como um arquivo XML. A estrutura do arquivo gerado fica assim:

1 <?xml version=“1.0“?>

2 <?mso-application progid=”Excel.Sheet”?>

3 <Workbook xmlns=“urn:schemas-microsoft-com:office:spreadsheet“

4 <!– … (outros namespaces default) –>

5 <Worksheet ss:Name=“MeuDT“>

6 <Table ss:ExpandedColumnCount=“3“ ss:ExpandedRowCount=“4“>

7 <Row>

8 <Cell ss:StyleID=“s62“>

9 <Data ss:Type=“String“>ID</Data>

10 </Cell>

11 <!– demais células ocultadas –>

12 </Row>

13 <Row>

14 <!– demais linhas ocultadas –>

15 </Row>

16 </Table>

17 <!– seções opcionais também ocultadas –>

18 </Worksheet>

19 </Workbook>

Existem uma séries de outras tags que podem fazer parte do arquivo, responsáveis por formatação, planilhas que estão abertas, maximizadas, etc, etc. Para ver um arquivo completo, basta criar uma planilha, preencher com alguns dados/formatações e Salvar Como..

Voltando à solução: se tanto o DataSet quanto o arquivo Excel são arquivos XML, será que não seria possível transformar um no outro? Considerando sua estrutura hierárquica bastante semelhante (tabelas/planilhas, linhas e células), esta parece ser a melhor solução. Mas existe algo que auxilie nesse sentido? A resposta é sim, e esse algo chama-se XSL.

O termo XSL vem de Extensible Stylesheet Language, e trata-se de uma linguagem que permite transformar um XML original em um formato qualquer. Como assim? De maneira resumida, o XSL permite a definição de tags que possuam uma lógica de filtro/iteração/reconhecimento para que para todo nodo de um XML seja gerado um novo texto, baseado num template. Em nosso caso isso implica, por exemplo, em dizer que todo elemento que esteja no segundo nível (MeuDT, MeuDT2) será uma nova planilha, cada nodo seguinte transforma-se em uma coluna e seus valores vão preenchendo as linhas. Pode parecer confuso, mas um XSL de exemplo ajuda a materializar:

18 <xsl:template match=“/*“>

19 <xsl:for-each select=“/*/*“>

20 <xsl:if test=“not(preceding::*[name(current()) = name()])“>

21 <Worksheet>

22 <xsl:attribute name=“ss:Name“>

23 <xsl:value-of select=“local-name()“/>

24 </xsl:attribute>

25 <Table>

Nesse pequeno trecho do XSL é realizado um foreach em todos os nodos do segundo nível e, para aqueles com nome diferente do anterior (para que ele não crie 1 planilha para cada linha do DataSet) é gerado um novo elemento WorkSheet com nome igual ao do nodo do primeiro nível (por causa do template match=”/*” logo acima). Pode parecer bastante confuso num primeiro momento, mas com alguns testes básicos tudo começa a fazer sentido. Você pode aprender mais sobre transformações XSL nesse link.

Para realizar a transformação completa de um DataSet em um Excel você vai precisar de um XSD bem mais completo que o exemplificado acima, mas não se preocupe, no final do artigo existe um link para um projeto que criei onde você consegue um 100%. Na verdade o que fiz foi pegar o arquivo utilizado nesse blog e realizar algumas modificações para que ele consiga gerar mais de um DataTable num mesmo DataSet (essa era uma limitação explicitada pelo próprio autor).

Criado o XSL, fica faltando somente o código da aplicação ASP.NET. O trecho de código é realmente simples e fica como esse abaixo:

61 protected void Button2_Click(object sender, EventArgs e)

62 {

63 DataSet ds = this.GetData(); // retorna dados teste

64 // limpa response

65 Response.ClearHeaders();

66 Response.ClearContent();

67 Response.Clear();

68 // informa que será um arquivo excel anexado

69 Response.ContentType = “application/vnd.ms-excel”;

70 Response.AddHeader(“Content-Disposition”, “attachment”);

71 Response.Write(CreateWorkbook(ds)); // envia o XML gerado para o cliente

72 Response.Flush();

73 Response.End();

74 }

75

76 public static string CreateWorkbook(DataSet ds)

77 {

78 // lê o XSL de um arquivo de recursos (Resources.Resource.Excel)

79 XslCompiledTransform xt = new XslCompiledTransform();

80 xt.Load(XmlReader.Create(new StringReader(Resources.Resource.Excel)));

81 // pega o XML do DataSet

82 XmlDataDocument xmlDataDoc = new XmlDataDocument(ds);

83 StringWriter stw = new StringWriter();

84 // transforma o XML jogando no StringWriter

85 xt.Transform(xmlDataDoc, null, stw);

86 return stw.ToString();

87 }

Por questões de exemplificação o código está o mais simples possível. Como vocês podem perceber meu XSL ficou como um arquivo de recurso do projeto, pra facilitar a leitura. A performance da transformação é realmente surpreendente e muito superior a qualquer interação com objetos COM que você possa ter no seu projeto. Comprovado por experiência própria e por outros depoimentos que li sobre isso durante uma discussão semelhante que tivemos na lista do DotNetFloripa.

O resultado é um arquivo Excel gerado com uma planilha por datatable e o título das colunas impresso, como mostrado pelo print abaixo. Para customizar (adicionar formatação, tirar o título, etc) basta que você salve um arquivo Excel, entenda a estrutura gerada por ele e replique com as informações necessárias no seu XSL. Acreditem, não é tarefa das mais complexas.

Com uma estrutura razoavelmente organizada você consegue criar exportações diferenciadas de acordo com sua demanda sem precisar criar novas classes que interajam com objetos COM nem realizar novos deploys de seu software para isto. Um típico caso de desenvolvedor e clientes felizes, o quê mais querer?

Para os interessados em reaproveitar o XSL, ou simplesmente ver um exemplo rodando, coloquei um website no RapidShare, como habitual. É só clicar aqui para baixar.

Abraços e até a próxima!

José Filipe