API tools faq
paste
Advertisement
Guest User

phpexcel problem

a guest
Aug 5th, 2013
168
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PHP 13.80 KB | None | 0 0
raw download clone embed print report
  1. <?
  2. require_once 'PHPExcel.php';
  3. clearstatcache();
  4.     /**
  5.     * When I uncomment SetCellAsPercentage() functionality at line 214-215 - export breaks;
  6.     * When I uncomment preset cell-border-style at line 66-80 - export breaks again;
  7.     */
  8. /** Error reporting */
  9. error_reporting(E_ALL);
  10.  
  11. ini_set('display_errors', TRUE);
  12. ini_set('display_startup_errors', TRUE);
  13. date_default_timezone_set('Europe/London');
  14.  
  15. define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
  16.  
  17. //fill in sample data
  18. $stat_show_excel= 10;
  19. $stat_client_ids = array("Test Client");
  20. $stat_client_id = 0;
  21. $stat_year=2013;
  22. $main_columns = array ( 1 => array ( 'nm_abrev' => 'Jan', 'nm' => 'Jan', 'num' => 1, ), 2 => array ( 'nm_abrev' => 'Feb', 'nm' => 'Feb', 'num' => 2, ), 3 => array ( 'nm_abrev' => 'Mar', 'nm' => 'Mar', 'num' => 3, ), 4 => array ( 'nm_abrev' => 'Apr', 'nm' => 'Apr', 'num' => 4, ), 5 => array ( 'nm_abrev' => 'May', 'nm' => 'May', 'num' => 5, ), 6 => array ( 'nm_abrev' => 'Jun', 'nm' => 'Jun', 'num' => 6, ), 7 => array ( 'nm_abrev' => 'Jul', 'nm' => 'Jul', 'num' => 7, ), 8 => array ( 'nm_abrev' => 'Aug', 'nm' => 'Aug', 'num' => 8, ), 9 => array ( 'nm_abrev' => 'Sep', 'nm' => 'Sep', 'num' => 9, ), 10 => array ( 'nm_abrev' => 'Oct', 'nm' => 'Oct', 'num' => 10, ), 11 => array ( 'nm_abrev' => 'Nov', 'nm' => 'Nov', 'num' => 11, ), 12 => array ( 'nm_abrev' => 'Dec', 'nm' => 'Dec', 'num' => 12, ), );
  23. $g_groups = array ( 0 => array ( 'group_name' => 'group name', 'group_ord' => '0', 'bloc_ord' => '0', 'months' => array ( 6 => '6', 7 => '7', ), ), 1 => array ( 'group_name' => 'group name', 'group_ord' => '1', 'bloc_ord' => '0', 'months' => array ( 6 => '6', 7 => '7', ), ), 2 => array ( 'group_name' => 'group name', 'group_ord' => '2', 'bloc_ord' => '0', 'months' => array ( 6 => '6', 7 => '7', ), ), 3 => array ( 'group_name' => 'group name', 'group_ord' => '3', 'bloc_ord' => '0', 'months' => array ( 6 => '6', 7 => '7', ), ), 4 => array ( 'group_name' => 'group name', 'group_ord' => '4', 'bloc_ord' => '0', 'months' => array ( 6 => '6', 7 => '7', ), ), 5 => array ( 'group_name' => 'group name', 'group_ord' => '5', 'bloc_ord' => '0', 'months' => array ( 6 => '6', 7 => '7', ), ), 6 => array ( 'group_name' => 'group name', 'group_ord' => '6', 'bloc_ord' => '0', 'months' => array ( 6 => '6', 7 => '7', ), ), 7 => array ( 'group_name' => 'group name', 'group_ord' => '7', 'bloc_ord' => '0', 'months' => array ( 6 => '6', 7 => '7', ), ), );
  24. $data_main_groups_total= array ( 'r' => array ( 0 => array ( 'points_got' => 102, 'points_from' => 143, 'sum' => 10200, 'cnt' => 143, 'total' => 71, ), 'row_avg' => array ( 'sum' => 94223.333332, 'cnt' => 1467, 'total' => 64, ), 1 => array ( 'points_got' => 205, 'points_from' => 328, 'sum' => 17300, 'cnt' => 291, 'total' => 59, ), 2 => array ( 'points_got' => 613, 'points_from' => 924, 'sum' => 13983.333332, 'cnt' => 295, 'total' => 47, ), 3 => array ( 'points_got' => 88, 'points_from' => 110, 'sum' => 8800, 'cnt' => 110, 'total' => 80, ), 4 => array ( 'points_got' => 166, 'points_from' => 258, 'sum' => 16600, 'cnt' => 258, 'total' => 64, ), 5 => array ( 'points_got' => 19, 'points_from' => 74, 'sum' => 1900, 'cnt' => 74, 'total' => 26, ), 6 => array ( 'points_got' => 98, 'points_from' => 111, 'sum' => 9800, 'cnt' => 111, 'total' => 88, ), 7 => array ( 'points_got' => 358, 'points_from' => 518, 'sum' => 15640, 'cnt' => 185, 'total' => 85, ), ), 'block_result' => array ( 0 => array ( 'sum' => 94223.333332, 'cnt' => 1467, 'total' => 64, ), ), 'c' => array ( 6 => array ( 'sum' => 84595.5555543, 'cnt' => '1320.00000000000000000000', 'total' => 64, ), 7 => array ( 'sum' => 9627.7777777, 'cnt' => '147.00000000000000000000', 'total' => 65, ), ), );
  25. $data_main_groups=array ( 0 => array ( 6 => 72, 7 => 64, ), 1 => array ( 6 => 58, 7 => 70, ), 2 => array ( 6 => 47, 7 => 51, ), 3 => array ( 6 => 81, 7 => 73, ), 4 => array ( 6 => 65, 7 => 59, ), 5 => array ( 6 => 21, 7 => 63, ), 6 => array ( 6 => 89, 7 => 83, ), 7 => array ( 6 => 85, 7 => 78, ), );
  26.  
  27. $export = new Exporter();
  28. //fill data and make calculations
  29. $export->Groups($stat_show_excel, $stat_client_ids, $stat_client_id, $stat_year, $main_columns, $g_groups, $data_main_groups_total, $data_main_groups);
  30. //export
  31. $export->close();
  32.  
  33. class Exporter {
  34.     protected $workbook;
  35.     protected $format;
  36.     protected $worksheet;
  37.  
  38.     private $objPHPExcel;
  39.     private $setCellAsPercentageCount = 0;
  40.  
  41.     function __construct() {
  42.         $this->objPHPExcel = new PHPExcel();
  43.         $this->objPHPExcel->getProperties()->setCreator("Client-X Office")
  44.             ->setLastModifiedBy("Client-X Office")
  45.             ->setTitle("Client-X Office")
  46.             ->setSubject("Client-X Office")
  47.             ->setDescription("This document is generated by Client-X system.")
  48.             ->setKeywords("Client-X mystery shopper")
  49.             ->setCategory("Client-X Office");
  50.         $this->objPHPExcel->removeSheetByIndex(0);
  51.         $this->SetFormats();
  52.     }
  53.  
  54.     function setFormats(){
  55.         $this->format['white'] = array(
  56.             'alignment' => array(
  57.                 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
  58.                 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
  59.             ),
  60.             'fill' => array(
  61.                 'type' => PHPExcel_Style_Fill::FILL_SOLID,
  62.                 'color' => array(
  63.                     'rgb' => 'FFCCFF'
  64.                 ),
  65.             ), //uncomment code below and export breaks
  66.             // 'borders' => array(
  67.             //  'inside'  => array(
  68.             //      'style' => PHPExcel_Style_Border::BORDER_THIN,
  69.             //      'color' => array(
  70.             //          'argb' => 'FFFF0000'
  71.             //      )
  72.             //  ),
  73.             //  'outline' => array(
  74.             //      'style' => PHPExcel_Style_Border::BORDER_THIN,
  75.             //      'color' => array(
  76.             //          'argb' => 'FFFF0000'
  77.             //      )
  78.             //  ),
  79.             // ),
  80.         );
  81.         $this->format['blue_total'] = array(
  82.             'font' => array(
  83.                 'bold' => true,
  84.                 'color' => array( 'argb' => 'FFFFFFFF'),
  85.             ),
  86.             'alignment' => array(
  87.                 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
  88.                 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
  89.             ),
  90.             'fill' => array(
  91.                 'type' => PHPExcel_Style_Fill::FILL_SOLID,
  92.                 'color' => array(
  93.                     'rgb' => '31859B'
  94.                 ),
  95.             ),
  96.             'borders' => array(
  97.                 'outline' => array(
  98.                     'style' => PHPExcel_Style_Border::BORDER_THIN,
  99.                     'color' => array('argb' => 'FF000000'),
  100.                 ),
  101.             ),
  102.         );
  103.     }
  104.  
  105. function close(){ //this method
  106.     // header('Content-Type: application/vnd.ms-excel');
  107.     // header('Content-Disposition: attachment;filename="result.xls"');
  108.     // header('Cache-Control: max-age=0');
  109.  
  110.     $objWriter = PHPExcel_IOFactory::createWriter($this->objPHPExcel, 'Excel5');
  111.     // $objWriter->save('php://output');
  112.     $objWriter->save(str_replace('.php', '.xls', __FILE__));
  113.         echo "Php memory limit: ".ini_get('memory_limit')."<br/>"; //2048M
  114.         echo "Php time limit: ". ini_get('max_execution_time')."<br/>"; //30
  115.         echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL; //4.75 MB
  116.         echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL; //4.75 MB
  117.         echo "SetCellAsPercentage called: ".$this->setCellAsPercentageCount++; //26
  118.  
  119.  
  120.  
  121.     // $this->workbook->close();
  122. }
  123.    
  124.     function AddHeader(){
  125.  
  126.         $this->SetBackground('A1:N5', '00CCFF');
  127.         $this->SetBackground('A6:N10', '000000');
  128.         $this->SetForeground('A6:N10', 'FFFFFF');
  129.         $this->SetBackground('A11:N14', 'FFFFFF');
  130.  
  131.         //Add logo image
  132.         //bmp file - 40kb
  133.     }
  134.  
  135.     private function SetCellAsPercentage($coords){
  136.         // $this->objPHPExcel->getActiveSheet()->getStyle($coords)
  137.         //  ->getNumberFormat()->setFormatCode('0.00%');
  138.         $this->setCellAsPercentageCount++;
  139.     }
  140.  
  141.     function Groups($stat_show_excel, $stat_client_ids,$stat_client_id, $stat_year, $main_columns, $g_groups, $data_main_groups_total, $data_main_groups){
  142.         $workSheetName = '1.Sections';
  143.         $newWorkSheet = new PHPExcel_Worksheet($this->objPHPExcel, $workSheetName);
  144.         $this->objPHPExcel->addSheet($newWorkSheet);
  145.  
  146.         $this->objPHPExcel->setActiveSheetIndexByName($workSheetName);
  147.         $this->AddHeader();
  148.  
  149.         $this->SetCellValue('A6', 'Клиент: '.$stat_client_ids[$stat_client_id]);
  150.         $this->SetCellValue('A7', 'Година: '.$stat_year);
  151.         $this->SetBackground('A15:N15', 'FFFFFF');
  152.         $this->AlignHorizontal('A15:N15', 'center');
  153.         $this->AlignVertical('A15:N15', 'center');
  154.         $this->SetBorders('A15:N15', 'top', 'thin');
  155.         $this->SetBorders('A15:N15', 'bottom', 'thin');
  156.  
  157.         $this->objPHPExcel->getActiveSheet()->getRowDimension('15')->setRowHeight(30);
  158.  
  159.         $this->SetCellValue('A15', 'Групи');
  160.         $this->objPHPExcel->getActiveSheet()->getStyle('A15')->getFont()->setBold(true);
  161.         $this->SetCellValue('B15', 'Общо');
  162.         $this->objPHPExcel->getActiveSheet()->getStyle('B15')->getFont()->setBold(true);
  163.  
  164.         $i = 1;
  165.         foreach ($main_columns as $v) {
  166.             $i++;
  167.             $coords = $this->ToCoords(14, $i);
  168.             $this->SetCellValue($coords, $v['nm']);
  169.             $this->objPHPExcel->getActiveSheet()->getStyle($coords)->getFont()->setBold(true);
  170.             // $this->objPHPExcel->getActiveSheet()->getColumnDimension($this->ToCoords(null, $i))->setAutoSize(true);
  171.             $this->objPHPExcel->getActiveSheet()->getColumnDimension($this->ToCoords(null, $i))->setWidth(7);
  172.         }
  173.  
  174.         $i = 15;
  175.  
  176.         foreach ($g_groups as $k => $v) {
  177.             $coords = $this->ToCoords($i, 0);
  178.             $this->SetCellValue($coords, $v['group_name']);
  179.             $this->SetBorders($coords, 'top', 'thin');
  180.             $this->SetBorders($coords, 'bottom', 'thin');
  181.             $this->AlignVertical($coords, 'center');
  182.             $this->SetBackground($coords, '99CCFF');
  183.             $this->WrapCell($coords);
  184.             $this->objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(70);
  185.             $this->objPHPExcel->getActiveSheet()->getStyle($coords)->getFont()->setBold(true);
  186.  
  187.             $coords = $this->ToCoords($i, 1);
  188.             if (isset($data_main_groups_total['r'][$v['group_ord']]['total'])) {
  189.                 $value = $data_main_groups_total['r'][$v['group_ord']]['total']/100;
  190.  
  191.                 $this->SetCellAsPercentage($coords);
  192.  
  193.                 $this->SetCellValue($coords, $value);
  194.                 $this->objPHPExcel->getActiveSheet()
  195.                     ->getStyle($coords)->applyFromArray($this->format['white']);
  196.  
  197.                 // $objPHPExcel->getActiveSheet()->getStyle($coords)->getNumberFormat()
  198.                 //  ->setFormatCode('#,##0.00%');
  199.             }
  200.  
  201.             $j = 2;
  202.  
  203.             foreach ($main_columns as $vv) {
  204.                 $coords = $this->ToCoords($i, $j);
  205.                 if (!empty($v['months'][$vv['num']])) {
  206.                     if (!($data_main_groups[$v['group_ord']][$vv['num']] === "N/A")) {
  207.                         $value = $data_main_groups[$v['group_ord']][$vv['num']]/100;
  208.                         $this->SetCellAsPercentage($coords);
  209.                     } else {
  210.                         $value = 'N/A';
  211.                     }
  212.                 }
  213.                 else {
  214.                     $value ='-';
  215.                 }
  216.                 $this->SetCellValue($coords, $value);
  217.                 $j++;
  218.             }
  219.            
  220.             $frCoords = $this->ToCoords($i, 2);
  221.             $toCoords = $this->ToCoords($i, 13);
  222.             $concatCoords = $frCoords.':'.$toCoords; //C16:N16
  223.             $this->objPHPExcel->getActiveSheet()
  224.                 ->getStyle($concatCoords)->applyFromArray($this->format['white']);
  225.  
  226.             $i++;
  227.         }
  228.         $frCoords = $this->ToCoords($i, 0);
  229.         $toCoords = $this->ToCoords($i, 13);
  230.         // $this->objPHPExcel->getActiveSheet()
  231.         //  ->getStyle($frCoords.':'.$toCoords)->applyFromArray($this->format['white']);
  232.  
  233.         $i++;
  234.  
  235.         if (count($g_groups)) {
  236.             $frCoords = $this->ToCoords($i, 0);
  237.             $toCoords = $this->ToCoords($i, 13);
  238.             $coords = $this->ToCoords($i, 0);
  239.  
  240.             $this->SetBackground($frCoords.':'.$toCoords, 'FFFFFF');
  241.             $this->setCellValue($coords, 'Общо');
  242.  
  243.             $j = 2;
  244.             foreach ($main_columns as $v) {
  245.                 $coords = $this->ToCoords($i, $j);
  246.  
  247.                 if (isset($data_main_groups_total['c'][$v['num']]['total'])) {
  248.                     $value = (float)$data_main_groups_total['c'][$v['num']]['total']/100;
  249.                     $this->SetCellAsPercentage($coords);
  250.                 }
  251.                 else {
  252.                
  253.                     $value = '-';
  254.                 }
  255.  
  256.                 $this->objPHPExcel->getActiveSheet()
  257.                     ->getStyle($frCoords.':'.$toCoords)->applyFromArray($this->format['blue_total']);
  258.                 $this->setCellValue($coords, $value);
  259.  
  260.                 $j++;
  261.             }
  262.         }
  263.         $this->objPHPExcel->getActiveSheet()->freezePane('C1');
  264.     }
  265.  
  266.  
  267.     //Controllers
  268.     function AlignHorizontal($coords, $alignment){
  269.         switch ($alignment) {
  270.             case 'center':
  271.                 $this->objPHPExcel->getActiveSheet()->getStyle($coords)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  272.                 break;
  273.            
  274.             default:
  275.                 # code...
  276.                 break;
  277.         }
  278.     }
  279.  
  280.     function AlignVertical($coords, $alignment){
  281.         $align = "";
  282.         switch ($alignment) {
  283.             case 'center':
  284.                 $align = PHPExcel_Style_Alignment::VERTICAL_CENTER;
  285.                 break;
  286.             default:
  287.                 return;
  288.                 break;
  289.         }
  290.         $this->objPHPExcel->getActiveSheet()->getStyle($coords)->getAlignment()->setVertical($align);
  291.     }
  292.  
  293.     function SetBorders($coords, $borderPosition, $style){
  294.         $borderStyle = '';
  295.         switch ($style) {
  296.             case 'thin':
  297.                 $borderStyle = PHPExcel_Style_Border::BORDER_THIN;
  298.                 break;
  299.             default:
  300.                 throw new Exception("Define a border style", 1);
  301.                 break;
  302.         }
  303.         switch ($borderPosition) {
  304.             case 'top':
  305.                 $this->objPHPExcel->getActiveSheet()->getStyle($coords)->getBorders()->getTop()->setBorderStyle($borderStyle);
  306.                 break;
  307.             case 'bottom':
  308.                 $this->objPHPExcel->getActiveSheet()->getStyle($coords)->getBorders()->getBottom()->setBorderStyle($borderStyle);
  309.                 break;
  310.             default:
  311.                 throw new Exception("Define border position", 1);
  312.                 break;
  313.         }
  314.     }
  315.  
  316.     function SetBackground($coords, $color){
  317.         $this->objPHPExcel->getActiveSheet()->getStyle($coords)->getFill()
  318.             ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
  319.             ->getStartColor()->setARGB('FF'.$color);
  320.     }
  321.  
  322.     function SetForeground($coords, $color){
  323.         $this->objPHPExcel->getActiveSheet()->getStyle($coords)
  324.             ->getFont()->getColor()->setARGB('FF'.$color);
  325.     }
  326.  
  327.     function SetCellValue($coords, $value){
  328.         $this->objPHPExcel->getActiveSheet()
  329.             ->setCellValue($coords, $value);
  330.     }
  331.  
  332.     function ToCoords($row, $col){
  333.         $coord = strtoupper(chr($col+97));
  334.         if(!is_null($row)){
  335.             $coord .= $row+1;
  336.         }
  337.         return $coord;
  338.     }
  339.  
  340.     function WrapCell($coords){
  341.         $this->objPHPExcel->getActiveSheet()->getStyle($coords)
  342.                 ->getAlignment()->setWrapText(true);
  343.     }
  344. }
  345. ?>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!