导出导入Excel(DataSet,DataGrid)

using System;
using System.Data;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Text.RegularExpressions;
using System.Xml;
using System.Xml.Xsl;
using System.IO;
using System.Xml.XPath;

namespace Document.Bll
{
    /**//// <summary>
    /// Summary description for ExportExcel.
    /// </summary>
    public class ExportExcel
    {
        public ExportExcel()
        {
            
        }
        GetXslFile#region GetXslFile
        private static void GetXslFile(DataSet ds,string xslPath)
        {
            string strColumn = "";
            string strRow    = "";
            string dsName    = ds.DataSetName;
            string tableName = ds.Tables[0].TableName;
            string header    = dsName + "/" + tableName;

            foreach(DataColumn clm in ds.Tables[0].Columns)
            {
                //特殊字符 <,>,",*,%,(,),& 替换
                //*************************************************
                //*************************************************
                // 符号         xml下的值      excel中的值
                //  < --------  _x003C_  ------ &lt;
                //  > --------　_x003E_  ------ &gt;
                //  " --------  _x0022_  ------ &quot;
                //  * --------  _x002A_  ------ *
                //  % --------  _x0025_  ------ %
                //  & --------  _x0026_  ------ &amp;
                //  ( --------  _x0028_  ------ (
                //  ) --------  _x0029_  ------ )
                //  = --------  _x003D_  ------ = 
                //*************************************************
                //*************************************************

                string strClmName = clm.ColumnName;
                string strRowName = clm.ColumnName;

                if(strClmName.IndexOf("&")!=-1)
                {
                    strClmName = strClmName.Replace("&","&amp;");
                }
                if(strClmName.IndexOf("<")!=-1)
                {
                    strClmName = strClmName.Replace("<","&lt;");
                }
                if(strClmName.IndexOf(">")!=-1)
                {
                    strClmName=strClmName.Replace(">","&gt;");
                }
                if(strClmName.IndexOf("\"")!=-1)
                {
                    strClmName=strClmName.Replace("\"","&quot;");
                }
                if(strRowName.IndexOf("<")!=-1)
                {
                    strRowName=strRowName.Replace("<","_x003C_");
                }
                if(strRowName.IndexOf(">")!=-1)
                {
                    strRowName=strRowName.Replace(">","_x003E_");
                }
                if(strRowName.IndexOf("\"")!=-1)
                {
                    strRowName=strRowName.Replace("\"","_x0022_");
                }
                if(strRowName.IndexOf("*")!=-1)
                {
                    strRowName=strRowName.Replace("*","_x002A_");
                }
                if(strRowName.IndexOf("%")!=-1)
                {
                    strRowName=strRowName.Replace("%","_x0025_");
                }
                if(strRowName.IndexOf("&")!=-1)
                {
                    strRowName=strRowName.Replace("&","_x0026_");
                }
                if(strRowName.IndexOf("(")!=-1)
                {
                    strRowName=strRowName.Replace("(","_x0028_");
                }
                if(strRowName.IndexOf(")")!=-1)
                {
                    strRowName=strRowName.Replace(")","_x0029_");
                }
                if(strRowName.IndexOf("=")!=-1)
                {
                    strRowName=strRowName.Replace("=","_x003D_");
                }


                strColumn += "<th>" + strClmName +"</th>" + "\r\n"; 
                strRow += "<td>" + "<xsl:value-of select=" + "\"" + strRowName + "\"" +"/>" + "</td>" + "\r\n";
            }

            string str = @"<xsl:stylesheet version=""1.0"" xmlns:xsl=""http://www.w3.org/1999/XSL/Transform"">
            <xsl:template match=""/"">
            <html xmlns:o=""urn:schemas-microsoft-com:office:office"" xmlns:x=""urn:schemas-microsoft-com:office:excel"" xmlns=""http://www.w3.org/TR/REC-html40""> 
            <head> 
            <meta http-equiv=""Content-Type"" content=""text/html;charset=utf-8"" /> 
            <style> 
            .xl24{mso-style-parent:style0;mso-number-format:""\@"";text-align:right;} 
            </style> 
            <xml> 
            <x:ExcelWorkbook> 
            <x:ExcelWorksheets> 
            <x:ExcelWorksheet> 
            <x:Name>Sheet1</x:Name> 
            <x:WorksheetOptions> 
    <x:ProtectContents>False</x:ProtectContents> 
    <x:ProtectObjects>False</x:ProtectObjects> 
    <x:ProtectScenarios>False</x:ProtectScenarios> 
            </x:WorksheetOptions> 
            </x:ExcelWorksheet> 
            </x:ExcelWorksheets> 
            </x:ExcelWorkbook> 
            </xml> 
            </head>  
            <body> ";
            str += "\r\n" + @"<table border=""1"" cellpadding=""0"" cellspacing=""0""> 
    <tr>" + "\r\n";
            str += strColumn;
            str += @" </tr> 
    <xsl:for-each select="""+header+@""">
    <tr>";
            str += "\r\n" + strRow;
            str += @"</tr> 
    </xsl:for-each> 
    </table> 
    </body> 
    </html> 
      
     
    </xsl:template> 
    </xsl:stylesheet> ";

            string path =xslPath;
            if(File.Exists(path))
            {
                File.Delete(path);
            }
            FileStream fs = File.Create(path);
            StreamWriter sw=new StreamWriter(fs);
            sw.Write(str);
            sw.Close();
            fs.Close();

        }

