PHPexcel创建多个SHEET操作

在 上张贴 由 admin发表回复
  1. <?php
  2. require_once "conn.php";
  3. require_once ‘Classes/DATATOExcel.php’;
  5. foreach($_POST as $key=>$val)  ${$key} = $val;
  6. foreach($_GET as $key=>$val)  ${$key} = $val;
  8. $out_excel = new \DATATOExcel();
  10. $out_file = "out_excel";
  11. //$ary_tables = $tb;
  13. //$out_excel->show_tables($conn,$sql_type);
  16. //输入数组：根据选择后的列组成SQL，根据SQL查询的结果
  17. //根据列名导出EXCEL
  18. $objPHPExcel = new \PHPExcel();
  19. $title_ary = array();
  20. $title_show_ary = array();
  21. $value_ary = array();
  23. $cellKey = array(
  24. ‘A’,’B’,’C’,’D’,’E’,’F’,’G’,’H’,’I’,’J’,’K’,’L’,’M’,
  25. ‘N’,’O’,’P’,’Q’,’R’,’S’,’T’,’U’,’V’,’W’,’X’,’Y’,’Z’,
  26. ‘AA’,’AB’,’AC’,’AD’,’AE’,’AF’,’AG’,’AH’,’AI’,’AJ’,’AK’,’AL’,’AM’,
  27. ‘AN’,’AO’,’AP’,’AQ’,’AR’,’AS’,’AT’,’AU’,’AV’,’AW’,’AX’,’AY’,’AZ’
  28. );
  31. //=====生成SHEET循环
  32. $objPHPExcel->createSheet();
  33. //设置活动SHEET
  34. $objPHPExcel->setActiveSheetIndex(0);
  35. //设置当前活动sheet的名称
  36. $objActSheet = $objPHPExcel->getActiveSheet();
  37. $objActSheet->setTitle(‘数据库表结构’);
  40. $objPHPExcel->getActiveSheet()->getColumnDimension(‘A’)->setWidth(10);//设置列宽度
  41. $objPHPExcel->getActiveSheet()->getColumnDimension(‘B’)->setWidth(30);//设置列宽度
  42. $objPHPExcel->getActiveSheet()->getColumnDimension(‘C’)->setWidth(30);//设置列宽度
  44. $objPHPExcel->setActiveSheetIndex(0)->setCellValue(‘A1′,’序号’);
  47. //设置column的border
  48. $objPHPExcel->getActiveSheet()->getstyle(‘A1’)->getBorders()->getTop()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  49. $objPHPExcel->getActiveSheet()->getstyle(‘A1’)->getBorders()->getBottom()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  50. $objPHPExcel->getActiveSheet()->getstyle(‘A1’)->getBorders()->getLeft()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  51. $objPHPExcel->getActiveSheet()->getstyle(‘A1’)->getBorders()->getRight()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  52. //设置border的color
  53. $objPHPExcel->getActiveSheet()->getstyle(‘A1’)->getBorders()->getTop()->getColor()->setARGB(‘FF000000’);
  54. $objPHPExcel->getActiveSheet()->getstyle(‘A1’)->getBorders()->getBottom()->getColor()->setARGB(‘FF000000’);
  55. $objPHPExcel->getActiveSheet()->getstyle(‘A1’)->getBorders()->getLeft()->getColor()->setARGB(‘FF000000’);
  56. $objPHPExcel->getActiveSheet()->getstyle(‘A1’)->getBorders()->getRight()->getColor()->setARGB(‘FF000000’);
  58. $objPHPExcel->setActiveSheetIndex(0)->setCellValue(‘B1′,’表名称’);
  61. $objPHPExcel->getActiveSheet()->getstyle(‘B1’)->getBorders()->getTop()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  62. $objPHPExcel->getActiveSheet()->getstyle(‘B1’)->getBorders()->getBottom()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  63. $objPHPExcel->getActiveSheet()->getstyle(‘B1’)->getBorders()->getLeft()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  64. $objPHPExcel->getActiveSheet()->getstyle(‘B1’)->getBorders()->getRight()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  66. $objPHPExcel->setActiveSheetIndex(0)->setCellValue(‘C1′,’实体名’);
  68. $objPHPExcel->getActiveSheet()->getstyle(‘C1’)->getBorders()->getTop()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  69. $objPHPExcel->getActiveSheet()->getstyle(‘C1’)->getBorders()->getBottom()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  70. $objPHPExcel->getActiveSheet()->getstyle(‘C1’)->getBorders()->getLeft()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  71. $objPHPExcel->getActiveSheet()->getstyle(‘C1’)->getBorders()->getRight()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  73. $i_js = 2;       
  74. foreach($tb as $tb_name=>$v)
  75. {
  76.         $objPHPExcel->setActiveSheetIndex(0)->setCellValue(‘A’.$i_js,$i_js);
  77.         $objPHPExcel->setActiveSheetIndex(0)->setCellValue(‘B’.$i_js,$tb_name);
  78.         $objPHPExcel->setActiveSheetIndex(0)->setCellValue(‘C’.$i_js,”);
  79.        
  80.         $objPHPExcel->getActiveSheet()->getstyle(‘A’.$i_js)->getBorders()->getTop()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  81.         $objPHPExcel->getActiveSheet()->getstyle(‘A’.$i_js)->getBorders()->getBottom()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  82.         $objPHPExcel->getActiveSheet()->getstyle(‘A’.$i_js)->getBorders()->getLeft()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  83.         $objPHPExcel->getActiveSheet()->getstyle(‘A’.$i_js)->getBorders()->getRight()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  85.         $objPHPExcel->getActiveSheet()->getstyle(‘B’.$i_js)->getBorders()->getTop()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  86.         $objPHPExcel->getActiveSheet()->getstyle(‘B’.$i_js)->getBorders()->getBottom()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  87.         $objPHPExcel->getActiveSheet()->getstyle(‘B’.$i_js)->getBorders()->getLeft()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  88.         $objPHPExcel->getActiveSheet()->getstyle(‘B’.$i_js)->getBorders()->getRight()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  90.         $objPHPExcel->getActiveSheet()->getstyle(‘C’.$i_js)->getBorders()->getTop()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  91.         $objPHPExcel->getActiveSheet()->getstyle(‘C’.$i_js)->getBorders()->getBottom()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  92.         $objPHPExcel->getActiveSheet()->getstyle(‘C’.$i_js)->getBorders()->getLeft()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  93.         $objPHPExcel->getActiveSheet()->getstyle(‘C’.$i_js)->getBorders()->getRight()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  94.        
  95.         $i_js++;
  96. }
  98. $sheet_id = 1;
  99. foreach($tb as $tb_name=>$v)
  100. {
  101.         $objPHPExcel->createSheet();
  102.         //设置活动SHEET
  103.     $objPHPExcel->setActiveSheetIndex($sheet_id);
  104.         //设置当前活动sheet的名称
  105.         $objActSheet = $objPHPExcel->getActiveSheet();
  106.         $objActSheet->setTitle($tb_name);
  107.        
  108.         $objPHPExcel->getActiveSheet()->getColumnDimension(‘A’)->setWidth(30);//设置列宽度
  109.         $objPHPExcel->getActiveSheet()->getColumnDimension(‘B’)->setWidth(30);//设置列宽度
  110.         $objPHPExcel->getActiveSheet()->getColumnDimension(‘C’)->setWidth(30);//设置列宽度
  111.         $objPHPExcel->setActiveSheetIndex($sheet_id)->setCellValue(‘A1′,’字段中文名’);
  112.         $objPHPExcel->setActiveSheetIndex($sheet_id)->setCellValue(‘B1′,’字段名’);
  113.         $objPHPExcel->setActiveSheetIndex($sheet_id)->setCellValue(‘C1′,’字段类型’);
  114.        
  115.         $objPHPExcel->getActiveSheet()->getstyle(‘A1’)->getBorders()->getTop()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  116.         $objPHPExcel->getActiveSheet()->getstyle(‘A1’)->getBorders()->getBottom()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  117.         $objPHPExcel->getActiveSheet()->getstyle(‘A1’)->getBorders()->getLeft()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  118.         $objPHPExcel->getActiveSheet()->getstyle(‘A1’)->getBorders()->getRight()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  119.        
  120.         $objPHPExcel->getActiveSheet()->getstyle(‘B1’)->getBorders()->getTop()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  121.         $objPHPExcel->getActiveSheet()->getstyle(‘B1’)->getBorders()->getBottom()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  122.         $objPHPExcel->getActiveSheet()->getstyle(‘B1’)->getBorders()->getLeft()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  123.         $objPHPExcel->getActiveSheet()->getstyle(‘B1’)->getBorders()->getRight()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  125.         $objPHPExcel->getActiveSheet()->getstyle(‘C1’)->getBorders()->getTop()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  126.         $objPHPExcel->getActiveSheet()->getstyle(‘C1’)->getBorders()->getBottom()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  127.         $objPHPExcel->getActiveSheet()->getstyle(‘C1’)->getBorders()->getLeft()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  128.         $objPHPExcel->getActiveSheet()->getstyle(‘C1’)->getBorders()->getRight()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  129.        
  130.         //根据数据库表名取得数据库列名以及列信息
  131.         $colnum_ary = $out_excel->show_columns($conn,$tb_name,$sql_type);
  132.        
  133.         //print_r($colnum_ary);
  134.         $col_row = 2;
  135.         foreach($colnum_ary as $c_k=>$c_line)
  136.         {
  137.                 $objPHPExcel->setActiveSheetIndex($sheet_id)->setCellValue(‘A’.$col_row,$c_line[8]);
  138.                 $objPHPExcel->setActiveSheetIndex($sheet_id)->setCellValue(‘B’.$col_row,$c_line[0]);
  139.                 $objPHPExcel->setActiveSheetIndex($sheet_id)->setCellValue(‘C’.$col_row,$c_line[1]);
  140.                
  141.                 $objPHPExcel->getActiveSheet()->getstyle(‘A’.$col_row)->getBorders()->getTop()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  142.                 $objPHPExcel->getActiveSheet()->getstyle(‘A’.$col_row)->getBorders()->getBottom()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  143.                 $objPHPExcel->getActiveSheet()->getstyle(‘A’.$col_row)->getBorders()->getLeft()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  144.                 $objPHPExcel->getActiveSheet()->getstyle(‘A’.$col_row)->getBorders()->getRight()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  146.                 $objPHPExcel->getActiveSheet()->getstyle(‘B’.$col_row)->getBorders()->getTop()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  147.                 $objPHPExcel->getActiveSheet()->getstyle(‘B’.$col_row)->getBorders()->getBottom()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  148.                 $objPHPExcel->getActiveSheet()->getstyle(‘B’.$col_row)->getBorders()->getLeft()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  149.                 $objPHPExcel->getActiveSheet()->getstyle(‘B’.$col_row)->getBorders()->getRight()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  151.                 $objPHPExcel->getActiveSheet()->getstyle(‘C’.$col_row)->getBorders()->getTop()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  152.                 $objPHPExcel->getActiveSheet()->getstyle(‘C’.$col_row)->getBorders()->getBottom()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  153.                 $objPHPExcel->getActiveSheet()->getstyle(‘C’.$col_row)->getBorders()->getLeft()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  154.                 $objPHPExcel->getActiveSheet()->getstyle(‘C’.$col_row)->getBorders()->getRight()->setBorderstyle(\PHPExcel_style_Border::BORDER_THIN);
  155.                
  156.                 $col_row++;
  157.         }
  158.        
  159.        
  160.         $sheet_id++;
  161. }
  162. //exit;
  164. //=============================================================================
  165. ob_end_clean();//清除缓冲区,避免乱码
  166. header(‘Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’);
  167. header(‘Content-Disposition: attachment;filename="’.$out_file.’.xlsx"’);
  168. header(‘Cache-Control: max-age=0’);
  169. // If you’re serving to IE 9, then the following may be needed
  170. //header(‘Cache-Control: max-age=1’);
  172. // If you’re serving to IE over SSL, then the following may be needed
  173. //header (‘Expires: Mon, 26 Jul 1997 05:00:00 GMT’); // Date in the past
  174. header (‘Expires:0’); // Date in the past
  175. header (‘Last-Modified: ‘.gmdate(‘D, d M Y H:i:s’).’ GMT’); // always modified
  176. header (‘Cache-Control: cache, must-revalidate’); // HTTP/1.1
  177. header (‘Pragma: public’); // HTTP/1.0
  179. $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel2007’);
  180. $objWriter->save(‘php://output’);
  181. exit;
  182. ?>

复制代码

发表回复