phpexcel 的摘录

最新推荐文章于 2024-06-29 09:51:21 发布
最新推荐文章于 2024-06-29 09:51:21 发布
阅读量627 收藏
点赞数
文章标签： phpexcel 
存档phpexcel的操作手册


public function export(){
        //此处全是一维数组
        $resumeState = C('RESUME_STATE');
        $processingStatus = C('PROCESSING_STATUS');
        $hunyin = C('HUNYIN');
        $sex = C('SEX');
        $AQE = C('AQE');
        
        //面试企业列表
        $C = M('Company');
        $company = $C->where('status=1')->field('id,title')->order($this->order)->select();
        
        //需要被出的数据
        $condition = $this->condition;
        $Model = $this->model;
        $Dao = M($Model);
        $list = $Dao->where($condition)->select();
        
        if (empty($list)){
            $this->error('没有可以导出的简历');
        }
        
        vendor('PHPExcel.PHPExcel');
        $fileName = $this->fileName;
        $fileName = empty($fileName)?'导出简历-'.date('Y-m-d',time()):$fileName;
        
        $PHPExcel = new PHPExcel();
        
        //设置基本信息
        $PHPExcel->getProperties()->setCreator("jecken")
        ->setLastModifiedBy("jecken")
        ->setTitle("上海**人力资源服务有限公司")
        ->setSubject("简历列表")
        ->setDescription("")
        ->setKeywords("简历列表")
        ->setCategory("");
        $PHPExcel->setActiveSheetIndex(0);
        $PHPExcel->getActiveSheet()->setTitle($fileName);
        
        //取得HR列表
        $hrlist = M('Admin')->field('id,nickname')->select();
        foreach ($hrlist as $key => $value){
            $hr[$value['id']] = $value['nickname'];
        }
        
        //存储Excel数据源到其他工作薄
        $PHPExcel->createSheet();
        $subObject = $PHPExcel->getSheet(1);
        $subObject->setTitle('data');
        foreach ($resumeState as $key => $value){
            $subObject->setCellValue('A'.$key,$value);
        }
        foreach ($processingStatus as $key => $value){
            $subObject->setCellValue('B'.$key,$value);
        }
        foreach ($company as $key => $value){
            $subObject->setCellValue('C'.($key+1),$value['title']);
            $companyList[$value['id']]=$value['title'];
        }
        $subObject->getColumnDimension('A')->setWidth(30);
        $subObject->getColumnDimension('B')->setWidth(30);
        $subObject->getColumnDimension('C')->setWidth(30);
        
        //保护数据源
        $subObject->getProtection()->setSheet(true);
        $subObject->protectCells('A1:C1000',time());
        
        //填入主标题
        $PHPExcel->getActiveSheet()->setCellValue('A1', '上海**人力资源服务有限公司');
        //填入副标题
        $PHPExcel->getActiveSheet()->setCellValue('A2', '简历列表(导出日期：'.date('Y-m-d',time()).')');
        
        //填入表头
        $PHPExcel->getActiveSheet()->setCellValue('A3', 'ID');
        $PHPExcel->getActiveSheet()->setCellValue('B3', '姓名');
        $PHPExcel->getActiveSheet()->setCellValue('C3', '性别');
        $PHPExcel->getActiveSheet()->setCellValue('D3', '年龄');
        $PHPExcel->getActiveSheet()->setCellValue('E3', '联系方式');
        $PHPExcel->getActiveSheet()->setCellValue('F3', '学历');
        $PHPExcel->getActiveSheet()->setCellValue('G3', '是否有AQE证书');
        $PHPExcel->getActiveSheet()->setCellValue('H3', '住址');
        $PHPExcel->getActiveSheet()->setCellValue('I3', '面试日期');
        $PHPExcel->getActiveSheet()->setCellValue('J3', '面试时间');
        $PHPExcel->getActiveSheet()->setCellValue('K3', '所属HR');
        
        $PHPExcel->getActiveSheet()->setCellValue('L3', '备注');
        $PHPExcel->getActiveSheet()->setCellValue('M3', '证件号码');
        $PHPExcel->getActiveSheet()->setCellValue('N3', 'QQ');
        $PHPExcel->getActiveSheet()->setCellValue('O3', '电子邮箱');
        $PHPExcel->getActiveSheet()->setCellValue('P3', '出生日期');
        $PHPExcel->getActiveSheet()->setCellValue('Q3', '婚姻状况');
        $PHPExcel->getActiveSheet()->setCellValue('R3', '户籍');
        $PHPExcel->getActiveSheet()->setCellValue('S3', '毕业时间');
        $PHPExcel->getActiveSheet()->setCellValue('T3', '毕业院校');
        
        $PHPExcel->getActiveSheet()->setCellValue('U3', '专业');
        $PHPExcel->getActiveSheet()->setCellValue('V3', '工作经历');
        $PHPExcel->getActiveSheet()->setCellValue('W3', '期望工作地点');
        $PHPExcel->getActiveSheet()->setCellValue('X3', '期望薪酬');
        $PHPExcel->getActiveSheet()->setCellValue('Y3', '期望职业');
        $PHPExcel->getActiveSheet()->setCellValue('Z3', '面试单位');
        $PHPExcel->getActiveSheet()->setCellValue('AA3', '面试岗位');
        $PHPExcel->getActiveSheet()->setCellValue('AB3', '目前状态');
        $PHPExcel->getActiveSheet()->setCellValue('AC3', '处理状态');
        $PHPExcel->getActiveSheet()->setCellValue('AD3', '自我评价');
        $PHPExcel->getActiveSheet()->setCellValue('AE3', '其他');
        
        //填入列表
        $k = 1;
        foreach ($list as $key => $value){
            $k++;
            
            $PHPExcel->getActiveSheet()->setCellValue('A'.($key+4), $value['id']);
            $PHPExcel->getActiveSheet()->setCellValue('B'.($key+4), $value['name']);
            
            //性别
            $PHPExcel->getActiveSheet()->getCell('C'.($key+4))->getDataValidation()
            -> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
            -> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
            -> setAllowBlank(false)
            -> setShowInputMessage(true)
            -> setShowErrorMessage(true)
            -> setShowDropDown(true)
            -> setErrorTitle('请选择性别')
            -> setError('您输入的值不在下拉框列表内.')
            -> setPromptTitle('性别')
            -> setFormula1('"'.join(',', $sex).'"');
            $PHPExcel->getActiveSheet()->setCellValue('C'.($key+4), $sex[$value['sex']]);
            
            //年龄
            $PHPExcel->getActiveSheet()->setCellValue('D'.($key+4), $value['age']);
            
            $PHPExcel->getActiveSheet()->setCellValue('E'.($key+4), $value['tel']);
            $PHPExcel->getActiveSheet()->setCellValue('F'.($key+4), $value['xueli']);
            
            //是否有AQE证书
            $PHPExcel->getActiveSheet()->getCell('G'.($key+4))->getDataValidation()
            -> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
            -> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
            -> setAllowBlank(false)
            -> setShowInputMessage(true)
            -> setShowErrorMessage(true)
            -> setShowDropDown(true)
            -> setErrorTitle('请选择是否有AQE证书')
            -> setError('您输入的值不在下拉框列表内.')
            -> setPromptTitle('是否有AQE证书')
            -> setFormula1('"'.join(',', $AQE).'"');
            $PHPExcel->getActiveSheet()->setCellValue('G'.($key+4), $AQE[$value['hasAQE']]);
            
            $PHPExcel->getActiveSheet()->setCellValue('H'.($key+4), $value['juzhudi']);
            $PHPExcel->getActiveSheet()->setCellValue('I'.($key+4), setDate($value['auditionTime'],'Y年m月d日'));//面试日期
            $PHPExcel->getActiveSheet()->setCellValue('J'.($key+4), setDate($value['auditionTime'],'H点i分'));//面试时间
            $PHPExcel->getActiveSheet()->setCellValue('K'.($key+4), $hr[$value['userid']]); //所属HR
            $PHPExcel->getActiveSheet()->setCellValue('L'.($key+4), $value['remark']);//备注
            
            $PHPExcel->getActiveSheet()->setCellValue('M'.($key+4), $value['cid']);//证件号码
            $PHPExcel->getActiveSheet()->setCellValue('N'.($key+4), $value['qq']);
            $PHPExcel->getActiveSheet()->setCellValue('O'.($key+4), $value['email']);
            $PHPExcel->getActiveSheet()->setCellValue('P'.($key+4), setDate($value['birthday']));
            
                
            
                
            //婚姻
            $PHPExcel->getActiveSheet()->getCell('Q'.($key+4))->getDataValidation()
            -> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
            -> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
            -> setAllowBlank(false)
            -> setShowInputMessage(true)
            -> setShowErrorMessage(true)
            -> setShowDropDown(true)
            -> setErrorTitle('请选择婚姻')
            -> setError('您输入的值不在下拉框列表内.')
            -> setPromptTitle('性别')
            -> setFormula1('"'.join(',', $hunyin).'"');
            $PHPExcel->getActiveSheet()->setCellValue('Q'.($key+4), $hunyin[$value['hunyin']]);
                
            $PHPExcel->getActiveSheet()->setCellValue('R'.($key+4), $value['huji']);
            $PHPExcel->getActiveSheet()->setCellValue('S'.($key+4), setDate($value['graduationTime']));
            $PHPExcel->getActiveSheet()->setCellValue('T'.($key+4), $value['graduationSchool']);
            $PHPExcel->getActiveSheet()->setCellValue('U'.($key+4), $value['specialty']);
            $PHPExcel->getActiveSheet()->setCellValue('V'.($key+4), $value['works']);
            $PHPExcel->getActiveSheet()->setCellValue('W'.($key+4), $value['expectAddress']);
            $PHPExcel->getActiveSheet()->setCellValue('X'.($key+4), $value['expectSalary']);
            $PHPExcel->getActiveSheet()->setCellValue('Y'.($key+4), $value['expectProfession']);
                
            //面试单位
            $PHPExcel->getActiveSheet()->getCell('Z'.($key+4))->getDataValidation()
            -> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
            -> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
            -> setAllowBlank(false)
            -> setShowInputMessage(true)
            -> setShowErrorMessage(true)
            -> setShowDropDown(true)
            -> setErrorTitle('输入的值有误')
            -> setError('您输入的值不在下拉框列表内.')
            -> setPromptTitle('面试单位')
            -> setFormula1('data!$C$1:$C$'.count($company));
            $PHPExcel->getActiveSheet()->setCellValue('Z'.($key+4), $companyList[$value['company']]);//面试单位
            $PHPExcel->getActiveSheet()->setCellValue('AA'.($key+4), $value['post']); //面试岗位
                
            //简历状态
            $PHPExcel->getActiveSheet()->getCell('AB'.($key+4))->getDataValidation()
            -> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
            -> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
            -> setAllowBlank(false)
            -> setShowInputMessage(true)
            -> setShowErrorMessage(true)
            -> setShowDropDown(true)
            -> setErrorTitle('输入的值有误')
            -> setError('您输入的值不在下拉框列表内.')
            -> setPromptTitle('简历状态')
            -> setFormula1('data!$A$1:$A$'.count($resumeState));
            $PHPExcel->getActiveSheet()->setCellValue('AB'.($key+4), $resumeState[$value['resumeState']]);
            
            //处理状态
            $PHPExcel->getActiveSheet()->getCell('AC'.($key+4))->getDataValidation()
            -> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
            -> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
            -> setAllowBlank(false)
            -> setShowInputMessage(true)
            -> setShowErrorMessage(true)
            -> setShowDropDown(true)
            -> setErrorTitle('输入的值有误')
            -> setError('您输入的值不在下拉框列表内.')
            -> setPromptTitle('处理状态')
            -> setFormula1('data!$B$1:$B$'.count($processingStatus));
            $PHPExcel->getActiveSheet()->setCellValue('AC'.($key+4), $processingStatus[$value['processingStatus']]);
                
            $PHPExcel->getActiveSheet()->setCellValue('AD'.($key+4), $value['selfIntroduction']);
            $PHPExcel->getActiveSheet()->setCellValue('AE'.($key+4), $value['other']);
        
            //设置每一行行高
            $PHPExcel->getActiveSheet()->getRowDimension($key+4)->setRowHeight(30);
        }
        
        //合并单元格
        $PHPExcel->getActiveSheet()->mergeCells('A1:AE1');
        $PHPExcel->getActiveSheet()->mergeCells('A2:AE2');
        
        //设置单元格宽度
        $PHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(6);
        $PHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
        $PHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
        $PHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
        $PHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
        $PHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
        $PHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(16);
        $PHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
        $PHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(10);
        $PHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10);
        $PHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(10);
        $PHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(30);
        $PHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
        $PHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
        $PHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(25);
        $PHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15);
        $PHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(10);
        $PHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(25);
        $PHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(15);
        $PHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(18);
        $PHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(15);
        $PHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(30);
        $PHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(15);
        $PHPExcel->getActiveSheet()->getColumnDimension('X')->setWidth(15);
        $PHPExcel->getActiveSheet()->getColumnDimension('Y')->setWidth(15);
        $PHPExcel->getActiveSheet()->getColumnDimension('Z')->setWidth(20);
        $PHPExcel->getActiveSheet()->getColumnDimension('AA')->setWidth(20);
        $PHPExcel->getActiveSheet()->getColumnDimension('AB')->setWidth(20);
        $PHPExcel->getActiveSheet()->getColumnDimension('AC')->setWidth(15);
        $PHPExcel->getActiveSheet()->getColumnDimension('AD')->setWidth(30);
        $PHPExcel->getActiveSheet()->getColumnDimension('AE')->setWidth(30);
        
        //设置表头行高
        $PHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(35);
        $PHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(22);
        $PHPExcel->getActiveSheet()->getRowDimension(3)->setRowHeight(20);
        
        //设置字体样式
        $PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setName('黑体');
        $PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
        $PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $PHPExcel->getActiveSheet()->getStyle('A3:AE3')->getFont()->setBold(true);
            
        $PHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setName('宋体');
        $PHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(16);
        
        $PHPExcel->getActiveSheet()->getStyle('A4:AE'.($k+2))->getFont()->setSize(10);
        //设置居中
        $PHPExcel->getActiveSheet()->getStyle('A1:AE'.($k+2))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            
        //所有垂直居中
        $PHPExcel->getActiveSheet()->getStyle('A1:AE'.($k+2))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            
        //设置单元格边框
        $PHPExcel->getActiveSheet()->getStyle('A3:AE'.($k+2))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        
        //设置自动换行
        $PHPExcel->getActiveSheet()->getStyle('A3:AE'.($k+2))->getAlignment()->setWrapText(true);
        
        
        //保存为2003格式
        $objWriter = new PHPExcel_Writer_Excel5($PHPExcel);
        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/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        
        //多浏览器下兼容中文标题
        $encoded_filename = urlencode($fileName);
        $ua = $_SERVER["HTTP_USER_AGENT"];
        if (preg_match("/MSIE/", $ua)) {
            header('Content-Disposition: attachment; filename="' . $encoded_filename . '.xls"');
        } else if (preg_match("/Firefox/", $ua)) {
            header('Content-Disposition: attachment; filename*="utf8\'\'' . $fileName . '.xls"');
        } else {
            header('Content-Disposition: attachment; filename="' . $fileName . '.xls"');
        }
        
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }








