more than 260 columns = damaged file - excel 2007

Topics: Developer Forum, User Forum
Jul 18, 2008 at 9:36 PM
Edited Jul 18, 2008 at 10:33 PM
Hi There,

I am using PHPExcel with the code below. Unfortunately i discovered the amount of rows you can create is limited to 260.

More than 260 columns results in a damaged file, and warning message in excel 2007.

I first noticed it while debugging my code, then, to test if my conclusion was right, i created the script below, based on a test script in this discussionlist.

Check my code below:

/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . 'c:/wamp/www/kabk/classes/');


/** PHPExcel */
include 'classes/PHPExcel.php';

/** PHPExcel_IOFactory */
include 'classes/PHPExcel/IOFactory.php';

$objPHPExcel = new PHPExcel();

$objPHPExcel->getProperties()->setCreator("test");
$objPHPExcel->getProperties()->setLastModifiedBy("test");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
$objPHPExcel->getProperties()->setKeywords("office 2007 openxml php");
$objPHPExcel->getProperties()->setCategory("Test result file");

$a = 260; //cols
$b = 10; // rows
$r=2; // Set row pointer
for($i=1;$i<=$b;$i++) {
    $c=0; // Set column pointer
    for($j=1;$j<=$a;++$j) {
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($c++,$r,"data $j");
    }
   $r++;  
}

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

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save("limiet_test.xlsx");


I know for a fact excel 2007 can handle 16k rows. Link to microsoft blog with excel limits listed

Is there some way to get around this PHPExcel 260 column limitation?

Regards,

Sander




Developer
Jul 19, 2008 at 6:13 AM
Yeah, it is not working with PHPExcel 1.6.2. But Maarten fixed that. You will have to download the latest source. Click on "Source Code" and grab the latest snapshot.

Related issue:
http://www.codeplex.com/PHPExcel/WorkItem/View.aspx?WorkItemId=6275


Jul 19, 2008 at 6:01 PM
Thanks for the information Koyama!