Zeros coming up as blanks

Feb 28, 2012 at 2:20 PM

I'm building a WordPress plugin using PHPExcel to export reports of student attendance.  So far, I'm able to successfully take the array of student data (including attendance) and convert and download it as a spreadsheet.  Unfortunately, there's an odd bug: when a student has attended zero events, that cell on the spreadsheet is blank instead of showing a zero.  I'd prefer it come out with an actual zero, but I can't find a way to do that.  Here's my script:

        error_reporting( E_ALL);
	ini_set( 'include_path', ini_get( 'include_path' ).';../Classes/' );

	include 'PHPExcel.php';
	include 'PHPExcel/Writer/Excel2007.php';
	
	$objPHPExcel = new PHPExcel();
	
	$objPHPExcel->setActiveSheetIndex( 0 );
	$objPHPExcel->getProperties()->setCreator( $user );
	$objPHPExcel->getProperties()->setLastModifiedBy( $user );
	$objPHPExcel->getProperties()->setTitle( $title );
	$objPHPExcel->getProperties()->setSubject( $title );
	$objPHPExcel->getProperties()->setDescription( $title );
	$objPHPExcel->getActiveSheet()->fromArray( $data, null, 'A1' );
	$objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getFont()->setBold(true);
	
	$columns = array( 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H' );
	
	foreach ( $columns as $column ) {
	
		$objPHPExcel->getActiveSheet()->getColumnDimension($column)->setAutoSize(true);
	
	} // End $columns foreach
		
	$file_name = str_replace( ' ', '_', $title);

	if ( $_POST['type'] == 'spreadsheet' ) {
	
		header( 'Content-type: application/vnd.ms-excel' );	
		header( 'Content-Disposition: attachment; filename="' . $file_name . '.xls"' );
	
		$objWriter = new PHPExcel_Writer_Excel2007( $objPHPExcel );
		$objWriter->save( 'php://output' );
	
	} 

$data contains the array of student info, which is structured like this:

Array
(
    [0] => Array
        (
            [0] => First Name
            [1] => Last Name
            [2] => BU ID
            [3] => Barcode
            [4] => Major
            [5] => Class
            [6] => Seminars
            [7] => Showcases
        )

    [1] => Array
        (
            [first_name] => Seth
            [last_name] => Abel
            [buid] => B00385307
            [barcode] => 100324707
            [student_major] => CMM
            [class] => MBU2100
            [seminar] => 0
            [showcase] => 1
        )

    [2] => Array
        (
            [first_name] => Zachary
            [last_name] => Abend
            [buid] => B00373103
            [barcode] => 100291996
            [student_major] => MBU
            [class] => MBU2100
            [seminar] => 0
            [showcase] => 1
        )
.
.
.

)

As you can see is the example data, the those students have each attended 1 showcase, but zero seminars.  When this spreadsheet is downloaded and opened in Excel, the 1 shows up fine, but the seminar cells are blank.  Since I'm using the fromArray() function to populate the spreadsheet, is there something I should do to make those zeros appear properly?

Mar 8, 2012 at 6:38 PM

Does anyone have an idea about how to fix this?  Mark?

Mar 14, 2012 at 4:30 PM

Finally got the answer on Stack Exchange:

Change

$objPHPExcel->getActiveSheet()->fromArray( $data, null, 'A1' );

...to...

$objPHPExcel->getActiveSheet()->fromArray( $data, null, 'A1', true );