function xlsToXslx($file_name_input, $file_name_output)
{
try {
$objPHPexcel = PHPExcel_IOFactory::load($file_name_input);
$objWriter = new PHPExcel_Writer_Excel2007($objPHPexcel);
$objWriter->setOffice2003Compatibility(true);
$objWriter->save($file_name_output);
return true;
} catch (Exception $e) {
return false;
}
}
public function generate($format = "Excel5", $docName = "Tabelle")
{
switch ($format) {
case 'Excel2007':
include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/Excel2007.php';
$writer = new PHPExcel_Writer_Excel2007($this);
$ext = 'xlsx';
$header = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
//supprime le pre-calcul
$writer->setPreCalculateFormulas(false);
break;
case 'Excel2003':
include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/Excel2007.php';
$writer = new PHPExcel_Writer_Excel2007($this);
$writer->setOffice2003Compatibility(true);
$ext = 'xlsx';
//supprime le pre-calcul
$writer->setPreCalculateFormulas(false);
break;
case 'Excel5':
include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/Excel5.php';
$writer = new PHPExcel_Writer_Excel5($this);
$ext = 'xls';
break;
case 'CSV':
include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/CSV.php';
$writer = new PHPExcel_Writer_CSV($this);
$writer->setDelimiter(",");
//l'op�rateur de s�paration est la virgule
$writer->setSheetIndex(0);
//Une seule feuille possible
$ext = 'csv';
break;
case 'PDF':
include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/PDF.php';
$writer = new PHPExcel_Writer_PDF($this);
$writer->setSheetIndex(0);
//Une seule feuille possible
$ext = 'pdf';
break;
case 'HTML':
include dirname(__FILE__) . '/../3rdParty/Classes/PHPExcel/Writer/HTML.php';
$writer = new PHPExcel_Writer_HTML($this);
$writer->setSheetIndex(0);
//Une seule feuille possible
$ext = 'html';
break;
}
header('Content-type:' . $header);
header('Content-Disposition:inline;filename=' . $docName . '.' . $ext);
$writer->save('php://output');
}
// {
// // Ligne accès
// $sheet->setCellValue('A'.$row_cnt,'No Images registered yet'); // Name
// $sheet->mergeCells('A'.$row_cnt.':'.numToLetter($theads_cnt).$row_cnt);
// $sheet->duplicateStyleArray($style_access_line,
// 'A'.$row_cnt.':'.numToLetter($theads_cnt).$row_cnt
// );
// }
// else
// {
// // Ligne accès
// $sheet->setCellValue('A'.$row_cnt,basename($docAccess->basename,'.jpg')); // Name
// $sheet->setCellValue('B'.$row_cnt,str_replace(getFtpImagesRoot(),'',$docAccess->srcname)); // Document Name
// $sheet->setCellValue('C'.$row_cnt,$docAccess->getREWURL()); // Link
// $sheet->setCellValue('D'.$row_cnt,$docAccess->getFileSize()); // Size
// $sheet->duplicateStyleArray($style_access_line,
// 'A'.$row_cnt.':'.numToLetter($theads_cnt).$row_cnt
// );
// }
// $sheet_indexes[$sheet_cnt-1][] = $row_cnt;
// $i++; $row_cnt++;
// }
// $stmt = NULL;
// unset($stmt);
$workbook->setActiveSheetIndex(0);
$writer = new PHPExcel_Writer_Excel2007($workbook);
$writer->setOffice2003Compatibility(true);
header('Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition:inline;filename=falcon_export_' . date('mdY') . '.xlsx');
// header('Content-type: application/vnd.ms-excel');
$writer->save('php://output');
function download_csv($list)
{
require_once './application/libraries/PHPExcel/IOFactory.php';
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array(' memoryCacheSize ' => '15120MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
ini_set('memory_limit', '1256M');
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
// baris pertama bold
$objPHPExcel->getActiveSheet()->getStyle("A1:L1")->getFont()->setBold(true);
// set width column
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(17);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(46);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(24);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(24);
$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(45);
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(20);
// alignment default left
$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
// alignment cell
$objPHPExcel->getActiveSheet()->getStyle('A2:P5')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A2:P5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//style
$boldArray = array('font' => array('bold' => true));
$objPHPExcel->getActiveSheet()->getStyle('A1:P5')->applyFromArray($boldArray);
// merge cell
$objPHPExcel->getActiveSheet()->mergeCells('A1:P1');
$objPHPExcel->getActiveSheet()->mergeCells('A2:P2');
$objPHPExcel->getActiveSheet()->mergeCells('A3:P3');
$objPHPExcel->getActiveSheet()->mergeCells('A4:P4');
// headernya
$objPHPExcel->getActiveSheet()->SetCellValue('A2', strtoupper("REPORT"));
$objPHPExcel->getActiveSheet()->SetCellValue('A3', strtoupper("FRISIAN FLAG"));
$objPHPExcel->getActiveSheet()->SetCellValue('A5', "No");
$objPHPExcel->getActiveSheet()->SetCellValue('B5', 'Trxid');
$objPHPExcel->getActiveSheet()->SetCellValue('C5', 'Member id');
$objPHPExcel->getActiveSheet()->SetCellValue('D5', 'Phone number');
$objPHPExcel->getActiveSheet()->SetCellValue('E5', 'Reward code');
$objPHPExcel->getActiveSheet()->SetCellValue('F5', 'Prod code');
$objPHPExcel->getActiveSheet()->SetCellValue('G5', 'SKU');
$objPHPExcel->getActiveSheet()->SetCellValue('H5', 'SMS Incoming Content');
$objPHPExcel->getActiveSheet()->SetCellValue('I5', 'SMS Incoming Date');
$objPHPExcel->getActiveSheet()->SetCellValue('J5', 'Send to link date');
$objPHPExcel->getActiveSheet()->SetCellValue('K5', 'Response status form link');
$objPHPExcel->getActiveSheet()->SetCellValue('L5', 'SMS outgoing content');
$objPHPExcel->getActiveSheet()->SetCellValue('M5', 'SMS outgoing date');
$objPHPExcel->getActiveSheet()->SetCellValue('N5', 'Send By');
$objPHPExcel->getActiveSheet()->SetCellValue('O5', 'Outgouing delivery status');
$objPHPExcel->getActiveSheet()->SetCellValue('P5', 'System Message');
$rowCount_p = 6;
$no_p = 1;
// echo "<pre>";print_r($list); echo "</pre>";die();
if ($list != null) {
$no_p = 1;
foreach ($list as $ls) {
$objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount_p, $no_p++);
$objPHPExcel->getActiveSheet()->SetCellValueExplicit('B' . $rowCount_p, $ls->trxid_links);
$objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount_p, $ls->memberid);
$objPHPExcel->getActiveSheet()->SetCellValueExplicit('D' . $rowCount_p, $ls->msisdn);
$objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount_p, $ls->rewardcode);
$objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount_p, $ls->prodcode);
$objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount_p, $ls->sku);
$objPHPExcel->getActiveSheet()->SetCellValue('H' . $rowCount_p, $ls->sms_from_mum);
$objPHPExcel->getActiveSheet()->SetCellValueExplicit('I' . $rowCount_p, $ls->date_sms);
$objPHPExcel->getActiveSheet()->SetCellValueExplicit('J' . $rowCount_p, $ls->date_sendlinks);
$objPHPExcel->getActiveSheet()->SetCellValueExplicit('K' . $rowCount_p, $ls->code_links);
$objPHPExcel->getActiveSheet()->SetCellValueExplicit('L' . $rowCount_p, $ls->sobject_sms_content);
$objPHPExcel->getActiveSheet()->SetCellValueExplicit('M' . $rowCount_p, $ls->date_receive_smscomm);
$objPHPExcel->getActiveSheet()->SetCellValueExplicit('N' . $rowCount_p, $ls->sendby);
$objPHPExcel->getActiveSheet()->SetCellValueExplicit('O' . $rowCount_p, $ls->delivery_status_sms);
$objPHPExcel->getActiveSheet()->SetCellValueExplicit('P' . $rowCount_p, $ls->sobject_message);
// $objPHPExcel->getActiveSheet()->SetCellValueExplicit('P'.$rowCount_p, );
$rowCount_p++;
}
}
$date_time = date("Ymd His");
$filename = str_replace(' ', '_', strtoupper("Report_" . $date_time));
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 . ".csv");
header("Content-Transfer-Encoding: binary ");
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->setOffice2003Compatibility(true);
$objWriter->save('php://output');
}
public static function export2Excel($objPHPExcel, $versionExcel = 'Excel2007', $fileName = '')
{
switch ($versionExcel) {
case 'Excel2007':
// redirect output to client browser with Excel2007
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->setOffice2003Compatibility(true);
$objWriter->setPreCalculateFormulas(false);
$objWriter->save('php://output');
break;
case 'Excel5':
// redirect output to client browser with Excel 2005
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($objPHPExcel);
$objWriter->setPreCalculateFormulas(false);
$objWriter->save('php://output');
break;
default:
break;
}
}
private function exportExcelCustomerList($statistics)
{
$statuses = $this->getDoctrine()->getManager()->getRepository('CoreBundle:ProductStatus')->findAll();
$excel = new \PHPExcel();
$worksheet = $excel->getActiveSheet();
$worksheet->setCellValueByColumnAndRow(0, 1, 'Customer');
$column = 1;
foreach ($statuses as $status) {
$worksheet->setCellValueByColumnAndRow($column++, 1, $status->getName());
}
$row = 2;
foreach ($statistics as $item) {
$worksheet->setCellValueByColumnAndRow(0, $row, $item['customer']->getName());
$column = 1;
foreach ($statuses as $status) {
$worksheet->setCellValueByColumnAndRow($column++, $row, empty($item['changes'][$status->getId()]) ? '0' : $item['changes'][$status->getId()]);
}
$row++;
}
header('Content-type: application/ms-excel');
header('Content-Disposition: attachment; filename=report.xlsx');
$objWriter = new \PHPExcel_Writer_Excel2007($excel);
$objWriter->setPreCalculateFormulas(false);
$objWriter->setOffice2003Compatibility(true);
$objWriter->save('php://output');
die;
}
/**
* undocumented function
*
* @return void
* @author apple
**/
function export()
{
$this->autoRender = FALSE;
$node = isset($this->request->query['node']) ? $this->request->query['node'] : FALSE;
if (!$node) {
return FALSE;
}
// node
$a_node = explode('/', $node);
$q_node = end(explode('/', $node));
$prefix = substr($q_node, 0, strripos($q_node, '_'));
$value = substr($q_node, strripos($q_node, '_') + 1, 100);
$roles = TRUE;
$params = array();
if ($prefix == 'grid_classm') {
// 班级管理里面的班级
$params['college'] = substr($a_node[0], strripos($a_node[0], '_') + 1);
$params['grade'] = substr($a_node[1], strripos($a_node[1], '_') + 1);
$params['subject'] = substr($a_node[2], strripos($a_node[2], '_') + 1);
$classResut = ClassRegistry::init('TClass')->find('first', array('conditions' => array('TClass.Id' => $value)));
$params['class'] = $classResut['TClass']['FName'];
$data = $this->TPerson->getClassPersonResult($params['college'], $params['grade'], $params['subject'], $params['class'], FALSE);
$roles = ClassRegistry::init('TChapterAdmin')->getRolesCase($this->uid, 'TClass', $value, 'export');
} else {
if ($prefix == 'grid_class') {
// 学院管理里面的班级
$classResut = ClassRegistry::init('TClass')->find('first', array('conditions' => array('TClass.Id' => $value)));
$params['college'] = $classResut['TCollege']['FName'];
$params['grade'] = date('Y', strtotime($classResut['TClass']['FStartdate']));
$params['subject'] = $classResut['TSpecialty']['FName'];
$params['class'] = $classResut['TClass']['FName'];
$data = $this->TPerson->getClassPersonResult($params['college'], $params['grade'], $params['subject'], $params['class'], FALSE);
$roles = ClassRegistry::init('TChapterAdmin')->getRolesCase($this->uid, 'TClass', $value, 'export');
} else {
if ($prefix == 'grid_college') {
// 学院
$classResut = ClassRegistry::init('TCollege')->find('first', array('conditions' => array('TCollege.Id' => $value)));
$params['college'] = $classResut['TCollege']['FName'];
$data = $this->TPerson->getCollegePersonResult($params['college'], FALSE);
$roles = ClassRegistry::init('TChapterAdmin')->getRolesCase($this->uid, 'TCollege', $value, 'export');
} else {
if ($prefix == 'grid_grade') {
// 年级
$college = substr($a_node[0], strripos($a_node[0], '_') + 1);
$classResut = ClassRegistry::init('TCollege')->find('first', array('conditions' => array('TCollege.Id' => $college)));
$params['college'] = $classResut['TCollege']['FName'];
$params['grade'] = $value;
$data = $this->TPerson->getGradePersonResult($params['college'], $params['grade'], FALSE);
$roles = ClassRegistry::init('TChapterAdmin')->getRolesCase($this->uid, 'TCollege', $college, 'export');
} else {
// 专业
$college = substr($a_node[0], strripos($a_node[0], '_') + 1);
$grade = substr($a_node[1], strripos($a_node[1], '_') + 1);
$specialty = substr($a_node[2], strripos($a_node[2], '_') + 1);
$classResut = ClassRegistry::init('TClass')->find('first', array('conditions' => array('TCollege.Id' => $college, "date_format(TClass.FStartDate, '%Y')" => $grade, "TClass.FSpecialty" => $specialty)));
$params['college'] = $classResut['TCollege']['FName'];
$params['grade'] = $grade;
$params['subject'] = $classResut['TSpecialty']['FName'];
$data = $this->TPerson->getSubjectPersonResult($params['college'], $params['grade'], $params['subject'], FALSE);
$roles = ClassRegistry::init('TChapterAdmin')->getRolesCase($this->uid, 'TCollege', $college, 'export');
}
}
}
}
// roles
if (!$roles) {
exit('Access Denied！<br /><br />对不起，您没有此操作权限。');
}
// 导出EXCEL
App::import('Vendor', 'PHPExcel/PHPExcel');
if (class_exists('PHPExcel')) {
if (!is_array($data)) {
return FALSE;
}
foreach ($data as $key => $vals) {
$objPHPExcel = new PHPExcel();
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->setOffice2003Compatibility(true);
//设置文档基本属性
$objProps = $objPHPExcel->getProperties();
$objProps->setCreator("NianCode");
$objProps->setLastModifiedBy("NianCode");
$objProps->setTitle("Office XLS Persons Document");
$objProps->setSubject("Office XLS Persons Document");
$objProps->setDescription("Office XLS Persons Document");
$objProps->setKeywords("Office XLS Persons Document");
$objProps->setCategory("Persons");
$objPHPExcel->setActiveSheetIndex(0);
$objActSheet = $objPHPExcel->getActiveSheet();
//设置当前活动sheet的名称
$objActSheet->setTitle('人员数据');
// debug
// echo '<pre>';print_r($data);exit;
//由PHPExcel根据传入内容自动判断单元格内容类型
$jj = 1;
$excel = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
$fieldIsExport = array('TPerson' => array('FMemberId' => array('cname' => "会员号"), 'FullName' => array('cname' => "姓名"), 'FSex' => array('cname' => "性别"), 'FEMail' => array('cname' => "常用邮箱"), 'FMobileNumber' => array('cname' => "手机", 'type' => 'string'), 'FIDNumber' => array('cname' => "证件号", 'type' => 'string'), 'FCompanyName' => array('cname' => "工作单位"), 'FCompanyPosition' => array('cname' => "现任职务"), 'FAddress' => array('cname' => "通讯地址"), 'CreateDate' => array('cname' => "创建日期")), 'T002' => array('Extra_college' => array('cname' => "学院"), 'Extra_grade' => array('cname' => "年级"), 'Extra_subject' => array('cname' => "专业"), 'Extra_class' => array('cname' => "所在班级")));
if (!is_array($data)) {
return FALSE;
}
foreach ($data as $key => $vals) {
// 表头
if ($key == 0) {
$ii = 1;
foreach ($fieldIsExport as $fe_key => $fe_vals) {
$prefix = $fe_key;
foreach ($fe_vals as $fe_k => $fe_v) {
$ve_value = $fe_v['cname'];
// 字段的值
$els_key = isset($excel[$ii - 1]) ? $excel[$ii - 1] : FALSE;
if ($els_key) {
// echo $els_key.'||'.$ve_value.'<br />';
$objActSheet->setCellValue($els_key . '1', $ve_value);
// 字符串内容
$objActSheet->getColumnDimension($els_key)->setAutoSize(true);
$objActSheet->getStyle($els_key)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
$objActSheet->getStyle($els_key)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objActSheet->getStyle($els_key)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
}
$ii++;
}
}
}
$ii = 1;
$isPlus = FALSE;
// 内容
foreach ($vals as $k => $v) {
if (in_array($k, array_keys($fieldIsExport))) {
$prefix = $k;
if ($isPlus) {
$ii = 1;
}
foreach ($v as $kk => $vv) {
$els2_arykes = array_keys($fieldIsExport[$prefix]);
$count = count($fieldIsExport['TPerson']);
if (in_array($kk, $els2_arykes)) {
$els2_key = isset($excel[$ii - 1]) ? $excel[$ii - 1] : FALSE;
$els2_key2 = $jj + 1;
$els2_all = $els2_key . $els2_key2;
$els2_kks = $k == 'T002' ? $ii - 1 - $count : $ii - 1;
$els2_vvkk = isset($els2_arykes[$els2_kks]) ? $els2_arykes[$els2_kks] : FALSE;
$els2_vv = isset($v[$els2_vvkk]) ? $v[$els2_vvkk] : '';
$els2_type = isset($fieldIsExport[$prefix][$kk]['type']) ? $fieldIsExport[$prefix][$kk]['type'] : FALSE;
// echo $els2_type.$els2_all.'||'.$els2_vv.'<br />';
if ($els2_key) {
if ($els2_type == 'string') {
$objActSheet->setCellValueExplicit($els2_all, $els2_vv, PHPExcel_Cell_DataType::TYPE_STRING);
// 字符串内容
} else {
$objActSheet->setCellValue($els2_all, $els2_vv);
// 字符串内容
}
}
$ii++;
}
}
}
if ($k == 'T002') {
$isPlus = TRUE;
}
}
$jj++;
}
// export excel
$filename = "person.xlsx";
$objWriter->save($filename);
// echo file_get_contents($filename);
$this->redirect("/{$filename}");
}
}
}
