用Xsl转换XMl生成Excel(可保留格式,使用StringTemplate模板引擎)

此技术需求:
1.office2003需要它将模板导出生成xml文件.(点击文件->保存,选择xml表格保存方式)
2.将此Xml改为Xslt文件,并保存为st结尾的模板文件
3.用StringTemplate读取这个文件,在内存中生成一个Xslt文件
这个模板引敬可到下面网站下载
http://www.stringtemplate.org/download.html
4.用Xslt将一个DataTable的数据对象转换为一个Xml字符串.
5.用生成文本的方式将Xml字符串生成一个Excel文件.

using System;
using System.Collections.Generic;
using System.Net.Mime;
using System.Text;

using System.Data.SqlClient;
using System.Data;
using DataTable=System.Data.DataTable;
using System.IO;
using System.Xml;
using System.Xml.Xsl;
using System.Threading;
using StringTemplate = Antlr.StringTemplate.StringTemplate;
using StringTemplateGroup = Antlr.StringTemplate.StringTemplateGroup;

namespace OPSS.Public.Common.Excel
{
    ///此是一个将DataTable转换为(xml格式)字符串的Excel帮助文件
    public class ExcelHelper
    {
          #region 生成Excel
         /// <summary>
            /// 生成Excel字符串，保存在当前对象的strPrintf中,从外部导入生成Excel或CSV格式的数据第一种方式
        /// </summary>
        /// <param name="DetailsTable">生成Excel的表</param>
        /// <param name="templePath">格式化模板相对路径</param>
        public static string ExportDetails(DataTable DetailsTable, string templePath)
        {
            try
            {
                if (DetailsTable.Rows.Count == 0)
                    throw new Exception(DetailsTable.TableName + "数据不存在");

                // 创建Dataset
                DataSet dsExport = new DataSet("Export");
                DataTable dtExport = DetailsTable.Copy();
                dtExport.TableName = "Values";
                dsExport.Tables.Add(dtExport);

                // 获取字段名
                string[] sHeaders = new string[dtExport.Columns.Count];
                string[] sFileds = new string[dtExport.Columns.Count];

                for (int i = 0; i < dtExport.Columns.Count; i++)
                {
                    sHeaders[i] = dtExport.Columns[i].ColumnName;
                    sFileds[i] = dtExport.Columns[i].ColumnName;
                }

                return Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, templePath);
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

       
        /// <summary>
        /// 从外部导入生成Excel或CSV格式的数据第二种方式
        /// <param name="DetailsTable">生成Excel的表</param>
        /// <param name="ColumnList">控制生成Excel的列表的顺序</param>
        /// <param name="templePath">格式化模板相对路径</param>
        /// </summary>
        public static string ExportDetails(DataTable DetailsTable, int[] ColumnList, string templePath)
        {
            try
            {
                if (DetailsTable.Rows.Count == 0)
                    throw new Exception(DetailsTable.TableName + "数据不存在");

                // 创建Dataset
                DataSet dsExport = new DataSet("Export");
                DataTable dtExport = DetailsTable.Copy();
                dtExport.TableName = "Values";
                dsExport.Tables.Add(dtExport);

                if (ColumnList.Length > dtExport.Columns.Count)
                    throw new Exception("ExportColumn List超出Columns数据");

                // 获取字段名
                string[] sHeaders = new string[ColumnList.Length];
                string[] sFileds = new string[ColumnList.Length];

                for (int i = 0; i < ColumnList.Length; i++)
                {
                    if ((ColumnList[i] < 0) || (ColumnList[i] >= dtExport.Columns.Count))
                        throw new Exception("ExportColumn List不在Columns范围");

                    sHeaders[i] = dtExport.Columns[ColumnList[i]].ColumnName;
                    sFileds[i] = dtExport.Columns[ColumnList[i]].ColumnName;
                }
                return Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, templePath);
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        /// <summary>
        /// 从外部导入生成Excel或CSV格式的数据第三种方式
        /// <param name="DetailsTable">生成Excel的表</param>
        /// <param name="ColumnList">控制生成Excel的列表的顺序</param>
        /// <param name="ColumnList">控制生成Excel的列头</param>
        /// <param name="templePath">格式化模板相对路径</param>
        /// </summary>
        public static string ExportDetails(DataTable DetailsTable, int[] ColumnList, string[] sHeaders, string templePath)
        {
            try
            {
                if (DetailsTable.Rows.Count == 0)
                    throw new Exception(DetailsTable.TableName + "数据不存在");

                // 创建Dataset
                DataSet dsExport = new DataSet("Export");
                DataTable dtExport = DetailsTable.Copy();
                dtExport.TableName = "Values";
                dsExport.Tables.Add(dtExport);

                if (ColumnList.Length != sHeaders.Length)
                    throw new Exception("ExportColumn List与 Columns数据长度不一致");
                else if (ColumnList.Length > dtExport.Columns.Count || sHeaders.Length > dtExport.Columns.Count)
                    throw new Exception("ExportColumn List超出 Columns数据");
                // 获取字段名
                string[] sFileds = new string[ColumnList.Length];

                for (int i = 0; i < ColumnList.Length; i++)
                {
                    if ((ColumnList[i] < 0) || (ColumnList[i] >= dtExport.Columns.Count))
                        throw new Exception("ExportColumn List不在Columns范围");
                    sFileds[i] = dtExport.Columns[ColumnList[i]].ColumnName;
                }

                return Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, templePath);
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }
        #endregion

       
        #region Export_with_XSLT_Windows
        /// <summary>
        /// 在windows方式下生成的Excel或csv文档方式
        /// </summary>
        /// <param name="dsExport">数据集</param>
        /// <param name="sHeaders">列头</param>
        /// <param name="sFileds">列字段</param>
        /// <param name="templePath">格式化模板相对路径</param>>
        private static string Export_with_XSLT_Windows(DataSet dsExport, string[] sHeaders, string[] sFileds, string templePath)
        {
            MemoryStream ms=null;
            TextWriter tr = null;
            System.IO.StringWriter sw = null;
           
            try
            {
                // XSLT to use for transforming this dataset. 
                string xmlStr = CreateStylesheet(sHeaders, sFileds, templePath);

               
                ms = new MemoryStream();
                tr = new StreamWriter(ms, Encoding.Default);
               
                tr.WriteLine(xmlStr);
                tr.Flush();
                ms.Flush();
                ms.Seek(0, SeekOrigin.Begin);
               
                XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
                XslCompiledTransform xslTran = new XslCompiledTransform();
                xslTran.Load(new XmlTextReader(ms), null, null);
               
               
                sw = new System.IO.StringWriter();
                xslTran.Transform(xmlDoc, null, sw);
                xmlStr = sw.ToString().Replace("utf-16","GB2312");
               
                return xmlStr;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
            finally
            {
                ms.Close();
                tr.Close();
                sw.Close();
            }
        }

        #endregion
       

        #region CreateStylesheet
        /// <summary>
        /// 读取模板将数据用xml方式存储
        /// <param name="sHeaders">列头</param>
        /// <param name="sFileds">列字段</param>
        /// <param name="templePath">格式化模板相对路径</param>>
        /// </summary>
        private static string CreateStylesheet(string[] sHeaders, string[] sFileds, string templePath)
        {
            string absoluteSkinRootDirectoryName = Path.Combine(new DirectoryInfo(AppDomain.CurrentDomain.BaseDirectory).Parent.Parent.FullName, templePath.Substring(0, templePath.LastIndexOf("\\")));
       
        try
        {
            StringTemplateGroup templates = new StringTemplateGroup("test", absoluteSkinRootDirectoryName);
            StringTemplate page1ST = templates.GetInstanceOf(Path.GetFileNameWithoutExtension(templePath));

            for (int i = 0; i < sHeaders.Length;i++ )
            {
                page1ST.SetAttribute("colHead" + i.ToString(), sHeaders[i]);
            }
            for (int i = 0; i < sFileds.Length; i++)
            {
                page1ST.SetAttribute("colName" + i.ToString(), sFileds[i]);
            }  
            return page1ST.ToString();
        }
       
        catch (Exception e)
        {
            throw new Exception("生成Excel文档错误!", e);
        }
        finally { }
    }
        #endregion   
    }
}


========================================================
此是一个Xslt模板文件

<?xml version="1.0" encoding="GB2312"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:fn="http://www.w3.org/2005/xpath-functions" xmlns:xdt="http://www.w3.org/2005/xpath-datatypes" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns="urn:schemas-microsoft-com:office:spreadsheet">
 <xsl:output method="xml" version="1.0" encoding="GB2312" indent="yes"/>
 <xsl:template match="/">
  <?mso-application progid="Excel.Sheet"?>
   <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns:x="urn:schemas-microsoft-com:office:excel"
    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:html="http://www.w3.org/TR/REC-html40"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:fn="http://www.w3.org/2005/xpath-functions"
    xmlns:xdt="http://www.w3.org/2005/xpath-datatypes">
    <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
     <Author>lk</Author>
     <LastAuthor>FtpDown</LastAuthor>
     <Created>2006-07-31T08:11:57Z</Created>
     <LastSaved>2006-08-01T01:56:32Z</LastSaved>
     <Company>www.ftpdown.com</Company>
     <Version>11.5606</Version>
    </DocumentProperties>
    <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
     <WindowHeight>6975</WindowHeight>
     <WindowWidth>14940</WindowWidth>
     <WindowTopX>240</WindowTopX>
     <WindowTopY>75</WindowTopY>
     <ProtectStructure>False</ProtectStructure>
     <ProtectWindows>False</ProtectWindows>
    </ExcelWorkbook>
    <Styles>
     <Style ss:ID="Default" ss:Name="Normal">
      <Alignment ss:Vertical="Center"/>
      <Borders/>
      <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>
      <Interior/>
      <NumberFormat/>
      <Protection/>
     </Style>
     <Style ss:ID="m28922332">
      <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
      <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
      </Borders>
      <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12" ss:Bold="1"/>
     </Style>
     <Style ss:ID="s21">
      <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
      <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
      </Borders>
      <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12" ss:Bold="1"/>
     </Style>
     <Style ss:ID="s22">
      <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
      <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
      </Borders>
      <NumberFormat ss:Format="Standard"/>
     </Style>
     <Style ss:ID="s23">
      <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
      <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
      </Borders>
     </Style>
     <Style ss:ID="s24">
      <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
      <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
      </Borders>
      <NumberFormat ss:Format="Standard"/>
     </Style>
     <Style ss:ID="s30">
      <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
      <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
      </Borders>
      <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12" ss:Bold="1"/>
      <NumberFormat ss:Format="Standard"/>
     </Style>
    </Styles>
    <Worksheet ss:Name="Sheet1">
     <Table ss:ExpandedColumnCount="7" ss:ExpandedRowCount="7" x:FullColumns="1"
      x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
      <Column ss:AutoFitWidth="0" ss:Width="66.75"/>
      <Column ss:AutoFitWidth="0" ss:Width="77.25"/>
      <Column ss:AutoFitWidth="0" ss:Width="108.75"/>
      <Column ss:AutoFitWidth="0" ss:Width="102"/>
      <Column ss:AutoFitWidth="0" ss:Width="90"/>
      <Column ss:AutoFitWidth="0" ss:Width="114"/>
      <Column ss:AutoFitWidth="0" ss:Width="88.5"/>
      <Row ss:AutoFitHeight="0" ss:Height="15"/>
     
      <Row ss:AutoFitHeight="0" ss:Height="15">
    <Cell ss:Index="2" ss:StyleID="s21"><Data ss:Type="String">$colHead0$</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String">$colHead1$</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String">$colHead2$</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String">$colHead3$</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String">$colHead4$</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String">$colHead5$</Data></Cell>
      </Row>
      <xsl:apply-templates select="Export/Values" ></xsl:apply-templates>
     </Table>
     <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
      <Unsynced/>
      <Selected/>
      <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>12</ActiveRow>
     <ActiveCol>5</ActiveCol>
    </Pane>
      </Panes>
      <ProtectObjects>False</ProtectObjects>
      <ProtectScenarios>False</ProtectScenarios>
     </WorksheetOptions>
    </Worksheet>
   </Workbook>
 </xsl:template>
 
  <!-- 数据行模板 -->
 <xsl:template match="Export/Values">
   <xsl:for-each select=".">
   <xsl:choose>
    <!-- 结尾统计行模板 --> 
    <xsl:when test="self::node()[$colName0$=' ']">
      <Row ss:AutoFitHeight="0">
      <Cell ss:Index="2" ss:MergeAcross="1" ss:StyleID="m28922332"><Data
        ss:Type="String"><xsl:value-of select="$colName1$" ></xsl:value-of></Data></Cell>
      <Cell ss:StyleID="s30"><Data ss:Type="Number"><xsl:value-of select="$colName2$" ></xsl:value-of></Data></Cell>
      <Cell ss:StyleID="s30"><Data ss:Type="Number"><xsl:value-of select="$colName3$" ></xsl:value-of></Data></Cell>
      <Cell ss:StyleID="s30"><Data ss:Type="Number"><xsl:value-of select="$colName4$" ></xsl:value-of></Data></Cell>
      <Cell ss:StyleID="s30"><Data ss:Type="Number"><xsl:value-of select="$colName5$" ></xsl:value-of></Data></Cell>
       </Row>
    </xsl:when>
    <xsl:otherwise>
     <Row ss:AutoFitHeight="0" ss:Height="15">
      <Cell ss:Index="2" ss:StyleID="s22"><Data ss:Type="String"><xsl:value-of select="$colName0$" ></xsl:value-of></Data></Cell>
      <Cell ss:StyleID="s23"><Data ss:Type="String"><xsl:value-of select="$colName1$" ></xsl:value-of></Data></Cell>
      <Cell ss:StyleID="s22"><Data ss:Type="Number"><xsl:value-of select="$colName2$" ></xsl:value-of></Data></Cell>
      <Cell ss:StyleID="s24"><Data ss:Type="Number"><xsl:value-of select="$colName3$" ></xsl:value-of></Data></Cell>
      <Cell ss:StyleID="s24"><Data ss:Type="Number"><xsl:value-of select="$colName4$" ></xsl:value-of></Data></Cell>
      <Cell ss:StyleID="s24"><Data ss:Type="Number"><xsl:value-of select="$colName5$" ></xsl:value-of></Data></Cell>
       </Row>
    </xsl:otherwise>
   </xsl:choose> 
    
   </xsl:for-each>   
 </xsl:template>
</xsl:stylesheet>



cs下取的工程路径
new DirectoryInfo(AppDomain.CurrentDomain.BaseDirectory).Parent.Parent.FullName