将DataTable导出为Excel (XML Spreadsheet).

前阵子公司有同事需要将搜索页面的搜索结果导出成Excel文件. 用几个不同的办法做了出来. 最后部署时发现: 用COM+组件行不通, 服务器上没装OFFICE; 用OWC也不行, 因为服务器是64位的, OWC不支持; 导出成Excel2003支持的xml, 这招最灵活, 也是一个简单可行的办法.

首先用Excel 2003新建一个空白Wookbook, 然后保存为XML Spreadsheet. 然后打开看它生成的XML代码就能了解XML Spreadsheet的基本结构了.
我先把DataTable生成XML数据, 再使用一个XLS把XML数据转换成XML Spreadsheet的结构.

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Reflection;
using System.Text;
using System.Xml;
using System.Xml.Xsl;

namespace AProject
{
    public class ExcelHelper
    {
        public static string ExportAsTempFile(DataTable dataTable, bool appendColumnNames)
        {
            if(dataTable == null)
            {
                return null;
            }

            return ExportAsTempFile(new DataTable[] { dataTable }, appendColumnNames);
        }

        public static string ExportAsTempFile(DataSet dataSet, bool appendColumnNames)
        {
            string fileName = Path.GetTempFileName();

            if(ExportFile(dataSet, fileName, appendColumnNames))
            {
                return fileName;
            }

            return null;
        }

        public static string ExportAsTempFile(DataTable[] dataTables, bool appendColumnNames)
        {
            string fileName = Path.GetTempFileName();

            if(ExportFile(dataTables, fileName, appendColumnNames))
            {
                return fileName;
            }

            return null;
        }

        public static bool ExportFile(DataTable dataTable, string fileName, bool appendColumnNames)
        {
            if(dataTable == null)
            {
                return false;
            }

            return ExportFile(new DataTable[] { dataTable }, fileName, appendColumnNames);
        }

        public static bool ExportFile(DataSet dataSet, string fileName, bool appendColumnNames)
        {
            if(dataSet == null)
            {
                return false;
            }

            DataTable[] dataTables = new DataTable[dataSet.Tables.Count];
            dataSet.Tables.CopyTo(dataTables, 0);
            return ExportFile(dataTables, fileName, appendColumnNames);
        }

        public static bool ExportFile(DataTable[] dataTables, string fileName, bool appendColumnNames)
        {
            if(dataTables == null || dataTables.Length == 0 || string.IsNullOrEmpty(fileName))
            {
                return false;
            }

            XmlDocument xmlDoc = GetXmlDataTables(dataTables, appendColumnNames);
            XmlDocument xlsDoc = TransformXml(xmlDoc);

            try
            {
                xlsDoc.Save(fileName);
                return true;
            }
            catch
            {
                return false;
            }
        }

        private static XmlDocument GetXmlDataTables(DataTable[] dataTables, bool appendColumnNames)
        {
            if(dataTables == null)
            {
                return null;
            }

            XmlDocument xmlDoc = new XmlDocument();
            XmlElement rootNode = xmlDoc.CreateElement("DTS");
            XmlElement tableNode;
            XmlElement rowNode;
            XmlElement colNode;
            DataTable dt;

            for(int i = 0; i < dataTables.Length; i++)
            {
                dt = dataTables[i];

                if(dt == null)
                {
                    break;
                }

                if(dt.TableName.Trim() == string.Empty)
                {
                    dt.TableName = "DataTable" + i.ToString();
                }

                tableNode = xmlDoc.CreateElement("DT");
                tableNode.SetAttribute("N", dt.TableName);

                if(appendColumnNames)
                {
                    rowNode = xmlDoc.CreateElement("DR");

                    foreach(DataColumn dc in dt.Columns)
                    {
                        colNode = xmlDoc.CreateElement("DC");
                        colNode.SetAttribute("N", dc.ColumnName);
                        colNode.SetAttribute("T", "String");
                        colNode.AppendChild(xmlDoc.CreateTextNode(dc.ColumnName));
                        rowNode.AppendChild(colNode);
                    }

                    tableNode.AppendChild(rowNode);
                }


                foreach(DataRow dr in dt.Rows)
                {
                    rowNode = xmlDoc.CreateElement("DR");

                    foreach(DataColumn dc in dt.Columns)
                    {
                        colNode = xmlDoc.CreateElement("DC");
                        colNode.SetAttribute("N", dc.ColumnName);
                        colNode.SetAttribute("T", GetDataType(dc.DataType));
                        colNode.AppendChild(xmlDoc.CreateTextNode(GetTextValue(dc.DataType, dr[dc.ColumnName])));
                        rowNode.AppendChild(colNode);
                    }

                    tableNode.AppendChild(rowNode);
                }

                rootNode.AppendChild(tableNode);
            }

            xmlDoc.AppendChild(rootNode);
            return xmlDoc;
        }

