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');
//输出到文件
}
