PHP PHPExcel_Settings::setZipClass方法代码示例

function read_xlsfile($filename)
{
    /** PHPExcel_IOFactory */
    include_once 'Classes/PHPExcel/IOFactory.php';
    $inputFileName = $filename;
    try {
        PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);
        $objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
    } catch (Exception $e) {
        die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage());
    }
    $sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
    $row = 0;
    foreach ($sheetData as $v => $i) {
        if ($row == 0) {
            $row = 1;
            continue;
        }
        if (count($i) == 3 && filter_var($i['C'], FILTER_VALIDATE_EMAIL)) {
            $query[] = "('" . implode("','", $i) . "')";
        }
    }
    if (count($query) == 1000) {
        bulk_insert($query);
        $query = '';
    }
    return $query;
}

<?php

/**
 * @var IndexController $this
 * @var array|IKommentar[] $kommentare
 */
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename=kommentare.xlsx');
header('Cache-Control: max-age=0');
define('PCLZIP_TEMPORARY_DIR', '/tmp/');
PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("Antragsgruen.de");
$objPHPExcel->getProperties()->setLastModifiedBy("Antragsgruen.de");
$objPHPExcel->getProperties()->setTitle($this->veranstaltung->name);
$objPHPExcel->getProperties()->setSubject("Kommentare");
$objPHPExcel->getProperties()->setDescription($this->veranstaltung->name . " - Kommentare zu Anträgen und Änderungsanträgen");
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Datum');
$objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Person');
$objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Antrag');
$objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Absatz / Zeilen');
$objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Kommentar');
if ($this->veranstaltung->getEinstellungen()->kommentare_unterstuetzbar) {
    $objPHPExcel->getActiveSheet()->SetCellValue('F1', 'Bewertung: Positiv');
    $objPHPExcel->getActiveSheet()->SetCellValue('G1', 'Bewertung: Negativ');
    $objPHPExcel->getActiveSheet()->getStyle("A1:G1")->applyFromArray(array("font" => array("bold" => true)));
} else {
    $objPHPExcel->getActiveSheet()->getStyle("A1:E1")->applyFromArray(array("font" => array("bold" => true)));
}
PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());

 public function import()
 {
     if ($this->request->is('post')) {
         if (isset($this->request['data']['Product']['file']) and $this->request['data']['Product']['file']['error'] === 0) {
             if (!in_array($this->request['data']['Product']['file']['type'], array('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 'application/vnd.ms-excel', 'application/octet-stream'))) {
                 $this->Session->setFlash('Extensión no permitida', 'flash');
                 return;
             }
             App::uses('PHPExcel', 'Lib/PHPExcel');
             if (!class_exists('PHPExcel')) {
                 throw new CakeException('Lib Class PHPExcel not found.');
                 return;
             }
             PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);
             $excel = PHPExcel_IOFactory::load($this->request['data']['Product']['file']['tmp_name']);
             $rows = $excel->getSheet()->toArray(null, true, true, false);
             foreach ($rows as $k => $v) {
                 $rows[$k] = array_filter($rows[$k]);
             }
             $rows = array_filter($rows);
             unset($rows[0]);
             // echo '<pre>';
             // print_r($rows);
             // echo '</pre>';
             // exit;
             foreach ($rows as $k => $v) {
                 if (array_key_exists(4, $v)) {
                     if ($Category = $this->Category->findByName($v[4])) {
                         $parent_category_id = $Category['Category']['parent_category_id'];
                         $category_id = $parent_category_id;
                     } else {
                         $this->Category->create();
                         $this->Category->name = $v[4];
                         $this->Category->parent_category_id = 0;
                         $this->Category->save($this->Category);
                         $parent_category_id = $this->Category->getInsertID();
                         $category_id = $parent_category_id;
                     }
                 }
                 if (array_key_exists(5, $v)) {
                     if ($Category = $this->Category->findByName($v[5])) {
                         $parent_category_id = $Category['Category']['parent_category_id'];
                         $category_id = $parent_category_id;
                     } else {
                         $this->Category->create();
                         $this->Category->name = $v[5];
                         $this->Category->parent_category_id = $parent_category_id;
                         $this->Category->save($this->Category);
                         $parent_category_id = $this->Category->getInsertID();
                         $category_id = $parent_category_id;
                     }
                 }
                 if (array_key_exists(6, $v)) {
                     if ($Category = $this->Category->findByName($v[6])) {
                         $parent_category_id = $Category['Category']['parent_category_id'];
                         $category_id = $parent_category_id;
                     } else {
                         $this->Category->create();
                         $this->Category->name = $v[6];
                         $this->Category->parent_category_id = $parent_category_id;
                         $this->Category->save($this->Category);
                         $parent_category_id = $this->Category->getInsertID();
                         $category_id = $parent_category_id;
                     }
                 }
                 if (array_key_exists(7, $v)) {
                     if ($Category = $this->Category->findByName($v[7])) {
                         $parent_category_id = $Category['Category']['parent_category_id'];
                         $category_id = $parent_category_id;
                     } else {
                         $this->Category->create();
                         $this->Category->name = $v[7];
                         $this->Category->parent_category_id = $parent_category_id;
                         $this->Category->save($this->Category);
                         $parent_category_id = $this->Category->getInsertID();
                         $category_id = $parent_category_id;
                     }
                 }
                 if (array_key_exists(8, $v)) {
                     if ($Category = $this->Category->findByName($v[8])) {
                         $parent_category_id = $Category['Category']['parent_category_id'];
                         $category_id = $parent_category_id;
                     } else {
                         $this->Category->create();
                         $this->Category->name = $v[8];
                         $this->Category->parent_category_id = $parent_category_id;
                         $this->Category->save($this->Category);
                         $parent_category_id = $this->Category->getInsertID();
                         $category_id = $parent_category_id;
                     }
                 }
                 if (array_key_exists(9, $v)) {
                     if ($Category = $this->Category->findByName($v[9])) {
                         $parent_category_id = $Category['Category']['parent_category_id'];
                         $category_id = $parent_category_id;
                     } else {
                         $this->Category->create();
                         $this->Category->name = $v[9];
                         $this->Category->parent_category_id = $parent_category_id;
                         $this->Category->save($this->Category);
//.........这里部分代码省略.........

 /**
  * Output document to browser
  * @param  string $filename document filename, null is gernerate by date.
  */
 protected function output($filename = null)
 {
     $filename = $filename ?: "export-" . date('Ymd') . '.xlsx';
     // change zip extension to solve unreadable in xlsx
     \PHPExcel_Settings::setZipClass(\PHPExcel_Settings::PCLZIP);
     // redirect output to client browser
     header('Content-type: application/vnd.ms-excel');
     header(sprintf('Content-Disposition: attachment; filename="%s"', $filename));
     $writer = \PHPExcel_IOFactory::createWriter($this->phpExcel, 'Excel2007');
     $writer->save('php://output');
 }

 public function download($export_type, $offset = null, $rows = null, $min_id = null, $max_id = null)
 {
     // Error handler
     global $registry;
     $registry = $this->registry;
     set_error_handler('error_handler_for_export_import', E_ALL);
     register_shutdown_function('fatal_error_shutdown_handler_for_export_import');
     // PHPExcel
     $cwd = getcwd();
     chdir(DIR_SYSTEM . 'vendor');
     require_once 'Classes/PHPExcel.php';
     PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_ExportImportValueBinder());
     chdir($cwd);
     // Find out whether all data is to be downloaded
     $all = !isset($offset) && !isset($rows) && !isset($min_id) && !isset($max_id);
     // Memory Optimization
     if ($this->config->get('export_import_settings_use_export_cache')) {
         $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
         $cacheSettings = array('memoryCacheSize' => '16MB');
         PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
     }
     // Export as ZIP (Beta)
     if ($this->config->get('export_import_settings_use_export_pclzip')) {
         PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);
     }
     try {
         // Set appropriate timeout limit
         set_time_limit(1800);
         // Create a new workbook
         $workbook = new PHPExcel();
         // Set some default styles
         $workbook->getDefaultStyle()->getFont()->setName('Arial');
         $workbook->getDefaultStyle()->getFont()->setSize(10);
         $workbook->getDefaultStyle()->getAlignment()->setWrapText(true);
         $workbook->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
         $workbook->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
         $workbook->getDefaultStyle()->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL);
         // Pre-define some commonly used styles
         $box_format = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFF0F0F0')));
         $text_format = array('numberformat' => array('code' => PHPExcel_Style_NumberFormat::FORMAT_TEXT));
         $price_format = array('numberformat' => array('code' => '######0.00'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT));
         $date_format = array('numberformat' => array('code' => '0000-00-00'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER));
         $datetime_format = array('numberformat' => array('code' => '0000-00-00 00:00:00'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER));
         $weight_format = array('numberformat' => array('code' => '##0.00'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT));
         // Create the worksheets
         $worksheet_index = 0;
         $languages = $this->getLanguages();
         $default_language_id = $this->getDefaultLanguageId();
         switch ($export_type) {
             case 'm':
                 // Creating the Customers worksheet
                 $workbook->setActiveSheetIndex($worksheet_index++);
                 $worksheet = $workbook->getActiveSheet();
                 $worksheet->setTitle('Customers');
                 $this->populateCustomersWorksheet($worksheet, $box_format, $text_format, $date_format, $datetime_format, $offset, $rows, $min_id, $max_id);
                 $worksheet->freezePaneByColumnAndRow(1, 2);
                 break;
             case 'c':
                 // Creating the Categories worksheet
                 $workbook->setActiveSheetIndex($worksheet_index++);
                 $worksheet = $workbook->getActiveSheet();
                 $worksheet->setTitle('Categories');
                 $this->populateCategoriesWorksheet($worksheet, $languages, $box_format, $text_format, $datetime_format, $offset, $rows, $min_id, $max_id);
                 $worksheet->freezePaneByColumnAndRow(1, 2);
                 // Creating the CategoryFilters worksheet
                 if ($this->existFilter()) {
                     $workbook->createSheet();
                     $workbook->setActiveSheetIndex($worksheet_index++);
                     $worksheet = $workbook->getActiveSheet();
                     $worksheet->setTitle('CategoryFilters');
                     $this->populateCategoryFiltersWorksheet($worksheet, $languages, $default_language_id, $box_format, $text_format, $min_id, $max_id);
                     $worksheet->freezePaneByColumnAndRow(1, 2);
                 }
                 break;
             case 'p':
                 // Creating the Products worksheet
                 $workbook->setActiveSheetIndex($worksheet_index++);
                 $worksheet = $workbook->getActiveSheet();
                 $worksheet->setTitle('Products');
                 $this->populateProductsWorksheet($worksheet, $languages, $default_language_id, $box_format, $price_format, $weight_format, $text_format, $date_format, $datetime_format, $offset, $rows, $min_id, $max_id);
                 $worksheet->freezePaneByColumnAndRow(1, 2);
                 // Creating the AdditionalImages worksheet
                 $workbook->createSheet();
                 $workbook->setActiveSheetIndex($worksheet_index++);
                 $worksheet = $workbook->getActiveSheet();
                 $worksheet->setTitle('AdditionalImages');
                 $this->populateAdditionalImagesWorksheet($worksheet, $box_format, $text_format, $min_id, $max_id);
                 $worksheet->freezePaneByColumnAndRow(1, 2);
                 // Creating the Specials worksheet
                 $workbook->createSheet();
                 $workbook->setActiveSheetIndex($worksheet_index++);
                 $worksheet = $workbook->getActiveSheet();
                 $worksheet->setTitle('Specials');
                 $this->populateSpecialsWorksheet($worksheet, $default_language_id, $box_format, $price_format, $text_format, $date_format, $min_id, $max_id);
                 $worksheet->freezePaneByColumnAndRow(1, 2);
                 // Creating the Discounts worksheet
                 $workbook->createSheet();
                 $workbook->setActiveSheetIndex($worksheet_index++);
                 $worksheet = $workbook->getActiveSheet();
                 $worksheet->setTitle('Discounts');
//.........这里部分代码省略.........

 public function getxorder()
 {
     ini_set('memory_limit', '-1');
     Configure::write('debug', 0);
     require '../Vendor/PhpExcel/PHPExcel.php';
     define('PCLZIP_TEMPORARY_DIR', '../tmp/');
     PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);
     $this->loadModel('Order');
     $x = $this->Order->find("all", array("contain" => array("Address", "Combination", "Combination.Vendor", "Customer"), "order" => "Order.id DESC"));
     //        debug($x);
     //        exit;
     // create new empty worksheet and set default font
     $this->PhpExcel->createWorksheet()->setDefaultFont('Calibri', 12);
     $this->PhpExcel->getSheet()->setTitle("Orders");
     // define table cells
     $table = array(array('label' => __('Used Discount'), 'filter' => true), array('label' => __('Vendor Name'), 'filter' => true), array('label' => __('Combination (Dish)'), 'filter' => true), array('label' => __('Essentials'), 'filter' => true), array('label' => __('Price')), array('label' => __('Paid Amount')), array('label' => __('Quantity')), array('label' => __('Customer ID'), 'filter' => true), array('label' => __('Customer Name'), 'filter' => true), array('label' => __('Contact No.'), 'width' => 20, 'wrap' => true), array('label' => __('Payment Method')), array('label' => __('Order @'), 'filter' => true), array('label' => __('ID'), 'filter' => true), array('label' => __('ORDER_ID'), 'filter' => true), array('label' => __('Delivery Address'), 'width' => 40, 'wrap' => true));
     // add heading with different font and bold text
     $this->PhpExcel->addTableHeader($table, array('name' => 'Cambria', 'bold' => true));
     // add data
     foreach ($x as $d) {
         $this->PhpExcel->addTableRow(array($d['Order']['discount_amount'] == 0 ? "NO" : "YES", @$d['Combination']['Vendor']['name'], $d['Order']['recipe_names'], $d['Order']['essentials'], $d['Combination']['price'], $d['Order']['discount_amount'] >= $d['Combination']['price'] ? 0.0 : abs($d['Order']['discount_amount'] - $d['Combination']['price']), $d['Order']['qty'], $d['Order']['customer_id'], $d['Address']['f_name'] . " " . $d['Address']['l_name'], $d['Address']['phone_number'], $d['Order']['paid_via'], date("d-m-Y h:i A", $d['Order']['timestamp']), $d['Order']['id'], $d['Order']['sku'], $d['Address']['address']));
     }
     // close table and output
     $file = $this->PhpExcel->addTableFooter();
     $this->PhpExcel->addSheet("Customers");
     $table = array(array('label' => __('id'), 'filter' => true), array('label' => __('Promo Code')), array('label' => __('Name'), 'filter' => true), array('label' => __('Mobile No.'), 'width' => 50, 'wrap' => true), array('label' => __('Registered On'), 'filter' => true), array('label' => __('Cash Promo')), array('label' => __('Total Orders')));
     $this->PhpExcel->addTableHeader($table, array('name' => 'Cambria', 'bold' => true));
     $cstDate = array();
     $this->loadModel('Customer');
     $cst = $this->Customer->find('all');
     foreach ($cst as $d) {
         $c = $this->Order->find("count", array("conditions" => array("Order.customer_id" => $d['Customer']['id'])));
         $cstDate[@date("d-m-Y", $d['Customer']['registered_on'])][] = $d;
         $this->PhpExcel->addTableRow(array($d['Customer']['id'], $d['Customer']['my_promo_code'], $d['Customer']['name'], $d['Customer']['mobile_number'], @date("d-m-Y h:i A", $d['Customer']['registered_on']), "Rs. " . $d['Customer']['cash_by_promo'], $c));
     }
     $this->PhpExcel->addTableFooter();
     $odr3 = array();
     $chartData = array();
     foreach ($x as $v3) {
         $d3 = date("d-m-Y", $v3['Order']['timestamp']);
         $odr3[$d3][] = $v3;
     }
     foreach ($odr3 as $kv3 => $cv3) {
         $chartData[$kv3]['total'] = count($cv3);
         $repeated = 0;
         $newCount = 0;
         $couponCount = 0;
         foreach ($cv3 as $ev3) {
             $tmp = $this->Order->find("count", array("conditions" => array("Order.customer_id" => $ev3['Order']['customer_id'])));
             if ($ev3['Order']['discount_amount'] != NULL && $ev3['Order']['discount_amount'] > 0) {
                 $couponCount++;
             }
             if (@date("d-m-Y", $ev3['Customer']['registered_on']) == @date("d-m-Y", $ev3['Order']['timestamp'])) {
                 $newCount++;
             }
             if ($tmp > 1) {
                 $repeated++;
             }
         }
         $chartData[$kv3]['repeated'] = $repeated;
         $chartData[$kv3]['newreg'] = isset($cstDate[$kv3]) ? count($cstDate[$kv3]) : 0;
         $chartData[$kv3]['Coupon'] = $couponCount;
         $chartData[$kv3]['new'] = $newCount;
     }
     //echo json_encode($chartData);
     //exit;
     $this->PhpExcel->addSheet("Chart 1");
     $table = array(array('label' => __('Date'), 'filter' => true), array('label' => __('Total Orders')), array('label' => __('Repeated')), array('label' => __('New Registered Customers')), array('label' => __('New Orders')), array('label' => __('Coupon'), 'filter' => true));
     $this->PhpExcel->addTableHeader($table, array('name' => 'Cambria', 'bold' => true));
     $this->loadModel('Customer');
     $cst = $this->Customer->find('all');
     foreach ($chartData as $eK => $eD) {
         $this->PhpExcel->addTableRow(array($eK, $eD['total'], $eD['repeated'], $eD['newreg'], $eD['new'], $eD['Coupon']));
     }
     $this->PhpExcel->addTableFooter();
     $this->PhpExcel->output();
     exit;
 }