</pre><pre name="code" class="html">1.header
[php]
header("Content-Type:application/vnd.ms-excel");
header("Content-Disposition:attachment;filename=sample.xls");
header("Pragma:no-cache");
header("Expires:0");
2.PHPExcel
http://www.codeplex.com/PHPExcel
http://www.phpexcel.net
开发包Tests目录有详细使用实例 支持中文,注意文件编码 文件保存为utf-8

写excel
[php]
//Include class
require_once('Classes/PHPExcel.php');
require_once('Classes/PHPExcel/Writer/Excel2007.php');
$objPHPExcel = new PHPExcel();

//Set properties 设置文件属性
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
$objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
$objPHPExcel->getProperties()->setKeywords("office 2007 openxml php");
$objPHPExcel->getProperties()->setCategory("Test result file");

//Add some data 添加数据
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Hello');//可以指定位置
$objPHPExcel->getActiveSheet()->setCellValue('A2', true);
$objPHPExcel->getActiveSheet()->setCellValue('A3', false);
$objPHPExcel->getActiveSheet()->setCellValue('B2', 'world!');
$objPHPExcel->getActiveSheet()->setCellValue('B3', 2);
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Hello');
$objPHPExcel->getActiveSheet()->setCellValue('D2', 'world!');

//循环
for($i = 1;$i<200;$i++) {
$objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $i);
$objPHPExcel->getActiveSheet()->setCellValue('B' . $i, 'Test value');
}

