/**
* The MysqlExportXls function is used to export mysql query result into an .xls file.
* @param MysqlExportXlsConnectOptions $connectOptions
* @param MysqlExportXlsFileOptions $fileOptions
* @return error message. Return empty string on success.
*/
function MysqlExportXls($connectOptions, $fileOptions, $query)
{
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator($fileOptions->creator);
$objPHPExcel->getProperties()->setLastModifiedBy($fileOptions->lastModifiedBy);
$objPHPExcel->getProperties()->setTitle($fileOptions->title);
$objPHPExcel->getProperties()->setSubject($fileOptions->subject);
$objPHPExcel->getProperties()->setDescription($fileOptions->description);
$objPHPExcel->setActiveSheetIndex(0);
$activeSheet = $objPHPExcel->getActiveSheet();
$activeSheet->setTitle($fileOptions->title);
// connect to mysql
$link = mysql_connect($connectOptions->host, $connectOptions->userName, $connectOptions->password);
if (!$link) {
return __FILE__ . ":" . __FUNCTION__ . ':' . 'Could not connect: ' . mysql_error($link);
}
// use database
$selectDb = mysql_select_db($connectOptions->useDatabase, $link);
if (!$selectDb) {
return __FILE__ . ":" . __FUNCTION__ . ':' . 'Could not select database' . mysql_error($link);
}
// PHPExcel use utf-8 encoding to save file only !!!
$setCharset = mysql_set_charset("utf8", $link);
if (!$setCharset) {
return __FILE__ . ":" . __FUNCTION__ . ':' . 'Could not set charset' . mysql_error($link);
}
// execute sql
$result = mysql_query($query, $link);
if (!$result) {
return __FILE__ . ":" . __FUNCTION__ . ':' . 'Query failed: ' . mysql_error($link);
}
// field names
$columnIndex = 0;
while ($field = mysql_fetch_field($result)) {
$activeSheet->SetCellValue(PHPExcel_Cell::stringFromColumnIndex($columnIndex) . '1', $field->name);
++$columnIndex;
}
$rowIndex = 2;
// 1 based, the firset row is for field names.
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$columnIndex = 0;
foreach ($line as $key => $col_value) {
$activeSheet->SetCellValue(PHPExcel_Cell::stringFromColumnIndex($columnIndex) . $rowIndex, $col_value === null ? "" : $col_value, PHPExcel_Cell_DataType::TYPE_STRING2);
++$columnIndex;
}
++$rowIndex;
}
// free mysql resource
mysql_free_result($result);
mysql_close($link);
// write data into file
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->setPreCalculateFormulas(FALSE);
// Why true by default ? oh god damn it!
$objWriter->save($fileOptions->name);
return "";
}
/**
* The ExportXlsFromArray function is used to dump an array into xls file.
*/
function ExportXlsFromArray(ExportXlsFileOptions $fileOptions, array $array)
{
if (count($array) == 0 || count($array[0]) == 0) {
return "Array is emtpty.";
}
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator($fileOptions->creator);
$objPHPExcel->getProperties()->setLastModifiedBy($fileOptions->lastModifiedBy);
$objPHPExcel->getProperties()->setTitle($fileOptions->title);
$objPHPExcel->getProperties()->setSubject($fileOptions->subject);
$objPHPExcel->getProperties()->setDescription($fileOptions->description);
$objPHPExcel->setActiveSheetIndex(0);
$activeSheet = $objPHPExcel->getActiveSheet();
$activeSheet->setTitle($fileOptions->title);
$fields = array();
// field names
$columnIndex = 0;
foreach ($array[0] as $key => $value) {
$fields[] = $key;
$activeSheet->SetCellValue(PHPExcel_Cell::stringFromColumnIndex($columnIndex) . '1', $key);
++$columnIndex;
}
$rowIndex = 2;
// 1 based, the firset row is for field names.
foreach ($array as $line) {
$columnIndex = 0;
foreach ($fields as $field) {
$activeSheet->SetCellValue(PHPExcel_Cell::stringFromColumnIndex($columnIndex) . $rowIndex, !isset($line[$field]) || $line[$field] === null ? "" : $line[$field], PHPExcel_Cell_DataType::TYPE_STRING2);
++$columnIndex;
}
++$rowIndex;
}
// write data into file
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->setPreCalculateFormulas(FALSE);
// Why true by default ? oh god damn it!
$objWriter->save($fileOptions->name);
return "";
}
public static function export2Excel($objPHPExcel, $versionExcel = 'Excel2007', $fileName = '')
{
switch ($versionExcel) {
case 'Excel2007':
// redirect output to client browser with Excel2007
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->setOffice2003Compatibility(true);
$objWriter->setPreCalculateFormulas(false);
$objWriter->save('php://output');
break;
case 'Excel5':
// redirect output to client browser with Excel 2005
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $fileName . '.xls"');
header('Cache-Control: max-age=0');
$objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->setPreCalculateFormulas(false);
$objWriter->save('php://output');
break;
default:
break;
}
}
