输出和下载excel报表(xls和csv格式)

最新推荐文章于 2019-06-10 19:25:19 发布

最新推荐文章于 2019-06-10 19:25:19 发布 · 310 阅读

文章标签：

本文介绍了一种利用XML格式来生成复杂XLS报表的方法，通过直接编辑XML代码并结合Excel的功能，实现动态输出数据，同时支持表格样式、运算函数及打印设置等功能。

对于excel支持的csv和xls格式的报表,一直以为xls生成较难一些,因为csv只是多行以","分割的文本文件,被excel读取之后可以显示于电子表格内,但是支持的效果不好,比如不能固定表格的宽高,不能设置单元格背景,不能设置复杂的表格样式,不能添加各种运算函数,不能设置打印区域,只能显示,充其量是个数据文件.

php生成csv很简单,但是对于生成可以完成很多功能的xls格式却有点力不从心,(据说需要调用微软的com组件,php还需要安装扩展),用记事本读取xls,满屏的乱码,找不到头绪.

从网上找过一个php写入xls文件的类,很简单,只有5个方法,但是是生成最简单的xls文件,比csv强不到哪里.但是,我从类里却看出了一点门道,这个类输出的是个xml代码,然后放出几个header声明了MIME类型,这样下载下来就是以xml写的xls格式文件.那么就是说,excel同样支持xml显示数据(这个我是刚知道的,)

于是我新建了一个xls文档,用excel打开,输入简单数据和设置了下格式之后,另存为 xml 表格格式,然后让excel打开这个xml文件,数据和格式都完整显示了.假如将这个xml的后缀改成xls,肯定也没问题了.

那么我们拿着这个xml文档用作模板让php读取,去动态输出数据,然后和那个类一样,抛出几个关键的header,就能搞定xls格式的报表下载了.

我一直使用thinkphp框架做php开发,设置的模板默认后缀为html,肯定不能读取xml的后缀,所以如果应用于框架级别的模板,你需要修改后缀xml为html等需要的.对于smarty模板引擎就完全不用担心了.

下面是个我生成的xml:

<?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">
  <Created>1996-12-17T01:32:42Z</Created>
  <LastSaved>2010-09-04T07:06:33Z</LastSaved>
  <Version>11.9999</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="s21">
   <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>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Names>
   <NamedRange ss:Name="Print_Area" ss:RefersTo="=Sheet1!R1C1:R17C5"/>
  </Names>
  <Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="3" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
   <Column ss:AutoFitWidth="0" ss:Width="52.5"/>
   <Column ss:Index="3" ss:AutoFitWidth="0" ss:Width="65.25"/>
   <Column ss:AutoFitWidth="0" ss:Width="81"/>
   <Row ss:AutoFitHeight="0">
    <Cell ss:StyleID="s21"><Data ss:Type="String">栏位1</Data><NamedCell
      ss:Name="Print_Area"/></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String">栏位2</Data><NamedCell
      ss:Name="Print_Area"/></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String">栏位3</Data><NamedCell
      ss:Name="Print_Area"/></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String">栏位4</Data><NamedCell
      ss:Name="Print_Area"/></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String">栏位5</Data><NamedCell
      ss:Name="Print_Area"/></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell ss:StyleID="s21"><Data ss:Type="Number">23</Data><NamedCell
      ss:Name="Print_Area"/></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="Number">23</Data><NamedCell
      ss:Name="Print_Area"/></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="Number">234</Data><NamedCell
      ss:Name="Print_Area"/></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="Number">4</Data><NamedCell
      ss:Name="Print_Area"/></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="Number">34</Data><NamedCell
      ss:Name="Print_Area"/></Cell>
    <Cell ss:Formula="=SUM(RC[-5]:RC[-1])"><Data ss:Type="Number">318</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell ss:StyleID="s21"><Data ss:Type="Number">23</Data><NamedCell
      ss:Name="Print_Area"/></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="Number">23</Data><NamedCell
      ss:Name="Print_Area"/></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="Number">234</Data><NamedCell
      ss:Name="Print_Area"/></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="Number">4</Data><NamedCell
      ss:Name="Print_Area"/></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="Number">34</Data><NamedCell
      ss:Name="Print_Area"/></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Unsynced/>
   <Selected/>
   <FreezePanes/>
   <FrozenNoSplit/>
   <SplitVertical>3</SplitVertical>
   <LeftColumnRightPane>3</LeftColumnRightPane>
   <ActivePane>1</ActivePane>
   <Panes>
    <Pane>
     <Number>3</Number>
    </Pane>
    <Pane>
     <Number>1</Number>
     <ActiveRow>5</ActiveRow>
     <ActiveCol>6</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>

这段xml本来有三个Worksheet,对应三个工作表,对于最后没用到的那俩就删除了,只保留了第一个.(这个可以根据需要添加),唯一的一个工作表标记<Worksheet ss:Name="Sheet1">,我们很简单就能看出来Sheet1是该工作表的名称,这个地方可以是动态设置的.

<Names>
<NamedRange ss:Name="Print_Area" ss:RefersTo="=Sheet1!R1C1:R17C5"/>
</Names>
这段是声明该工作表的打印区域,后面的属性不太懂,貌似是个参考的打印区域.实际的打印区域在后面设置.

<Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="3" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
这段很重要,对于需要表格线显示的xls,会自动出现这个table标记,属性ExpandedColumnCount是列数,一般列数是固定的,如果不固定请动态设置这里.ExpandedRowCount是行数,而行数一般是不固定的,所以需要设置这里和下面的数据行数一致.如果这两个属性和实际的数据行数和列数不一致,excel就会报错.一定要注意.

<Column ss:AutoFitWidth="0" ss:Width="52.5"/>
这种是设置列属性的,一般让excel去做这部分,不需要手工去改.

好了,上面是一行数据,和html的table里的

<tr>

</tr>

是不是很像?

你只需要动态输出Data标记间的数据就行了,显示的效果让excel去做,千万注意cell的个数和row行数,一定要和table标记里的那两个属性一致.

<Cell ss:Formula="=SUM(RC[-5]:RC[-1])"><Data ss:Type="Number">318</Data></Cell>
这个单元格是在excel里设置过函数的

通观这大段的xml代码,实际需要我们操作的很少,很多只需要在excel里面设置好就行了.

了解了excel支持的这段xml格式,你还头疼xls格式的报表下载吗? 呵呵