//日期格式化
$objPHPExcel->getActiveSheet()->setCellValue('D1', time());
$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);

//Add comment 添加注释
$objPHPExcel->getActiveSheet()->getComment('E11')->setAuthor('PHPExcel');
$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun('PHPExcel:');
$objCommentRichText->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun("\r\n");
$objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun('Total amount on the current invoice, excluding VAT.');

//Add rich-text string 添加文字 可设置样式
$objRichText = new PHPExcel_RichText( $objPHPExcel->getActiveSheet()->getCell('A18') );
$objRichText->createText('This invoice is ');
$objPayable = $objRichText->createTextRun('payable within thirty days after the end of the month');
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN ) );
$objRichText->createText(', unless specified otherwise on the invoice.');

//Merge cells 合并分离单元格
$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');
$objPHPExcel->getActiveSheet()->unmergeCells('A18:E22');

//Protect cells 保护单元格
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);//Needs to be set to true in order to enable any worksheet protection!
$objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel');

//Set cell number formats 数字格式化
$objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
$objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('E4'), 'E5:E13' );

//Set column widths 设置列宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);

//Set fonts 设置字体
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);

//Set alignments 设置对齐
$objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setWrapText(true);

//Set column borders 设置列边框
$objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A10')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E10')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);