        private static string GetTextValue(Type type, object value)
        {
            string text;

            if(type == typeof(DateTime))
            {
                text = ((DateTime)value).ToString("yyyy-MM-ddTHH:mm:ssZ");
            }
            else
            {
                text = value.ToString();
            }

            return text;
        }

        private static string GetDataType(Type type)
        {
            string dataType;

            if(type == typeof(string))
            {
                dataType = "String";
            }
            else if(type == typeof(DateTime))
            {
                dataType = "DateTime";
            }
            else if(type == typeof(bool))
            {
                dataType = "Boolean";
            }
            else
            {
                dataType = "Number";
            }

            return dataType;
        }

        private static XmlDocument TransformXml(XmlDocument xmlDoc)
        {
            XmlDocument xlsDoc = new XmlDocument();
            XslCompiledTransform xslt = new XslCompiledTransform();
            Assembly assembly = Assembly.GetExecutingAssembly();

            using(Stream s = assembly.GetManifestResourceStream("AProject.Resources.XmlSpreadsheet.xsl"))
            {
                if(s != null)
                {
                    xslt.Load(XmlReader.Create(s));
                    MemoryStream output = new MemoryStream();
                    XmlTextWriter xmlWriter = new XmlTextWriter(output, Encoding.UTF8);
                    xslt.Transform(xmlDoc, xmlWriter);
                    output.Position = 0;
                    xlsDoc.Load(output);
                    xlsDoc.PrependChild(xlsDoc.CreateXmlDeclaration("1.0", null, null));
                    output = null;
                }
            }

            return xlsDoc;
        }
    }
}

   xml version="1.0" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
    <xsl:output method='xml' version='1.0'/>
    <xsl:template match="DTS">
        <ss:Workbook >
            <ss:Styles>
                <ss:Style ss:ID="Default">
                    <ss:NumberFormat ss:Format="General"/>
                
    ss:Style>
                <ss:Style ss:ID="DateTime">
                    <ss:NumberFormat ss:Format="General Date"/>
                
     ss:Style>
            
      ss:Styles>
            <xsl:apply-templates select="DT" />
        
       ss:Workbook>
    
        xsl:template> <xsl:template match="DT"> <ss:Worksheet> <xsl:attribute name="ss:Name"> <xsl:value-of select="@N"/> 
         xsl:attribute> <ss:Table> <xsl:apply-templates select="DR" /> 
          ss:Table> 
           ss:Worksheet> 
            xsl:template> <xsl:template match="DR"> <ss:Row> <xsl:apply-templates select="DC" /> 
             ss:Row> 
              xsl:template> <xsl:template match="DC"> <ss:Cell> <xsl:choose> <xsl:when test="@T = 'DateTime'"> <xsl:attribute name="ss:StyleID"> <xsl:text>DateTime
               xsl:text> 
                xsl:attribute> 
                 xsl:when> 
                  xsl:choose> <ss:Data> <xsl:attribute name="ss:Type"> <xsl:value-of select="@T"/> 
                   xsl:attribute> <xsl:choose> <xsl:when test="@T = 'String'"> <xsl:text disable-output-escaping="yes"><![CDATA[
                    xsl:text> <xsl:value-of select="."/> <xsl:text disable-output-escaping="yes">]]>
                     xsl:text> 
                      xsl:when> <xsl:otherwise> <xsl:value-of select="."/> 
                       xsl:otherwise> 
                        xsl:choose> 
                         ss:Data> 
                          ss:Cell> 
                           xsl:template> 
                            xsl:stylesheet>

 

参考链接: XML Spreadsheet Reference