[转载]PHP 输出带格式的 Excel 文件

第一步：生成一个excel的xml模板，保存为excel-xml.tpl

view sourceprint?

01.<?xml version="1.0"?>

02.<?mso-application progid="Excel.Sheet"?>

03.<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

04.xmlns:o="urn:schemas-microsoft-com:office:office"

05.xmlns:x="urn:schemas-microsoft-com:office:excel"

06.xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

07.xmlns:html="http://www.w3.org/TR/REC-html40">

08.<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">

09.<Author>Diana</Author>

10.<LastAuthor>Diana</LastAuthor>

11.<Created>2006-04-25T11:58:52Z</Created>

12.<LastSaved>2006-04-25T13:10:20Z</LastSaved>

13.<Version>11.5606</Version>

14.</DocumentProperties>

15.<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">

16.<WindowHeight>12495</WindowHeight>

17.<WindowWidth>16035</WindowWidth>

18.<WindowTopX>0</WindowTopX>

19.<WindowTopY>105</WindowTopY>

20.<ProtectStructure>False</ProtectStructure>

21.<ProtectWindows>False</ProtectWindows>

22.</ExcelWorkbook>

23.<Styles>

24.<Style ss:ID="Default" ss:Name="Normal">

25.<Alignment ss:Vertical="Center"/>

26.<Borders/>

27.<Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>

28.<Interior/>

29.<NumberFormat/>

30.<Protection/>

31.</Style>

32.<Style ss:ID="s21">

33.<Font ss:FontName="宋体" x:CharSet="134" ss:Size="18" ss:Bold="1"/>

34.</Style>

35.<Style ss:ID="s29">

36.<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>

37.<Borders>

38.<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>

39.<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>

40.<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>

41.<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>

42.</Borders>

43.</Style>

44.<Style ss:ID="s35">

45.<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>

46.<Borders>

47.<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>

48.<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>

49.<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>

50.<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>

51.</Borders>

52.<NumberFormat ss:Format="@"/>

53.</Style>

54.</Styles>

55.<Worksheet ss:Name="Sheet1">

56.<Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="21" x:FullColumns="1"

57.x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="17.25">

58.<Column ss:AutoFitWidth="0" ss:Width="36"/>

59.<Column ss:AutoFitWidth="0" ss:Width="69"/>

60.<Column ss:AutoFitWidth="0" ss:Width="78.75"/>

61.<Row ss:Height="22.5">

62.<Cell ss:StyleID="s21"><Data ss:Type="String">员工信息表</Data></Cell>

63.</Row>

64.<Row ss:AutoFitHeight="0"/>

65.<Row ss:AutoFitHeight="0">

66.<Cell ss:StyleID="s29"><Data ss:Type="String">序号</Data></Cell>

67.<Cell ss:StyleID="s29"><Data ss:Type="String">工号</Data></Cell>

68.<Cell ss:StyleID="s29"><Data ss:Type="String">姓名</Data></Cell>

69.<Cell ss:StyleID="s29"><Data ss:Type="String">性别</Data></Cell>

70.<Cell ss:StyleID="s29"><Data ss:Type="String">年龄</Data></Cell>

71.</Row>

72.{section name=list loop=$Emps}

73.<Row ss:AutoFitHeight="0">

74.<Cell ss:StyleID="s29"><Datass:Type="Number">{$smarty.section.customer.rownum}</Data></Cell>

75.<Cell ss:StyleID="s35"><Data ss:Type="String">{$Emps[list].id}</Data></Cell>

76.<Cell ss:StyleID="s29"><Data ss:Type="String">{$Emps[list].name}</Data></Cell>

77.<Cell ss:StyleID="s29"><Data ss:Type="String">{$Emps[list].sexual}</Data></Cell>

78.<Cell ss:StyleID="s29"><Data ss:Type="Number">{$Emps[list].age}</Data></Cell>

79.</Row>

80.{/section}

81.</Table>

82.</Worksheet>

83.</Workbook>

说明：上面的模板里用到了smarty的标签，要使用上面的模板，请通过smarty来调用。

第二步：

生成一个测试用的php文件,excel.php

view sourceprint?

01.<?php

02.// 实验资料，实际作业中，这里应该是从数据库取得资料

03.$emps[0]['id'] = '00001';

04.$emps[0]['name'] = 'ABC';

05.$emps[0]['sexual'] = '男';

06.$emps[0]['age'] = 28;

07. 

08.$emps[1]['id'] = '00002';

09.$emps[1]['name'] = 'BBC';

10.$emps[1]['sexual'] = '男';

11.$emps[1]['age'] = 23;

12. 

13.$emps[2]['id'] = '00003';

14.$emps[2]['name'] = 'CBA';

15.$emps[2]['sexual'] = '女';

16.$emps[2]['age'] = 20;

17. 

18.ini_set('include_path', '/data/website/htdocs/includes');

19.require_once('Smarty.php');

20.$smarty = new Smarty();

21. 

22.$smarty->assign('Emps', $emps);

23. 

24.// 输出文件头，表明是要输出 excel 文件

25.header("Content-type: application/vnd.ms-excel");

26.header("Content-Disposition: attachment; filename=test.xls");

27.$smarty->display('excel-xml.tpl');

28.?>

最后生成的excel效果：