phpexcel按照自定义样式导出excel文件

笔记001

//文件引入
$header_arr = 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','AA', 'AB', 
'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP',
 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ','BA');
//初始化PHPExcel()
$objPHPExcel = new PHPExcel();
$ex_last = "I1";
$ex_ = "I";
/** 垂直居中 */
$objPHPExcel->getDefaultStyle()->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
/** 水平居中 */
$objPHPExcel->getDefaultStyle()->getAlignment()
->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
//宽度固定
$objPHPExcel->getActiveSheet()->getColumnDimension("A")->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension("B")->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension("C")->setWidth(14);
$objPHPExcel->getActiveSheet()->getColumnDimension("D")->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension("E")->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension("F")->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension("G")->setWidth(12);

$styleArray2 = [
    'borders' => [
//                'outline' => [  //外边框加粗
//                    "style" => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
//                ]
	  'allborders' => [  //所有边框
		  'style' => PHPExcel_Style_Border::BORDER_THIN, //细边框
		  'color'=> ['rgb'=>'FFFFFF']
	]
]
];
// $objPHPExcel->getActiveSheet()->getStyle("A1:I5")->applyFromArray($styleArray2); $dao_title = "测试一下";
 //合并A1到E1   赋值
 $objActSheet->mergeCells("A1:E1")->setCellValue('A1', $dao_title);  
//A1到E1 加粗  设置字体大小16
 $objActSheet->getStyle("A1:E1")->getFont()->setBold(true)->setSize(16);
//内容居右显示
 $objActSheet->getStyle("A1")->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
//内容居左
$objActSheet->getStyle("A1")->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
//自适应A4纸
$objActSheet->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

如果要直接放入完整的URL直接setCellValue('A1','URL'); 
即可，如果想插入像HTML中的 a 标签一样，如需执行两步。
$objActSheet->setCellValue('CSDN');
$objActSheet->getCell('A1')->getHyperlink()->setUrl("http://www.");

笔记002

error_reporting(E_ALL); require_once '../Classes/PHPExcel.php';  $objPHPExcel = new PHPExcel(); // 设置文件属性$objPHPExcel->getProperties()->setCreator("C1G")                             ->setLastModifiedBy("C1G")                             ->setTitle("phpexcel Test Document")                             ->setSubject("phpexcel Test Document")                             ->setDescription("Test document for phpexcel, 
generated using PHP classes.")                             ->setKeywords("office 2007 openxml php c1gstudio")                             ->setCategory("Test"); //设置当前活动的sheet$objPHPExcel->setActiveSheetIndex(0); //设置sheet名字$objPHPExcel->getActiveSheet()->setTitle('phpexcel demo'); //设置默认行高$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15); //由PHPExcel根据传入内容自动判断单元格内容类型$objPHPExcel->getActiveSheet()->setCellValue('A1', "Firstname");$objPHPExcel->getActiveSheet()->setCellValue('B1', "Lastname");$objPHPExcel->getActiveSheet()->setCellValue('C1', "Phone");$objPHPExcel->getActiveSheet()->setCellValue('D1', "Fax");$objPHPExcel->getActiveSheet()->setCellValue('E1', "Address");$objPHPExcel->getActiveSheet()->setCellValue('F1', "ZIP");$objPHPExcel->getActiveSheet()->setCellValue('G1', "DATE");  $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 8, 'firstname');$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 8, 'lastname');  // utf8格式字符$objPHPExcel->setActiveSheetIndex(0)            ->setCellValue('A15', 'Miscellaneous glyphs')            ->setCellValue('A16', 'éàèùâêîôûëïüÿäöüç')            ->setCellValue('A17', 'phpexcel演示'); $objPHPExcel->getActiveSheet()->setCellValue('A9', "502");$objPHPExcel->getActiveSheet()->setCellValue('B9', "99");$objPHPExcel->getActiveSheet()->setCellValue('C9', "=SUM(A9:B9)"); //设置列宽$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth('20');  // 设置行高$objPHPExcel->getActiveSheet()->getRowDimension('9')->setRowHeight(20); // 加粗$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true); // 中文$objPHPExcel->getActiveSheet()->setCellValue('A2', "小风");$objPHPExcel->getActiveSheet()->setCellValue('B2', "wang"); // 设置单元格格式$objPHPExcel->getActiveSheet()->getCell('C2')
->setValueExplicit('861391327543258', PHPExcel_Cell_DataType::TYPE_NUMERIC); // 日期$objPHPExcel->getActiveSheet()->setCellValue('G2', '2008-12-31');$objPHPExcel->getActiveSheet()->getStyle('G2')->getNumberFormat()
->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH); // 时间戳$time = gmmktime(0,0,0,12,31,2008); // int(1230681600)$objPHPExcel->getActiveSheet()->setCellValue('G3', PHPExcel_Shared_Date::PHPToExcel($time));$objPHPExcel->getActiveSheet()->getStyle('G3')->getNumberFormat()
->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH); // url$objPHPExcel->getActiveSheet()->setCellValue('G11', 'blog.c1gstudio.com');$objPHPExcel->getActiveSheet()->getCell('G11')->getHyperlink()
->setUrl('http://blog.c1gstudio.com'); // 另一个sheet$objPHPExcel->getActiveSheet()->setCellValue('G12', 'sheetb');$objPHPExcel->getActiveSheet()->getCell('G12')->getHyperlink()
->setUrl("sheet://'sheetb'!A1"); // 水平居上$objPHPExcel->getActiveSheet()->getStyle('A9:B9')->getAlignment()
->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP); // 单元格换行$objPHPExcel->getActiveSheet()->getStyle('G2:G3')->getAlignment()->setWrapText(true); // 合并$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');  // 隐藏D列$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setVisible(false); //$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setOutlineLevel(1);$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setVisible(false);$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setOutlineLevel(1);$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setVisible(false);$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setCollapsed(true); // 固定第一行$objPHPExcel->getActiveSheet()->freezePane('A2'); // 保护工作表$objPHPExcel->getActiveSheet()->getProtection()->setPassword('PHPExcel');$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);$objPHPExcel->getActiveSheet()->getProtection()->setSort(true);$objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);$objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true); //设置边框$sharedStyle1 = new PHPExcel_Style();$sharedStyle1->applyFromArray(    array(
'borders' => array(      'left'=> array('style' => PHPExcel_Style_Border::BORDER_MEDIUM))         ));$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "B1:B10"); // 创建一个新的工作表$objWorksheet1 = $objPHPExcel->createSheet();$objWorksheet1->setTitle('sheetb');  $objPHPExcel->setActiveSheetIndex(1);  // 创建一个图片$gdImage = @imagecreatetruecolor(200, 20) or die('Cannot Initialize new GD image stream');$textColor = imagecolorallocate($gdImage, 255, 255, 255);imagestring($gdImage, 1, 5, 5,  'Created with PHPExcel (c1gstudio.com)', $textColor); // 把创建的图片添加到工作表$objDrawing = new PHPExcel_Worksheet_MemoryDrawing();$objDrawing->setName('Sample image');$objDrawing->setDescription('Sample image');$objDrawing->setImageResource($gdImage);$objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);$objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);$objDrawing->setHeight(36);$objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); $objPHPExcel->setActiveSheetIndex(0); // 保存$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');$objWriter->save('testexcel'.time().'.xls');