1. 新建一个Excel文档,并填写表头与两行左右的内容,然后另存为XML表格 格式 并修改成Xslt模板;
2. 将要导入的数据生成XML格式文档;
3. 通过Xslt模板将数据生成,并设定Response.ContentType = "application/vnd.ms-excel";
4. 刷新输出页保存文件即为Excel格式的文档
ExportCards.xsl模板代码
- <?xml version="1.0" encoding="utf-8"?>
- <xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:fn="http://www.w3.org/2005/xpath-functions" xmlns:xdt="http://www.w3.org/2005/xpath-datatypes" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns="urn:schemas-microsoft-com:office:spreadsheet">
- <xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>
- <xsl:template match="BitLab.Xml">
- <xsl:param name="Collection" select="Cards/Card" />
- <?mso-application progid="Excel.Sheet"?>
- <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">
- <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
- <Created>1996-12-17T01:32:42Z</Created>
- <LastSaved>2008-07-04T02:10:38Z</LastSaved>
- <Version>11.6360</Version>
- </DocumentProperties>
- <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
- <RemovePersonalInformation/>
- </OfficeDocumentSettings>
- <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
- <WindowHeight>4530</WindowHeight>
- <WindowWidth>8505</WindowWidth>
- <WindowTopX>480</WindowTopX>
- <WindowTopY>120</WindowTopY>
- <AcceptLabelsInFormulas/>
- <ProtectStructure>False</ProtectStructure>
- <ProtectWindows>False</ProtectWindows>
- </ExcelWorkbook>
- <Styles>
- <Style ss:ID="Default" ss:Name="Normal">
- <Alignment ss:Vertical="Bottom"/>
- <Borders/>
- <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>
- <Interior/>
- <NumberFormat/>
- <Protection/>
- </Style>
- <Style ss:ID="s28">
- <Font ss:FontName="宋体" x:CharSet="134"/>
- </Style>
- <Style ss:ID="s29">
- <Font ss:FontName="宋体" x:CharSet="134"/>
- <NumberFormat ss:Format=""¥"#,##0.00;"¥"\-#,##0.00"/>
- </Style>
- <Style ss:ID="s30">
- <Font ss:FontName="Verdana" x:Family="Swiss"/>
- </Style>
- <Style ss:ID="s31">
- <Font ss:FontName="Verdana" x:Family="Swiss"/>
- <NumberFormat ss:Format=""¥"#,##0.00;"¥"\-#,##0.00"/>
- </Style>
- <Style ss:ID="s32">
- <Font ss:FontName="Verdana" x:Family="Swiss"/>
- <NumberFormat ss:Format="General Date"/>
- </Style>
- </Styles>
- <Worksheet ss:Name="Cards">
- <Table ss:ExpandedColumnCount="7" ss:ExpandedRowCount="{1+count($Collection)}" x:FullColumns="1"
- x:FullRows="1" ss:StyleID="s30" ss:DefaultColumnWidth="54">
- <Column ss:StyleID="s30" ss:Width="45.75" ss:Span="1"/>
- <Column ss:Index="3" ss:StyleID="s30" ss:Width="129.75" ss:Span="1"/>
- <Column ss:Index="5" ss:StyleID="s31" ss:Width="54.75"/>
- <Column ss:Index="7" ss:StyleID="s30" ss:Width="127.5"/>
- <Row>
- <Cell ss:StyleID="s28"><Data ss:Type="String">标识列</Data></Cell>
- <Cell ss:StyleID="s28"><Data ss:Type="String">批次号</Data></Cell>
- <Cell ss:StyleID="s28"><Data ss:Type="String">卡序列号</Data></Cell>
- <Cell ss:StyleID="s28"><Data ss:Type="String">卡密码</Data></Cell>
- <Cell ss:StyleID="s29"><Data ss:Type="String">面额</Data></Cell>
- <Cell ss:StyleID="s28"><Data ss:Type="String">状态</Data></Cell>
- <Cell ss:StyleID="s28"><Data ss:Type="String">创建时间</Data></Cell>
- </Row>
- <xsl:for-each select="$Collection">
- <Row>
- <Cell><Data ss:Type="Number"> <xsl:value-of select="@ID" /> </Data></Cell>
- <Cell><Data ss:Type="Number"> <xsl:value-of select="@BatchID" /> </Data></Cell>
- <Cell ss:StyleID="s30"><Data ss:Type="String"> <xsl:value-of select="@SN" /> </Data></Cell>
- <Cell ss:StyleID="s30"><Data ss:Type="String"> <xsl:value-of select="@Password" /> </Data></Cell>
- <Cell ss:StyleID="s31"><Data ss:Type="Number"> <xsl:value-of select="@Par" /> </Data></Cell>
- <Cell ss:StyleID="s30"><Data ss:Type="String"> <xsl:value-of select="@State" /> </Data></Cell>
- <Cell ss:StyleID="s32"><Data ss:Type="String"> <xsl:value-of select="@CreateDate" /> </Data></Cell>
- </Row>
- </xsl:for-each>
- </Table>
- <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
- <Selected/>
- <Panes>
- <Pane>
- <Number>3</Number>
- </Pane>
- </Panes>
- <ProtectObjects>False</ProtectObjects>
- <ProtectScenarios>False</ProtectScenarios>
- </WorksheetOptions>
- </Worksheet>
- </Workbook>
- </xsl:template>
- </xsl:stylesheet>
导出代码:
- public partial class ExportCardForm : Czintel.Moodou.UI.ManagePage
- {
- protected void Page_Load(object sender, EventArgs e)
- {
- this.Components.Remove(ServerContext.User);
- Response.ContentType = "application/vnd.ms-excel";
- this.StyleSheet = "ExportCards.xsl";
- ComponentCollection<Card> cards = Cards.LoadCards();
- this.Components.Add(cards);
- }
- }
具体转换过程在基类里,这儿就不写了!