How to combine a column in Excel using PHPExcel

I already tested this Merging cells in Excel by row and column together using PHPExcel , but showing it only to add a row, however I applied some tests for the column, but none works.

Although this code successfully creates an excel sheet, the output in excel causes an error.

EDIT

however, this works if you remove llop and put it in simple words -

$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:B1');
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Swapnesh');

The main code for adding data is

// Add some data
echo date('H:i:s') , " Add some data" , EOL;
$i = "A";
$j ="B";
for($num =1; $num <= 5; $num++ )
{
    $concat =  "{$i}1:{$j}1";
    $objPHPExcel->setActiveSheetIndex(0)->mergeCells($concat);
    $i++;$j++;
} 
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Swapnesh');

All my code is

<?php
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

date_default_timezone_set('Asia/Calcutta');

/** Include PHPExcel */
require_once 'Classes/PHPExcel.php';


// Create new PHPExcel object
echo date('H:i:s') , " Create new PHPExcel object" , EOL;
$objPHPExcel = new PHPExcel();

// Set document properties
echo date('H:i:s') , " Set document properties" , EOL;
$objPHPExcel->
getProperties()->setCreator("Swapnesh Sinha")
                             ->setLastModifiedBy("Swapnesh")
                             ->setTitle("Office 2007 XLSX Test Document")
                             ->setSubject("Office 2007 XLSX Test Document")
                             ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
                             ->setKeywords("office 2007 openxml php")
                             ->setCategory("Test result file");


// Add some data
echo date('H:i:s') , " Add some data" , EOL;
$i = "A";
$j ="B";
for($num =1; $num <= 5; $num++ )
{
    $concat =  "{$i}1:{$j}1";
    $objPHPExcel->setActiveSheetIndex(0)->mergeCells($concat);
    $i++;$j++;
} 
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Swapnesh');

// Rename worksheet
echo date('H:i:s') , " Rename worksheet" , EOL;
$objPHPExcel->getActiveSheet()->setTitle('Student Data');


// Set document security
echo date('H:i:s') , " Set document security" , EOL;
$objPHPExcel->getSecurity()->setLockWindows(true);
$objPHPExcel->getSecurity()->setLockStructure(true);
$objPHPExcel->getSecurity()->setWorkbookPassword("PHPExcel");


// Set sheet security
echo date('H:i:s') , " Set sheet security" , EOL;
$objPHPExcel->getActiveSheet()->getProtection()->setPassword('PHPExcel');
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); // This should be enabled in order to enable any of the following!
$objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
$objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);
$objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);


// Save Excel 2007 file
echo date('H:i:s') , " Write to Excel2007 format" , EOL;
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
//$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
$filename = "Student-data-sheet".".xlsx";
$objWriter->save($filename);
echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;


// Echo memory peak usage
echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL;

// Echo done
echo date('H:i:s') , " Done writing file" , EOL;
echo 'File has been created in ' , getcwd() , EOL; 
+5
source share
2 answers

I figure out the solution myself -

In fact, in the case of merging columns, we do not need iteration. let's say I want to merge from column A1to E1.

$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:E1');

, mergeCells(), .

+10

, . , , , . , excel .

excel, .

/* manage excel index */
function getColumn($prevColumn){
    if(strlen($prevColumn)==1&&$prevColumn!="Z")
    { $prevColumn++;return $prevColumn; }

    $colum = '';
    $list = str_split($prevColumn);

    if(count($list)==1&&$list[0]=='Z')
    {
        $list[0] = "A";
        $list[1] = "A";
    }
    else if(count($list)==2&&$list[1]!='Z')
    {
        $list[1]++;
}
    else if(count($list)==2&&$list[1]=='Z')
    {
        $list[0] = "B";
        $list[1] = "A";
}
return implode('',$list);
}

## us this like below

$column = "A";
$row = 1;
foreach($array_to_print as $details)
{

$column = getColumn($column); // product next column B
$Excelobj->getActiveSheet()->setCellValue($column.$row, $details);
$column = getColumn($column);// product next column C
$Excelobj->setActiveSheetIndex(0)->mergeCells($from_cell.':'.$to_cell);// now this will  work till A-ZZ column in excel
}
+5

All Articles