XML结合XSLT生成Excel(导入到多个Worksheet)

最新推荐文章于 2023-02-17 17:02:18 发布

weixin_30765577

最新推荐文章于 2023-02-17 17:02:18 发布

阅读量915

点赞数

文章标签： ui xhtml 数据库

原文链接：http://www.cnblogs.com/aspsmile/archive/2008/08/05/971478.html

版权

1、.aspx

Code

@ Page Language="C#" AutoEventWireup="true" CodeFile="ExportAsExcel.aspx.cs" Inherits="ExportAsExcel" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<title>Untitled Page</title>

</head>

<body>

<div>

<asp:Button ID="btnExport" runat="server" OnClick="btnExport_Click" Text="Export" /></div>

</form>

</body>

</html>

2、code-behide

Code

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

using System.IO;

using System.Text;

public partial class ExportAsExcel : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnExport_Click(object sender, EventArgs e)

{

string strConn = "server=.;database=AdventureWorks;uid=sa;pwd=chang;";

SqlConnection conn = new SqlConnection(strConn);

SqlDataAdapter adapter = new SqlDataAdapter("select top 100 employeeId, title from HumanResources.Employee order by employeeId; select top 200 productId, name as productName from Production.Product order by productId", conn);

DataSet ds = new DataSet();

adapter.Fill(ds);

string fileName = System.DateTime.Now.Ticks.ToString();

string templatePath = Server.MapPath("Demo.xsl");

string objectPath = Server.MapPath(fileName + ".xls");

ExcelBuilder excel = new ExcelBuilder();

if (excel.BuildExcel(ds, objectPath, templatePath))

{

rewrite(objectPath);

DownLoad(Page.Response, objectPath, fileName + ".xls");

}

private void DownLoad(System.Web.HttpResponse response, string filepath, string VirtualName)

{

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/vnd.ms-excel";

response.WriteFile(filepath);

response.Flush();

response.End();

}

catch (Exception)

{

}

finally

{

if (File.Exists(filepath))

{

File.Delete(filepath);

}

private void rewrite(string objectPath)

{

try

{

StreamReader sr = new StreamReader(objectPath);

string content = sr.ReadToEnd();

sr.Close();

StreamWriter sw = new StreamWriter(objectPath, false, Encoding.Unicode);

sw.Write("<?xml version=\"1.0\"?><?mso-application progid=\"Excel.Sheet\"?>" + content);

sw.Close();

}

catch (Exception ex)

{

Response.Write(ex.Message);

}

finally

{

}

3、ExcelBuilder

Code

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;

public class ExcelBuilder

{

public ExcelBuilder()

{

}

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

}

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

<xsl:template match=""/"">

<head>

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

if (File.Exists(XslPath))

{

File.Delete(XslPath);

}

FileStream fs = File.Create(XslPath);

StreamWriter sw = new StreamWriter(fs);

sw.Write(str);

sw.Close();

fs.Close();

}

private void GetXmlFile(DataSet ds, string XmlFilePath)

{

if (File.Exists(XmlFilePath))

{

File.Delete(XmlFilePath);

}

FileStream fs = File.Create(XmlFilePath);

StreamWriter writer = new StreamWriter(fs);

writer.Write(ds.GetXml());

writer.Close();

fs.Close();

}

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;

}

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;

}

4、XSLT(demo.xsl)

Code

<?xml version="1.0" encoding="utf-8"?>

<xsl:stylesheet version="1.0"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:o="urn:schemas-microsoft-com:office:office"

xmlns:x="urn:schemas-microsoft-com:office:excel"

xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:html="http://www.w3.org/TR/REC-html40">

<ProtectStructure>False</ProtectStructure>

<ProtectWindows>False</ProtectWindows>

</ExcelWorkbook>

</Style>

<Font ss:FontName="Arial" x:CharSet="134" ss:Size="11" ss:Color="#000000"

ss:Bold="1"/>

</Style>

</Style>

</Styles>

<Table ss:ExpandedColumnCount="2" x:FullColumns="1"

x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="13.5">

<Row>

<Data ss:Type="String">employeeId</Data>

</Cell>

<Data ss:Type="String">title</Data>

</Cell>

</Row>

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

<Row>

<Cell>

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

</Data>

</Cell>

<Cell>

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

</Data>

</Cell>

</Row>

</xsl:for-each>

</Table>

</PageSetup>

<ProtectObjects>False</ProtectObjects>

<ProtectScenarios>False</ProtectScenarios>

</WorksheetOptions>

</Worksheet>

<Table ss:ExpandedColumnCount="2" x:FullColumns="1"

x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="13.5">

<Row>

<Data ss:Type="String">productId</Data>

</Cell>

<Data ss:Type="String">productName</Data>

</Cell>

</Row>

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

<Row>

<Cell>

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

</Data>

</Cell>

<Cell>

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

</Data>

</Cell>

</Row>

</xsl:for-each>

</Table>

</PageSetup>

<ProtectObjects>False</ProtectObjects>

<ProtectScenarios>False</ProtectScenarios>

</WorksheetOptions>

</Worksheet>

</Workbook>

</xsl:template>

</xsl:stylesheet>

转载于:https://www.cnblogs.com/aspsmile/archive/2008/08/05/971478.html

weixin_30765577

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
XML结合XSLT生成Excel(导入到多个Worksheet)

1、.aspxCode<%@PageLanguage="C#"AutoEventWireup="true"CodeFile="ExportAsExcel.aspx.cs"Inherits="ExportAsExcel"%><!DOCTYPEhtmlPUBLIC"-//W3C//DTDXHTML1.0Transitional//EN""...
复制链接

扫一扫