PHP PHPExcel_Writer_Excel5::setPreCalculateFormulas方法代码示例

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