コード例 #1
0
 /**
  * Streams to the browser the provided array of objects as a basic Excel document
  * with headers.  if the objects have an associated Map class, then footers will be
  * added to sum any numeric fields.  otherwise no footers are added
  * 
  * Note that PEAR Spreadsheet_Excel_Writer must be installed
  * @link http://pear.php.net/package/Spreadsheet_Excel_Writer
  * 
  * @param Array an array of Phreezable objects, obtained for example, using DataSet->ToObjectArray
  * @param Phreezer $phreezer is needed to get field maps
  * @param string (optional) The title of the report
  */
 static function OutputAsExcel(array $objects, Phreezer $phreezer, $reportTitle = "Data Export", $fileName = "export.xls")
 {
     require_once 'Spreadsheet/Excel/Writer.php';
     // create the workbook and worksheet
     $workbook = new Spreadsheet_Excel_Writer();
     $worksheet = $workbook->addWorksheet("Export");
     $BOLD_MED =& $workbook->addFormat();
     $BOLD_MED->setSize(16);
     $BOLD_MED->SetBold();
     $BOLD_REG =& $workbook->addFormat();
     $BOLD_REG->setSize(11);
     $BOLD_REG->SetBold();
     $NORMAL =& $workbook->addFormat();
     $NORMAL->setSize(11);
     $CURRENCY =& $workbook->addFormat();
     $CURRENCY->setNumFormat('0.00');
     $CURRENCY->setSize(11);
     $CURRENCY->setAlign('right');
     $worksheet->writeString(0, 0, $reportTitle, $BOLD_MED);
     // default to no columns
     $fields = array();
     $columns = array();
     $is_numeric = array();
     $fieldmap_exists = false;
     // print the headers
     // while we're looping, also parse the fields so we don't have to do
     // it repeatedly when looping through data
     if (isset($objects[0])) {
         try {
             // see if there is a fieldmap for this object
             $fields = $phreezer->GetFieldMaps(get_class($objects[0]));
             $fieldmap_exists = true;
             // these are the columns we'll use for enumeration from here on
             $columns = array_keys($fields);
         } catch (Exception $ex) {
             // no fieldmaps exist, so use the reflection class instead
             $reflect = new ReflectionClass($objects[0]);
             $publicAttributes = $reflect->getProperties(ReflectionProperty::IS_PUBLIC);
             $staticAttributes = $reflect->getStaticProperties();
             // only include non-static public properties
             $props = array_diff($publicAttributes, $staticAttributes);
             foreach ($props as $prop) {
                 $column = $prop->getName();
                 $columns[] = $column;
             }
         }
         $current_column = 0;
         foreach ($columns as $column) {
             // save this so we don't check it every time when looping through data
             $is_numeric[$column] = $fieldmap_exists ? $fields[$column]->IsNumeric() : false;
             $worksheet->writeString(2, $current_column, $column, $BOLD_REG);
             $current_column++;
         }
     }
     $current_row = 3;
     // loop through all of the data
     foreach ($objects as $object) {
         $current_column = 0;
         foreach ($columns as $column) {
             if ($fieldmap_exists == false || $is_numeric[$column] == true) {
                 $worksheet->write($current_row, $current_column, $object->{$column}, $NORMAL);
             } else {
                 $worksheet->writeString($current_row, $current_column, $object->{$column}, $NORMAL);
             }
             $current_column++;
         }
         $current_row++;
     }
     // lastly write to the footer to sum the numeric columns
     $current_column = 0;
     foreach ($columns as $column) {
         if ($is_numeric[$column]) {
             $columnLetter = ExportUtility::GetColumnLetter($current_column);
             $formula = "=SUM(" . $columnLetter . "3:" . $columnLetter . ($current_row - 1) . ")";
             // notice the @ sign in front because this will fire a deprecated warning due to use of "split"
             @$worksheet->write($current_row, $current_column, $formula, $BOLD_REG);
         }
         $current_column++;
     }
     $workbook->send($fileName);
     // this has errors suppressed due to strict mode
     @$workbook->close();
 }
