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'); }
/** * Save PHPExcel to file * * @param string $pFileName * @throws Exception */ public function save($pFilename = null) { parent::save($pFilename); $googleDocsClient = Zend_Gdata_ClientLogin::getHttpClient($this->_username, $this->_password, Zend_Gdata_Docs::AUTH_SERVICE_NAME); $googleDocsService = new Zend_Gdata_Docs($googleDocsClient); $googleDocsService->uploadFile($pFilename, basename($pFilename), null, Zend_Gdata_Docs::DOCUMENTS_LIST_FEED_URI); @unlink($pFilename); }
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); }
public function export($file_path = null) { $filename = $this->owner->filename; if (strpos($filename, '.xls') === false) { $filename .= '.xls'; } $xls = new \PHPExcel(); $xls->setActiveSheetIndex(0); $sheet = $xls->getActiveSheet(); // Columns $row = 1; $col = 0; foreach ($this->columns as $column) { $sheet->setCellValueByColumnAndRow($col, $row, $column); $style = $sheet->getStyleByColumnAndRow($col, $row); $style->getFont()->setBold(true); // $style->applyFromArray(['alignment' => ['horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER]]); $col++; } foreach ($this->data as $data_row) { $row++; $col = 0; foreach ($this->columns as $column) { $sheet->setCellValueByColumnAndRow($col, $row, $data_row[$column]); $col++; } } $objWriter = new \PHPExcel_Writer_Excel5($xls); if ($file_path) { $objWriter->save($file_path); } else { header('Content-type: application/vnd.ms-excel'); header('Content-disposition: attachment;filename=' . $filename); $objWriter->save('php://output'); } }
/** * 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); }
/** * @param $ids 用户id * @param $date 时间 * @param $depart_id 部门 */ public static function exportExcel($ids, $starrdate, $enddate, $departname = '') { vendor('PHPExcel'); $starrModel = M('Starr'); $dbdata = $starrModel->join("inner join gpa_detail on gpa_starr.id = gpa_detail.starr_id")->where("gpa_detail.last_uptime between " . $starrdate . " and " . $enddate . " and gpa_detail.starr_id in (" . $ids . ")")->select(); $dataprepare = array(); if (!empty($dbdata)) { foreach ($dbdata as $key => &$value) { $value['gpa_list'] = unserialize($value['gpa_list']); $total = $value['gpa_list']['chuanxone']['score'] + $value['gpa_list']['chuangxtwo']['score'] + $value['gpa_list']['chuangxthree']['score'] + $value['gpa_list']['zhidone']['score'] + $value['gpa_list']['zhudtwo']['score'] + $value['gpa_list']['outone']['score'] + $value['gpa_list']['outtwo']['score'] + $value['gpa_list']['outthree']['score'] + $value['gpa_list']['outfour']['score'] + $value['gpa_list']['outfive']['score'] + $value['gpa_list']['outsix']['score'] + $value['gpa_list']['outsev']['score'] + $value['gpa_list']['ydjx']['score'] + $value['gpa_list']['hp']['score']; $dataprepare[] = array($value['starr_name'], $value['gpa_list']['chuanxone']['score'], $value['gpa_list']['chuangxtwo']['score'], $value['gpa_list']['chuangxthree']['score'], $value['gpa_list']['zhidone']['score'], $value['gpa_list']['zhudtwo']['score'], $value['gpa_list']['outone']['score'], $value['gpa_list']['outtwo']['score'], $value['gpa_list']['outthree']['score'], $value['gpa_list']['outfour']['score'], $value['gpa_list']['outfive']['score'], $value['gpa_list']['outsix']['score'], $value['gpa_list']['outsev']['score'], $value['gpa_list']['ydjx']['score'], $value['gpa_list']['hp']['score'], $total); } } $excelheader = C('excelhead'); $excelIndex = array_keys($excelheader); //字母 $excelTitle = array_values($excelheader); //标题 $excel = new \PHPExcel(); $outExcel = new \PHPExcel_Writer_Excel5($excel); for ($i = 0; $i < count($excelTitle); $i++) { $excel->getActiveSheet()->setCellValue("{$excelIndex[$i]}1", "{$excelTitle[$i]}"); } $datacount = count($dataprepare); if ($datacount <= 0) { exit('no more data!'); } for ($i = 2; $i <= $datacount + 1; $i++) { $j = 0; foreach ($dataprepare[$i - 2] as $key => $value) { $excel->getActiveSheet()->setCellValue("{$excelIndex[$j]}{$i}", "{$value}"); $j++; } } 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=' . $departname . 'xls'); header("Content-Transfer-Encoding:binary"); $outExcel->save('php://output'); exit; }
public function output($filename, $data) { $objExcel = new PHPExcel(); $objWriter = new PHPExcel_Writer_Excel5($objExcel); // 用于 2007 格式 //$objWriter->setOffice2003Compatibility(true); $objActSheet = $objExcel->getActiveSheet(); //由PHPExcel根据传入内容自动判断单元格内容类型 // $objActSheet->setCellValue('A1', '字符串内容'); // 字符串内容 // $objActSheet->setCellValue('A2', '10010E-000001'); // 数值 // $objActSheet->setCellValue('A3', '1001000000001'); // 布尔值 // $objActSheet->setCellValueExplicit('b2', '1001000000001', PHPExcel_Cell_DataType::TYPE_STRING); 设置单元格格式 foreach ($data as $k => $v) { //循环行 foreach ($v as $kk => $vv) { //循环列 if (is_numeric($vv) && strlen($vv) > 10) { $objActSheet->setCellValueExplicit($this->column[$kk] . ($k + 1), $vv, PHPExcel_Cell_DataType::TYPE_STRING); $objActSheet->getStyle($this->column[$kk] . ($k + 1))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER); } else { $objActSheet->setCellValue($this->column[$kk] . ($k + 1), $vv); //单元格赋值 } } } $outputFileName = $filename; $ua = $_SERVER['HTTP_USER_AGENT']; $encoded_filename = urlencode($outputFileName); $encoded_filename = str_replace('+', '%20', $encoded_filename); if (preg_match('/MSIE/', $ua)) { header('Content-Disposition: attachment; filename="' . $encoded_filename . '"'); } elseif (preg_match('/Firefox/', $ua)) { header('Content-Disposition: attachment; filename*="utf8\'\'' . $outputFileName . '"'); } else { header('Content-Disposition: attachment; filename="' . $outputFileName . '"'); } header('Content-Type: application/force-download'); header('Content-Type: application/octet-stream'); header('Content-Type: application/download'); header('Content-Transfer-Encoding: binary'); header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); header('Cache-Control: must-revalidate, post-check=0, pre-check=0'); header('Pragma: no-cache'); $objWriter->save('php://output'); }
function index() { $this->load->library('PHPExcel'); $sheet = $this->phpexcel->getActiveSheet(); $sheet->getColumnDimension('A')->setWidth(5); $sheet->setCellValue('A1', 'First Row'); $writer = new PHPExcel_Writer_Excel5($this->phpexcel); $filename = 'just_some_random_name.xls'; //save our workbook as this file name header('Content-Type: application/vnd.ms-excel'); //mime type header('Content-Disposition: attachment;filename="' . $filename . '"'); //tell browser what's the file name header('Cache-Control: max-age=0'); //no cache $writer->save('php://output'); }
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'); }
function writeExelFile($filename, $data, $header) { require_once 'Classes/PHPExcel.php'; $pExcel = new PHPExcel(); $pExcel->setActiveSheetIndex(0); $aSheet = $pExcel->getActiveSheet(); array_unshift($data, $header); for ($i = 0; $data[$i]; $i++) { for ($j = 0; $j < count($data[$i]); $j++) { $aSheet->setCellValueByColumnAndRow($j, $i + 1, $data[$i][$j]); } } for ($i = 0; $i < count($header); $i++) { $aSheet->getStyleByColumnAndRow($i, 1)->applyFromArray(array('fill' => array('type' => PHPExcel_STYLE_FILL::FILL_SOLID, 'color' => array('rgb' => 'CFCFCF')))); } $objWriter = new PHPExcel_Writer_Excel5($pExcel); $objWriter->save($filename); }
public function buildInvExcel($books) { App::import("Vendor", "phpexcel/PHPExcel"); App::import("Vendor", "phpexcel/PHPExcel/Writer/Excel5"); $r['path'] = TMP . 'tests' . DS; $r['file'] = 'tmp_books_inv_' . $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, 'ISBN'); $excel->getActiveSheet()->setCellValueByColumnAndRow(3, 1, '作者/編者'); $excel->getActiveSheet()->setCellValueByColumnAndRow(4, 1, '出版公司'); $excel->getActiveSheet()->setCellValueByColumnAndRow(5, 1, '購買日期'); $excel->getActiveSheet()->setCellValueByColumnAndRow(6, 1, '盤點日期'); $excel->getActiveSheet()->setCellValueByColumnAndRow(7, 1, '狀態'); $excel->getActiveSheet()->setCellValueByColumnAndRow(8, 1, '盤點'); $i = 1; foreach ($books as $book) { $i++; $excel->getActiveSheet()->setCellValueExplicitByColumnAndRow(0, $i, $book['Book_Instance']['id'], PHPExcel_Cell_DataType::TYPE_STRING); $excel->getActiveSheet()->setCellValueByColumnAndRow(1, $i, $book['Book']['book_name']); $excel->getActiveSheet()->setCellValueExplicitByColumnAndRow(2, $i, $book['Book']['isbn'], PHPExcel_Cell_DataType::TYPE_STRING); $excel->getActiveSheet()->setCellValueByColumnAndRow(3, $i, $book['Book']['book_author']); $excel->getActiveSheet()->setCellValueByColumnAndRow(4, $i, $book["Book"]["book_publisher"]); $excel->getActiveSheet()->setCellValueByColumnAndRow(5, $i, $book['Book_Instance']['purchase_date']); $excel->getActiveSheet()->setCellValueByColumnAndRow(7, $i, $book['Book_Status']['status_name']); if ($book['System_Take_Stock']['id'] == null) { $excel->getActiveSheet()->setCellValueByColumnAndRow(8, $i, ''); $excel->getActiveSheet()->setCellValueByColumnAndRow(6, $i, ''); } else { $excel->getActiveSheet()->setCellValueByColumnAndRow(6, $i, $book['System_Take_Stock']['update_date']); $excel->getActiveSheet()->setCellValueByColumnAndRow(8, $i, '已盤點'); } } $objWriter = new PHPExcel_Writer_Excel5($excel); $objWriter->save($file); return $r; }
/** * 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 function generateReport() { require_once PATH_LIBS . 'phpexcel/PHPExcel.php'; require_once PATH_LIBS . 'phpexcel/PHPExcel/Writer/Excel2007.php'; $xls = PHPExcel_IOFactory::load($_SERVER['DOCUMENT_ROOT'] . '/../reports/report.xls'); $xls->setActiveSheetIndex(0); $sheet = $xls->getActiveSheet(); $KoncentrTovarishestvo = KoncentrTovarishestvo::find_by_sql('SELECT * FROM `counter` inner join `koncentrator_tovarishestvo` on `koncentrator_tovarishestvo`.`name_konc` = `counter`.`concetrator` WHERE `id_tovar` = ' . $_REQUEST['tovarishestvo']); $date_start = explode('.', $_REQUEST[Report::DATE_START]); $date_start_string = $date_start[2] . '.' . $date_start[1] . '.' . $date_start[0]; $date_end = explode('.', $_REQUEST[Report::DATE_END]); $date_end_string = $date_end[2] . '.' . $date_end[1] . '.' . $date_end[0]; $i = 10; $j = 1; foreach ($KoncentrTovarishestvo as $counter) { $user = User::find('all', ['conditions' => ['counter = ? and concetrator =?', $counter->counter, $counter->concetrator]]); if ($user != null) { $sheet->setCellValue('A' . $i, $j); $sheet->setCellValue('B' . $i, 'ТП-Рында'); $sheet->setCellValue('C' . $i, $user[0]->fio); $sheet->setCellValue('E' . $i, $user[0]->address); $sheet->setCellValue('F' . $i, $user[0]->n_scet); $sheet->setCellValue('M' . $i, $counter->date); $sheet->setCellValue('N' . $i, (int) $counter->reading); $i++; $j++; } } $i += 3; $sheet->setCellValue('M' . $i, 'От Исполнителя: ______________________________________'); $i++; $sheet->setCellValue('M' . $i, '(должность, подпись, ФИО)'); $objWriter = new PHPExcel_Writer_Excel5($xls); $Tovarishestvo = Tovarishestva::find('all', ['conditions' => ['id = ?', $_REQUEST['tovarishestvo']]]); header("Content-type: application/vnd.ms-excel"); header("Content-Disposition: attachment; filename=" . $Tovarishestvo[0]->name . " " . $_REQUEST[Report::DATE_START] . "-" . $_REQUEST[Report::DATE_END] . ".xls"); $objWriter->save('php://output'); }
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('综合'); $this->checkoutFirst($objActSheet, $project_id); //个人信息 //data //数据 $data = new ProjectComData(); $data->project_check($project_id); $inquery_data = $data->getInqueryAnsComDetail($project_id); $objPHPExcel->createSheet(1); //添加一个表 $objPHPExcel->setActiveSheetIndex(1); //设置第2个表为活动表,提供操作句柄 $objActSheet = $objPHPExcel->getActiveSheet(); // 获取当前活动的表 $objActSheet->setTitle('人数统计-单项统计'); $this->checkoutSecond($objActSheet, $project_id, $inquery_data); $objPHPExcel->createSheet(2); //添加一个表 $objPHPExcel->setActiveSheetIndex(2); //设置第2个表为活动表,提供操作句柄 $objActSheet = $objPHPExcel->getActiveSheet(); // 获取当前活动的表 $objActSheet->setTitle('人数统计-交叉项统计'); $this->checkoutThird($objActSheet, $project_id, $inquery_data); $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); $file_name = './tmp/' . $project_id . '_inqueryans_data.xls'; $objWriter->save($file_name); return $file_name; }
function create_file($patch_file, $name_file, $array_output, $header) { // Создаем объект класса PHPExcel $xls = new PHPExcel(); // Устанавливаем индекс активного листа $xls->setActiveSheetIndex(0); // Получаем активный лист $sheet = $xls->getActiveSheet(); // Подписываем лист $sheet->setTitle('the report from ' . date("m.d.y")); $style_borders = array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => '808080')); $str_num = 1; foreach ($array_output as $key => $val) { foreach ($header as $k => $v) { if ($str_num != 1) { //вставляем значения в ячейки $sheet->setCellValue($v['column'] . $str_num, $val[$k]); } else { // Вставляем текст заголовока таблицы по ячейкам $sheet->setCellValue($v['column'] . $str_num, $v['title']); $sheet->getColumnDimension($v['column'])->setAutoSize(true); $sheet->getStyle($v['column'] . $str_num)->getFont()->setBold(true); $sheet->getStyle($v['column'] . $str_num)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $sheet->getStyle($v['column'] . $str_num)->getFill()->getStartColor()->setRGB('EEEEEE'); } $sheet->getStyle($v['column'] . $str_num)->getBorders()->getBottom()->applyFromArray($style_borders); $sheet->getStyle($v['column'] . $str_num)->getBorders()->getLeft()->applyFromArray($style_borders); $sheet->getStyle($v['column'] . $str_num)->getBorders()->getTop()->applyFromArray($style_borders); $sheet->getStyle($v['column'] . $str_num)->getBorders()->getRight()->applyFromArray($style_borders); $sheet->getStyle($v['column'] . $str_num)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); } $str_num++; } // записывем файл $objWriter = new PHPExcel_Writer_Excel5($xls); $objWriter->save($patch_file . $name_file); }
public function home() { global $user; $v = $this->__(array('ssid', 'code', 'view', 'e', 'f', 'a' => 0, 'offset' => 0)); $total = 0; $per_page = 15; $sql = $sql_total = $sql_where = ''; if ($v['view'] == 'search') { if (!f($v['ssid'])) { _fatal(); } $cached = sql_cache('', $v['ssid']); $sql_total = $cached['query']; $sql = $sql_total . ' LIMIT {v_limit_start}, {v_limit_end}'; $v['a_view'] = array('mode_alias' => 'search', 'mode_name' => 'Buscar', 'mode_sql' => $sql, 'mode_sql_total' => $sql_total); } else { $modes = $this->init_mode(); $v['view'] = isset($modes[$v['view']]) ? $v['view'] : 'all'; $v['a_view'] = $modes[$v['view']]; if ($v['a_view']['mode_alias'] == 'all' && $user->v('user_type') != U_FOUNDER) { $sql = 'SELECT * FROM _groups_members WHERE member_uid = ?'; if (!($row = _fieldrow(sql_filter($sql, $user->v('user_id'))))) { redirect(_link($this->m(), array('view' => 'own'))); } } } $filter_uid = $user->v('user_id'); if (f($v['f'])) { $sql = 'SELECT user_id FROM _members WHERE user_username = ?'; $filter_uid = _field(sql_filter($sql, $v['f']), 'user_id', $filter_uid); } $status_alias = $this->init_status_list('status_alias', 'status_id'); if ($v['e'] == 'table') { $v['a_view']['mode_sql'] = str_replace('LIMIT {v_limit_start}, {v_limit_end}', '', $v['a_view']['mode_sql']); } $v_sql = array('userid' => $filter_uid, 'group' => $user->auth_groups(), 'limit_start' => $v['offset'], 'limit_end' => $per_page, 'closed' => $status_alias['closed']); $tickets_sql = _template_query($v['a_view']['mode_sql'], $v_sql); $tickets_sql_total = _template_query($v['a_view']['mode_sql_total'], $v_sql); if (!f($tickets_sql) || !f($tickets_sql_total)) { _fatal(); } if ($row = _fieldrow($tickets_sql_total)) { $total = isset($row['total']) ? $row['total'] : _numrows($row); } if ($tickets = _rowset($tickets_sql)) { $groups = $user->_groups(); $status_list = $this->init_status_list(); if ($v['e'] == 'table') { $sql_tickets = preg_replace('#^SELECT (.*?)[\\n]#is', 'SELECT ticket_id' . "\n", $tickets_sql); // // Assignees $sql = 'SELECT a.assign_ticket, m.user_firstname, m.user_lastname FROM _tickets_assign a, _members m WHERE a.assign_ticket IN (' . $sql_tickets . ') AND a.user_id = m.user_id ORDER BY a.assign_ticket'; $tech_assoc = _rowset($sql, 'assign_ticket', false, true); //_pre($tech_assoc, true); // // Notes $sql = 'SELECT m.user_id, m.user_firstname, m.user_lastname, n.ticket_id, n.note_text, n.note_time, n.note_cc FROM _members m, _tickets_notes n WHERE n.ticket_id IN (' . $sql_tickets . ') AND n.user_id = m.user_id ORDER BY n.ticket_id, n.note_time'; $notes_assoc = _rowset($sql, 'ticket_id', false, true); @set_time_limit(0); // // Include the PHPExcel classes require_once XFS . 'core/excel/PHPExcel.php'; require_once XFS . 'core/excel/PHPExcel/Writer/Excel5.php'; require_once XFS . 'core/css.php'; $phpcss = new phpcss(); $phpcss->parse('./style/css/default.css'); // Start to build the spreadsheet $excel = new PHPExcel(); $excel->setActiveSheetIndex(0); $excel->getActiveSheet()->getHeaderFooter()->setOddFooter("&RPage &P of &N"); // $excel->getActiveSheet()->setCellValue('A1', 'Titulo'); $excel->getActiveSheet()->setCellValue('B1', 'Asignado'); $excel->getActiveSheet()->setCellValue('C1', 'Categoria'); $excel->getActiveSheet()->setCellValue('D1', 'Solicitante'); $excel->getActiveSheet()->setCellValue('E1', 'Fecha/hora'); $excel->getActiveSheet()->setCellValue('F1', 'Texto'); $excel->getActiveSheet()->setCellValue('G1', 'Estado'); $excel->getActiveSheet()->getStyle("A1:G1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $excel->getActiveSheet()->getStyle("A1:G1")->getFont()->setBold(true); $excel->getActiveSheet()->getStyle("A1:G1")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('EBEBC6'); $excel->getActiveSheet()->getColumnDimension('A')->setWidth(20); $excel->getActiveSheet()->getColumnDimension('B')->setWidth(15); $excel->getActiveSheet()->getColumnDimension('C')->setWidth(10); $excel->getActiveSheet()->getColumnDimension('D')->setWidth(15); $excel->getActiveSheet()->getColumnDimension('E')->setWidth(15); $excel->getActiveSheet()->getColumnDimension('F')->setWidth(30); $excel->getActiveSheet()->getColumnDimension('G')->setWidth(10); $i = 2; foreach ($tickets as $row) { $row_color = $phpcss->property_get('.ticket_status_' . $status_list[$row['ticket_status']]['status_alias'], 'background'); $row_color = preg_replace('#^\\#([A-Za-z0-9]+).*?$#is', '\\1', $row_color); if (!isset($row['cat_name'])) { $row['cat_name'] = ''; } $assignees = ''; if (isset($tech_assoc[$row['ticket_id']])) { foreach ($tech_assoc[$row['ticket_id']] as $rowtech) { $assignees .= (f($assignees) ? ', ' : '') . _fullname($rowtech); } } $excel->getActiveSheet()->setCellValue("A{$i}", entity_decode($row['ticket_title'])); $excel->getActiveSheet()->setCellValue("B{$i}", entity_decode($assignees)); $excel->getActiveSheet()->setCellValue("C{$i}", entity_decode($row['cat_name'])); $excel->getActiveSheet()->setCellValue("D{$i}", entity_decode(_fullname($row))); $excel->getActiveSheet()->setCellValue("E{$i}", entity_decode(_format_date($row['ticket_start']))); $excel->getActiveSheet()->setCellValue("F{$i}", entity_decode($row['ticket_text'])); $excel->getActiveSheet()->setCellValue("G{$i}", entity_decode($status_list[$row['ticket_status']]['status_name'])); $excel->getActiveSheet()->getStyle("A{$i}:G{$i}")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); $excel->getActiveSheet()->getStyle("A{$i}:G{$i}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB($row_color); $i++; if (isset($notes_assoc[$row['ticket_id']])) { foreach ($notes_assoc[$row['ticket_id']] as $rownote) { $excel->getActiveSheet()->setCellValue("B{$i}", entity_decode(_fullname($rownote))); $excel->getActiveSheet()->setCellValue("E{$i}", entity_decode(_format_date($rownote['note_time']))); $excel->getActiveSheet()->setCellValue("F{$i}", entity_decode($rownote['note_text'])); $excel->getActiveSheet()->mergeCells("F{$i}:G{$i}"); $excel->getActiveSheet()->getStyle("A{$i}:G{$i}")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); $i++; } } } $excel->getActiveSheet()->freezePane('A2'); // // Output the headers header('Content-Type: application/vnd.ms-excel;'); header('Content-type: application/x-msexcel'); header('Content-Disposition: attachment; filename="solicitudes-' . date('Y-m-d') . '.xls"'); header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); header('Last-Modified: ' . gmdate('D,d M YH:i:s') . ' GMT'); header('Cache-Control: no-cache, must-revalidate'); header('Pragma: no-cache'); // Output the spreadsheet in binary format $writer = new PHPExcel_Writer_Excel5($excel); $writer->save('php://output'); exit; } $u_link = array('view' => $v['view'], 'f' => $v['f']); if ($v['view'] == 'search') { $u_link['ssid'] = $v['ssid']; } $sv = array('TITLE' => $v['a_view']['mode_name'], 'SIZE' => $total, 'CURRENT' => $v['offset'], 'U_ETABLE' => _link($this->m(), array_merge($u_link, array('e' => 'table'))), 'G_ETABLE' => _lang('TICKET_ETABLE_YES'), 'U_TICKET' => _link($this->m(), array('x1' => 'view', 'a' => '*', 'offset' => $v['offset'])), 'U_STATUS' => _link($this->m(), array('x1' => 'search', 'x2' => 'status', 'e' => $v['e']))); foreach ($tickets as $i => $row) { if (!$i) { $pags = _pagination(_link($this->m(), $u_link), 'offset:%d', $total, $per_page, $v['offset']); _style('tickets', array_merge($sv, $pags)); } if (!isset($row['ticket_group']) || !$row['ticket_group']) { $row['ticket_group'] = 1; } $ticket_row = array('ID' => $row['ticket_id'], 'URL' => _link($this->m(), array('x1' => 'view', 'a' => $row['ticket_id'], 'offset' => $v['offset'])), 'STATUS' => $this->init_status($row['ticket_status']), 'STATUS_NAME' => $status_list[$row['ticket_status']]['status_name'], 'TITLE' => dvar($row['ticket_title'], _lang('TICKET_NO_SUBJECT')), 'TEXT' => _message($row['ticket_text']), 'START' => _format_date($row['ticket_start']), 'AUTHOR' => _fullname($row), 'GROUP' => $user->v('is_founder') ? $groups[$row['ticket_group']]['group_email'] : ''); _style('tickets.row', _vs($ticket_row, 'v')); if (isset($tech_assoc[$row['ticket_id']])) { foreach ($tech_assoc[$row['ticket_id']] as $ti => $tech_name) { if (!$ti) { _style('tickets.row.techs'); } _style('tickets.row.techs.rowt', array('NAME' => _fullname($tech_name))); } } if (isset($notes_assoc[$row['ticket_id']])) { foreach ($notes_assoc[$row['ticket_id']] as $ti => $note_row) { if (!$ti) { _style('tickets.row.notes'); } _style('tickets.row.notes.rown', array('TEXT' => $note_row['note_text'], 'TIME' => _format_date($note_row['note_time']), 'REALNAME' => _fullname($note_row))); } } } } else { if ($v['offset']) { redirect(_link($this->m(), array('view' => $v['view']))); } _style('noresults'); } v_style(array('V_CHOWN' => f($v['f']) ? $v['f'] : '')); return $this->__home_common($v['offset'], $v['view']); }
// if ($ttdApprovedBy != 0 || $ttdApprovedBy != "-") { // if (file_exists('../img/'.$ttdApprovedBy)) { // $objApprovedSign->setName('ttdApproved'); // $objApprovedSign->setDescription('ttd approved image'); // $objApprovedSign->setPath('../img/'.$ttdApprovedBy); // $objApprovedSign->setHeight(60); // $objApprovedSign->setCoordinates('H'.($startRow+1)); // $offsetX =(146 - $objApprovedSign->getWidth())/2; // $objApprovedSign->setOffsetX($offsetX); // $objApprovedSign->setWorksheet($objExcel->getActiveSheet()); // } // } // WRITE DIGITAL SIGNATURE FOOTER // $objExcel->getActiveSheet()->SetCellValue('E'.($startRow+4), $nama); // $objExcel->getActiveSheet()->SetCellValue('F'.($startRow+4), $orderedBy); $objExcel->getActiveSheet()->SetCellValue('H' . ($startRow + 4), $nama); //$objExcel->getActiveSheet()->mergeCells('F'.($startRow+4).':G'.($startRow+4)); // APPLY STYLES IN DIGITAL SIGNATURE $objExcel->getActiveSheet()->getStyle('H' . $startRow . ':H' . ($startRow + 4))->applyFromArray($styleBorder); $objExcel->getActiveSheet()->getStyle('H' . $startRow . ':H' . ($startRow + 4))->applyFromArray($styleCenter); // UNSET STYLE VARIABLES unset($styleBorder); unset($styleCenter); // PROTECT EXCEL DOCUMENT FOR BEING EDITING $objExcel->getActiveSheet()->getProtection()->setPassword('Sukasari1234'); $objExcel->getActiveSheet()->getProtection()->setSheet(true); // TELL BROWSER TO DOWNLOAD EXCEL DOCUMENT header('Content-type: application/vnd.ms-excel'); header('Content-Disposition: attachment; filename="' . $noDO . '_' . $layout . '.xls"'); $objWriter->save('php://output');
public function managerExport($arr, $role, $project_id) { $objPHPExcel = new PHPExcel(); $objActSheet = $objPHPExcel->getActiveSheet(); /* * excel属性 */ $file_name = ''; if ($role == 'L') { $objPHPExcel->getProperties()->setTitle('领导excel'); $objPHPExcel->getProperties()->setSubject('领导excel'); $file_name = './tmp/' . $project_id . '_leaders.xls'; } else { if ($role == 'I') { $objPHPExcel->getProperties()->setTitle('面询专家excel'); $objPHPExcel->getProperties()->setSubject('面询专家excel'); $file_name = './tmp/' . $project_id . '_interviewers.xls'; } else { throw new Exception('No this type-' . $role); } } /* * 设置单元格的值 */ $objActSheet->setCellValue('A1', '用户名(编号)'); $objActSheet->setCellValue('B1', '密码'); $objActSheet->setCellValue('C1', '姓名'); $objActSheet->setCellValue('D1', '项目编号'); foreach ($arr as $key => $item) { $key = $key + 2; $objActSheet->setCellValue('A' . $key, $item->username); $objActSheet->setCellValue('B' . $key, $item->password); $objActSheet->setCellValue('C' . $key, $item->name); $objActSheet->setCellValue('D' . $key, $item->project_id); } $objActSheet->getColumnDimension('A')->setAutoSize(true); $objActSheet->getColumnDimension('B')->setAutoSize(true); $objActSheet->getColumnDimension('C')->setAutoSize(true); $objActSheet->getColumnDimension('D')->setAutoSize(true); $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); $objWriter->save($file_name); return $file_name; }
/** * 把数组数据输出到 Excel 表格 * * @param array $dataArray 数据格式为 array(array(第一行数据), array(第二行数据), ...) * @param string $fileName 文件名,不带 xls 后缀,不带路径 * @param string $filePath 输出文件的路径,如果为 null 表示输出到浏览器作为附件下载 * @param int $maxCellWidth 最大单元格宽度限制 * * */ public static function dumpArrayToExel(array $dataArray, $fileName, $filePath = null, $maxCellWidth = null) { $objPHPExcel = new \PHPExcel(); // 设置工作 sheet $objPHPExcel->setActiveSheetIndex(0); $activeSheet = $objPHPExcel->getActiveSheet(); $activeSheet->fromArray($dataArray); // 调整列大小,防止数据挤到一起去了 $maxColumnIndex = $activeSheet->getColumnDimension($activeSheet->getHighestColumn())->getColumnIndex(); for ($columnIndex = 'A'; $columnIndex != $maxColumnIndex; $columnIndex++) { $activeSheet->getColumnDimension($columnIndex)->setAutoSize(true); } $activeSheet->calculateColumnWidths(); if ($maxCellWidth > 0) { for ($columnIndex = 'A'; $columnIndex != $maxColumnIndex; $columnIndex++) { if ($activeSheet->getColumnDimension($columnIndex)->getWidth() > $maxCellWidth) { $activeSheet->getColumnDimension($columnIndex)->setAutoSize(false); $activeSheet->getColumnDimension($columnIndex)->setWidth($maxCellWidth); $activeSheet->getStyle($columnIndex)->getAlignment()->setWrapText(true); } } } // 输出为 Excel5 格式 $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel); if (empty($filePath)) { header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment; filename="' . urlencode($fileName) . '.xls"'); header('Cache-Control: max-age=0'); $objWriter->save('php://output'); //输出到浏览器 return; } $objWriter->save($filePath . '/' . urlencode($fileName) . '.xls'); //输出到文件 }