我的番茄炒蛋
生活如此精彩,挑战无处不在!

导航

统计
 
public class ExportExcel
{
    
public
 ExportExcel()
    
{
            
    }


    
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();

    }


    
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();
    }


    
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
;
        }

    }


    
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();
    }
        

    
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();
        }

    }

}
posted on 2005-12-17 10:12  bluesky  阅读(...)  评论(...编辑  收藏