//Set border colors 设置边框颜色
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getTop()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getRight()->getColor()->setARGB('FF993300');

//Set fills 设置填充
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080');

//Add a hyperlink to the sheet 添加链接
$objPHPExcel->getActiveSheet()->setCellValue('E26', 'www.phpexcel.net');
$objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl('http://www.phpexcel.net');
$objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setTooltip('Navigate to website');
$objPHPExcel->getActiveSheet()->getStyle('E26')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

//Add a drawing to the worksheet 添加图片
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath('./images/officelogo.jpg');
$objDrawing->setHeight(36);
$objDrawing->setCoordinates('B15');
$objDrawing->setOffsetX(110);
$objDrawing->setRotation(25);
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(45);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

//Play around with inserting and removing rows and columns
$objPHPExcel->getActiveSheet()->insertNewRowBefore(6, 10);
$objPHPExcel->getActiveSheet()->removeRow(6, 10);
$objPHPExcel->getActiveSheet()->insertNewColumnBefore('E', 5);
$objPHPExcel->getActiveSheet()->removeColumn('E', 5);

//Add conditional formatting
$objConditional1 = new PHPExcel_Style_Conditional();
$objConditional1->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS);
$objConditional1->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_LESSTHAN);
$objConditional1->setCondition('0');
$objConditional1->getStyle()->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
$objConditional1->getStyle()->getFont()->setBold(true);

