采用XML+XSL样式表制作报表

采用XML+XSL样式表制作报表

 

一、问题的产生:

在项目中,需要对一些交易和业务查询结果制作报表,最初的方法是制作了专门的打印报表类来专门制作报表,但问题是报表的设计需要使用画图技术来制作表格,而画图的方法很难解决数据的多样性带来的可扩展性的挑战,比如数据长度增加不能很好的自动换行,汇总结果需要人工来控制,报表的打印显示也不够美观,还有,需要对把报表结果存储为EXCEL文件。所以这些问题给我们带来了很大的挑战。

二、解决思路:

    考虑到报表的可视化设计、可扩展性、美观以及存储,采用XML存储数据,XSL样式表来设计报表样式的技术解决我们的困难。

三、解决步骤:

    1、首先根据需求的报表样式设计报表的样式表;

    2、把报表所需的数据存储为XML文件;

    3、通过XSL样式表来把XML文件显示出来既是我们所要的报表;

    4、使用IE自带的打印功能把这个具有样式表的XML文件打印出来;

    5、可以把XMLXSL进行转换,可以转换为我们所要的EXCEL文件。

四、详细设计:

    1、报表样式需求如图1

   

    2、样式表XSL设计如图:

   

    代码如下:

    <?xml version="1.0" encoding="GB2312"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:key name="LogItem-TotalAmount" match="LogItem" use="totalAmount" />

<xsl:template match="InvoiceReport">

<html>

<head>

<title>Receipt/TAX Invoice Report</title>

