準(zhǔn)備工作：

1.下載phpexcel1.7.6類(lèi)包；

2.解壓至TP框架的ThinkPHP\Vendor目錄下,改類(lèi)包文件夾名為PHPExcel176,目錄結(jié)構(gòu)如下圖；

編寫(xiě)代碼(以一個(gè)訂單匯總數(shù)據(jù)為例)：

1. 創(chuàng)建數(shù)據(jù)庫(kù)及表;

2. 創(chuàng)建tp項(xiàng)目,配置項(xiàng)目的數(shù)據(jù)庫(kù)連接,這些基本的就不說(shuō)了；

3. 在項(xiàng)目的Lib\Action下創(chuàng)建一個(gè)新的類(lèi)文件ExportStatisticsAction.class.php,然后在 index方法中實(shí)現(xiàn)excel導(dǎo)出；

4. 導(dǎo)出方法的步驟：

①查詢(xún)數(shù)據(jù)

②導(dǎo)入phpexcel類(lèi)庫(kù)

③創(chuàng)建excel對(duì)象并設(shè)置excel對(duì)象的屬性

④設(shè)置excel的行列樣式(字體、高寬、顏色、邊框、合并等)

⑤繪制報(bào)表表頭

⑥將查詢(xún)數(shù)據(jù)寫(xiě)入excel

⑦設(shè)置excel的sheet的名稱(chēng)

⑧設(shè)置excel報(bào)表打開(kāi)后初始的sheet

⑨設(shè)置輸出的excel的頭參數(shù)及文件名

⑩調(diào)用創(chuàng)建excel的方法生成excel文件

代碼如下：(可根據(jù)需求進(jìn)行循環(huán)操作)

[php] view plain copy

1. <?php

2. /**

3. * Created by lonm.shi.

4. * Date: 2012-02-09

5. * Time: 下午4:54

6. * To change this template use File | Settings | File Templates.

7. */

9. class ExportStatisticsAction extends Action {

10. public function index(){

11. $model= D("OrdersView");

12. $OrdersData= $model->select(); //查詢(xún)數(shù)據(jù)得到$OrdersData二維數(shù)組

14. vendor("PHPExcel176.PHPExcel");

16. // Create new PHPExcel object

17. $objPHPExcel = new PHPExcel();

18. // Set properties

19. $objPHPExcel->getProperties()->setCreator("ctos")

20. ->setLastModifiedBy("ctos")

21. ->setTitle("Office 2007 XLSX Test Document")

22. ->setSubject("Office 2007 XLSX Test Document")

23. ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")

24. ->setKeywords("office 2007 openxml php")

25. ->setCategory("Test result file");

27. //set width

28. $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8);

29. $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10);

30. $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25);

31. $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);

32. $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(50);

33. $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);

34. $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(12);

35. $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(12);

36. $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(12);

37. $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(30);

39. //設(shè)置行高度

40. $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(22);

42. $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);

44. //set font size bold

45. $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);

46. $objPHPExcel->getActiveSheet()->getStyle('A2:J2')->getFont()->setBold(true);

48. $objPHPExcel->getActiveSheet()->getStyle('A2:J2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

49. $objPHPExcel->getActiveSheet()->getStyle('A2:J2')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

51. //設(shè)置水平居中

52. $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

53. $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

54. $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

55. $objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

56. $objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

57. $objPHPExcel->getActiveSheet()->getStyle('G')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

58. $objPHPExcel->getActiveSheet()->getStyle('H')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

59. $objPHPExcel->getActiveSheet()->getStyle('I')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

61. //合并cell

62. $objPHPExcel->getActiveSheet()->mergeCells('A1:J1');

64. // set table header content

65. $objPHPExcel->setActiveSheetIndex(0)

66. ->setCellValue('A1', '訂單數(shù)據(jù)匯總 時(shí)間:'.date('Y-m-d H:i:s'))

67. ->setCellValue('A2', '訂單ID')

68. ->setCellValue('B2', '下單人')

69. ->setCellValue('C2', '客戶(hù)名稱(chēng)')

70. ->setCellValue('D2', '下單時(shí)間')

71. ->setCellValue('E2', '需求機(jī)型')

72. ->setCellValue('F2', '需求數(shù)量')

73. ->setCellValue('G2', '需求交期')

74. ->setCellValue('H2', '確認(rèn)BOM料號(hào)')

75. ->setCellValue('I2', 'PMC確認(rèn)交期')

76. ->setCellValue('J2', 'PMC交貨備注');

78. // Miscellaneous glyphs, UTF-8

79. for($i=0;$i<count($OrdersData)-1;$i++){

80. $objPHPExcel->getActiveSheet(0)->setCellValue('A'.($i+3), $OrdersData[$i]['id']);

81. $objPHPExcel->getActiveSheet(0)->setCellValue('B'.($i+3), $OrdersData[$i]['realname']);

82. $objPHPExcel->getActiveSheet(0)->setCellValue('C'.($i+3), $OrdersData[$i]['customer_name']);

83. $objPHPExcel->getActiveSheet(0)->setCellValue('D'.($i+3), toDate($OrdersData[$i]['create_time'])); //這里調(diào)用了common.php的時(shí)間戳轉(zhuǎn)換函數(shù)

84. $objPHPExcel->getActiveSheet(0)->setCellValue('E'.($i+3), $OrdersData[$i]['require_product']);

85. $objPHPExcel->getActiveSheet(0)->setCellValue('F'.($i+3), $OrdersData[$i]['require_count']);

86. $objPHPExcel->getActiveSheet(0)->setCellValue('G'.($i+3), $OrdersData[$i]['require_time']);

87. $objPHPExcel->getActiveSheet(0)->setCellValue('H'.($i+3), $OrdersData[$i]['product_bom_encoding']);

88. $objPHPExcel->getActiveSheet(0)->setCellValue('I'.($i+3), $OrdersData[$i]['delivery_time']);

89. $objPHPExcel->getActiveSheet(0)->setCellValue('J'.($i+3), $OrdersData[$i]['delivery_memo']);

90. $objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':J'.($i+3))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

91. $objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':J'.($i+3))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

92. $objPHPExcel->getActiveSheet()->getRowDimension($i+3)->setRowHeight(16);

93. }

96. // sheet命名

97. $objPHPExcel->getActiveSheet()->setTitle('訂單匯總表');

100. // Set active sheet index to the first sheet, so Excel opens this as the first sheet

101. $objPHPExcel->setActiveSheetIndex(0);

104. // excel頭參數(shù)

105. header('Content-Type: application/vnd.ms-excel');

106. header('Content-Disposition: attachment;filename="訂單匯總表('.date('Ymd-His').').xls"'); //日期為文件名后綴

107. header('Cache-Control: max-age=0');

109. $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //excel5為xls格式,excel2007為xlsx格式

110. $objWriter->save('php://output');

112. }

113. }

5.調(diào)用導(dǎo)出方法直接 http://項(xiàng)目/index.php/ExportStatistics/index,項(xiàng)目中調(diào)用直接__APP__/ExportStatistics/index,生成的報(bào)表是下載方式來(lái)保存.phpexcel1.7.6沒(méi)有發(fā)現(xiàn)什么編碼問(wèn)題,速度也很快,注意導(dǎo)出的方法中不能有任何頁(yè)面輸出信息或調(diào)試信息,否則導(dǎo)出的excel會(huì)提示格式不對(duì).效果如下：

導(dǎo)出報(bào)表