//Set autofilter 自动过滤
$objPHPExcel->getActiveSheet()->setAutoFilter('A1:C9');

//Hide "Phone" and "fax" column 隐藏列
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setVisible(false);

//Set document security 设置文档安全
$objPHPExcel->getSecurity()->setLockWindows(true);
$objPHPExcel->getSecurity()->setLockStructure(true);
$objPHPExcel->getSecurity()->setWorkbookPassword("PHPExcel");

//Set sheet security 设置工作表安全
$objPHPExcel->getActiveSheet()->getProtection()->setPassword('PHPExcel');
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);// This should be enabled in order to enable any of the following!
$objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
$objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);
$objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);

//Calculated data 计算
echo 'Value of B14 [=COUNT(B2:B12)]: ' . $objPHPExcel->getActiveSheet()->getCell('B14')->getCalculatedValue() . "\r\n";

//Set outline levels
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setOutlineLevel(1);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setCollapsed(true);

//Freeze panes
$objPHPExcel->getActiveSheet()->freezePane('A2');

//Rows to repeat at top
$objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 1);

//Set data validation 验证输入值
$objValidation = $objPHPExcel->getActiveSheet()->getCell('B3')->getDataValidation();
$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_WHOLE );
$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_STOP );
$objValidation->setAllowBlank(true);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setErrorTitle('Input error');
$objValidation->setError('Number is not allowed!');
$objValidation->setPromptTitle('Allowed input');
$objValidation->setPrompt('Only numbers between 10 and 20 are allowed.');
$objValidation->setFormula1(10);
$objValidation->setFormula2(20);
$objPHPExcel->getActiveSheet()->getCell('B3')->setDataValidation($objValidation);

