Xslt – MyNewBlog

這裡是asp.net Code的部份，這裡作了一個Excel檔案。

protected string ExportExeclByXSLT_Direct(DataSet dsReportData)
{
string fileName = “././DownLoadFile/ExcelExport.xls";
string xslPath = “././Xsl_Template/Excel.xslt";
string xlsPath = “././DownLoadFile/ExcelExport.xls";
StreamWriter oExcelWriter = System.IO.File.CreateText(Server.MapPath(xlsPath));
try
{
XmlDataDocument xddData = new XmlDataDocument(dsReportData);
XslCompiledTransform xt = new XslCompiledTransform();
xt.Load(Server.MapPath(xslPath));
xt.Transform(xddData, null, oExcelWriter);
return fileName;
}
catch (Exception ex)
{
return “";
}
finally
{
oExcelWriter.Close();
}
}

這裡是Excel下載的頁面(DownLoad.aspx)

protected void Page_Load(object sender, EventArgs e)
{
NameValueCollection coll = Request.QueryString;
String strRequest = coll[“file"];
if (strRequest != “")
{
String path = Server.MapPath(strRequest);
FileInfo file = new FileInfo(path);
StreamReader sr = null;
try
{
if (file.Exists)
{
//Method-1 將檔案資料讀入Stream,這時檔案已經可以刪除了..之後再把檔案內容輸出即可
//Read File Content
sr = new StreamReader(path);
Response.Clear();
Response.AddHeader(“Content-Disposition", “attachment; filename=ExcelExport.xls");
Response.AddHeader(“Content-Length", file.Length.ToString());
Response.ContentType = “application/vnd.ms-excel";
Response.Write(sr.ReadToEnd());
Response.End();

//Method-2 這是第二個方法,直接把檔案內容寫到前端,此時是不允許將檔案給刪除的,因為檔案是讀取的目標
//Response.Clear();
//Response.AddHeader(“Content-Disposition", “attachment; filename=ExcelExport.xls");
//Response.AddHeader(“Content-Length", file.Length.ToString());
//Response.ContentType = “application/vnd.ms-excel";
//Response.WriteFile(file.FullName);
//Response.End();

}

}
catch
{

}
finally
{
if (sr != null) sr.Close(); //Close Reader 注意:要記得把連接關閉
if (file.Exists)file.Delete(); //Delete Temp DownLoad File
}
}
}

這裡是XSLT的部份，負責將DataSet轉換成Excel的XML資料

<xsl:stylesheet version="1.0″
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" >

<xsl:template match="*">
<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<ss:Worksheet ss:Name="Main">
<ss:Table>
<!–資料標題–>
<ss:Row>
<xsl:for-each select="Main[position() = 1]/*">
<ss:Cell>
<ss:Data ss:Type="String">
<xsl:value-of select="local-name()"/>
</ss:Data>
</ss:Cell>
</xsl:for-each>
</ss:Row>
<xsl:for-each select="Main">
<!–資料內容–>
<ss:Row>
<xsl:for-each select="*">
<ss:Cell>
<ss:Data ss:Type="String">
<xsl:value-of select="."/>
</ss:Data>
</ss:Cell>
</xsl:for-each>
</ss:Row>
</xsl:for-each>
</ss:Table>
</ss:Worksheet>
<ss:Worksheet ss:Name="HQ_Detail">
<ss:Table>
<!–資料標題–>
<ss:Row>
<xsl:for-each select="HQ_Detail[position() = 1]/*">
<ss:Cell>
<ss:Data ss:Type="String">
<xsl:value-of select="local-name()"/>
</ss:Data>
</ss:Cell>
</xsl:for-each>
</ss:Row>
<xsl:for-each select="HQ_Detail">
<ss:Row>
<xsl:for-each select="*">
<ss:Cell>
<ss:Data ss:Type="String">
<xsl:value-of select="."/>
</ss:Data>
</ss:Cell>
</xsl:for-each>
</ss:Row>
</xsl:for-each>
</ss:Table>
</ss:Worksheet>
<ss:Worksheet ss:Name="NL_Detail">
<ss:Table>
<!–資料標題–>
<ss:Row>
<xsl:for-each select="NL_Detail[position() = 1]/*">
<ss:Cell>
<ss:Data ss:Type="String">
<xsl:value-of select="local-name()"/>
</ss:Data>
</ss:Cell>
</xsl:for-each>
</ss:Row>
<xsl:for-each select="NL_Detail">
<ss:Row>
<xsl:for-each select="*">
<ss:Cell>
<ss:Data ss:Type="String">
<xsl:value-of select="."/>
</ss:Data>
</ss:Cell>
</xsl:for-each>
</ss:Row>
</xsl:for-each>
</ss:Table>
</ss:Worksheet>
<ss:Worksheet ss:Name="CH_Detail">
<ss:Table>
<!–資料標題–>
<ss:Row>
<xsl:for-each select="CH_Detail[position() = 1]/*">
<ss:Cell>
<ss:Data ss:Type="String">
<xsl:value-of select="local-name()"/>
</ss:Data>
</ss:Cell>
</xsl:for-each>
</ss:Row>
<xsl:for-each select="CH_Detail">
<ss:Row>
<xsl:for-each select="*">
<ss:Cell>
<ss:Data ss:Type="String">
<xsl:value-of select="."/>
</ss:Data>
</ss:Cell>
</xsl:for-each>
</ss:Row>
</xsl:for-each>
</ss:Table>
</ss:Worksheet>

</ss:Workbook>
</xsl:template>

</xsl:stylesheet>

private int ExportExcel(string strCondition)

{

//Get Export Data

ORDER_REQUEST objOR = new ORDER_REQUEST();

DataSet dsResult = new DataSet();

objOR.GetList_ModelExport(strCondition, out dsResult); //DataSetName = “NewDataSet" , TableName = “data"

//Check Data Existed Or Not

if (dsResult != null && dsResult.Tables.Count != 0 && dsResult.Tables[0].Rows.Count > 0)

{

//Get FileName

Random rd = new Random();

String filePathHtml = “././DownLoadFile/" + Convert.ToString(rd.Next()) + “.html";

String filePathExcel = filePathHtml.Replace(“html", “xls");

//Create the FileStream to write with.

using (FileStream fs = new System.IO.FileStream(Server.MapPath(filePathHtml), FileMode.Create))

{

//Create an XmlTextWriter for the FileStream.

using (XmlTextWriter xtw = new System.Xml.XmlTextWriter(fs, System.Text.Encoding.Unicode))

{

//Transform the XML To HTML Using the stylesheet.

XmlDataDocument xmlDoc = new XmlDataDocument(dsResult);

XslCompiledTransform xslTran = new XslCompiledTransform();

xslTran.Load(Server.MapPath(“ExcelFormat.xslt"));

xslTran.Transform(xmlDoc, null, xtw);

xtw.Close();

}

fs.Close();

}

StreamReader sr = new StreamReader(Server.MapPath(filePathHtml), Encoding.UTF8);

//StringWriter stringWrite = new StringWriter();

//HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

String content = “";

content = sr.ReadToEnd();

content = content.Replace(“t", “");

content = content.Replace(“r", “");

content = content.Replace(“n", “");

//Delete HTML File

sr.Close();

FileInfo file = new FileInfo(Server.MapPath(filePathHtml));

if (file.Exists) file.Delete();

//Create EXCEL File For DownLoad

System.IO.StreamWriter oExcelWriter = System.IO.File.CreateText(Server.MapPath(filePathExcel));

oExcelWriter.WriteLine(content);

oExcelWriter.Close();

//User Can Download From the Other Page

Response.Redirect(“DownLoad.aspx?file=" + filePathExcel);

}

//Do Nothing

return 0;

}

<code>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0″>

<xsl:template match="/">

<HTML>

<HEAD>

<STYLE>

.HDR { background-color:bisque;font-weight:bold }

</STYLE>

</HEAD>

<BODY>

<TABLE>

<TD CLASS="HDR">接單需求代號</TD>

<TD CLASS="HDR">工作週別代號</TD>

<TD CLASS="HDR">業務人員名稱</TD>

<xsl:for-each select="NewDataSet/data">

<TR>

<TD>

<xsl:value-of select="OrderRequestID"/>

</TD>

<TD>

<xsl:value-of select="WorkWeekID"/>

</TD>

<TD>

<xsl:value-of select="TotalCount"/>

</TD>

<TD>

<xsl:value-of select="ModelID"/>

</TD>

<TD>

<xsl:value-of select="Quantity"/>

</TD>

<TD>

<xsl:value-of select="SalesName"/>

</TD>

<TD>

<xsl:value-of select="DeptID"/>

</TD>

<TD>

<xsl:value-of select="CustomerName"/>

</TD>

</TR>

</xsl:for-each>

</TABLE>

</BODY>

</HTML>

</xsl:template>

</xsl:stylesheet>

</code>

日	一	二	三	四	五	六
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31

MyNewBlog

DownLoad Excel MultiSheet

From DataSet To Html & Excel (方法一)