Hi All,
I am using pl/sql to generate excel report by using xml, but the problem is that size of the xls file generated using this technique is huge compared to normal xls file. In case of less data (e.g. less than 10000 rows) it works fine but the more records i am adding into the file more it is getting slow and in case where data is more than 25000 rows xls file it not at all opening. Is there any alternate way to generate excel file with less size of any way by which i can compress the size of excel file by changing xml coding.
my sample xml code which i am using is
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<xsl:output method="xml" encoding="UTF-8"/>
<xsl:template match="/">
<xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<Styles>
<Style ss:ID="header">
<Interior ss:Color="#D9D9D9" ss:Pattern="Solid"/>
<Font ss:Size="10" ss:FontName="MS Sans Serif"/>
</Style>
<Style ss:ID="regular">
<Font ss:Size="10" ss:FontName="MS Sans Serif"/>
</Style>
</Styles>
<xsl:apply-templates/>
</Workbook>
</xsl:template>
<xsl:template match="ROWSET/all|ROWSET/agg|ROWSET/agg_tot"><Worksheet ss:Name="{../@name}">
<Table>
<Row>
<xsl:for-each select="ROW[1]/*">
<Cell ss:StyleID="header">
<Data ss:Type="String">
<xsl:value-of select="translate(local-name(), ''_'', ''_'')"/>
</Data>
</Cell>
</xsl:for-each>
</Row>
<xsl:apply-templates/>
</Table>
<x:WorksheetOptions>
<x:FrozenNoSplit/>
<x:SplitHorizontal>1</x:SplitHorizontal>
<x:TopRowBottomPane>1</x:TopRowBottomPane>
<x:ActivePane>2</x:ActivePane>
<xsl:if test="@color">
<x:TabColorIndex><xsl:value-of select="@color"/></x:TabColorIndex>
</xsl:if>
</x:WorksheetOptions>
</Worksheet>
</xsl:template>
<xsl:template match="ROW"><Row><xsl:apply-templates select="*"/></Row></xsl:template>
<xsl:template match="CLUSTER_LGD_A|EVENTO|FC_COD_CLI_HOST|FLAG_RETT|FC_COD_ABI|ANAG|FORBORNE|DESCR|CLASSIFICAZIONECOLLETTIVE|STATO|RAT_SMELCC">
<Cell ss:StyleID="regular">
<Data ss:Type="String">
<xsl:value-of select="."/>
</Data>
</Cell>
</xsl:template>
<xsl:template match="RETT_DR|RETT_BON|PD_TD|CURE_RATE_D|ANALITICA|UTIL_DEF|PD_TD_DETT|UTILIZZO|RETT_ALL|PD_SMELCC|LGD_A|CURE_RATE|LIP|COVERAGE_A|COLLETTIVA|COVERAGE">
<Cell ss:StyleID="regular">
<xsl:choose>
<xsl:when test="string-length(current()) > 0">
<Data ss:Type="Number">
<xsl:value-of select="number()"/>
</Data>
</xsl:when>
<xsl:otherwise>
<Data ss:Type="String">
<xsl:value-of select="."/>
</Data>
</xsl:otherwise>
</xsl:choose>
</Cell>
</xsl:template>
</xsl:stylesheet>
At run time i am passing my query to this and generating xml file and saving it as xls. Any help or clue will be helpful for me.