PHP PHPExcel_Writer_Excel2007类代码示例

 public function toExcel($SiteName, $StrarTime, $FinshTime)
 {
     /** Error reporting */
     error_reporting(E_ALL);
     /** Include path **/
     ini_set('include_path', ini_get('include_path') . ';../Classes/');
     /** PHPExcel */
     include 'PHPExcel.php';
     /** PHPExcel_Writer_Excel2007 */
     include 'PHPExcel/Writer/Excel2007.php';
     // Create new PHPExcel object
     // echo date('H:i:s') . " Create new PHPExcel object\n";
     $objPHPExcel = new PHPExcel();
     // Set properties
     // echo date('H:i:s') . " Set properties\n";
     $objPHPExcel->getProperties()->setCreator("Chen Po Hsun");
     $objPHPExcel->getProperties()->setLastModifiedBy("Po Hsun");
     $objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
     $objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
     $objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
     // Add some data
     // echo date('H:i:s') . " Add some data\n";
     $time_diff = (strtotime($time1) - strtotime($time2)) / (60 * 60) + 1;
     $begin = 2;
     $objPHPExcel->setActiveSheetIndex(0);
     $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'SiteName');
     $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Country!');
     $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'PM2.5');
     $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'PM10');
     $objPHPExcel->getActiveSheet()->SetCellValue('F1', 'CO');
     $data['query'] = $this->sql->select_site($SiteName, $StrarTime);
     while ($begin < $time_diff + 2) {
         $objPHPExcel->getActiveSheet()->SetCellValue('B1', $data['SiteName']);
         $objPHPExcel->getActiveSheet()->SetCellValue('C1', $data['Country']);
         $objPHPExcel->getActiveSheet()->SetCellValue('D1', $data['PM2.5']);
         $objPHPExcel->getActiveSheet()->SetCellValue('E1', $data['PM10']);
         $objPHPExcel->getActiveSheet()->SetCellValue('F1', $data['CO']);
         $objPHPExcel->getActiveSheet()->SetCellValue('A$begin', $data['PublishTime']);
         $begin++;
         $data['query'] = $this->sql->select_site($SiteName, $FinshTime);
     }
     // Rename sheet
     // echo date('H:i:s') . " Rename sheet\n";
     $objPHPExcel->getActiveSheet()->setTitle('Test');
     // Save Excel 2007 file
     // echo date('H:i:s') . " Write to Excel2007 format\n";
     $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
     $objWriter->save(str_replace('.php', '.xlsx', __FILE__));
     // Echo done
     // echo date('H:i:s') . " Done writing file.\r\n";
 }

 function display()
 {
     global $current_user;
     $c = range('A', 'Z');
     // Create new PHPExcel object
     $objPHPExcel = new PHPExcel();
     // set column size to auto
     foreach ($c as $columnID) {
         $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
     }
     // Set properties
     $objPHPExcel->getProperties()->setCreator($current_user->display_name);
     $objPHPExcel->getProperties()->setLastModifiedBy($current_user->display_name);
     $objPHPExcel->getProperties()->setTitle($this->title);
     $objPHPExcel->getProperties()->setSubject($this->subject);
     $objPHPExcel->getProperties()->setDescription($this->description);
     // Add some data
     $objPHPExcel->setActiveSheetIndex(0);
     // table header
     $x = 0;
     foreach ($this->cols as $v) {
         $objPHPExcel->getActiveSheet()->SetCellValue($c[$x] . '1', $v)->getStyle($c[$x] . '1')->getFont()->setBold(true);
         // A1, B1, etc
         $x++;
     }
     // table row
     foreach ($this->data as $i => $item) {
         $j = $i + 2;
         // A2, B2, etc
         $x = 0;
         $index = $i + 1;
         $item->index = 0;
         foreach ($this->cols as $column_name => $v) {
             if ($column_name == 'index') {
                 $item->index = 10;
             }
             #$val = $item->$column_name;
             $method = 'column_' . $column_name;
             if (method_exists($this->table_obj, $method)) {
                 $val = $this->table_obj->{$method}($item, $column_name);
             } else {
                 $val = $this->table_obj->column_default($item, $column_name);
             }
             $objPHPExcel->getActiveSheet()->SetCellValue($c[$x] . $j, $val, $format);
             $x++;
         }
     }
     $objPHPExcel->getActiveSheet()->setTitle('Sheet 1');
     $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
     header('Content-type: application/vnd.ms-excel');
     header('Content-Disposition: attachment; filename="' . $this->filename . '"');
     $objWriter->save('php://output');
     exit;
 }

 public function flush()
 {
     if (!$this->active) {
         return;
     }
     $this->outputResponse->setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
     $writer = new \PHPExcel_Writer_Excel2007($this->excelDoc);
     $tempFile = tempnam(sys_get_temp_dir(), 'NiysuExcel');
     $writer->save($tempFile);
     $this->outputResponse->appendData(file_get_contents($tempFile));
     unlink($tempFile);
 }

 /**
  * Outputs export footer
  *
  * @return  bool        Whether it suceeded
  *
  * @access  public
  */
 function PMA_exportFooter()
 {
     global $workbook;
     global $tmp_filename;
     $tmp_filename = tempnam(realpath($GLOBALS['cfg']['TempDir']), 'pma_xlsx_');
     $workbookWriter = new PHPExcel_Writer_Excel2007($workbook);
     $workbookWriter->save($tmp_filename);
     if (!PMA_exportOutputHandler(file_get_contents($tmp_filename))) {
         return FALSE;
     }
     unlink($tmp_filename);
     unset($GLOBALS['workbook']);
     unset($GLOBALS['sheet_index']);
     return TRUE;
 }

 public function output()
 {
     $writer = new PHPExcel_Writer_Excel2007($this->spreadsheet);
     $file = "app/temp/" . uniqid() . "_report.xlsx";
     $writer->save($file);
     Application::redirect("/{$file}");
 }

 /**
  * Dumps XLS file to memory & retrieves content
  * @param \PHPExcel $xls
  * @return string
  */
 private function getBuffer(\PHPExcel $xls)
 {
     ob_start();
     $writer = new \PHPExcel_Writer_Excel2007($xls);
     $writer->save('php://output');
     return ob_get_clean();
 }

 public function finalize()
 {
     $writer = new \PHPExcel_Writer_Excel2007($this->excel);
     $writer->setPreCalculateFormulas(false);
     $writer->save($this->filename);
     return $this->filename;
 }

 function _output($title)
 {
     header('Content-Type: application/vnd.openXMLformats-officedocument.spreadsheetml.sheet');
     header('Content-Disposition: attachment;filename="' . $title . '.xlsx"');
     header('Cache-Control: max-age=0');
     $objWriter = new PHPExcel_Writer_Excel2007($this->xls);
     $objWriter->save('php://output');
     exit;
 }

