Nested row grouping in last row - no expander?

Topics: Developer Forum
Nov 30, 2011 at 9:41 AM
Edited Nov 30, 2011 at 11:49 AM

Hello!

I have a problem with the nested grouping in the last row. I am reading a tree from the database which should be exported to excel using grouping.

The problem is, that the last row in the third level is not showing a collapsable/expandable grouping area at the third level. But using the numbers above (1 / 2 / 3 /4) will show/hide the correct nodes in the tree.

I also tried an isolation of the problem with a smaller example but the same problem occurs. The fact is that the layer dots at the place of the grouping are in the correct column (see under layer 4 screenshot on the left side) but there is no expander.

I am trying since hours to fix the problem. Is there someone who can help me?

Small compressed isolated example:

$objPHPExcel->getActiveSheet()->setCellValue('A1', "test1");	
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setOutlineLevel(0);
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setVisible(true);
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setCollapsed(false);

$i=2;
while($i<1000)
{

	$objPHPExcel->getActiveSheet()->setCellValue('A' . $i, "test".$i);	
	$objPHPExcel->getActiveSheet()->getRowDimension($i)->setOutlineLevel(1);
	$objPHPExcel->getActiveSheet()->getRowDimension($i)->setVisible(true);
	$objPHPExcel->getActiveSheet()->getRowDimension($i)->setCollapsed(false);
	$i++;
	
	for($j=0;$j<5;$j++)
	{
		$objPHPExcel->getActiveSheet()->setCellValue('A' . $i, "test".$i);	
		$objPHPExcel->getActiveSheet()->getRowDimension($i)->setOutlineLevel(2);
		$objPHPExcel->getActiveSheet()->getRowDimension($i)->setVisible(true);
		$objPHPExcel->getActiveSheet()->getRowDimension($i)->setCollapsed(false);		
		$i++;
		
		for($k=0;$k<5;$k++)
		{
			$objPHPExcel->getActiveSheet()->setCellValue('A' . $i, "test".$i);	
			$objPHPExcel->getActiveSheet()->getRowDimension($i)->setOutlineLevel(3);
			$objPHPExcel->getActiveSheet()->getRowDimension($i)->setVisible(true);
			$objPHPExcel->getActiveSheet()->getRowDimension($i)->setCollapsed(false);			
			$i++;
		}
	}
}

The complete code:

<?php
/**
 * PHPExcel
 *
 * Copyright (C) 2006 - 2011 PHPExcel
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 *
 * @category   PHPExcel
 * @package    PHPExcel
 * @copyright  Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
 * @version    1.7.6, 2011-02-27
 */

/** Error reporting */
error_reporting(E_ALL);

date_default_timezone_set('Europe/London');

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

include '../global/globalfunctions.php';

$rowdimensiongap=0;


// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set properties
$objPHPExcel->getProperties()->setCreator("Bjoern Karpenstein")
							 ->setLastModifiedBy("Bjoern Karpenstein")
							 ->setTitle("4Sight Report for country ".$_GET["country"])
							 ->setSubject("4Sight Report for country ".$_GET["country"])
							 ->setDescription("This report has been generated using 4Sight Reporting")
							 ->setKeywords("4Sight Reporting APO Bjoern Karpenstein Daniela Sennert")
							 ->setCategory("4Sight Report for country ".$_GET["country"]);

        set_time_limit (1000);
    	database_connect();
 	    $result = mssql_query("SELECT DISTINCT countryname FROM [compcodetocountryregion] WHERE countryname='".$_GET["country"]."'");
 	    
 	    $zeile=4;
 	    $outlinelevel=0;
 	    
 	    // Ebene 1 Länderauswahl
    	while (($row = mssql_fetch_array($result, MSSQL_BOTH))) 
    	{ 
    		$country=$row[0];
    		
    		$objPHPExcel->getActiveSheet()->setCellValue('A' . $zeile, $country);
    		$objPHPExcel->getActiveSheet()->setCellValue('M' . $zeile, $outlinelevel);
    		
			$objPHPExcel->getActiveSheet()->getRowDimension($zeile)->setOutlineLevel($outlinelevel);
			$objPHPExcel->getActiveSheet()->getRowDimension($zeile)->setVisible(true);
			$objPHPExcel->getActiveSheet()->getRowDimension($zeile)->setCollapsed(false);

			$result2 = mssql_query("SELECT DISTINCT businessunit FROM [gruppe]");
    		
    		$zeile++;
    		$outlinelevel++;
	    	
	    	
	    	// Ebene 2 Businessunits
	    	while (($row2 = mssql_fetch_array($result2, MSSQL_BOTH))) 
    		{ 
    			$businessunit=$row2[0];
    			
    			$objPHPExcel->getActiveSheet()->setCellValue('B' . $zeile, $businessunit);
    			$objPHPExcel->getActiveSheet()->setCellValue('M' . $zeile, $outlinelevel);
				$objPHPExcel->getActiveSheet()->getRowDimension($zeile)->setOutlineLevel($outlinelevel);
				$objPHPExcel->getActiveSheet()->getRowDimension($zeile)->setVisible(true);
				$objPHPExcel->getActiveSheet()->getRowDimension($zeile)->setCollapsed(false);
    		
	    		// Ebene 3 Produktgruppe zu Businessunit
    		   	$result3 = mssql_query("SELECT description FROM [gruppe] WHERE [businessunit]='".$businessunit."'");
    		   	
     			$zeile++;    		
     			$outlinelevel++;	
	    	
		    	while (($row3 = mssql_fetch_array($result3, MSSQL_BOTH))) 
    			{ 
    				$productgroup=$row3[0];
    				
    				$objPHPExcel->getActiveSheet()->setCellValue('C' . $zeile, $productgroup);
    				$objPHPExcel->getActiveSheet()->setCellValue('M' . $zeile, $outlinelevel);
					$objPHPExcel->getActiveSheet()->getRowDimension($zeile)->setOutlineLevel($outlinelevel);
					$objPHPExcel->getActiveSheet()->getRowDimension($zeile)->setVisible(true);
					$objPHPExcel->getActiveSheet()->getRowDimension($zeile)->setCollapsed(false);
    		
    				$zeile++;
    				$outlinelevel++;    		

		    		/********************** An dieser Stelle beginnen die Planwerte ****************************/
    				
    				
    				$objPHPExcel->getActiveSheet()->setCellValue('D' . $zeile, "Geht");
					$objPHPExcel->getActiveSheet()->getRowDimension($zeile)->setOutlineLevel($outlinelevel);
					$objPHPExcel->getActiveSheet()->getRowDimension($zeile)->setVisible(true);
					$objPHPExcel->getActiveSheet()->getRowDimension($zeile)->setCollapsed(false);
					    				
    				$zeile++;
    				
    				$objPHPExcel->getActiveSheet()->setCellValue('D' . $zeile, "Das?");
					$objPHPExcel->getActiveSheet()->getRowDimension($zeile)->setOutlineLevel($outlinelevel);
					$objPHPExcel->getActiveSheet()->getRowDimension($zeile)->setVisible(true);
					$objPHPExcel->getActiveSheet()->getRowDimension($zeile)->setCollapsed(false);    				
    				$zeile++;
					$objPHPExcel->getActiveSheet()->getRowDimension($zeile)->setOutlineLevel($outlinelevel);
					$objPHPExcel->getActiveSheet()->getRowDimension($zeile)->setVisible(true);
					$objPHPExcel->getActiveSheet()->getRowDimension($zeile)->setCollapsed(false);        				    				

					
					/**************************************** ENDE DER PLANWERTE ******************************************************/
    				$outlinelevel--;
    				$objPHPExcel->getActiveSheet()->getRowDimension($zeile)->setOutlineLevel($outlinelevel);
					$objPHPExcel->getActiveSheet()->getRowDimension($zeile)->setVisible(true);  
					$objPHPExcel->getActiveSheet()->getRowDimension($zeile)->setCollapsed(false);

    			}
    			
    			$outlinelevel--;
				$objPHPExcel->getActiveSheet()->getRowDimension($zeile)->setOutlineLevel($outlinelevel);
				$objPHPExcel->getActiveSheet()->getRowDimension($zeile)->setVisible(true);
				$objPHPExcel->getActiveSheet()->getRowDimension($zeile)->setCollapsed(false);
    		}			
    	} 

	    mssql_free_result($result);
    	database_close();
            

// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Simple');


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


// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="bier.xlsx"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;