/** * 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; } }