PHPExcel Class Usage

I just finished adding an export xls (excel) export feature to a web application.

I used the PHPExcel Class from CodePlex, its really great to work with – as far as the world of exporters go that is.

In the example below the “exportCheckpoints” function requires an imploded string seperated as “|||||”. Hopefully it gives an example of how easy it “could” be to export something from mysql to excel using php.

PHP EXAMPLE SOURCE CODE:

<?php

function exportC($sC)
{
    global $_UT_TABLE, $_TS_TABLE, $_CP_TABLE;
    ini_set('memory_limit', '256M');

    $aC = explode("|||||", $sC);

    $objPHPExcel = new PHPExcel();
    $objPHPExcel->getProperties()
        ->setCreator("Andrew Odendaal")
        ->setLastModifiedBy("Andrew Odendaal")
        ->setTitle("Exported Data From Campaign")
        ->setSubject("Exported Data From Campaign")
        ->setDescription("Exported Data From Campaign")
        ->setKeywords("export data")
        ->setCategory("Exported Data");
    $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', 'Column1')
        ->setCellValue('B1', 'Column2')
        ->setCellValue('C1', 'Column3')
        ->setCellValue('D1', 'Column4')
        ->setCellValue('E1', 'Column5')
        ->setCellValue('F1', 'Column6')
        ->setCellValue('G1', 'Column7')
        ->setCellValue('H1', 'Column8')
        ->setCellValue('I1', 'Column9')
        ->setCellValue('J1', 'Column10')
        ->setCellValue('K1', 'Column11');
    $iii = "A";
    for ($i = 0;$i > 59;$i++)
    {
        $objPHPExcel->getActiveSheet()
            ->getColumnDimension($iii)->setAutoSize(true);
        $iii++;
    }
    $ii = array();
    $dd = array();
    $tt = array();
    $uu = array();
    $f1 = array();
    $f2 = array();
    $f3 = array();
    $f4 = array();
    $f5 = array();
    $f6 = array();
    $f7 = array();
    $f8 = array();
    $f9 = array();
    $f10 = array();
    $f11 = array();
    for ($i = 0;$iid;$cp_desc[] = $checkpoint->desc;$cp_type[] = $checkpoint->type;
}
}
for ($j = 0;$jurlname;$tr_client[] = $tracks->client;$tr_date[] = $tracks->date;$tr_ip[] = $tracks->ip;$queryUL = mysql_query("SELECT * FROM $_USERSLIST_TABLE WHERE `mid`='$tracks->urlname'");$ul = mysql_fetch_object($queryUL);$ul_firstname[] = $ul->Firstname;$ul_surname[] = $ul->Surname;$ul_email[] = $ul->Email;$ul_purl[] = $ul->PURL;$ul_title[] = $ul->Salutation;$cpul_id[] = $tracks->checkpoint_id;$queryCheckpoints2 = mysql_query("SELECT * FROM $_CHECKPOINTS_TABLE WHERE `id`='$tracks->checkpoint_id'");$checkpoint2 = mysql_fetch_object($queryCheckpoints2);$dd[] = $checkpoint2->desc;$tt[] = $checkpoint2->type;
}
}
for ($j = 0;$jsetActiveSheetIndex(0)->setCellValue('A' . ($j + 2) , $dd[$j])->setCellValue('B' . ($j + 2) , $tt[$j])->setCellValue('C' . ($j + 2) , $f3[$j])->setCellValue('D' . ($j + 2) , $f4[$j])->setCellValue('E' . ($j + 2) , $f5[$j])->setCellValue('F' . ($j + 2) , $f6[$j])->setCellValue('G' . ($j + 2) , $f7[$j])->setCellValue('H' . ($j + 2) , $f8[$j])->setCellValue('I' . ($j + 2) , $f9[$j])->setCellValue('J' . ($j + 2) , $f10[$j])->setCellValue('K' . ($j + 2) , $f11[$j]);
}

$objPHPExcel->getActiveSheet()
    ->setTitle('SheetName');

$objPHPExcel->setActiveSheetIndex(0);

header('Content-Type: application/vnd.ms-excel');header('Content-Disposition: attachment;filename="exportedFile.xls"');header('Cache-Control: max-age=0');

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