function downExcel($map, $data)
{
include_once IA_ROOT . '/framework/class/PHPExcel.php';
include_once IA_ROOT . '/framework/class/PHPExcel/Writer/Excel5.php';
$excel = new PHPExcel();
//$map['title'] = iconv('utf-8", "gb2312", $map['title']);
$excel->getProperties()->setCreator("时代地产");
$excel->setActiveSheetIndex(0);
$excel->getActiveSheet()->setTitle($map['title']);
$excel->setActiveSheetIndex(0);
$sheet = $excel->getActiveSheet();
$c = range('A', 'Z');
$i = 1;
$cell = '';
foreach ($map['fields'] as $k => $f) {
$cell = $c[$k] . $i;
$sheet->setCellValue($cell, $f['title']);
}
foreach ($data as $item) {
$i++;
foreach ($map['fields'] as $k => $f) {
$cell = $c[$k] . $i;
$value = $item[$f['field']];
if ($f['type'] == 1) {
$value = date('Y-m-d H:i:s', $item[$f['field']]);
}
//数值类型
if ($f['type'] == 2) {
$sheet->setCellValueExplicit($cell, $value, PHPExcel_Cell_DataType::TYPE_STRING);
} else {
$sheet->setCellValue($cell, $value);
}
}
}
$writer = new PHPExcel_Writer_Excel5($excel);
header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type: application/vnd.ms-excel;charset=UTF-8");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
//header("Content-Disposition:attachment;filename=" . $map['title'] . ".xls");
$ua = strtoupper($_SERVER["HTTP_USER_AGENT"]);
$filename = basename($map['title'] . ".xls");
if (preg_match("/IE/", $ua)) {
$encoded_filename = str_replace("+", "%20", urlencode($filename));
//header('Content-Disposition: attachment; filename=' . $filename);
header('Content-Disposition: attachment; filename="' . $encoded_filename . '"');
} else {
header('Content-Disposition: attachment; filename=' . $filename);
}
header("Content-Transfer-Encoding:binary");
$writer->save("php://output");
}
public function buildBookStatReportExcel($books)
{
App::import("Vendor", "phpexcel/PHPExcel");
App::import("Vendor", "phpexcel/PHPExcel/Writer/Excel5");
$r['path'] = TMP . 'tests' . DS;
$r['file'] = 'tmp_books_stat_' . $this->Session->read('user_id');
$file = $r['path'] . $r['file'];
$excel = new PHPExcel();
$excel->setActiveSheetIndex(0);
$excel->getActiveSheet()->setTitle('Books');
$excel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, '級別');
$excel->getActiveSheet()->setCellValueByColumnAndRow(1, 1, '書籍名稱');
$excel->getActiveSheet()->setCellValueByColumnAndRow(2, 1, '作者');
$excel->getActiveSheet()->setCellValueByColumnAndRow(3, 1, 'ISBN');
$excel->getActiveSheet()->setCellValueByColumnAndRow(4, 1, '索書號');
$excel->getActiveSheet()->setCellValueByColumnAndRow(5, 1, '出借分校');
$excel->getActiveSheet()->setCellValueByColumnAndRow(6, 1, '出借次數');
$i = 1;
foreach ($books as $book) {
$i++;
$excel->getActiveSheet()->setCellValueExplicitByColumnAndRow(0, $i, $book['books']['cate_id'], PHPExcel_Cell_DataType::TYPE_STRING);
$excel->getActiveSheet()->setCellValueByColumnAndRow(1, $i, $book['books']['book_name']);
$excel->getActiveSheet()->setCellValueByColumnAndRow(2, $i, $book['books']['book_author']);
$excel->getActiveSheet()->setCellValueExplicitByColumnAndRow(3, $i, $book['books']['isbn'], PHPExcel_Cell_DataType::TYPE_STRING);
$excel->getActiveSheet()->setCellValueExplicitByColumnAndRow(4, $i, $book['books']['book_search_code'], PHPExcel_Cell_DataType::TYPE_STRING);
$excel->getActiveSheet()->setCellValueByColumnAndRow(5, $i, $book['system_locations']['location_name']);
$excel->getActiveSheet()->setCellValueByColumnAndRow(6, $i, $book[0]['cnt']);
}
$objWriter = new PHPExcel_Writer_Excel5($excel);
$objWriter->save($file);
return $r;
}
public static function renderData(array $itemsIterator, array $fields, $filename)
{
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("PrintWeek");
$objPHPExcel->getProperties()->setTitle("Report");
$objPHPExcel->getProperties()->setSubject("Report");
$objPHPExcel->getProperties()->setDescription("Report");
$objPHPExcel->setActiveSheetIndex(0);
/**
* Выводим строку названий столбцов
*/
$col = 0;
foreach ($fields as $name) {
$objPHPExcel->getActiveSheet()->SetCellValue(self::getCellCoordinate($col++, 1), $name);
}
/**
* Основной вывод информации
*/
$row = 2;
foreach ($itemsIterator as $item) {
$col = 0;
foreach ($fields as $name => $title) {
$objPHPExcel->getActiveSheet()->SetCellValue(self::getCellCoordinate($col++, $row), $item->{$name});
}
$row++;
}
$objPHPExcel->getActiveSheet()->setTitle('Report');
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save($filename);
}
public function pruebaExcel()
{
$sheetId = 0;
$this->phpexcel->createSheet(NULL, $sheetId);
$this->phpexcel->setActiveSheetIndex($sheetId);
$this->phpexcel->getActiveSheet()->setTitle("Excel de Prueba");
$sheet = $this->phpexcel->getActiveSheet();
$sheet->getColumnDimension('A')->setWidth(30);
$sheet->getColumnDimension('B')->setWidth(30);
$sheet->getColumnDimension('C')->setWidth(30);
$sheet->getColumnDimension('D')->setWidth(30);
$sheet->getColumnDimension('E')->setWidth(30);
$sheet->getColumnDimension('F')->setWidth(30);
$sheet->getColumnDimension('G')->setWidth(30);
$styleArray = array('font' => array('bold' => true));
$sheet->setCellValue('A3', 'Columna A3');
$sheet->setCellValue('B3', 'Columna BB');
$sheet->setCellValue('C3', 'Columna C3');
$sheet->setCellValue('D3', 'Columna D3');
$sheet->setCellValue('E3', 'Columna E3');
$sheet->setCellValue('F3', 'Columna F3');
$sheet->setCellValue('G3', 'Columna G3');
$sheet->setCellValue('A4', 'Columna A4');
$sheet->setCellValue('B4', 'Columna B4');
$sheet->setCellValue('C4', 'Columna C4');
$sheet->setCellValue('D4', 'Columna D4');
$sheet->setCellValue('E4', 'Columna E4');
$sheet->setCellValue('F4', 'Columna F4');
$sheet->setCellValue('G4', 'Columna G4');
$writer = new PHPExcel_Writer_Excel5($this->phpexcel);
header('Content-type: application/vnd.ms-excel');
$writer->save('php://output');
}
public function renderFooter()
{
$filename = tempnam(\Yii::getAlias('@runtime'), 'xls');
$objWriter = new \PHPExcel_Writer_Excel5($this->objPHPExcel);
$objWriter->save($filename);
$content = file_get_contents($filename);
unlink($filename);
return $content;
}
/**
* The MysqlExportXls function is used to export mysql query result into an .xls file.
* @param MysqlExportXlsConnectOptions $connectOptions
* @param MysqlExportXlsFileOptions $fileOptions
* @return error message. Return empty string on success.
*/
function MysqlExportXls($connectOptions, $fileOptions, $query)
{
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator($fileOptions->creator);
$objPHPExcel->getProperties()->setLastModifiedBy($fileOptions->lastModifiedBy);
$objPHPExcel->getProperties()->setTitle($fileOptions->title);
$objPHPExcel->getProperties()->setSubject($fileOptions->subject);
$objPHPExcel->getProperties()->setDescription($fileOptions->description);
$objPHPExcel->setActiveSheetIndex(0);
$activeSheet = $objPHPExcel->getActiveSheet();
$activeSheet->setTitle($fileOptions->title);
// connect to mysql
$link = mysql_connect($connectOptions->host, $connectOptions->userName, $connectOptions->password);
if (!$link) {
return __FILE__ . ":" . __FUNCTION__ . ':' . 'Could not connect: ' . mysql_error($link);
}
// use database
$selectDb = mysql_select_db($connectOptions->useDatabase, $link);
if (!$selectDb) {
return __FILE__ . ":" . __FUNCTION__ . ':' . 'Could not select database' . mysql_error($link);
}
// PHPExcel use utf-8 encoding to save file only !!!
$setCharset = mysql_set_charset("utf8", $link);
if (!$setCharset) {
return __FILE__ . ":" . __FUNCTION__ . ':' . 'Could not set charset' . mysql_error($link);
}
// execute sql
$result = mysql_query($query, $link);
if (!$result) {
return __FILE__ . ":" . __FUNCTION__ . ':' . 'Query failed: ' . mysql_error($link);
}
// field names
$columnIndex = 0;
while ($field = mysql_fetch_field($result)) {
$activeSheet->SetCellValue(PHPExcel_Cell::stringFromColumnIndex($columnIndex) . '1', $field->name);
++$columnIndex;
}
$rowIndex = 2;
// 1 based, the firset row is for field names.
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$columnIndex = 0;
foreach ($line as $key => $col_value) {
$activeSheet->SetCellValue(PHPExcel_Cell::stringFromColumnIndex($columnIndex) . $rowIndex, $col_value === null ? "" : $col_value, PHPExcel_Cell_DataType::TYPE_STRING2);
++$columnIndex;
}
++$rowIndex;
}
// free mysql resource
mysql_free_result($result);
mysql_close($link);
// write data into file
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->setPreCalculateFormulas(FALSE);
// Why true by default ? oh god damn it!
$objWriter->save($fileOptions->name);
return "";
}
/**
* _output
*
* @param mixed $fileName
*
* @access protected
* @return void
*/
function _output($fileName)
{
//$starting_pos = ord('C'); //unused
header("Content-type: application/vnd.ms-excel");
header('Content-Disposition: attachment;filename=' . $fileName . ".xls");
header('Cache-Control: max-age=0');
$objWriter = new PHPExcel_Writer_Excel5($this->xls);
$objWriter->setTempDir(TMP);
$objWriter->save('php://output');
}
function downloadStudentResult($class_id, $topic_manage_id)
{
$this->CI->load->model('student_info_model');
$this->CI->load->model('class_model');
$this->CI->load->model('topic_model');
$this->CI->load->model('student_mark_model');
$this->CI->load->helper('inflector');
$this->CI->load->library(['utils']);
$class = $this->CI->class_model->find_by_pkey($class_id);
$topic = $this->CI->topic_model->getTopicIdByTopicManageId($topic_manage_id);
$topics = array_filter(array_map('trim', explode(',', $topic->topic_id)));
$studentsMark = $this->CI->student_mark_model->getMarkStudents($topics, $class_id);
$studentsMark = $this->CI->utils->makeList('student_id', $studentsMark);
$students = $this->CI->student_info_model->getAllStudents($class_id);
$this->CI->load->library('PhpOffice/PHPExcel');
$sheet = $this->CI->phpexcel->getActiveSheet();
$title_class = underscore($class->class_name) . '.xls';
$row = 1;
// header
$sheet->setCellValue('A' . $row, 'STT');
$sheet->setCellValue('B' . $row, 'Họ tên');
$sheet->setCellValue('C' . $row, 'Điểm');
$sheet->setCellValue('D' . $row, 'Ghi chú');
$sheet->setCellValue('E' . $row, 'IP');
$listIndentities = array();
$ipList = array();
foreach ($students as $key => $student) {
++$row;
$sheet->setCellValue("A{$row}", $student->indentity_number);
$sheet->setCellValue("B{$row}", $student->fullname);
if (isset($studentsMark[$student->student_id])) {
$resultOfStudent = $studentsMark[$student->student_id];
if (!empty($resultOfStudent->ip_address) && in_array($resultOfStudent->ip_address, $ipList)) {
$sheet->setCellValue("D{$row}", "Trùng địa chỉ IP");
} else {
$sheet->setCellValue("D{$row}", "");
$ipList[] = $resultOfStudent->ip_address;
}
$sheet->setCellValue("E{$row}", $resultOfStudent->ip_address);
$sheet->setCellValue("C{$row}", (double) $resultOfStudent->score);
} else {
$sheet->setCellValue("D{$row}", "Chưa làm bài");
$sheet->setCellValue("C{$row}", "");
}
if (in_array($student->indentity_number, $listIndentities)) {
$sheet->setCellValue("D{$row}", "Trùng mã số học sinh");
}
$listIndentities[] = $student->indentity_number;
}
unset($sheet);
header('Content-type: application/vnd.ms-excel');
header("Content-Disposition: attachment; filename=\"{$title_class}\"");
$writer = new PHPExcel_Writer_Excel5($this->CI->phpexcel);
$writer->save('php://output');
}
/**
* Return Excel Workbook in buffer
*
* @return string Excel string buffer
*/
function GetBuffer()
{
// store tmpfile in TMPPATH need accessright
$objWriter = new \PHPExcel_Writer_Excel5($this);
$path = TMPPATH . DS . uniqid('xls_') . '.xls';
$objWriter->save($path);
$handle = fopen($path, "r");
$contents = fread($handle, filesize($path));
fclose($handle);
return $contents;
}
public function excelExport($project_id)
{
PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;
$objPHPExcel = new PHPExcel();
$objPHPExcel->createSheet(0);
$objPHPExcel->setActiveSheetIndex(0);
//设置第一个内置表
$objActSheet = $objPHPExcel->getActiveSheet();
// 获取当前活动的表
$objActSheet->setTitle('项目总体数据表');
//获取项目下的所有成员id
$examinee = $this->modelsManager->createBuilder()->columns(array('number', 'id', 'state', 'name'))->from('Examinee')->where('Examinee.type = 0 AND Examinee.project_id = ' . $project_id)->getQuery()->execute()->toArray();
//异常处理
if (empty($examinee)) {
throw new Exception('项目的被试人数为0,无法进行项目数据表生成');
}
$members_not_finished = array();
foreach ($examinee as $value) {
if ($value['state'] < 4) {
$members_not_finished[$value['number']] = $value['name'];
}
}
if (!empty($members_not_finished)) {
$list = '项目中部分成员未完成测评过程，如下:<br/>';
foreach ($members_not_finished as $key => $value) {
$list .= $key . '：' . $value . '<br/>';
}
throw new Exception(print_r($list, true));
}
$i = 0;
$result = new ProjectData();
$start_column = 'D';
$last = 'D';
$last_data = null;
foreach ($examinee as $examinee_info) {
$data = array();
$data = $result->getindividualComprehensive($examinee_info['id']);
if ($i === 0) {
$this->makeTable($data, $objActSheet);
}
$last = $start_column;
$last_data = $data;
$this->joinTable($data, $objActSheet, $start_column++, $examinee_info['number']);
$i++;
}
// 计算平均值
$this->joinAvg($objActSheet, $last_data, 'D', $last);
//根据项目第一人成绩统计打表
//循环写入每个人的成绩
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$file_name = './tmp/' . $project_id . '_project_data.xls';
$objWriter->save($file_name);
return $file_name;
}
function CreateExcel()
{
$write = new PHPExcel_Writer_Excel5($GLOBALS['excel']);
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header('Content-Disposition:attachment;filename="testdata.xls"');
header("Content-Transfer-Encoding:binary");
$write->save('php://output');
}
/** {@inheritdoc} */
public function appendRows($path, array $rows)
{
$phpExcel = \PHPExcel_IOFactory::load($path);
$phpExcel->setActiveSheetIndex(0);
$rowIndex = $phpExcel->getActiveSheet()->getHighestRow() + 1;
foreach ($rows as $row) {
foreach ($row as $columnIndex => $value) {
$phpExcel->getActiveSheet()->setCellValueByColumnAndRow($columnIndex, $rowIndex, $value);
}
$rowIndex++;
}
$objWriter = new \PHPExcel_Writer_Excel5($phpExcel);
$objWriter->save($path);
}
/**
* Outputs export footer
*
* @return bool Whether it suceeded
*
* @access public
*/
function PMA_exportFooter()
{
global $workbook;
global $tmp_filename;
$tmp_filename = tempnam(realpath($GLOBALS['cfg']['TempDir']), 'pma_xls_');
$workbookWriter = new PHPExcel_Writer_Excel5($workbook);
$workbookWriter->save($tmp_filename);
if (!PMA_exportOutputHandler(file_get_contents($tmp_filename))) {
return FALSE;
}
unlink($tmp_filename);
unset($GLOBALS['workbook']);
unset($GLOBALS['sheet_index']);
return TRUE;
}
/**
* Formats the specified response.
* @param \yii\web\Response $response the response to be formatted.
*/
public function format($response)
{
//$response->getHeaders()->set('Content-Type', 'application/vnd.ms-excel');
$response->setDownloadHeaders(basename(\Yii::$app->request->pathInfo) . '.xls', 'application/vnd.ms-excel');
if ($response->data === null) {
return;
}
\PHPExcel_Settings::setCacheStorageMethod(\PHPExcel_CachedObjectStorageFactory::cache_to_sqlite3);
$styles = $this->getStyles();
$objPHPExcel = new \PHPExcel();
$sheet = $objPHPExcel->getActiveSheet();
$offset = 1;
/*
* serialize filter
$sheet->setCellValue('A1', $opcje['nazwaAnaliza']);
$sheet->duplicateStyle($styles['default'], 'A1:C4');
$sheet->getRowDimension(1)->setRowHeight(18);
$sheet->getStyle('A1')->getFont()->setBold(true)->setSize(15);
$sheet->getStyle('C3:C4')->getFont()->setBold(true);
$offset = 6;
*/
$data = $response->data;
if (!isset($data['items'])) {
// single model
$this->addLine($sheet, $offset, array_keys($data));
$this->addLine($sheet, $offset + 1, array_values($data));
for ($i = 1, $lastColumn = 'A'; $i < count($data); $i++, $lastColumn++) {
}
$sheet->duplicateStyle($styles['header'], 'A' . $offset . ':' . $lastColumn . $offset);
} else {
// a collection of models
if (($firstRow = reset($data['items'])) !== false) {
$this->addLine($sheet, $offset, array_keys($firstRow));
}
$startOffset = ++$offset;
$item = [];
foreach ($data['items'] as $item) {
$this->addLine($sheet, $offset++, $item);
}
$this->addSummaryRow($sheet, $startOffset, $offset, $item);
}
$filename = tempnam(\Yii::getAlias('@runtime'), 'xls');
$objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save($filename);
$response->content = file_get_contents($filename);
unlink($filename);
}
public function getCSV()
{
$filename = $this->stripName($this->options['LM_DOWNLOAD']);
$pe = new \PHPExcel();
$array = array_merge([$this->headerArray], $this->dataArray);
$pe->setActiveSheetIndex(0)->fromArray($array);
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header("Content-Disposition: attachment;filename={$filename}.xls");
header("Content-Transfer-Encoding: binary ");
$objWriter = new \PHPExcel_Writer_Excel5($pe);
$objWriter->save('php://output');
}
/**
* @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;
}
/**
* 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 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');
}
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);
}
function submit()
{
$this->load->library('form_validation');
$periodleave = $this->m_setting->getSetting();
$datefrom = $periodleave[0]['leaveperiod_start'];
$dateto = $periodleave[0]['leaveperiod_end'];
$period = $this->input->post('period');
$exec = $this->input->post('exec');
$this->form_validation->set_rules('period', 'Year Period', 'required');
$this->form_validation->set_error_delimiters('<span class="help-block" style="color:red">', '</span>');
if ($this->form_validation->run() == FALSE) {
$this->index();
} else {
$data['title'] = $this->m_setting->getSetting();
if ($exec == "toexcel") {
$this->load->library('PHPExcel');
$detil = $this->m_leave->reportleave($datefrom, $dateto);
$data = array();
$data[] = array("# Ingress No.", "#Emp No", "Employee Name", "Dept", "Annual Entitled", "Annual Used", "Annual Remaining", "Casual", "Casual_Used", "Casual_Remaining", "Sick_Used");
$data[] = array("# Ingress No.", "#Emp No", "Employee Name", "Dept", "Annual Entitled", "Annual Used", "Annual Remaining", "Casual", "Casual_Used", "Casual_Remaining", "Sick_Used");
$i = 1;
foreach ($detil as $row) {
$data[] = array($row['userid'], $row['emp_no'], $row['EmployeeName'], $row['Dept'], $row['Annual_Entitled'], $row['Annual_Used'], $row['Annual_Remaining'], $row['Casual'], $row['Casual_Used'], $row['Casual_Remaining'], $row['Sick_Used']);
$i++;
}
//excel export
$sheet = $this->phpexcel->getActiveSheet();
$sheet->setCellValue("A1", "Leave Balance Period " . $period);
for ($i = 2; $i < count($data) + 1; $i++) {
$sheet->setCellValue("A" . $i, $data[$i - 1][0]);
$sheet->setCellValue("B" . $i, $data[$i - 1][1]);
$sheet->setCellValue("C" . $i, $data[$i - 1][2]);
$sheet->setCellValue("D" . $i, $data[$i - 1][3]);
$sheet->setCellValue("E" . $i, $data[$i - 1][4]);
$sheet->setCellValue("F" . $i, $data[$i - 1][5]);
$sheet->setCellValue("G" . $i, $data[$i - 1][6]);
$sheet->setCellValue("H" . $i, $data[$i - 1][7]);
$sheet->setCellValue("I" . $i, $data[$i - 1][8]);
$sheet->setCellValue("J" . $i, $data[$i - 1][9]);
$sheet->setCellValue("K" . $i, $data[$i - 1][10]);
}
$writer = new PHPExcel_Writer_Excel5($this->phpexcel);
$filename = "Leave Balance Period " . $period . ".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');
}
}
}
function mpsubmit()
{
$this->load->library('form_validation');
$leave_start = $this->input->post('leave_start');
$leave_end = $this->input->post('leave_end');
$exec = $this->input->post('exec');
$this->form_validation->set_rules('leave_start', 'Date From', 'required|callback_checkdates');
$this->form_validation->set_rules('leave_end', 'Date to', 'required');
$this->form_validation->set_error_delimiters('<span class="help-block" style="color:red">', '</span>');
if ($this->form_validation->run() == FALSE) {
$this->misspunch();
} else {
$date1 = date('Y-m-d', strtotime(str_replace('/', '-', $leave_start)));
$date2 = date('Y-m-d', strtotime(str_replace('/', '-', $leave_end)));
$this->session->set_userdata('date1', $leave_start);
$this->session->set_userdata('date2', $leave_end);
$data['title'] = $this->m_setting->getSetting();
if ($exec == "toexcel") {
$this->load->library('PHPExcel');
$detil = $this->m_outsource->getMissPunch($date1, $date2);
$data = array();
$data[] = array("# No.", "#ID NO", "Employee Name", "Dept", "Date", "IN", "OUT", "Schedule");
$data[] = array("# No.", "#ID NO", "Employee Name", "Dept", "Date", "IN", "OUT", "Schedule");
$i = 1;
foreach ($detil as $row) {
$data[] = array($i, $row['userid'], $row['EmployeeName'], $row['Department'], $row['date'], $row['att_in'], $row['att_out'], $row['schedulename']);
$i++;
}
//excel export
$sheet = $this->phpexcel->getActiveSheet();
$sheet->setCellValue("A1", "MissPunch From " . $leave_start . " To " . $leave_end);
for ($i = 2; $i < count($data) + 1; $i++) {
$sheet->setCellValue("A" . $i, $data[$i - 1][0]);
$sheet->setCellValue("B" . $i, $data[$i - 1][1]);
$sheet->setCellValue("C" . $i, $data[$i - 1][2]);
$sheet->setCellValue("D" . $i, $data[$i - 1][3]);
$sheet->setCellValue("E" . $i, $data[$i - 1][4]);
$sheet->setCellValue("F" . $i, $data[$i - 1][5]);
$sheet->setCellValue("G" . $i, $data[$i - 1][6]);
$sheet->setCellValue("H" . $i, $data[$i - 1][7]);
}
$writer = new PHPExcel_Writer_Excel5($this->phpexcel);
$filename = "MissPunch " . $leave_start . " - " . $leave_end . ".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 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 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');
}
}
public function array2excel($data, $output = true)
{
//数组转换为excel文件
$PHPExcel = new PHPExcel();
$WorkSheet = $PHPExcel->getActiveSheet();
set_time_limit(0);
@ini_set('memory_limit', '256M');
$row_index = 1;
foreach ($data as $row) {
//循环赋值
$col_index = 0;
foreach ($row as $col) {
$WorkSheet->setCellValueByColumnAndRow($col_index, $row_index, $col);
$col_index++;
}
$row_index++;
}
$Writer = new PHPExcel_Writer_Excel5($PHPExcel);
$filename = time();
if ($output) {
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-Disposition:inline;filename="' . $filename . '.xls"');
header("Content-Transfer-Encoding: binary");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Pragma: no-cache");
$Writer->save('php://output');
//直接输出
} else {
$Writer->save($filename . '.xls');
//生成为文件
}
$PHPExcel->disconnectWorksheets();
unset($PHPExcel);
}
/**
* 把数组数据输出到 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');
//输出到文件
}