        #endregion

        GetXmlFile#region GetXmlFile
        private static void GetXmlFile(DataSet ds,string xmlFilePath)
        {
            string strXml = ds.GetXml();
            if(File.Exists(xmlFilePath))
            {
                File.Delete(xmlFilePath);
            }
            FileStream fs1 = File.Create(xmlFilePath);
            StreamWriter writer = new StreamWriter(fs1);
            writer.Write(strXml);
            writer.Close();
            fs1.Close();
        }

        #endregion

        BuildExcel#region BuildExcel
        private static void BuildExcel(DataSet ds,string path)
        {
            if(File.Exists(path))
            {
                File.Delete(path);
            }
            string m_path    = path.Substring(0,path.Length-4);
            string m_fileXml = m_path + ".xml";
            string m_fileXsl = m_path + ".xsl";
            string m_fileXls = m_path + ".xls";

            try
            {
                GetXmlFile(ds,m_fileXml);
                GetXslFile(ds,m_fileXsl);

                //Excel changed
                XmlDocument doc = new XmlDocument();
                doc.Load(m_fileXml);
                XslTransform xslt = new XslTransform();
                xslt.Load(m_fileXsl);
                XmlElement root = doc.DocumentElement;
                XPathNavigator nav = root.CreateNavigator();
                XmlTextWriter writer = new XmlTextWriter(m_fileXls,null);
                xslt.Transform(nav,null,writer,null);
                writer.Close();
                File.Delete(m_fileXml);
                File.Delete(m_fileXsl);
            }
            catch
            {
                throw;
            }
        }

        #endregion

        ToExcel#region ToExcel
        public static void ToExcel(System.Web.UI.Control ctl,string FileName)
        {
            HttpContext.Current.Response.Charset ="UTF-8";
            HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.Default;
            HttpContext.Current.Response.ContentType ="application/ms-excel";
            HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename="+""+FileName+".xls");
            ctl.Page.EnableViewState =false;
            System.IO.StringWriter  tw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
            ctl.RenderControl(hw);
            HttpContext.Current.Response.Write(tw.ToString());
            HttpContext.Current.Response.End();
        }        
        #endregion

        DownloadFile#region DownloadFile
        public static void DownloadFile(string physicalFilePath)
        {
            FileStream stream=null;
            try 
            {
                stream = new FileStream(physicalFilePath, FileMode.Open, FileAccess.Read, FileShare.Read);    
                int bufSize = (int)stream.Length;
                byte[] buf = new byte[bufSize];

                int bytesRead = stream.Read(buf, 0, bufSize);
                HttpContext.Current.Response.ContentType = "application/octet-stream"; 
                HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename="+System.IO.Path.GetFileName(physicalFilePath));
                HttpContext.Current.Response.OutputStream.Write(buf, 0, bytesRead);
                HttpContext.Current.Response.End();
            }
            finally 
            {
                stream.Close();
            }
        }
        #endregion
    }
}