学步园

方法1：我们常用的导出Excel，但是无法实现对于Excel表里面内容进行格式化处理。
如果格式化处理一般就要操作com组件,但是感觉总有点后遗症(进程无法关闭等等问题)。

    //以流的形式向客户端输出
    public void CreateExcel(System.Data.DataTable dt)
    {
        StringWriter sw = new StringWriter();
        string rowStr = "";
        //取所有列名
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            rowStr = rowStr + dt.Columns[i] + "\t";
        }

        sw.WriteLine(rowStr);
        //取每行数据
        for (int j = 0; j < dt.Rows.Count; j++)
        {
            rowStr = "";
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                rowStr = rowStr + dt.Rows[j][i].ToString() + "\t";
            }
            sw.WriteLine(rowStr);
        }
        sw.Close();
        string filename = HttpUtility.UrlEncode(DateTime.Now.ToString("yyyyMMdd-HHmm"));
        Response.AddHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
        Response.ContentType = "application/ms-excel";
        Response.ContentEncoding = Encoding.GetEncoding("GB2312");
        Response.Write(sw);
        Response.End();
    }

 

方法2：实现对复杂格式的Excel表导出。
打开一个Excel表另存为，发现保存类型里面有个【XML表格】选项.
保存以后用记事本打开发现都是XMl数据格式,而且发现把后缀名xml直接改为xls就变成原先的Excel表。
我们就会想,如果我们在服务器输出这种格式的文件,是不是就可以不操作com组建,导出复杂格式的Excel表了。

下面是xml格式的Excel表:

<?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">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>自动生成</Author>
  <LastAuthor>自动生成</LastAuthor>
  <LastPrinted>2009-06-29T02:48:02Z</LastPrinted>
  <Created>2009-06-22T00:48:00Z</Created>
  <LastSaved>2009-09-16T07:56:36Z</LastSaved>
  <Company>服务器</Company>
  <Version>11.5606</Version>
 </DocumentProperties>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>10245</WindowHeight>
  <WindowWidth>13875</WindowWidth>
  <WindowTopX>120</WindowTopX>
  <WindowTopY>60</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Center"/>
   <Borders/>
   <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="m20988562">
   <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="黑体" x:CharSet="134" ss:Size="12"/>
  </Style>
  <Style ss:ID="m20988572">
   <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="黑体" x:CharSet="134" ss:Size="12"/>
  </Style>
  <Style ss:ID="m20988582">
   <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="黑体" x:CharSet="134" ss:Size="12"/>
  </Style>
  <Style ss:ID="m20988592">
   <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="黑体" x:CharSet="134" ss:Size="12"/>
  </Style>
  <Style ss:ID="m20988602">
   <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="黑体" x:CharSet="134" ss:Size="12"/>
  </Style>
  <Style ss:ID="m20988612">
   <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="黑体" x:CharSet="134" ss:Size="12"/>
  </Style>
  <Style ss:ID="m20988622">
   <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="黑体" x:CharSet="134" ss:Size="12"/>
  </Style>
  <Style ss:ID="m20988632">
   <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="黑体" x:CharSet="134" ss:Size="12"/>
  </Style>
  <Style ss:ID="m20988642">
   <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="黑体" x:CharSet="134" ss:Size="12"/>
  </Style>
  <Style ss:ID="s21">
   <Alignment ss:Vertical="Center" ss:WrapText="1"/>
  </Style>
  <Style ss:ID="s22">
   <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
  </Style>
  <Style ss:ID="s24">
   <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="方正小标宋简体" x:CharSet="134" ss:Size="22"/>
  </Style>
  <Style ss:ID="s34">
   <Alignment ss:Horizontal="Left" ss:Vertical="Center" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="宋体" x:CharSet="134" ss:Size="10.5"/>
  </Style>
  <Style ss:ID="s35">
   <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="宋体" x:CharSet="134" ss:Size="10.5"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Names>
   <NamedRange ss:Name="Print_Titles" ss:RefersTo="=Sheet1!R2:R3"/>
  </Names>
  <Table ss:ExpandedColumnCount="9" ss:ExpandedRowCount="4" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="35.0625">
   <Column ss:StyleID="s21" ss:AutoFitWidth="0" ss:Width="213.75"/>
   <Column ss:StyleID="s22" ss:AutoFitWidth="0" ss:Width="99.75"/>
   <Column ss:StyleID="s22" ss:AutoFitWidth="0" ss:Width="56.25"/>
   <Column ss:StyleID="s22" ss:AutoFitWidth="0" ss:Width="71.25"/>
   <Column ss:StyleID="s22" ss:AutoFitWidth="0" ss:Width="42"/>
   <Column ss:StyleID="s22" ss:AutoFitWidth="0" ss:Width="39.75"/>
   <Column ss:StyleID="s22" ss:AutoFitWidth="0" ss:Width="81"/>
   <Column ss:StyleID="s22"