<?php
/*
* Created on 2014-2-26
* Author yellow
*/
include "../../PHPExcel.php";
include "../../PHPExcel/Writer/Excel2007.php";
//创建一个Excel操作对象
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("yellow")
->setLastModifiedBy('yellow')
->setTitle('生成Excel')
->setDescription('描述:生成Excel')
->setKeywords('Excel')
->setCategory('分类');
//激活第一个选项卡,然后填充数据
$sheet = $objPHPExcel->createSheet(0)->setTitle('互动售楼系统');
$cap_data = '互动售楼系统报价';
$sheet->setCellValue('A1', $cap_data);
$sheet->mergeCells('A1:J1');
$sheet->getStyle('A1:J1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$sheet->getStyle('A1:J1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$sheet->getStyle('A1:J1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
$sheet->getStyle('A1:J1')->getFont()->setName('微软雅黑')->setSize(18);
$sheet->getRowDimension(1)->setRowHeight(40);
/**设置头部行高**/
$head_height = array(
'A'=>10,
'B'=>8,
'C'=>30,
'D'=>80,
'E'=>15,
'F'=>15,
'G'=>15,
'H'=>40,
);
foreach( $head_height as $k=>$v ){
//设置宽度
$sheet->getColumnDimension($k)->setWidth($v);
}
/**设置头部内容**/
$head_data = array(
'A2'=>'分类',
'B2'=>'序号',
'C2'=>'制作项目',
'D2'=>'制作说明',
'E2'=>'对外报价',
'F2'=>'数量',
'G2'=>'总报价',
'H2'=>'备注',
);
foreach( $head_data as $k=>$v ){
//设置值
$sheet->setCellValue($k, $v);
//设置bold
$sheet->getStyle($k)->getFont()->setName('微软雅黑')->setSize(14)->setBold(true);
//设置对齐方式
$sheet->getStyle($k)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$sheet->getStyle($k)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//设置border
$sheet->getStyle($k)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$sheet->getStyle($k)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$sheet->getStyle($k)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$sheet->getStyle($k)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//设置border的color
$sheet->getStyle($k)->getBorders()->getTop()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
$sheet->getStyle($k)->getBorders()->getRight()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
$sheet->getStyle($k)->getBorders()->getBottom()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
$sheet->getStyle($k)->getBorders()->getLeft()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
//设置填充色彩
$sheet->getStyle($k)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$sheet->getStyle($k)->getFill()->getStartColor()->setRGB('FF'.'FFFF99');
//当循环到特殊列时字体变为红色
if( $k=='E2' || $k=='F2' || $k=='G2' ){
$sheet->getStyle($k)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
}
}
/**设置主体内容**/
$data = array(
array(
'categoly'=>'内容部分',
'orderno'=>'1',
'make_project'=>'片头LOGO演绎',
'make_instr'=>'10秒左右的视频制作',
'foreign_price'=>'2000元/个',
'num'=>'1个',
'total_price'=>'2000元',
'remarks'=>'备注'
),
array(
'categoly'=>'内容部分',
'orderno'=>'2',
'make_project'=>'UI界面设计',
'make_instr'=>'程序框架设计及动态交互设计',
'foreign_price'=>'6000元/个',
'num'=>'1个',
'total_price'=>'6000元',
'remarks'=>''
),
array(
'categoly'=>'内容部分',
'orderno'=>'3',
'make_project'=>'文案创意撰写',
'make_instr'=>'系统中要表现的项目内容的创意和文案撰写',
'foreign_price'=>'6000元/套',
'num'=>'1个',
'total_price'=>'2000元',
'remarks'=>'建议不做'
),
array(
'categoly'=>'内容部分',
'orderno'=>'4',
'make_project'=>'文案创意撰写',
'make_instr'=>'系统中要表现的项目内容的创意和文案撰写',
'foreign_price'=>'6000元/套',
'num'=>'1个',
'total_price'=>'2000元',
'remarks'=>'建议不做'
),
array(
'categoly'=>'内容部分',
'orderno'=>'5',
'make_project'=>'文案创意撰写',
'make_instr'=>'系统中要表现的项目内容的创意和文案撰写',
'foreign_price'=>'6000元/套',
'num'=>'1个',
'total_price'=>'2000元',
'remarks'=>'建议不做'
),
array(
'categoly'=>'内容部分',
'orderno'=>'6',
'make_project'=>'文案创意撰写',
'make_instr'=>'系统中要表现的项目内容的创意和文案撰写',
'foreign_price'=>'6000元/套',
'num'=>'1个',
'total_price'=>'2000元',
'remarks'=>'建议不做'
),
array(
'categoly'=>'内容部分',
'orderno'=>'7',
'make_project'=>'文案创意撰写',
'make_instr'=>'系统中要表现的项目内容的创意和文案撰写',
'foreign_price'=>'6000元/套',
'num'=>'1个',
'total_price'=>'2000元',
'remarks'=>'建议不做'
),
array(
'categoly'=>'内容部分',
'orderno'=>'8',
'make_project'=>'文案创意撰写',
'make_instr'=>'系统中要表现的项目内容的创意和文案撰写',
'foreign_price'=>'6000元/套',
'num'=>'1个',
'total_price'=>'2000元',
'remarks'=>'建议不做'
),
);
$count = count($data);
for( $i=3; $i<$count+3; $i++ ){
//设置值
$sheet->setCellValue('A'.$i, $data[$i-3]['categoly']);
$sheet->setCellValue('B'.$i, $data[$i-3]['orderno']);
$sheet->setCellValue('C'.$i, $data[$i-3]['make_project']);
$sheet->setCellValue('D'.$i, $data[$i-3]['make_instr']);
$sheet->setCellValue('E'.$i, $data[$i-3]['foreign_price']);
$sheet->setCellValue('F'.$i, $data[$i-3]['num']);
$sheet->setCellValue('G'.$i, $data[$i-3]['total_price']);
$sheet->setCellValue('H'.$i, $data[$i-3]['remarks']);
//设置bold
$sheet->getStyle('A'.$i.':'.'H'.$i)->getFont()->setName('微软雅黑')->setSize(12);
$sheet->getStyle('A'.$i.':'.'H'.$i)->getFont()->setName('微软雅黑')->setSize(12);
//设置前3列为粗体
$sheet->getStyle('A'.$i.':'.'C'.$i)->getFont()->setName('微软雅黑')->setSize(12)->setBold(true);
//设置对齐方式
$sheet->getStyle('A'.$i.':'.'C'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$sheet->getStyle('A'.$i.':'.'C'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//设置border的color
$temp = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H');
foreach( $temp as $v ){
//设置border
$sheet->getStyle($v.$i)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$sheet->getStyle($v.$i)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$sheet->getStyle($v.$i)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$sheet->getStyle($v.$i)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//设置border的color
$sheet->getStyle($v.$i)->getBorders()->getTop()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
$sheet->getStyle($v.$i)->getBorders()->getRight()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
$sheet->getStyle($v.$i)->getBorders()->getBottom()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
$sheet->getStyle($v.$i)->getBorders()->getLeft()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
}
//当循环到特殊列时字体变为红色
$sheet->getStyle('E'.$i)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$sheet->getStyle('E'.$i)->getFill()->getStartColor()->setRGB('FF'.'FF8080');
}
// 设置合并单元格
$sheet->mergeCells('A'.'3'.':'.'A'.($count+3-1));
/**设置在浏览器下载**/
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');
header('Content-Disposition:attachment;filename=create_excel.xls');
header('Content-Transfer-Encoding:binary');
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save('php://output');
?>
PHPExcel案例记录整理
最新推荐文章于 2023-07-02 13:35:19 发布