function xlsToXslx($file_name_input, $file_name_output)
{
    try {
        $objPHPexcel = PHPExcel_IOFactory::load($file_name_input);
        $objWriter = new PHPExcel_Writer_Excel2007($objPHPexcel);
        $objWriter->setOffice2003Compatibility(true);
        $objWriter->save($file_name_output);
        return true;
    } catch (Exception $e) {
        return false;
    }
}

 public function generate($format = "Excel5", $docName = "Tabelle")
 {
     switch ($format) {
         case 'Excel2007':
             include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/Excel2007.php';
             $writer = new PHPExcel_Writer_Excel2007($this);
             $ext = 'xlsx';
             $header = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
             //supprime le pre-calcul
             $writer->setPreCalculateFormulas(false);
             break;
         case 'Excel2003':
             include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/Excel2007.php';
             $writer = new PHPExcel_Writer_Excel2007($this);
             $writer->setOffice2003Compatibility(true);
             $ext = 'xlsx';
             //supprime le pre-calcul
             $writer->setPreCalculateFormulas(false);
             break;
         case 'Excel5':
             include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/Excel5.php';
             $writer = new PHPExcel_Writer_Excel5($this);
             $ext = 'xls';
             break;
         case 'CSV':
             include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/CSV.php';
             $writer = new PHPExcel_Writer_CSV($this);
             $writer->setDelimiter(",");
             //l'op�rateur de s�paration est la virgule
             $writer->setSheetIndex(0);
             //Une seule feuille possible
             $ext = 'csv';
             break;
         case 'PDF':
             include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/PDF.php';
             $writer = new PHPExcel_Writer_PDF($this);
             $writer->setSheetIndex(0);
             //Une seule feuille possible
             $ext = 'pdf';
             break;
         case 'HTML':
             include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/HTML.php';
             $writer = new PHPExcel_Writer_HTML($this);
             $writer->setSheetIndex(0);
             //Une seule feuille possible
             $ext = 'html';
             break;
     }
     header('Content-type:' . $header);
     header('Content-Disposition:inline;filename=' . $docName . '.' . $ext);
     $writer->save('php://output');
 }

 public function create_worksheet($excel_data = NUll)
 {
     //check if the excel data has been set if not exit the excel generation
     if (count($excel_data) > 0) {
         //echo "<pre/>";
         //print_r($excel_data);
         $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
         $cacheSettings = array('memoryCacheSize' => '2MB');
         PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
         ini_set('max_execution_time', 123456);
         $objPHPExcel = new PHPExcel();
         $objPHPExcel->getProperties()->setCreator("CD4");
         $objPHPExcel->getProperties()->setLastModifiedBy($excel_data['doc_creator']);
         $objPHPExcel->getProperties()->setTitle($excel_data['doc_title']);
         $objPHPExcel->getProperties()->setSubject($excel_data['doc_title']);
         $objPHPExcel->getProperties()->setDescription("");
         // Add some data
         //	echo date('H:i:s') . " Add some data\n";
         $objPHPExcel->setActiveSheetIndex(0);
         $rowExec = 1;
         //Looping through the cells
         $column = 0;
         // foreach ($excel_data['column_data'] as $cell) {
         // 	$objPHPExcel -> getActiveSheet() -> setCellValueByColumnAndRow($column, $rowExec, $cell);
         // 	$objPHPExcel -> getActiveSheet() -> getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($column)) -> setAutoSize(true);
         // 	$column++;
         // }
         // $rowExec = 2;
         // $column = 0;
         // foreach ($excel_data['row_data'] as $cell) {
         // //Looping through the cells per facility
         // 	$objPHPExcel -> getActiveSheet() -> setCellValueByColumnAndRow($column, $rowExec, $cell);
         // 	$rowExec++;
         // 	$column++;
         // }
         $objPHPExcel->getActiveSheet()->fromArray($excel_data['row_data'], NULL, 'A1');
         // Rename sheet
         //	echo date('H:i:s') . " Rename sheet\n";
         $objPHPExcel->getActiveSheet()->setTitle('Simple');
         // Save Excel 2007 file
         //echo date('H:i:s') . " Write to Excel2007 format\n";
         $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
         // We'll be outputting an excel file
         header('Content-type: application/vnd.ms-excel');
         // It will be called file.xls
         header("Content-Disposition: attachment; filename=" . $excel_data['file_name']);
         // Write file to the browser
         $objWriter->save('php://output');
         // Echo done
     }
 }

 /**
  * @return array
  */
 public function downloadAction()
 {
     if (!$this->isGranted('event.entries')) {
         return $this->redirect()->toRoute('home');
     }
     $id = (int) $this->params()->fromRoute('id');
     $trial = $this->getRepository()->find($id);
     if (!$trial || !$trial->getEvent()->isApproved()) {
         return $this->notFoundAction();
     }
     $scoreSheet = $this->getEventService()->createScoreSheet($trial);
     header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
     header('Content-Disposition: attachment; filename=\'USCSS Trial Score Sheet - ' . $trial . '.xlsx\'');
     header('Cache-Control: max-age=0');
     $output = new \PHPExcel_Writer_Excel2007($scoreSheet);
     $output->save('php://output');
     return $this->getResponse();
 }

 /**
  *
  */
 public function render()
 {
     $excel = new \PHPExcel();
     $excel->getDefaultStyle()->getFont()->setName('Arial');
     $excel->getDefaultStyle()->getFont()->setSize(10);
     $this->_sheet = $excel->getActiveSheet();
     $this->_sheet->setTitle($this->_tabs_title);
     $this->_sheet->getTabColor()->setARGB('FFc3e59e');
     $this->_sheet->fromArray($this->makeExportData());
     //resize
     $this->excelWidth();
     //height header
     header('Content-Type: application/excel');
     header('Content-Disposition: attachment; filename="' . $this->getFile() . '"');
     header('Cache-Control: max-age=0');
     $writer = new \PHPExcel_Writer_Excel2007($excel);
     $writer->save('php://output');
 }

 public function summaryReportAction($year, $quarter)
 {
     $periodMananger = $this->get('wealthbot_ria.period.manager');
     $reportMananger = $this->get('wealthbot_ria.billing_report.manager');
     // Generate report
     $periods = $periodMananger->getQuarterPeriod($year, $quarter);
     $excel = $reportMananger->generateSummary($periods);
     // Generate filename
     $periods = array_keys($periods);
     $quarter = empty($quarter) ? min($periods) . '-' . max($periods) : $quarter;
     $filename = "billing_summary_q{$quarter}_{$year}";
     $response = new Response();
     $response->headers->add(array('Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 'Content-Disposition' => 'attachment;filename="' . $filename . '.xlsx"', 'Cache-Control' => 'max-age=0'));
     ob_start();
     $writer = new \PHPExcel_Writer_Excel2007($excel);
     $writer->save('php://output');
     $response->setContent(ob_get_clean());
     return $response;
 }

 /**
  * Dumps the stats table
  * @param  StatsTable $statsTable
  * @return string
  */
 public function dump(StatsTable $statsTable)
 {
     $excel = new \PHPExcel();
     $excel->getDefaultStyle()->applyFromArray($this->getDefaultStyleArray());
     $sheet = $excel->getSheet();
     $row = 1;
     $data = $statsTable->getData();
     $width = count(reset($data));
     // HEADERS //
     if ($this->enableHeaders) {
         $headerStyle = new \PHPExcel_Style();
         $headerStyle->applyFromArray($this->getHeadersStyleArray());
         $col = 0;
         foreach ($statsTable->getHeaders() as $header) {
             $sheet->setCellValueByColumnAndRow($col, $row, $header);
             $col++;
         }
         $sheet->duplicateStyle($headerStyle, 'A1:' . \PHPExcel_Cell::stringFromColumnIndex($width - 1) . '1');
         $row++;
     }
     // DATA //
     foreach ($statsTable->getData() as $data) {
         $this->applyValues($sheet, $row, $data, $statsTable->getDataFormats());
         $row++;
     }
     // AGGREGATIONS //
     if ($this->enableAggregation) {
         $this->applyValues($sheet, $row, $statsTable->getAggregations(), $statsTable->getAggregationsFormats(), $this->getAggregationsStyleArray());
     }
     // FINAL FORMATTING //
     for ($col = 0; $col < $width; $col++) {
         $sheet->getColumnDimension(\PHPExcel_Cell::stringFromColumnIndex($col))->setAutoSize(true);
     }
     $xlsDumper = new \PHPExcel_Writer_Excel2007($excel);
     $pFilename = @tempnam(\PHPExcel_Shared_File::sys_get_temp_dir(), 'phpxltmp');
     $xlsDumper->save($pFilename);
     $contents = file_get_contents($pFilename);
     @unlink($pFilename);
     unset($excel);
     unset($xlsDumper);
     return $contents;
 }