//Create a new worksheet, after the default sheet 创建新的工作标签
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(1);

//Set header and footer. When no different headers for odd/even are used, odd header is assumed. 页眉页脚
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&C&HPlease treat this document as confidential!');
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');

//Set page orientation and size 方向大小
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

//Rename sheet 重命名工作表标签
$objPHPExcel->getActiveSheet()->setTitle('Simple');

//Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

//Save Excel 2007 file 保存
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));

//Save Excel 5 file 保存
require_once('Classes/PHPExcel/Writer/Excel5.php');
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save(str_replace('.php', '.xls', __FILE__));

//1.6.2新版保存
require_once('Classes/PHPExcel/IOFactory.php');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xls', __FILE__));

读excel
[php]
//Include class
require_once('Classes/PHPExcel/Reader/Excel2007.php');
$objReader = new PHPExcel_Reader_Excel2007;
$objPHPExcel = $objReader->load("05featuredemo.xlsx");

读写csv
[php]
require_once("05featuredemo.inc.php");
require_once('Classes/PHPExcel/Writer/CSV.php');
require_once('Classes/PHPExcel/Reader/CSV.php');
require_once('Classes/PHPExcel/Writer/Excel2007.php');
//Write to CSV format 写
$objWriter = new PHPExcel_Writer_CSV($objPHPExcel);
$objWriter->setDelimiter(';');
$objWriter->setEnclosure('');
$objWriter->setLineEnding("\r\n");
$objWriter->setSheetIndex(0);
$objWriter->save(str_replace('.php', '.csv', __FILE__));
//Read from CSV format 读
$objReader = new PHPExcel_Reader_CSV();
$objReader->setDelimiter(';');
$objReader->setEnclosure('');
$objReader->setLineEnding("\r\n");
$objReader->setSheetIndex(0);
$objPHPExcelFromCSV = $objReader->load(str_replace('.php', '.csv', __FILE__));
//Write to Excel2007 format
$objWriter2007 = new PHPExcel_Writer_Excel2007($objPHPExcelFromCSV);
$objWriter2007->save(str_replace('.php', '.xlsx', __FILE__));

写html
[php]
require_once("05featuredemo.inc.php");
require_once('Classes/PHPExcel/Writer/HTML.php');
//Write to HTML format
$objWriter = new PHPExcel_Writer_HTML($objPHPExcel);
$objWriter->setSheetIndex(0);
$objWriter->save(str_replace('.php', '.htm', __FILE__));

写pdf
[php]
require_once("05featuredemo.inc.php");
require_once('Classes/PHPExcel/IOFactory.php');
//Write to PDF format www.2cto.com
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
$objWriter->setSheetIndex(0);
$objWriter->save(str_replace('.php', '.pdf', __FILE__));
//Echo memory peak usage
echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) .

