asela nuwan asela nuwan - 3 months ago 15
PHP Question

How do I remove column in PHPExcel

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.

excel file

Answer

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)