<style>

 .abc {border-width:1px;border-style:solid;border-color:#000000}

 .reportno{font-size:10px}

 .title{border-width:1px;border-style:solid;border-color:#000000;font-size:20px}

 td{font-family:Arial;font-size:15px}

</style>

</head>

<body topmargin="20" leftmargin="20" rightmargin="20" bottommargin="20">

<table width="100%" cellspacing="0" cellpadding="3" align="center">  

 <tr>   

    <td bgcolor="#ffffff" >

        <table width="100%" cellpadding="1" cellspacing="0">

        <tr>

          <td colspan="12"></td>

          <td align="right" class="reportno">Report No:BI-R1<xsl:text disable-output-escaping="yes">&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;</xsl:text></td>         

        </tr>

      </table>     

        <table width="100%" cellpadding="3" cellspacing="0">       

        <tr>

          <td align="center" height="30" colspan="13" class="title"><b>Receipt/TAX Invoice Report</b></td>

        </tr>

        </table>

        <table width="100%" border="0" cellpadding="1" cellspacing="0">    

        <tr>

          <td height="50" colspan="3" >PEA Area: <xsl:value-of select="PeaArea" /></td>

          <td colspan="3">TAX ID: <xsl:value-of select="TaxID" /></td>

          <td colspan="5">Selected Period</td>         

          <td colspan="2">Issue Date: <xsl:value-of select="IssueDate" /></td>

        </tr>

        <tr>

          <td height="50" colspan="6" >PEA Sub Area Code: <xsl:value-of select="AreaCode" /></td>         

          <td colspan="2">Start Date: <xsl:value-of select="StartDate" /></td>

          <td colspan="5">End Date: <xsl:value-of select="EndDate" /></td>         

        </tr>       

      </table>

      <table cellpadding="3" cellspacing="0">    

        <tr align="center">

          <td class="abc">Printer Serial No.</td>

          <td class="abc">Receipt No./Tax</td>

          <td class="abc">Purchase Date</td>         

          <td class="abc">Consumer ID</td>

          <td class="abc">Renew Smart Card (Baht)</td>

          <td class="abc">FT Cal. Back +/- (Baht)</td>

          <td class="abc">Estimated Energy Unit (KWh)</td>

          <td class="abc">Purchase Amount (Baht)</td>

          <td class="abc">VAT 7% (Baht)</td>

          <td class="abc">Total Amount (Baht)</td>

          <td class="abc">Cash Receiver</td>

        </tr>      

        <xsl:for-each select="InvoiceItem[count(. | key('LogItem-TotalAmount', totalAmount)[last()]) = 1]">

            <xsl:sort select="totalAmount" />                         

        </xsl:for-each>    

        <xsl:for-each select="InvoiceItem">

             <tr>

               <td align="left" class="abc"><xsl:value-of select="PrintSn" /><xsl:text disable-output-escaping="yes">&amp;nbsp;</xsl:text></td>       

               <td align="left" class="abc"><xsl:value-of select="ReceiptNo" /><xsl:text disable-output-escaping="yes">&amp;nbsp;</xsl:text></td>

               <td align="left" class="abc"><xsl:value-of select="PurchaseDate" /><xsl:text disable-output-escaping="yes">&amp;nbsp;</xsl:text></td>                                                                                                   

               <td align="left" class="abc"><xsl:value-of select="ConsumerID" /><xsl:text disable-output-escaping="yes">&amp;nbsp;</xsl:text></td>

               <td align="right" class="abc"><xsl:text disable-output-escaping="yes">&amp;nbsp;</xsl:text><xsl:value-of select="format-number(CardAmount, '###,##0.00')" /></td>

               <td align="right" class="abc"><xsl:text disable-output-escaping="yes">&amp;nbsp;</xsl:text><xsl:value-of select="format-number(BackAmount, '###,##0.00')" /></td>

               <td align="right" class="abc"><xsl:text disable-output-escaping="yes">&amp;nbsp;</xsl:text><xsl:value-of select="format-number(EnergyUnit, '###,##0.00')" /></td>

               <td align="right" class="abc"><xsl:text disable-output-escaping="yes">&amp;nbsp;</xsl:text><xsl:value-of select="format-number(PurchaseAmount, '###,##0.00')" /></td>

               <td align="right" class="abc"><xsl:text disable-output-escaping="yes">&amp;nbsp;</xsl:text><xsl:value-of select="format-number(VatAmount, '###,##0.00')" /></td>

               <td align="right" class="abc"><xsl:text disable-output-escaping="yes">&amp;nbsp;</xsl:text><xsl:value-of select="format-number(TotalAmount, '###,##0.00')" /></td>

               <td align="left" class="abc"><xsl:value-of select="Reveiver" /><xsl:text disable-output-escaping="yes">&amp;nbsp;</xsl:text></td>      

            </tr>     

        </xsl:for-each>

        <tr align="center">

          <td colspan="4"  class="abc">Total</td>

          <td class="abc"><b><xsl:value-of select="format-number(sum(InvoiceItem/CardAmount), '###,##0.00')" /><xsl:text disable-output-escaping="yes">&amp;nbsp;</xsl:text></b></td>

          <td class="abc"><b><xsl:value-of select="format-number(sum(InvoiceItem/BackAmount), '###,##0.00')" /><xsl:text disable-output-escaping="yes">&amp;nbsp;</xsl:text></b></td>         

          <td class="abc"><b><xsl:value-of select="format-number(sum(InvoiceItem/EnergyUnit), '###,##0.00')" /><xsl:text disable-output-escaping="yes">&amp;nbsp;</xsl:text></b></td>

          <td class="abc"><b><xsl:value-of select="format-number(sum(InvoiceItem/PurchaseAmount), '###,##0.00')" /><xsl:text disable-output-escaping="yes">&amp;nbsp;</xsl:text></b></td>

          <td class="abc"><b><xsl:value-of select="format-number(sum(InvoiceItem/VatAmount), '###,##0.00')" /><xsl:text disable-output-escaping="yes">&amp;nbsp;</xsl:text></b></td>

          <td class="abc"><b><xsl:value-of select="format-number(sum(InvoiceItem/TotalAmount), '###,##0.00')" /><xsl:text disable-output-escaping="yes">&amp;nbsp;</xsl:text></b></td>

          <td class="abc"><xsl:text disable-output-escaping="yes">&amp;nbsp;</xsl:text></td>         

        </tr>  

      </table>

    </td>

</tr>

</table>

</body>

</html>

</xsl:template>

</xsl:stylesheet>

3、使用tinyXML来解析XML,把查询数据存储到XML文件。

内容如下:

    <?xml version="1.0" encoding="GB2312" ?>

<?xml-stylesheet href="Receipt.xsl" type="text/xsl" ?>

<InvoiceReport>

    <PeaArea>bj</PeaArea>

    <AreaCode>0000</AreaCode>

    <TaxID>5469465890</TaxID>

    <StartDate>23/07/07</StartDate>

    <EndDate>23/08/07</EndDate>

    <IssueDate>23/08/50</IssueDate>

    <InvoiceItem>

        <PrintSn>print1</PrintSn>

        <ReceiptNo>50000000100</ReceiptNo>

        <PurchaseDate>2007-08-13</PurchaseDate>

        <PurchaseTime>16:08</PurchaseTime>

        <ConsumerID>0505185712000001</ConsumerID>

        <CardAmount>0.00</CardAmount>

        <BackAmount>0.00</BackAmount>

        <ExtraAmount>0.00</ExtraAmount>

        <EnergyUnit>0.00</EnergyUnit>

        <PurchaseAmount>1000.00</PurchaseAmount>

        <VatAmount>70.00</VatAmount>

        <TotalAmount>1070.00</TotalAmount>

        <Reveiver>Administrator</Reveiver>

    </InvoiceItem>

    <InvoiceItem>

        <PrintSn>print1</PrintSn>

        <ReceiptNo>50000000101</ReceiptNo>

        <PurchaseDate>2007-08-13</PurchaseDate>

        <PurchaseTime>17:08</PurchaseTime>

        <ConsumerID>0505185712000001</ConsumerID>

        <CardAmount>0.00</CardAmount>

        <BackAmount>0.00</BackAmount>

        <ExtraAmount>0.00</ExtraAmount>

        <EnergyUnit>0.00</EnergyUnit>

        <PurchaseAmount>1.00</PurchaseAmount>

        <VatAmount>0.07</VatAmount>

        <TotalAmount>1.07</TotalAmount>

        <Reveiver>Administrator</Reveiver>

    </InvoiceItem>

    <InvoiceItem>

        <PrintSn>print1</PrintSn>

        <ReceiptNo>50000000102</ReceiptNo>

        <PurchaseDate>2007-08-13</PurchaseDate>

        <PurchaseTime>17:08</PurchaseTime>

        <ConsumerID>0505185712000001</ConsumerID>

        <CardAmount>0.00</CardAmount>

        <BackAmount>0.00</BackAmount>

        <ExtraAmount>0.00</ExtraAmount>

        <EnergyUnit>0.00</EnergyUnit>

        <PurchaseAmount>100.00</PurchaseAmount>

        <VatAmount>7.00</VatAmount>

        <TotalAmount>107.00</TotalAmount>

        <Reveiver>Administrator</Reveiver>

    </InvoiceItem>     

</InvoiceReport>

4、报表结果:

在应用程序中使用了CHtmlCtrl控件类来加载XML浏览报表结果如图:

   

    显示很好,可以自动扩展,汇总等。

    主要代码:

    static CHtmlCtrl m_page;   

    VERIFY(m_page.CreateFromStatic(IDC_HTMLVIEW, this));

    m_page.Navigate2(m_url,NULL,NULL);

    5、打印报表预览如图:

   

    代码:

    ExecWB(OLECMDID_PRINTPREVIEW, OLECMDEXECOPT_PROMPTUSER, NULL, NULL);

6、存储为EXCEL如图:

代码:

VARIANT_BOOL    status;

    IXMLDOMDocument *pXMLDoc, *pXSLDoc;

    CoCreateInstance(CLSID_DOMDocument, NULL, CLSCTX_INPROC_SERVER,

        IID_IXMLDOMDocument, (void**)&pXMLDoc);

    CoCreateInstance(CLSID_DOMDocument, NULL, CLSCTX_INPROC_SERVER,

        IID_IXMLDOMDocument, (void**)&pXSLDoc);

    CString xmlFile = g_apppath + "report//Receipt.xml";

    CString xslFile = g_apppath + "report//Receipt.xsl";

    HRESULT hr = pXMLDoc->load(COleVariant((LPCSTR)xmlFile), &status);

    if (S_OK != hr) {

        AfxMessageBox("load error");

        return ;

    }

    hr = pXSLDoc->load(COleVariant((LPCSTR)xslFile), &status);

    if (S_OK != hr) {

        AfxMessageBox("load error");

        return ;

    }

    BSTR sHTML;  //存储转换后的数据

    hr = pXMLDoc->transformNode(pXSLDoc, &sHTML);

    if (S_OK != hr) {

        AfxMessageBox("transformNode error");

        return ;

    }

    //另存为EXCEL文件

TCHAR szSaveFileNames[MAX_PATH] = {0};

    OPENFILENAME ofn;

    ZeroMemory( &ofn, sizeof(ofn));

    ofn.lStructSize = sizeof(OPENFILENAME);

    ofn.hwndOwner = this->m_hWnd;

    ofn.lpstrFilter = TEXT("Microsoft Office Excel Files (*.xls)|*.xls");

    ofn.Flags = OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT | OFN_PATHMUSTEXIST | OFN_NOCHANGEDIR;

    ofn.lpstrFile = szSaveFileNames;

    ofn.nMaxFile = sizeof(szSaveFileNames);

    ofn.nFilterIndex = 1;

    ofn.lpstrFileTitle = NULL;

    ofn.nMaxFileTitle = 0;

    ofn.lpstrDefExt = TEXT("xls");

    CString strPath = g_apppath + "report";

    ofn.lpstrInitialDir = strPath;

 

    if ( !GetSaveFileName( &ofn ) )

        return;

           

    FILE *stream;

    if( (stream = fopen( szSaveFileNames, "w" )) == NULL ) {

        AfxMessageBox( "The file 'data' can not be written/n" );

        return ;

    }

    else {

        fprintf( stream, "%s", (CString)sHTML);

        fflush(stream);

        fclose(stream);

        AfxMessageBox("Save Successful!");         

    }

五、结论:

    很好的解决了问题。效果很好!

六、参考资料:

    tinyXMLXMLXSLCHtmlCtrlExecWBOPENFILENAME;

 
阅读更多
个人分类: XML
想对作者说点什么? 我来说一句

xml与xsl制作表格实例

2013年06月15日 1KB 下载

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