慕斯先生
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
php读取excel及写入excel （强烈推荐）支持高版本php
04-27
2010年上传了一版php读取excel及写入excel （强烈推荐），但是随着php版本的提升，会有一些废弃的方法，于是修改了一版支持高版本php，欢迎下载
PHPExcel.zip
01-12
下面将详细介绍PHPExcel库的核心功能、使用场景、主要类和方法以及一些常见操作。 一、核心功能 1. 文件读取：PHPExcel支持读取多种格式的电子表格文件，包括.XLS、.XLSX、.CSV、.ODS等。 2. 文件写入：可以创建新...
PHP操作Excel – PHPExcel 基本用法详解
热门推荐
diandian_520的专栏
08-03 6万+
1.header header("Content-Type:application/vnd.ms-excel"); header("Content-Disposition:attachment;filename=sample.xls"); header("Pragma:no-cache"); header("Expires:0"); 2.PHPExcel http://www.codeple
PHPExcel 基本用法详解
slqgenius的博客
08-05 453
[php] view plain copy 1.header   [php]   header("Content-Type:application/vnd.ms-excel");   header("Content-Disposition:attachment;filename=sample.xls");   header("Pragma:no-cache")
PHPExcel
zhang928za
04-30 455
下面是总结的几个使用方法 include ‘PHPExcel.php’; include ‘PHPExcel/Writer/Excel2007.php’; //或者include ‘PHPExcel/Writer/Excel5.php’; 用于输出.xls的 创建一个excel $objPHPExcel = new PHPExcel(); 保存excel—2007格式 $objWri...
php excel
m0_65804175的博客
05-06 264
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);设置A4纸张大小 $objPHPExcel->getActiveSheet()->getStyle('A'.$NOW_row)->getFont()->setSize(15);//字体大小 $objPHPExcel->getActiveSheet(
PHPExcel-1.8.zip_phpexcel
09-20
在“PHPExcel-1.8.zip”这个压缩包中，包含了PHPExcel的1.8版本的所有源代码和相关资源。此版本可能包含以下部分： 1. **源代码**：主要包括PHPExcel的核心类库，这些类库定义了工作簿（Workbook）、工作表...
解决PHP7.4下不支持PHPExcel类
03-19
在PHP开发过程中，PHPExcel是一个非常流行的库，用于读写Excel文件。然而，随着PHP版本的升级，特别是从PHP5向PHP7.x过渡时，有些旧的库可能不再兼容，其中包括PHPExcel。在PHP7.4环境下遇到不支持PHPExcel的问题，...
PHPExcel-1.8.2.zip
01-12
- **PHPExcel类库**：它是由Mark Baker开发的，提供了一组丰富的类，这些类可以模拟Excel的各种对象，如工作簿、工作表、单元格、样式、公式等。 - **工作簿（Workbook）**：是Excel文件的基本单位，相当于一个...
php读取excel及写入excel （强烈推荐）
05-07
包含了php操作excel的读取类及写入类。 解决了服务器处理位数不同而出现的 Fatal error: Allowed memory size of xxxxx bytes exhausted (tried to allocate 71 bytes) in /xxx/xxx/ss/oleread.inc on line 133]错误 强烈推荐！！！！！
PHP读写Excel
weixin_30951743的博客
08-26 89
PHP读写Excel PHP读写Excel可以通过第三方库phpexcel比较优雅地完成，由于PHP对于字符串处理的优势，读写PHP非常方便。 库导入 这里使用composer包管理工具，以下是配置信息 { "name": "", "description": "test the excel", "require": { "PHPExcel/PHPExcel...
php读写excel文件
梁吉林的博客
11-08 4673
php操作excel文件
PHP操作Excel
倾城一笑stu
11-06 6723
php操作Excel其实就是用了一个PHPExcel类，这个类提供了很多方法，支持生成Excel文件，支持生成报表等等。 首先我们得去官网下载这个PHPExcel类。 百度PHPExcel，搜索结果最上面显示的那个就是PHPExcel的官网（http://phpexcel.codeplex.com/），点击进入，官网首页右侧有一个download按钮，点击下载即可，下载完后解压，找到里面的Cl
PHPExcel 详解
我只是个小学生 能力有限 一直抱着学习的态度
10-28 3979
PHPExcel 下载PHPExcel插件 慕课网学习 文件格式 Excel2007
php---excel
最新发布
qq_57700056的博客
06-29 303
在 PHP 中导出 Excel 文件可以使用多种库，其中最流行的是。这个库是由PHPExcel演变而来的，功能强大且适用于各种 Excel 操作。下面是一个简单的示例，展示如何使用库来创建和导出一个 Excel 文件。
php excel读写,用PHP读取Excel文件
weixin_42394913的博客
03-10 208
// Here is the simple code using COM object in PHPclass Excel_ReadWrite{private $XLSHandle;private $WrkBksHandle;private $xlBook;function __construct() {$this->XLSHandle = new COM("excel.applicatio...
PHPExcel开发与应用指南
"PHPExcel开发手册是一份英文文档，尽管语言有障碍，但因其高质量的内容而被推荐。这份手册涵盖了关于PHPExcel库的详细信息，包括前提条件、软件需求、安装指南以及许多有用的链接和工具。此外，它深入讲解了库的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值