public void ExportExcel(string field, string fvalue){
//創建導出類
DAL.CExcelXMLWriter excel = new DAL.CExcelXMLWriter();
//獲取xsl模板
string templatePath = this.Request.PhysicalApplicationPath + "\\Template\\xsl\\Workcenter.xsl";
//獲取序列號把文件更名
string filePath = this.Request.PhysicalApplicationPath + "Temp\\Workcenter.xls";
//調用導出類生成Excel
string sql;
if (field == "(all*)" || fvalue =="" ){
sql = "select WorkCenter,Description,Plant,Category,convert(varchar,validdate,111) as ValidDate,CostCenter,MachineTon,MachineType,EfficiencyRate,MachineQty,Employee,QuotationRouting,wcGroup from setWorkcenter where active='A'";
} else {
sql = "select WorkCenter,Description,Plant,Category,convert(varchar,validdate,111) as ValidDate,CostCenter,MachineTon,MachineType,EfficiencyRate,MachineQty,Employee,QuotationRouting,wcGroup from setWorkcenter where active='A' and "+ field.Trim() +" like '%"+ fvalue.Trim() +"%' ";
}
DataSet ds = func.GetDataTable(sql);
// //加入表頭部分 Activity Price
// DataTable dt = new DataTable("title");
// dt.Columns.Add("Title");
// dt.Rows.Add(new object[] {"Workcenter"});
// ds.Tables.Add(dt);
//開始啟動導出
excel.BuildExcel(ds, filePath, templatePath);
//將導到的EXCEL 文件傳送到Client下載
DownLoadToClient(Page.Response, filePath,"Workcenter.xls");
//刪除導出的臨時文件
if (File.Exists(filePath)){
File.Delete(filePath);
}
}
public static void DownLoadToClient(System.Web.HttpResponse response, string filepath, string VirtualName) {
// if(realFilename.Trim()=="") {
// response.Write("<script>alert('Invalid filename!');</script>");
// return;
// }
//
try {
if(File.Exists(filepath)) {
FileInfo fi=new FileInfo(filepath);
response.Clear();
response.ClearHeaders();
response.Buffer=false;
response.AppendHeader("Content-Disposition","attachment;filename="+System.Web.HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(VirtualName)));
response.AppendHeader("Content-Length",fi.Length.ToString());
response.ContentType="application/octet-stream";
response.WriteFile(filepath);
response.Flush();
response.End();
}
}
catch(Exception){}
}
------------------------------------------------------------------------
以下為類文件
using System;
using System.Collections.Specialized;
using System.Text;
using System.Text.RegularExpressions;
using System.Xml;
using System.Xml.Xsl;
using System.Xml.XPath;
using System.Data;
using System.IO;
namespace DAL
{
/// <summary>
/// CExcelXMLWriter 的摘要描述。
/// </summary>
public class CExcelXMLWriter
{
public CExcelXMLWriter()
{
//
// TODO: 在此加入建構函式的程式碼
//
}
/// <summary>
/// 建立XML文檔格式文件(XSL)
/// </summary>
/// <param name="ds">要輸出的數據集</param>
/// <param name="XslPath">xsl文檔存放路徑</param>
private void GetXSLFile(DataSet ds,string XslPath, params string[] Titles)
{
string strColumn = "";
string strRow = "";
string dsName=ds.DataSetName;
string tableName=ds.Tables[0].TableName;
string header = dsName + "/" + tableName;
foreach(string title in Titles)
{
strColumn += "<th>" + title +"</th>" + "\r\n";
strRow += "<td>" + "<xsl:value-of select=" + "\"" + title + "\"" +"/>" + "</td>" + "\r\n";
}
// foreach(DataColumn clm in ds.Tables[0].Columns)
// {
// //特殊字符替換 <,>,",*,%,(,),& 替換
// //*************************************************
// //*************************************************
// // 符號 xml下的值 excel中的值
// // < -------- _x003C_ ------ <
// // > -------- _x003E_ ------ >
// // " -------- _x0022_ ------ "
// // * -------- _x002A_ ------ *
// // % -------- _x0025_ ------ %
// // & -------- _x0026_ ------ &
// // ( -------- _x0028_ ------ (
// // ) -------- _x0029_ ------ )
// // = -------- _x003D_ ------ =
// //*************************************************
// //*************************************************
//
// string strClmName = clm.ColumnName;
// string strRowName = clm.ColumnName;
//
// if(strClmName.IndexOf("&")!=-1)
// strClmName=strClmName.Replace("&","&");
// if(strClmName.IndexOf("<")!=-1)
// strClmName=strClmName.Replace("<","<");
// if(strClmName.IndexOf(">")!=-1)
// strClmName=strClmName.Replace(">",">");
// if(strClmName.IndexOf("\"")!=-1)
// strClmName=strClmName.Replace("\"",""");
//
// 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();
}
/// <summary>
/// 根據數據集，生成替換后的XML文檔
/// </summary>
/// <param name="ds">數據集</param>
/// <param name="XmlFilePath">XML文檔路徑</param>
private 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();
}
/// <summary>
/// 生成EXCEL文檔
/// </summary>
/// <param name="path">EXCEL輸出完整路徑</param>
/// <param name="ds">數據集</param>
public bool BuildExcel(DataSet ds,string path, params string[] Titles)
{
bool flag = false;
if(File.Exists(path))
{
File.Delete(path);
}
string _path = path.Substring(0,path.Length-4);
string _fileXml=_path + ".xml";
string _fileXsl=_path + ".xsl";
string _fileXls=_path+".xls";
try
{
GetXmlFile(ds,_fileXml);
GetXSLFile(ds,_fileXsl,Titles);
XmlDocument doc = new XmlDocument();
doc.Load(_fileXml);
XslTransform xslt = new XslTransform();
xslt.Load(_fileXsl);
XmlElement root = doc.DocumentElement;
XPathNavigator nav = root.CreateNavigator();
XmlTextWriter writer = new XmlTextWriter(_fileXls, null);
xslt.Transform(nav, null, writer, null);
writer.Close();
File.Delete(_fileXml);
File.Delete(_fileXsl);
flag = true;
}
catch
{
throw;
}
return flag;
}
/// <summary>
/// 生成EXCEL文檔
/// </summary>
/// <param name="path">EXCEL輸出完整路徑</param>
/// <param name="ds">數據集</param>
public bool BuildExcel(DataSet ds, string tempPath, string xslPath)
{
bool flag = false;
if(File.Exists(tempPath))
{
File.Delete(tempPath);
}
string _path = tempPath.Substring(0,tempPath.Length-4);
string _fileXml=_path + ".xml";
string _fileXsl=_path + ".xsl";
string _fileXls=_path+".xls";
try
{
GetXmlFile(ds,_fileXml);
XmlDocument doc = new XmlDocument();
doc.Load(_fileXml);
XslTransform xslt = new XslTransform();
xslt.Load(xslPath);
XmlElement root = doc.DocumentElement;
XPathNavigator nav = root.CreateNavigator();
XmlTextWriter writer = new XmlTextWriter(_fileXls, null);
xslt.Transform(nav, null, writer, null);
writer.Close();
File.Delete(_fileXml);
flag = true;
}
catch(Exception ex)
{
string msg = ex.Message;
throw;
}
return flag;
}
/// <summary>
/// 生成EXCEL文檔
/// </summary>
/// <param name="path">EXCEL輸出完整路徑</param>
/// <param name="ds">數據集</param>
public bool BuildExcelWithSheets(DataSet ds, string tempPath, string xslPath)
{
bool flag = false;
if (File.Exists(tempPath))
{
File.Delete(tempPath);
}
string _path = tempPath.Substring(0, tempPath.Length - 4);
string _fileXml = _path + ".xml";
string _fileXsl = _path + ".xsl";
string _fileXls = _path + ".xls";
string _fileXsd = _path + ".xsd";
try
{
GetXmlFile(ds, _fileXml);
XmlDocument doc = new XmlDocument();
doc.Load(_fileXml);
XslTransform xslt = new XslTransform();
xslt.Load(xslPath);
XmlElement root = doc.DocumentElement;
XPathNavigator nav = root.CreateNavigator();
XmlTextWriter writer = new XmlTextWriter(_fileXsd, null);
xslt.Transform(nav, null,writer,null) ;
writer.Close();
File.Delete(_fileXml);
StreamReader rd = new StreamReader(_fileXsd, Encoding.UTF8);
string ss = rd.ReadToEnd();
ss = ss.Replace("<", "<");
ss = ss.Replace(">", ">");
rd.Close();
StreamWriter sw = new StreamWriter(_fileXls);
sw.Write(ss);
File.Delete(_fileXsd);
sw.Close();
flag = true;
}
catch (Exception ex)
{
string msg = ex.Message;
throw;
}
return flag;
}
}
}