コード例 #2
0
 /**
  * Streams to the browser the provided array of objects as a basic Excel document
  * with headers.  if the objects have an associated Map class, then footers will be
  * added to sum any numeric fields.  otherwise no footers are added
  * 
  * Note that PEAR PHPExcel must be installed
  * @link https://phpexcel.codeplex.com/
  * 
  * @param Array an array of Phreezable objects, obtained for example, using DataSet->ToObjectArray
  * @param Phreezer $phreezer is needed to get field maps
  * @param string (optional) The title of the report
  */
 static function OutputAsExcel(array $objects, Phreezer $phreezer, $reportTitle = "Data Export", $fileName = "export.xls", $creator = "Phreeze Library")
 {
     require_once "PEAR/PHPExcel.php";
     // create the workbook and worksheet
     $workbook = new PHPExcel();
     // set workbook properties
     $workbook->getProperties()->setCreator($creator)->setTitle($fileName);
     $workbook->setActiveSheetIndex(0);
     $worksheet = $workbook->getActiveSheet();
     $current_column = "A";
     $current_row = 1;
     $worksheet->setCellValue($current_column . $current_row, $reportTitle);
     $worksheet->getStyle($current_column . $current_row)->getFont()->setBold(true)->setSize(16);
     $worksheet->getStyle($current_column . $current_row)->getFont()->setName('Arial');
     // default to no columns
     $fields = array();
     $columns = array();
     $is_numeric = array();
     $fieldmap_exists = false;
     $current_row = 3;
     // print the headers
     // while we're looping, also parse the fields so we don't have to do
     // it repeatedly when looping through data
     if (isset($objects[0])) {
         try {
             // see if there is a fieldmap for this object
             $fields = $phreezer->GetFieldMaps(get_class($objects[0]));
             $fieldmap_exists = true;
             // these are the columns we'll use for enumeration from here on
             $columns = array_keys($fields);
         } catch (Exception $ex) {
             // no fieldmaps exist, so use the reflection class instead
             $reflect = new ReflectionClass($objects[0]);
             $publicAttributes = $reflect->getProperties(ReflectionProperty::IS_PUBLIC);
             $staticAttributes = $reflect->getStaticProperties();
             // only include non-static public properties
             $props = array_diff($publicAttributes, $staticAttributes);
             foreach ($props as $prop) {
                 $column = $prop->getName();
                 $columns[] = $column;
             }
         }
         foreach ($columns as $column) {
             // save this so we don't check it every time when looping through data
             $is_numeric[$column] = $fieldmap_exists ? $fields[$column]->IsNumeric() : false;
             $worksheet->setCellValue($current_column . $current_row, $column);
             $worksheet->getStyle($current_column . $current_row)->getFont()->setBold(true)->setSize(11);
             $worksheet->getStyle($current_column . $current_row)->getFont()->setName('Arial');
             $current_column++;
         }
     }
     $current_row = 4;
     // loop through all of the data
     foreach ($objects as $object) {
         $current_column = "A";
         foreach ($columns as $column) {
             if ($fieldmap_exists == false || $is_numeric[$column] == true) {
                 $worksheet->setCellValue($current_column . $current_row, $object->{$column});
             } else {
                 $worksheet->setCellValue($current_column . $current_row, $object->{$column});
             }
             $current_column++;
         }
         $current_row++;
     }
     // lastly write to the footer to sum the numeric columns
     $col = 0;
     foreach ($columns as $column) {
         if ($is_numeric[$column]) {
             $columnLetter = ExportUtility::GetColumnLetter($current_column);
             $formula = "=SUM(" . $columnLetter . "3:" . $columnLetter . ($current_row - 1) . ")";
             // notice the @ sign in front because this will fire a deprecated warning due to use of "split"
             @$worksheet->setCellValue($current_row, $current_column, $formula);
             $worksheet->getStyle($current_column . $current_row)->getFont()->setBold(true);
             $worksheet->getStyle($current_column . $current_row)->getFont()->setName('Arial');
         }
         $current_column++;
     }
     $workbook->getDefaultStyle()->getFont()->setName('Arial')->setSize(11);
     $workbook->setActiveSheetIndex(0);
     $writer = PHPExcel_IOFactory::createWriter($workbook, 'Excel5');
     // set headers to excel:
     header('Content-type: application/vnd.ms-excel');
     header('Content-Disposition: attachment; filename="' . $fileName . '"');
     header('Cache-Control: max-age=0');
     $writer->save('php://output');
 }