CI中使用PHPExcel导出数据到Excel

3. class Table_export extends CI_Controller {

4. function __construct()

5. {
6. parent::__construct();

7. // Here you should add some sort of user validation

8. // to prevent strangers from pulling your table data
9. }

10. function index($table_name)

11. {
12. $this->load->database();
13. $query = $this->db->query("select * from `$table_name` WHERE del= 1");
14. // $query = mb_convert_encoding("gb2312", "UTF-8", $query);
15. if(!$query)
16. return false;

17. // Starting the PHPExcel library

18. $this->load->library('PHPExcel');
19. $this->load->library('PHPExcel/IOFactory');

20. $objPHPExcel = new PHPExcel();

21. $objPHPExcel->getProperties()->setTitle("export")->setDescription("none");

22. $objPHPExcel->setActiveSheetIndex(0)

23. ->setCellValue('A1', iconv('gbk', 'utf-8', '中文Hello'))
24. ->setCellValue('B2', 'world!')
25. ->setCellValue('C1', 'Hello');
26. // Field names in the first row
27. $fields = $query->list_fields();
28. $col = 0;
29. foreach ($fields as $field)
30. {
31. $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $field);
32. $col++;
33. }

34. // Fetching the table data

35. $row = 2;
36. foreach($query->result() as $data)
37. {
38. $col = 0;
39. foreach ($fields as $field)
40. {
41. $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $data->$field);
42. $col++;
43. }

44. $row++;

45. }

46. $objPHPExcel->setActiveSheetIndex(0);

47. $objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');

48. //发送标题强制用户下载文件

49. header('Content-Type: application/vnd.ms-excel');
50. header('Content-Disposition: attachment;filename="Products_'.date('dMy').'.xls"');
51. header('Cache-Control: max-age=0');

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

53. }
54. }
55. ?>

复制代码

加入数据库有表名为products，然后访问http://www.yourwebsite.com/table_export/index/products 就可以导出excel文件了。