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}"); } } }