Can not populate any Excell cells beyond 3rd row...

Topics: Developer Forum, User Forum
Aug 18, 2014 at 4:43 PM
Hello,

I'm using PHPExcel 1.79.

The issue that I'm having is populating and formatting any cells beyond the 3rd row. I can populate any cells in the 2 rows, but beyond that... nothing.

Has anyone else encountered this problem? If so, how did you resolve it? Any help is appreciated...

The following is what I've used to format and populate so far.

//
// Format first row heading...
//


$objPHPExcel->getActiveSheet()->getStyle('C1:E1')
            ->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
// Format vertical headings.
$objPHPExcel->getActiveSheet()->getStyle('C1:I1')
            ->getAlignment()->setTextRotation(-90);
$objPHPExcel->getActiveSheet()->getStyle('L1:M1')
            ->getAlignment()->setTextRotation(-90);
//Fill row 2 as a border
$objPHPExcel->getActiveSheet()->getStyle('A2:ZZ2')
            ->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()->setARGB('00dddddd');
// Fill columns/cells
$objPHPExcel->getActiveSheet()->getStyle('C1')
            ->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
                       ->getStartColor()->setARGB('00dddddd');
$objPHPExcel->getActiveSheet()->getStyle('F1')
            ->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()->setARGB('00dddddd');
$objPHPExcel->getActiveSheet()->getStyle('H1')
            ->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()->setARGB('00dddddd');
$objPHPExcel->getActiveSheet()->getStyle('H2:H50')
            ->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()->setARGB('00dddddd');
$objPHPExcel->getActiveSheet()->getStyle('N1')
            ->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()->setARGB('00dddddd');
$objPHPExcel->getActiveSheet()->getStyle('N2:N50')
            ->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()->setARGB('00dddddd');
$objPHPExcel->getActiveSheet()->getStyle('P1:P50')
            ->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()->setARGB('00ffdddd');
$objPHPExcel->getActiveSheet()->getStyle('R1:R100')
            ->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()->setARGB('00dddddd');
// Add a bottom border below the menu
//$objPHPExcel->getActiveSheet()->getStyle('A2:ZZ3')->getBorders()->applyFromArray(
$objPHPExcel->getActiveSheet()->getStyle('A20:H20')->getBorders()->applyFromArray(
    array(
            'bottom' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => array(
                                'argb' =>'00ffdddd')
                            )
        )  
    );
// Set Column widths
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(4);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(4);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(4);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(4);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(4);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(4);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(4);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(4);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(35);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(6);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(35);
$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(10);
Aug 18, 2014 at 8:00 PM
OK...

Figured out the issue. The problem was with the following:
$objPHPExcel->getActiveSheet()->getStyle('A2:ZZ2')

After changing the "ZZ2" to a more realistic value the cells are populating correctly.
Coordinator
Aug 18, 2014 at 9:28 PM
The column limit for BIFF files (Excel5) is IV, the column limit for OfficeOpenXML (Excel2007) files is XFD
Aug 19, 2014 at 1:54 PM
Hi Mark,
Thanks for that info. I wasn't aware, but should have known, that there is a column limit.

----------
Keiland Pullen
NCC ITS Web - Team
630.637.5464