第一步:生成一个excel的xml模板,保存为excel-xml.tpl
<?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>Diana</Author> <LastAuthor>Diana</LastAuthor> <Created>2006-04-25T11:58:52Z</Created> <LastSaved>2006-04-25T13:10:20Z</LastSaved> <Version>11.5606</Version> </DocumentProperties> <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> <WindowHeight>12495</WindowHeight> <WindowWidth>16035</WindowWidth> <WindowTopX>0</WindowTopX> <WindowTopY>105</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="s21"> <Font ss:FontName="宋体" x:CharSet="134" ss:Size="18" ss:Bold="1"/> </Style> <Style ss:ID="s29"> <Alignment ss:Horizontal="Center" ss:Vertical="Center"/> <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> <Style ss:ID="s35"> <Alignment ss:Horizontal="Center" ss:Vertical="Center"/> <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> <NumberFormat ss:Format="@"/> </Style> </Styles> <Worksheet ss:Name="Sheet1"> <Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="21" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="17.25"> <Column ss:AutoFitWidth="0" ss:Width="36"/> <Column ss:AutoFitWidth="0" ss:Width="69"/> <Column ss:AutoFitWidth="0" ss:Width="78.75"/> <Row ss:Height="22.5"> <Cell ss:StyleID="s21"><Data ss:Type="String">员工信息表</Data></Cell> </Row> <Row ss:AutoFitHeight="0"/> <Row ss:AutoFitHeight="0"> <Cell ss:StyleID="s29"><Data ss:Type="String">序号</Data></Cell> <Cell ss:StyleID="s29"><Data ss:Type="String">工号</Data></Cell> <Cell ss:StyleID="s29"><Data ss:Type="String">姓名</Data></Cell> <Cell ss:StyleID="s29"><Data ss:Type="String">性别</Data></Cell> <Cell ss:StyleID="s29"><Data ss:Type="String">年龄</Data></Cell> </Row> {section name=list loop=$Emps} <Row ss:AutoFitHeight="0"> <Cell ss:StyleID="s29"><Data ss:Type="Number">{$smarty.section.customer.rownum}</Data></Cell> <Cell ss:StyleID="s35"><Data ss:Type="String">{$Emps[list].id}</Data></Cell> <Cell ss:StyleID="s29"><Data ss:Type="String">{$Emps[list].name}</Data></Cell> <Cell ss:StyleID="s29"><Data ss:Type="String">{$Emps[list].sexual}</Data></Cell> <Cell ss:StyleID="s29"><Data ss:Type="Number">{$Emps[list].age}</Data></Cell> </Row> {/section} </Table> </Worksheet> </Workbook>
说明:上面的模板里用到了smarty的标签,要使用上面的模板,请通过smarty来调用。
第二步:
生成一个测试用的php文件,excel.php
<?php
// 实验资料,实际作业中,这里应该是从数据库取得资料
$emps[0]['id'] = '00001';
$emps[0]['name'] = 'ABC';
$emps[0]['sexual'] = '男';
$emps[0]['age'] = 28;
$emps[1]['id'] = '00002';
$emps[1]['name'] = 'BBC';
$emps[1]['sexual'] = '男';
$emps[1]['age'] = 23;
$emps[2]['id'] = '00003';
$emps[2]['name'] = 'CBA';
$emps[2]['sexual'] = '女';
$emps[2]['age'] = 20;
ini_set('include_path', '/data/website/htdocs/includes');
require_once('Smarty.php');
$smarty = new Smarty();
$smarty->assign('Emps', $emps);
// 输出文件头,表明是要输出 excel 文件
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=test.xls");
$smarty->display('excel-xml.tpl');
?>
最后生成的excel效果: