Export XML and XSLT report to Excel

Asked

Viewed 99 times

1

I’m having trouble exporting a report I developed in XML and XSLT...

I was already exporting the simple report done in HTML, but this week I started studying XSLT and I came across the problem to export to Excel.

For me to reuse the logic of exporting as before, I needed to take XML and turn it into HTML and move on to the $_SESSION['HTML'].

XSLT code:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="TCL">
    <link rel="stylesheet" type="text/css" href="Telas/Includes/css/relatorios.css"/>
    <table>
        <xsl:apply-templates/>
    </table>
</xsl:template>

<xsl:template match="cabecalho">
    <tr>
        <th colspan="6">
            <table>
                <tr>
                    <td class="Logo">
                        <img>
                            <xsl:attribute name="src">Imagens/LogoRelatorios200x55.png</xsl:attribute>
                        </img>
                    </td>
                    <td style="text-align:center;">
                        <span class="TituloRelatorio">Relatório de TCL's</span>
                        <br/>
                        <span class="PeriodoRelatorio">
                            Entre os dias
                            <xsl:value-of select="dtini"/> e
                            <xsl:value-of select="dtfim"/>
                        </span>

                    </td>
                </tr>
            </table>
        </th>
    </tr>
</xsl:template>

<xsl:template match="categoria">
    <tr class="CabecalhoDados">
        <th style="width: 150px;">Cliente</th>
        <th style="width: 150px;">Veículos</th>
        <th style="width: 150px;">Valor Contrato</th>
        <th style="width: 80px;">Cortesia</th>
        <th style="width: 80px;">%</th>
        <th style="border-right: solid 1px; width: 150px;">Sub Total</th>
    </tr>
    <xsl:apply-templates/>
</xsl:template>

<xsl:template match="veiculo">
    <tr class="DadosRelatorio">
        <td style="text-align:left;">
            <xsl:value-of select="cliente"/>
        </td>
        <td style="text-align:left;">
            <xsl:value-of select="placa"/>
        </td>
        <td style="text-align:right;">
            R$
            <xsl:value-of select="valorcontrato"/>
        </td>
        <td style="text-align:left;">
            <xsl:value-of select="cortesia"/>
        </td>
        <td style="text-align:right;">
            <xsl:value-of select="proporcao"/>
        </td>
        <td style="text-align:right; border-right: solid 1px;">
            R$
            <xsl:value-of select="subtotal"/>
        </td>
    </tr>
</xsl:template>

<xsl:template match="rodape">
    <tr>
        <th colspan="2" class="CabecalhoDados">
            Qtd. de Registros:
            <xsl:value-of select="qtdRegistros"/>
        </th>

        <th colspan="2" class="CabecalhoDados">
            Contratos: R$
            <xsl:value-of select="valorescontrato"/>
        </th>

        <th colspan="2" class="CabecalhoDados">
            Total: R$
            <xsl:value-of select="totalgeral"/>
        </th>
    </tr>
</xsl:template>

Code that generates XML along with XSLT:

<form action="principal.php?ACAO=ExportarExcel" method="POST" target="_blank">
<input type="hidden" name="MODULO" value="Distribuicao"/>
<input type="hidden" name="TELA" value="GerarRelPersonalizado"/>

<div class="clBotoes">
    <input class="botao" type="button" value="Imprimir" id="btnImprimir" onClick="window.print()"/>
    <input class="botao" type="submit" value="Exportar para Excel" id="btnSalvar"/>
</div>
</form>
<?php
$DadosXML = array_to_xml($_dados);
//$_dados é um array de objetos com todas as informações(dados) do BD

$linkXML = "<?xml version='1.0' encoding='UTF-8'?>
            <TCL>
                <cabecalho>
                    <img/>
                    <dtini>" . $_POST['dataini'] . "</dtini>
                    <dtfim>" . $_POST['datafim'] . "</dtfim>
                </cabecalho>
                " . $DadosXML . "";

$linkXSLT = URLBASE . "Telas/Financeiro/XSL/RelTcl.xsl"; 

// XML
$xml_doc = new DOMDocument();
$xml_doc->loadXML($linkXML);

// XSL
$xsl_doc = new DOMDocument();
$xsl_doc->load($linkXSLT);

// Proc
$proc = new XSLTProcessor();
$proc->importStylesheet($xsl_doc);
$newdom = $proc->transformToDoc($xml_doc);

$_SESSION['HTML'] = //Recebe o html completo, ou a table para exportar
$_SESSION['NOMERELATORIO'] = "Relatorio";

print $newdom->saveXML();

function array_to_xml($array) {
$DadosXML = "<categoria>";
$SomaContrato = 0;
$SomaTotal = 0;
$QtdRegistros = count($array);
foreach ($array as $key => $value) {
    $SomaContrato = $SomaContrato + $value->ValorContrato;
    $SomaTotal = $SomaTotal + $value->SubTotal;

    $proporcaoTemp = ($value->Proporcao < 0) ? 0 : $value->Proporcao;
    $valorContratoTemp = ($value->ValorContrato < 0) ? '0,00' : number_format($value->ValorContrato, 2, ',', '.');
    $valorSubTotalTemp = ($value->SubTotal < 0) ? '0,00' : number_format($value->SubTotal, 2, ',', '.');

    $DadosXML .= "<veiculo id='{$key}'>
                        <cliente>{$value->Cliente}</cliente>
                        <placa>{$value->Veiculo}</placa>
                        <valorcontrato>" . $valorContratoTemp . "</valorcontrato>
                        <cortesia>{$value->Cortesia}</cortesia>
                        <proporcao>" . number_format($proporcaoTemp, 2, ',', '.') . "</proporcao>
                        <subtotal>" . $valorSubTotalTemp . "</subtotal>
                    </veiculo>";
}

$DadosXML .= "</categoria>
                <rodape>
                    <qtdRegistros>" . number_format($QtdRegistros, 0, ',', '.') . "</qtdRegistros>
                    <valorescontrato>" . number_format($SomaContrato, 2, ',', '.') . "</valorescontrato>
                    <totalgeral>" . number_format($SomaTotal, 2, ',', '.') . "</totalgeral>
                </rodape>   </TCL>";
return $DadosXML;
}
?>
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.