使用xml来创建excel文档，运行时无需excel

最新推荐文章于 2020-10-21 10:15:38 发布

weixin_30349597

最新推荐文章于 2020-10-21 10:15:38 发布

阅读量139

点赞数

原文链接：http://www.cnblogs.com/jiang_chao/archive/2009/12/22/1629857.html

版权

1.把以下代码复制到记事本里面，后缀名改成xls，根据需要修改所要的模板

Code
<?xml version="1.0"?>
<?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"
xmlns:u1="http://www.w3.org/TR/REC-html40/">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>Automated Report Generator Example</Author>
  <Created>2008-09-23T01:35:55Z</Created>
  <Company>5Demo.com</Company>
  <Version>11.9999</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>8955</WindowHeight>
  <WindowWidth>11355</WindowWidth>
  <WindowTopX>480</WindowTopX>
  <WindowTopY>15</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font x:Family="Swiss"/>
   <Interior/>
   <Protection/>
  </Style>
  <Style ss:ID="s21">
   <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
  </Style>
</Styles>
<Worksheet ss:Name="MyReport">
  <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="1" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="14.25">
   <Column ss:AutoFitWidth="0" ss:Width="53.25"/>
   <Column ss:AutoFitWidth="0" ss:Width="29.25"/>
   <Column ss:AutoFitWidth="0" ss:Width="69.75"/>
   <Row ss:AutoFitHeight="0" ss:Height="15">
    <Cell ss:StyleID="s21"><Data ss:Type="String">Plant</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String">Type</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String">MaterialType</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Unsynced/>
   <Print>
    <ValidPrinterInfo/>
    <PaperSizeIndex>9</PaperSizeIndex>
    <HorizontalResolution>200</HorizontalResolution>
    <VerticalResolution>200</VerticalResolution>
   </Print>
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>4</ActiveRow>
     <ActiveCol>3</ActiveCol>
    </Pane>
    <x:LeftColumnVisible>8</x:LeftColumnVisible>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet2">
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
</Worksheet>
</Workbook>

2.定义列数和行数方法

sb1.AppendLine(String.Format(" <Table ss:ExpandedColumnCount=""{0}"" ss:ExpandedRowCount=""{1}"" x:FullColumns=""1""", cols.ToString, Rows.ToString))

3. StringBuilder 另存对话框

Code
Download("excelfile.xls",sb.ToString)   'excelfile.xls 是生成的Excel名字。

  Private Sub Download(ByVal fileName As String, ByVal FileBody As String)
        Me.Response.ClearHeaders()
        Me.Response.Clear()
        Me.Response.Expires = 0
        Me.Response.Buffer = True
        Me.Response.AddHeader("Accept-Language", "zh_tw")
        Me.Response.AddHeader("Content-Disposition", "attachment; filename=" & Chr(34) & HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) & Chr(34))
        Me.Response.ContentType = "application/octet-stream"
        Me.Response.Write(FileBody)
        Me.Response.End()
    End Sub

4.直接保存到指定的地方

Dim fileNamePath As String = FilePath + "R" + strSNO + ".xls"
Dim sw As StreamWriter = New StreamWriter(fileNamePath)

sw.WriteLine("......")