PHP PHPExcel_Writer_Excel5类代码示例

function downExcel($map, $data)
{
    include_once IA_ROOT . '/framework/class/PHPExcel.php';
    include_once IA_ROOT . '/framework/class/PHPExcel/Writer/Excel5.php';
    $excel = new PHPExcel();
    //$map['title'] = iconv('utf-8", "gb2312", $map['title']);
    $excel->getProperties()->setCreator("时代地产");
    $excel->setActiveSheetIndex(0);
    $excel->getActiveSheet()->setTitle($map['title']);
    $excel->setActiveSheetIndex(0);
    $sheet = $excel->getActiveSheet();
    $c = range('A', 'Z');
    $i = 1;
    $cell = '';
    foreach ($map['fields'] as $k => $f) {
        $cell = $c[$k] . $i;
        $sheet->setCellValue($cell, $f['title']);
    }
    foreach ($data as $item) {
        $i++;
        foreach ($map['fields'] as $k => $f) {
            $cell = $c[$k] . $i;
            $value = $item[$f['field']];
            if ($f['type'] == 1) {
                $value = date('Y-m-d H:i:s', $item[$f['field']]);
            }
            //数值类型
            if ($f['type'] == 2) {
                $sheet->setCellValueExplicit($cell, $value, PHPExcel_Cell_DataType::TYPE_STRING);
            } else {
                $sheet->setCellValue($cell, $value);
            }
        }
    }
    $writer = new PHPExcel_Writer_Excel5($excel);
    header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
    header("Content-Type:application/force-download");
    header("Content-Type: application/vnd.ms-excel;charset=UTF-8");
    header("Content-Type:application/octet-stream");
    header("Content-Type:application/download");
    //header("Content-Disposition:attachment;filename=" . $map['title'] . ".xls");
    $ua = strtoupper($_SERVER["HTTP_USER_AGENT"]);
    $filename = basename($map['title'] . ".xls");
    if (preg_match("/IE/", $ua)) {
        $encoded_filename = str_replace("+", "%20", urlencode($filename));
        //header('Content-Disposition: attachment; filename=' . $filename);
        header('Content-Disposition: attachment; filename="' . $encoded_filename . '"');
    } else {
        header('Content-Disposition: attachment; filename=' . $filename);
    }
    header("Content-Transfer-Encoding:binary");
    $writer->save("php://output");
}

 public function buildBookStatReportExcel($books)
 {
     App::import("Vendor", "phpexcel/PHPExcel");
     App::import("Vendor", "phpexcel/PHPExcel/Writer/Excel5");
     $r['path'] = TMP . 'tests' . DS;
     $r['file'] = 'tmp_books_stat_' . $this->Session->read('user_id');
     $file = $r['path'] . $r['file'];
     $excel = new PHPExcel();
     $excel->setActiveSheetIndex(0);
     $excel->getActiveSheet()->setTitle('Books');
     $excel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, '級別');
     $excel->getActiveSheet()->setCellValueByColumnAndRow(1, 1, '書籍名稱');
     $excel->getActiveSheet()->setCellValueByColumnAndRow(2, 1, '作者');
     $excel->getActiveSheet()->setCellValueByColumnAndRow(3, 1, 'ISBN');
     $excel->getActiveSheet()->setCellValueByColumnAndRow(4, 1, '索書號');
     $excel->getActiveSheet()->setCellValueByColumnAndRow(5, 1, '出借分校');
     $excel->getActiveSheet()->setCellValueByColumnAndRow(6, 1, '出借次數');
     $i = 1;
     foreach ($books as $book) {
         $i++;
         $excel->getActiveSheet()->setCellValueExplicitByColumnAndRow(0, $i, $book['books']['cate_id'], PHPExcel_Cell_DataType::TYPE_STRING);
         $excel->getActiveSheet()->setCellValueByColumnAndRow(1, $i, $book['books']['book_name']);
         $excel->getActiveSheet()->setCellValueByColumnAndRow(2, $i, $book['books']['book_author']);
         $excel->getActiveSheet()->setCellValueExplicitByColumnAndRow(3, $i, $book['books']['isbn'], PHPExcel_Cell_DataType::TYPE_STRING);
         $excel->getActiveSheet()->setCellValueExplicitByColumnAndRow(4, $i, $book['books']['book_search_code'], PHPExcel_Cell_DataType::TYPE_STRING);
         $excel->getActiveSheet()->setCellValueByColumnAndRow(5, $i, $book['system_locations']['location_name']);
         $excel->getActiveSheet()->setCellValueByColumnAndRow(6, $i, $book[0]['cnt']);
     }
     $objWriter = new PHPExcel_Writer_Excel5($excel);
     $objWriter->save($file);
     return $r;
 }

 public static function renderData(array $itemsIterator, array $fields, $filename)
 {
     $objPHPExcel = new PHPExcel();
     $objPHPExcel->getProperties()->setCreator("PrintWeek");
     $objPHPExcel->getProperties()->setTitle("Report");
     $objPHPExcel->getProperties()->setSubject("Report");
     $objPHPExcel->getProperties()->setDescription("Report");
     $objPHPExcel->setActiveSheetIndex(0);
     /**
      * Выводим строку названий столбцов
      */
     $col = 0;
     foreach ($fields as $name) {
         $objPHPExcel->getActiveSheet()->SetCellValue(self::getCellCoordinate($col++, 1), $name);
     }
     /**
      * Основной вывод информации
      */
     $row = 2;
     foreach ($itemsIterator as $item) {
         $col = 0;
         foreach ($fields as $name => $title) {
             $objPHPExcel->getActiveSheet()->SetCellValue(self::getCellCoordinate($col++, $row), $item->{$name});
         }
         $row++;
     }
     $objPHPExcel->getActiveSheet()->setTitle('Report');
     $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
     $objWriter->save($filename);
 }

 public function pruebaExcel()
 {
     $sheetId = 0;
     $this->phpexcel->createSheet(NULL, $sheetId);
     $this->phpexcel->setActiveSheetIndex($sheetId);
     $this->phpexcel->getActiveSheet()->setTitle("Excel de Prueba");
     $sheet = $this->phpexcel->getActiveSheet();
     $sheet->getColumnDimension('A')->setWidth(30);
     $sheet->getColumnDimension('B')->setWidth(30);
     $sheet->getColumnDimension('C')->setWidth(30);
     $sheet->getColumnDimension('D')->setWidth(30);
     $sheet->getColumnDimension('E')->setWidth(30);
     $sheet->getColumnDimension('F')->setWidth(30);
     $sheet->getColumnDimension('G')->setWidth(30);
     $styleArray = array('font' => array('bold' => true));
     $sheet->setCellValue('A3', 'Columna A3');
     $sheet->setCellValue('B3', 'Columna BB');
     $sheet->setCellValue('C3', 'Columna C3');
     $sheet->setCellValue('D3', 'Columna D3');
     $sheet->setCellValue('E3', 'Columna E3');
     $sheet->setCellValue('F3', 'Columna F3');
     $sheet->setCellValue('G3', 'Columna G3');
     $sheet->setCellValue('A4', 'Columna A4');
     $sheet->setCellValue('B4', 'Columna B4');
     $sheet->setCellValue('C4', 'Columna C4');
     $sheet->setCellValue('D4', 'Columna D4');
     $sheet->setCellValue('E4', 'Columna E4');
     $sheet->setCellValue('F4', 'Columna F4');
     $sheet->setCellValue('G4', 'Columna G4');
     $writer = new PHPExcel_Writer_Excel5($this->phpexcel);
     header('Content-type: application/vnd.ms-excel');
     $writer->save('php://output');
 }

 public function renderFooter()
 {
     $filename = tempnam(\Yii::getAlias('@runtime'), 'xls');
     $objWriter = new \PHPExcel_Writer_Excel5($this->objPHPExcel);
     $objWriter->save($filename);
     $content = file_get_contents($filename);
     unlink($filename);
     return $content;
 }

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

 /**
  * _output
  *
  * @param mixed $fileName
  *
  * @access protected
  * @return void
  */
 function _output($fileName)
 {
     //$starting_pos = ord('C');  //unused
     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($this->xls);
     $objWriter->setTempDir(TMP);
     $objWriter->save('php://output');
 }

 function downloadStudentResult($class_id, $topic_manage_id)
 {
     $this->CI->load->model('student_info_model');
     $this->CI->load->model('class_model');
     $this->CI->load->model('topic_model');
     $this->CI->load->model('student_mark_model');
     $this->CI->load->helper('inflector');
     $this->CI->load->library(['utils']);
     $class = $this->CI->class_model->find_by_pkey($class_id);
     $topic = $this->CI->topic_model->getTopicIdByTopicManageId($topic_manage_id);
     $topics = array_filter(array_map('trim', explode(',', $topic->topic_id)));
     $studentsMark = $this->CI->student_mark_model->getMarkStudents($topics, $class_id);
     $studentsMark = $this->CI->utils->makeList('student_id', $studentsMark);
     $students = $this->CI->student_info_model->getAllStudents($class_id);
     $this->CI->load->library('PhpOffice/PHPExcel');
     $sheet = $this->CI->phpexcel->getActiveSheet();
     $title_class = underscore($class->class_name) . '.xls';
     $row = 1;
     // header
     $sheet->setCellValue('A' . $row, 'STT');
     $sheet->setCellValue('B' . $row, 'Họ tên');
     $sheet->setCellValue('C' . $row, 'Điểm');
     $sheet->setCellValue('D' . $row, 'Ghi chú');
     $sheet->setCellValue('E' . $row, 'IP');
     $listIndentities = array();
     $ipList = array();
     foreach ($students as $key => $student) {
         ++$row;
         $sheet->setCellValue("A{$row}", $student->indentity_number);
         $sheet->setCellValue("B{$row}", $student->fullname);
         if (isset($studentsMark[$student->student_id])) {
             $resultOfStudent = $studentsMark[$student->student_id];
             if (!empty($resultOfStudent->ip_address) && in_array($resultOfStudent->ip_address, $ipList)) {
                 $sheet->setCellValue("D{$row}", "Trùng địa chỉ IP");
             } else {
                 $sheet->setCellValue("D{$row}", "");
                 $ipList[] = $resultOfStudent->ip_address;
             }
             $sheet->setCellValue("E{$row}", $resultOfStudent->ip_address);
             $sheet->setCellValue("C{$row}", (double) $resultOfStudent->score);
         } else {
             $sheet->setCellValue("D{$row}", "Chưa làm bài");
             $sheet->setCellValue("C{$row}", "");
         }
         if (in_array($student->indentity_number, $listIndentities)) {
             $sheet->setCellValue("D{$row}", "Trùng mã số học sinh");
         }
         $listIndentities[] = $student->indentity_number;
     }
     unset($sheet);
     header('Content-type: application/vnd.ms-excel');
     header("Content-Disposition: attachment; filename=\"{$title_class}\"");
     $writer = new PHPExcel_Writer_Excel5($this->CI->phpexcel);
     $writer->save('php://output');
 }

 /**
  * Return Excel Workbook in buffer 
  * 
  * @return string Excel string buffer
  */
 function GetBuffer()
 {
     // store tmpfile in TMPPATH need accessright
     $objWriter = new \PHPExcel_Writer_Excel5($this);
     $path = TMPPATH . DS . uniqid('xls_') . '.xls';
     $objWriter->save($path);
     $handle = fopen($path, "r");
     $contents = fread($handle, filesize($path));
     fclose($handle);
     return $contents;
 }

 public function excelExport($project_id)
 {
     PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;
     $objPHPExcel = new PHPExcel();
     $objPHPExcel->createSheet(0);
     $objPHPExcel->setActiveSheetIndex(0);
     //设置第一个内置表
     $objActSheet = $objPHPExcel->getActiveSheet();
     // 获取当前活动的表
     $objActSheet->setTitle('项目总体数据表');
     //获取项目下的所有成员id
     $examinee = $this->modelsManager->createBuilder()->columns(array('number', 'id', 'state', 'name'))->from('Examinee')->where('Examinee.type = 0 AND Examinee.project_id =  ' . $project_id)->getQuery()->execute()->toArray();
     //异常处理
     if (empty($examinee)) {
         throw new Exception('项目的被试人数为0,无法进行项目数据表生成');
     }
     $members_not_finished = array();
     foreach ($examinee as $value) {
         if ($value['state'] < 4) {
             $members_not_finished[$value['number']] = $value['name'];
         }
     }
     if (!empty($members_not_finished)) {
         $list = '项目中部分成员未完成测评过程，如下:<br/>';
         foreach ($members_not_finished as $key => $value) {
             $list .= $key . '：' . $value . '<br/>';
         }
         throw new Exception(print_r($list, true));
     }
     $i = 0;
     $result = new ProjectData();
     $start_column = 'D';
     $last = 'D';
     $last_data = null;
     foreach ($examinee as $examinee_info) {
         $data = array();
         $data = $result->getindividualComprehensive($examinee_info['id']);
         if ($i === 0) {
             $this->makeTable($data, $objActSheet);
         }
         $last = $start_column;
         $last_data = $data;
         $this->joinTable($data, $objActSheet, $start_column++, $examinee_info['number']);
         $i++;
     }
     // 计算平均值
     $this->joinAvg($objActSheet, $last_data, 'D', $last);
     //根据项目第一人成绩统计打表
     //循环写入每个人的成绩
     $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
     $file_name = './tmp/' . $project_id . '_project_data.xls';
     $objWriter->save($file_name);
     return $file_name;
 }

