[FIXED] How do I remove column in PHPExcel

Issue

I want to remove highlighted column from my Excel sheet. Please check the code:

// generate report
if($genReport) {
    include_once("../../../includes/dbcon/mysql-crm.php");

    //01.generate first date and last day
    $month_end = date('d', strtotime($toDate));
    $month_first = date('d', strtotime($fromDate));

    include("../../../includes/addons/php-excel-gen/PHPExcel.php");
    $objPHPExcel = new PHPExcel();
    $objPHPExcel->getProperties()->setCreator("Shanka Nuwan")
                         ->setLastModifiedBy("Shanka Nuwan")
                         ->setTitle("Call Center Report - ".$reportNamePart)
                         ->setSubject("Call Center Report - ".$reportNamePart)
                         ->setDescription("Call Center Report - ".$reportNamePart)
                         ->setKeywords("Call Center Report - ".$reportNamePart)
                         ->setCategory("Call Center");
    $objPHPExcel->setActiveSheetIndex(0);
    $objPHPExcel->getActiveSheet()->mergeCells('A1:C1');
    $objPHPExcel->getDefaultStyle()->getFont()->setSize(15);

    $objRichText = new PHPExcel_RichText();
    $objPayable = $objRichText->createTextRun($reportNamePart);
    $objPayable->getFont()->setBold(true);
    $objPayable->getFont()->setSize(14); 
    $objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN ) );
    $objPHPExcel->getActiveSheet()->getCell('A1')->setValue($objRichText);

    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A2', 'Date');
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A3', 'SHOWROOM');

    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
    $objPHPExcel->getDefaultStyle()->getFont()->setSize(10); 

        //add allboder style
        $BStyle = array(
            'borders' => array(
              'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              )
            )
          );
        $objPHPExcel->getActiveSheet()->getStyle('A2:BI105')->applyFromArray($BStyle);

    $row = 4;

    // generate report column
    $column = 'B';
    $column2 = 'C';
    $x = $month_first;

    for(;$x<=$month_end;) {

        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($column2.'3', 'Enquiry');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($column2.'2', $x);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setVisible(false);

        $x++;

        if($x<=$month_end) {
            $column2++;
            $column = $column2;
            $column2++;
        }
    }

    // add total column after dates
    $column2++;
    $column = $column2;
    $column2++;
    //$objPHPExcel->getActiveSheet()->mergeCells($column.'2:'.$column2.'2');
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A105', 'Total');
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($column2.'3', 'Enquiries Total');
    /*
    $styleArray = array(
    'font'  => array(
        'bold'  => true,
        'color' => array('rgb' => 'FF0000'),
        'size'  => 15,
        'name'  => 'Verdana'
    ));
    */
    $styleArray = array('font'  => array('color' => array('rgb' => 'FF0000')));
    //$objPHPExcel->getActiveSheet()->getStyle($column.'2')->applyFromArray($styleArray);
    //$objPHPExcel->getActiveSheet()->getStyle($column.'3')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->getStyle($column2.'3')->applyFromArray($styleArray);


    //report name letter style
    $objPHPExcel->getDefaultStyle()->getFont()->setSize(11); 
    $objPHPExcel->getActiveSheet()->getStyle('A2:'.$column2.'2')->getFont()->setBold(true);

    $style = array(
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            )
        );
    //align report headers
    $objPHPExcel->getActiveSheet()->getStyle('A2:'.$column2.'2')->applyFromArray($style);
    $objPHPExcel->getActiveSheet()->getStyle('A3:'.$column2.'3')->applyFromArray($style);

    //load location
    $get_sql ="SELECT
    showrooms.showroom_id,
    showrooms.showroom_code,
    showrooms.showroom_name,
    showrooms.showroom_address,
    showrooms.address_city,
    showrooms.manager_name,
    showrooms.manager_mobile,
    showrooms.manager_id,
    showrooms.shop_email,
    showrooms.shop_phone,
    showrooms.shop_fax,
    showrooms.showroom_type,
    showrooms.added_date,
    showrooms.last_update,
    showrooms.added_by,
    showrooms.status_id
    FROM
    showrooms
    INNER JOIN showroom_type ON showrooms.showroom_type = showroom_type.showroom_type_id
    WHERE showrooms.showroom_id > '0' and  
    showrooms.showroom_type = '1' AND 
    showrooms.status_id = '1'
    ORDER BY
    showrooms.showroom_name ASC";

    $get_showroom = $db->get_results($get_sql);
    if($db->num_rows>0){
        $row = 4;
        foreach($get_showroom as $row_showroom){    
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$row , $row_showroom->showroom_name);
            //get showroom id
            $showroom_email= explode('@',$row_showroom->shop_email);
            $showroom_id = $showroom_email[0];
            //check showroom already intract with showroom task
            $sso_user = $db->get_row("SELECT users.user_id FROM users WHERE users.login_name = '".$showroom_id."'");
            $total_inqury = 0;
            if($db->num_rows == 1){
                $user_id =  $sso_user->user_id;
                //date check
                $column = 'B';
                $column2 = 'C';
                $day = $month_first;
                for(;$day<=$month_end;){
                    $byear = date('Y',strtotime($fromDate));
                    $month = date('m',strtotime($fromDate));

                    $check_date = strtotime($byear.'-'.$month.'-'.$day);
                    $user_login = $db->get_row("SELECT 
                                                    Count(sys_id) AS locount 
                                                    FROM 
                                                        sys_user_login 
                                                    WHERE 
                                                        user_id = '".$user_id."' 
                                                    AND log_date = '".date('Y-m-d',$check_date)."'");//$db->debug();
                    if($db->num_rows == 1){
                        if($user_login->locount > 0){
                            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($column.$row , '');
                            $styleArray = array('font'  => array('color' => array('rgb' => 'FF0000')));
                            $objPHPExcel->getActiveSheet()->getStyle($column.$row)->applyFromArray($styleArray);
                            //$total_login++;

                            //back groud color for cell
                            $objPHPExcel->getActiveSheet()->getStyle($column2.$row)->applyFromArray(
                                                                    array(
                                                                        'fill' => array(
                                                                            'type' => PHPExcel_Style_Fill::FILL_SOLID,
                                                                            'color' => array('rgb' => 'FFFF00')
                                                                        )
                                                                    )
                                                                );
                        }else{
                            //$objPHPExcel->setActiveSheetIndex(0)->setCellValue($column.$row , '');
                        }
                    }else{
                        //$objPHPExcel->setActiveSheetIndex(0)->setCellValue($column.$row , '');
                    }

                    //count inquery
                    $inquery_day = $dbCrm->get_row("SELECT
                                                    Count(customers.cu_id) AS inq_day 
                                                    FROM
                                                    customers
                                                    WHERE
                                                    customers.added_by = '".$user_id."' AND
                                                    customers.added_date LIKE '".date('Y-m-d',$check_date)."%'");//$db->debug();
                    if($dbCrm->num_rows == 1){
                        if($inquery_day->inq_day > 0){
                            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($column2.$row ,$inquery_day->inq_day);
                            $total_inqury += $inquery_day->inq_day;
                        }else{
                            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($column2.$row , '0');
                        }
                    }else{
                        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($column2.$row , '0');
                    }

                    $day++;
                    if($day<=$month_end){
                        $column2++;
                        $column = $column2;
                        $column2++;
                    }
                }
                $column2++;
                $column = $column2;
                $column2++;

                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($column2.$row, $total_inqury);                               
                                $objPHPExcel->getActiveSheet()
                                            ->setCellValue(
                                                'C105',
                                                '=SUM(A10:E9)'
                                            );
                $styleArray = array('font'  => array('color' => array('rgb' => 'FF0000')));
                $objPHPExcel->getActiveSheet()->getStyle($column2.$row)->applyFromArray($styleArray);
                $total_inqury = 0;
            }
            $row++;
        }
    }


    //genarate report data
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="showroom-task-report-'.$byear.'-'.strtolower(date('F', mktime(0, 0, 0, $month, 10))).'.xls"');
    header('Cache-Control: max-age=0');

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');
}
?>

below excel out will generate from the above coding. I tried lot of hours to figure out how to get ride of it. Still can't find the solution.

Solution

You use the Worksheet object's removeColumn() or removeColumnByIndex() methods.

removeColumn() accepts a column id by name (e.g. D, F, L).

removeColumnByIndex() accepts a column id by its index number (e.g. 3, 6, 12)

Answered By - Mark Baker

Answer Checked By - David Marino (PHPFixing Volunteer)