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?
I think you are a victim of loosely-typed comparisons here - you have specified null
as your second parameter to fromArray()
, which represents the Value in source array that stands for blank cell
. Since null == 0
this means that zero will result in a blank cell unless you set the fourth argument to true
.
Try changing:
$objPHPExcel->getActiveSheet()->fromArray( $data, null, 'A1' );
...to...
$objPHPExcel->getActiveSheet()->fromArray( $data, null, 'A1', true );
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With