function CreateExcel()
{
    $write = new PHPExcel_Writer_Excel5($GLOBALS['excel']);
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
    header("Content-Type:application/force-download");
    header("Content-Type:application/vnd.ms-execl");
    header("Content-Type:application/octet-stream");
    header("Content-Type:application/download");
    header('Content-Disposition:attachment;filename="testdata.xls"');
    header("Content-Transfer-Encoding:binary");
    $write->save('php://output');
}

 /** {@inheritdoc} */
 public function appendRows($path, array $rows)
 {
     $phpExcel = \PHPExcel_IOFactory::load($path);
     $phpExcel->setActiveSheetIndex(0);
     $rowIndex = $phpExcel->getActiveSheet()->getHighestRow() + 1;
     foreach ($rows as $row) {
         foreach ($row as $columnIndex => $value) {
             $phpExcel->getActiveSheet()->setCellValueByColumnAndRow($columnIndex, $rowIndex, $value);
         }
         $rowIndex++;
     }
     $objWriter = new \PHPExcel_Writer_Excel5($phpExcel);
     $objWriter->save($path);
 }

 /**
  * 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_xls_');
     $workbookWriter = new PHPExcel_Writer_Excel5($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;
 }

 /**
  * Formats the specified response.
  * @param \yii\web\Response $response the response to be formatted.
  */
 public function format($response)
 {
     //$response->getHeaders()->set('Content-Type', 'application/vnd.ms-excel');
     $response->setDownloadHeaders(basename(\Yii::$app->request->pathInfo) . '.xls', 'application/vnd.ms-excel');
     if ($response->data === null) {
         return;
     }
     \PHPExcel_Settings::setCacheStorageMethod(\PHPExcel_CachedObjectStorageFactory::cache_to_sqlite3);
     $styles = $this->getStyles();
     $objPHPExcel = new \PHPExcel();
     $sheet = $objPHPExcel->getActiveSheet();
     $offset = 1;
     /*
      * serialize filter
     $sheet->setCellValue('A1', $opcje['nazwaAnaliza']);
     $sheet->duplicateStyle($styles['default'], 'A1:C4');
     $sheet->getRowDimension(1)->setRowHeight(18);
     $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(15);
     $sheet->getStyle('C3:C4')->getFont()->setBold(true);
     $offset = 6;
     */
     $data = $response->data;
     if (!isset($data['items'])) {
         // single model
         $this->addLine($sheet, $offset, array_keys($data));
         $this->addLine($sheet, $offset + 1, array_values($data));
         for ($i = 1, $lastColumn = 'A'; $i < count($data); $i++, $lastColumn++) {
         }
         $sheet->duplicateStyle($styles['header'], 'A' . $offset . ':' . $lastColumn . $offset);
     } else {
         // a collection of models
         if (($firstRow = reset($data['items'])) !== false) {
             $this->addLine($sheet, $offset, array_keys($firstRow));
         }
         $startOffset = ++$offset;
         $item = [];
         foreach ($data['items'] as $item) {
             $this->addLine($sheet, $offset++, $item);
         }
         $this->addSummaryRow($sheet, $startOffset, $offset, $item);
     }
     $filename = tempnam(\Yii::getAlias('@runtime'), 'xls');
     $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
     $objWriter->save($filename);
     $response->content = file_get_contents($filename);
     unlink($filename);
 }

 public function getCSV()
 {
     $filename = $this->stripName($this->options['LM_DOWNLOAD']);
     $pe = new \PHPExcel();
     $array = array_merge([$this->headerArray], $this->dataArray);
     $pe->setActiveSheetIndex(0)->fromArray($array);
     header("Pragma: public");
     header("Expires: 0");
     header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
     header("Content-Type: application/force-download");
     header("Content-Type: application/octet-stream");
     header("Content-Type: application/download");
     header("Content-Disposition: attachment;filename={$filename}.xls");
     header("Content-Transfer-Encoding: binary ");
     $objWriter = new \PHPExcel_Writer_Excel5($pe);
     $objWriter->save('php://output');
 }