標籤:調整 filename clu 工作 current rip max-age format tco
匯入匯出excel使用PHPexcel類很簡單快捷,記錄下該類常用的操作,以備不時之需。
<?php require_once ROOT_PATH . ‘includes/phpexcel/Classes/PHPExcel.php‘;//引入phpexcel類庫 $objPHPExcel = new PHPExcel();//執行個體化phpexcel // Set properties 設定匯出的文檔資訊 $objPHPExcel->getProperties()->setCreator("dabai") //設定建立人 ->setLastModifiedBy("dabai") //最後修改人 ->setTitle("Office 2007 XLSX Document") //標題 ->setSubject("Office 2007 XLSX Document") //題目 ->setDescription("Document for Office 2007 XLSX, generated using PHP classes.") //描述 ->setKeywords("office 2007 openxml php") //關鍵字 ->setCategory("tongji"); //種類 //設定當前的sheet,即第幾頁excel表格 $objPHPExcel->setActiveSheetIndex(0); $sheet = $objPHPExcel->getActiveSheet();// 可講當頁賦值給 $sheet 變數 方便後面調用 $sheet->setTitle(‘Simple‘); //設定該分頁的分頁名字 //設定excel預設的置中方式 $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//水平置中 $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); //垂直方向上中間置中 //設定預設的儲存格寬度和行高 $sheet->getDefaultRowDimension()->setRowHeight(30); $sheet->getDefaultColumnDimension()->setWidth(10); //設定相應儲存格的寬度 $sheet->getColumnDimension(‘A‘)->setWidth(10); $sheet->getColumnDimension(‘B‘)->setAutoSize(true); //設定相應儲存格的高度 $sheet->getRowDimension(‘A‘)->setRowHeight(10); //設定某一列對齊 $sheet->getStyle(‘E‘)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);//靠左對齊; //填入資料 $sheet->setCellValue(‘A1‘, ‘序號‘)->setCellValue(‘B1‘, ‘名稱‘);//可連貫操作,也可以一條一條添加
$sheet->setCellValueExplicit(‘G‘ . $start, $val[‘bankcard‘],PHPExcel_Cell_DataType::TYPE_STRING); //科學計數法調整為正常數字顯示 //凍結儲存格 $sheet->freezePane(‘A2‘);//凍結A2的左列和上列。 //合并分離儲存格 $sheet->mergeCells(‘A1:B4‘); //在開始的儲存格添加值. /*迴圈的資料中有合并,有分行時。技巧:迴圈時記下開始點和結束點。例如: if ($key == 0) { $start = $key + 2; $end = $start + $goods_len - 1; } else { $start = $end + 1; $end = $start + $goods_len - 1; } */ //分離儲存格 $sheet->->unmergeCells(‘A18:E22‘); // 設定儲存格格式 $sheet->getCell(‘C2‘)->setValueExplicit(‘861391327543258‘, PHPExcel_Cell_DataType::TYPE_NUMERIC); //可以設定儲存格的格式 //Add comment 添加註釋 $sheet->getComment(‘E11‘)->setAuthor(‘PHPExcel‘); $objCommentRichText = $sheet->getComment(‘E11‘)->getText()->createTextRun(‘PHPExcel:‘); $objCommentRichText->getFont()->setBold(true); $sheet->getComment(‘E11‘)->getText()->createTextRun("\r\n"); $sheet->getComment(‘E11‘)->getText()->createTextRun(‘Total amount on the current invoice, excluding VAT.‘); //設定字型、加粗 $sheet->getStyle(‘A1‘)->getFont()->setName(‘Candara‘); //字型 $sheet->getStyle(‘A‘)->getFont()->setBold(true); //加粗 //設定字型顏色 $sheet->getStyle(‘B1‘)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE); //設定邊框 $sheet->getStyle(‘A1‘)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $sheet->getStyle(‘A1‘)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $sheet->getStyle(‘A1‘)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $sheet->getStyle(‘A1‘)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); //設定邊框顏色 $sheet->getStyle(‘A1‘)->getBorders()->getLeft()->getColor()->setARGB(‘FF993300‘); $sheet->getStyle(‘A1‘)->getBorders()->getTop()->getColor()->setARGB(‘FF993300‘); //或者用數組參數 $styleArray = array( ‘borders‘ => array( ‘allborders‘ => array( //‘style‘ => PHPExcel_Style_Border::BORDER_THICK,//邊框是粗的 ‘style‘ => PHPExcel_Style_Border::BORDER_THIN,//細邊框 //‘color‘ => array(‘argb‘ => ‘FFFF0000‘), ), ) ); $sheet->getStyle(‘A1:N‘ . $n)->applyFromArray($styleArray);//從A1到N$n的邊框 //設定填充顏色 $sheet->getStyle(‘A1‘)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $sheet->getStyle(‘A1‘)->getFill()->getStartColor()->setARGB(‘FF808080‘); //添加連結 $sheet->setCellValue(‘A1‘, ‘www.aaa.net‘); $sheet->getCell(‘A1‘)->getHyperlink()->setUrl(‘http://www.aaa.com‘); $sheet->getCell(‘A1‘)->getHyperlink()->setTooltip(‘Navigate to website‘); $sheet->getStyle(‘A1‘)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
//Set document security 設定文檔安全
$objPHPExcel->getSecurity()->setLockWindows(true);
$objPHPExcel->getSecurity()->setLockStructure(true);
$objPHPExcel->getSecurity()->setWorkbookPassword("PHPExcel");
//Set sheet security 設定工作表安全
$sheet->getProtection()->setPassword(‘PHPExcel‘);
$sheet->getProtection()->setSheet(true);//
$sheet->getProtection()->setSort(true);
$sheet->getProtection()->setInsertRows(true);
$sheet->getProtection()->setFormatCells(true);
//建立新的工作標籤
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(1);
// 輸出 header(‘Content-Type: application/vnd.ms-excel‘); header(‘Content-Disposition: attachment;filename="‘ . $filename . ‘.xls"‘); header(‘Cache-Control: max-age=0‘); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel5‘); $objWriter->save(‘php://output‘);
使用PHPExcel匯出excel備忘錄