ThinkPHP中调用PHPExcel的实现代码
核心代码:
//引入PHPExcel vendor('PHPExcel.PHPExcel'); //CreatenewPHPExcelobject $objPHPExcel=newPHPExcel(); //设置文档属性 $objPHPExcel->getProperties() ->setCreator("web100.cc") ->setLastModifiedBy("web100.cc") ->setTitle("Office2007XLSXTestDocument") ->setSubject("Office2007XLSXTestDocument") ->setDescription("TestdocumentgeneratedusingPHPclasses.") ->setKeywords("office2007openxmlphp") ->setCategory("Testresultfile"); //设置当前活动的sheet $objPHPExcel->setActiveSheetIndex(0); //选定当前Sheet $objActSheet=$objPHPExcel->11getActiveSheet(); //设置sheet名字 $objActSheet->setTitle('phpexceldemo'); //设置默认行高 $objActSheet->getDefaultRowDimension()->setRowHeight(15); //由PHPExcel根据传入内容自动判断单元格内容类型 $objActSheet->setCellValue('A1',"Firstname"); $objActSheet->setCellValue('B1',"Lastname"); $objActSheet->setCellValue('C1',"Phone"); $objActSheet->setCellValue('D1',"Fax"); $objActSheet->setCellValue('E1',"Address"); $objActSheet->setCellValue('F1',"ZIP"); $objActSheet->setCellValue('G1',"DATE"); $objActSheet->setCellValueByColumnAndRow(0,8,'firstname'); $objActSheet->setCellValueByColumnAndRow(1,8,'lastname'); //单元格填充值 $objActSheet->setCellValue('A2',"小风"); $objActSheet->setCellValue('B2',"wang"); //设置列宽 $objActSheet->getColumnDimension('C')->setWidth('20'); //设置行高 $objActSheet->getRowDimension('9')->setRowHeight(20);//第9行 //设置字体 $objActSheet->getStyle('A1')->getFont()->setName('宋体'); //字号 $objActSheet->getStyle('A1')->getFont()->setSize(12); //加粗 $objActSheet->getStyle('A1')->getFont()->setBold(true); //说明:$objActSheet->getStyle('A1:G10'),可以通过这种方式来选择一片单元格! //设置单元格格式 $objActSheet->getCell('C2')->setValueExplicit('861391327543258',PHPExcel_Cell_DataType::TYPE_NUMERIC); //日期 $objActSheet->setCellValue('G2','2008-12-31'); $objActSheet->getStyle('G2')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH); //时间戳 $time=gmmktime(0,0,0,12,31,2008);//int(1230681600) $objActSheet->setCellValue('G3',PHPExcel_Shared_Date::PHPToExcel($time)); $objActSheet->getStyle('G3')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH); //url $objActSheet->setCellValue('G11','www.spalybow.com'); $objActSheet->getCell('G11')->getHyperlink()->setUrl('http://www.spalybow.com'); //另一个sheet $objActSheet->setCellValue('G12','sheetb'); $objActSheet->getCell('G12')->getHyperlink()->setUrl("sheet://'sheetb'!A1"); //水平居上 $objActSheet->getStyle('A9:B9')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP); //单元格换行 $objActSheet->getStyle('G2:G3')->getAlignment()->setWrapText(true); //合并 $objActSheet->mergeCells('A18:E22'); //隐藏D列 $objActSheet->getColumnDimension('D')->setVisible(false); // $objActSheet->getColumnDimension('E')->setOutlineLevel(1); $objActSheet->getColumnDimension('E')->setVisible(false); $objActSheet->getColumnDimension('F')->setOutlineLevel(1); $objActSheet->getColumnDimension('F')->setVisible(false); $objActSheet->getColumnDimension('F')->setCollapsed(true); //固定第一行 $objActSheet->freezePane('A2'); //保护工作表 $objActSheet->getProtection()->setPassword('PHPExcel'); $objActSheet->getProtection()->setSheet(true); $objActSheet->getProtection()->setSort(true); $objActSheet->getProtection()->setInsertRows(true); $objActSheet->getProtection()->setFormatCells(true); //设置边框 $sharedStyle1=newPHPExcel_Style(); $sharedStyle1->applyFromArray( array('borders'=>array( 'left'=>array('style'=>PHPExcel_Style_Border::BORDER_MEDIUM) ) )); $objActSheet->setSharedStyle($sharedStyle1,"B1:B10"); //创建一个新的工作表 $objWorksheet1=$objPHPExcel->createSheet(); $objWorksheet1->setTitle('sheetb'); $objPHPExcel->setActiveSheetIndex(1); //创建一个图片 $gdImage=@imagecreatetruecolor(200,20)ordie('CannotInitializenewGDimagestream'); $textColor=imagecolorallocate($gdImage,255,255,255); imagestring($gdImage,1,5,5,'CreatedwithPHPExcel(c1gstudio.com)',$textColor); //把创建的图片添加到工作表 $objDrawing=newPHPExcel_Worksheet_MemoryDrawing(); $objDrawing->setName('Sampleimage'); $objDrawing->setDescription('Sampleimage'); $objDrawing->setImageResource($gdImage); $objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG); $objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT); $objDrawing->setHeight(36); $objDrawing->setWorksheet($objActSheet); $objPHPExcel->setActiveSheetIndex(0); //保存 $objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5'); $objWriter->save('testexcel'.time().'.xls');