PHPExcel类使用【原创】

PHPExcel类是一个功能强大的PHP对Excel格式进行操作的类，今天正好用到，研究了一下。

直接看代码吧，代码上有注释。

require 'PHPExcel.php';  //引入PHPExcel类文件

 $objExcel = new PHPExcel();  // 创建一个处理对象实例 

$objWriter = new PHPExcel_Writer_Excel2007($objExcel);  //保存为Excel2007格式

$objExcel->setActiveSheetIndex(0);  //缺省情况下，PHPExcel会自动创建第一个sheet被设置SheetIndex=0  

$objActSheet = $objExcel->getActiveSheet();  //获取当前激活sheet的句柄

$objActSheet->setTitle('学生信息');  //设置当前激活sheet的sheet名称

$outputFileName = "students_teachers_info.xlsx";  

$counter = 1;
//设置单元格的值
$objActSheet->setCellValue('A' . $counter, '学号');  
$objActSheet->setCellValue('B' . $counter , '姓名');            
$objActSheet->setCellValue('C' . $counter, '性别');          
$objActSheet->setCellValue('D' . $counter, '年龄');  
$objActSheet->setCellValue('E' . $counter, '院系');            
$objActSheet->setCellValue('F' . $counter, '班级');          

//将mysql中的数据写到Excel表中
$link = mysql_connect ( 'localhost', 'root', '' );
mysql_select_db ( 'students', $link );
mysql_query('SET NAMES utf8', $link);

$sql = "select * from students" ;
$query = mysql_query($sql);
$counter ++;
while ($rows = mysql_fetch_assoc($query)) {
	$objActSheet->setCellValue('A'.$counter, $rows['student_no']);
	$objActSheet->setCellValue('B'.$counter, $rows['student_name']);  
	$objActSheet->setCellValue('C'.$counter, $rows['student_sex']);  
	$objActSheet->setCellValue('D'.$counter, $rows['student_age']);  
	$objActSheet->setCellValue('E'.$counter, $rows['student_department']);  
	$objActSheet->setCellValue('F'.$counter, $rows['student_class']);  	
	
	$objWriter->save($outputFileName); //写入到文件  
	$counter ++;
}

//创建一个新的sheet，用来保存教师信息
$objExcel->createSheet()->setTitle('教师信息');  //设置当前激活sheet的sheet名称

$objExcel->setActiveSheetIndex(1);  

$objActSheet = $objExcel->getActiveSheet();  

$counter = 1; //设置单元格的值 $objActSheet->setCellValue('A' . $counter, '编号'); 

$objActSheet->setCellValue('B' . $counter , '姓名'); 
$objActSheet->setCellValue('C' . $counter, '性别'); 
$objActSheet->setCellValue('D' . $counter, '年龄');
$objActSheet->setCellValue('E' . $counter, '职称');
$objActSheet->setCellValue('F' . $counter, '院系'); 

//将mysql中的数据写到Excel表中 
$sql = "select * from teachers" ;
$query = mysql_query($sql); $counter ++; 
while ($rows = mysql_fetch_assoc($query)) {
	$objActSheet->setCellValue('A'.$counter, $rows['teacher_no']);
	$objActSheet->setCellValue('B'.$counter, $rows['teacher_name']);
	$objActSheet->setCellValue('C'.$counter, $rows['teacher_sex']); 
	$objActSheet->setCellValue('D'.$counter, $rows['teacher_age']);
	$objActSheet->setCellValue('E'.$counter, $rows['teacher_title']); 
	$objActSheet->setCellValue('F'.$counter, $rows['teacher_department']);
	$objWriter->save($outputFileName); //写入到文件 
	$counter ++; 
}



以上是工作中用到的从mysql中导出excel表的方法，下面是一些更详细的设置，没有做过验证。用到时可以试试



$objPHPExcel = new PHPExcel();

$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);//PHP类phpExcel保存excel—2007格式

$objWriter=new PHPExcel_Writer_Excel5($objPHPExcel);//非2007格式

$objWriter->save("xxx.xlsx");

//直接输出到浏览器
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
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="resume.xls" ');
header("Content-Transfer-Encoding:binary");
$objWriter->save("php://output");

//设置表属性
$objPHPExcel->getProperties()->setCreator(”xiaoyu”);//创建人
$objPHPExcel->getProperties()->setLastModifiedBy(”xiaoyu”);//最后修改人
$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”);//种类
$objPHPExcel->setActiveSheetIndex(0);//PHP类phpExcel设置当前的sheet
$objPHPExcel->getActiveSheet()->setTitle(’Simple’);//设置sheet的name

//设置单元格的值
$objPHPExcel->getActiveSheet()->setCellValue(’A1′, ‘String’)
$objPHPExcel->getActiveSheet()->setCellValue(’A2′, 12);
$objPHPExcel->getActiveSheet()->setCellValue(’A3′, true);
$objPHPExcel->getActiveSheet()->setCellValue(’C5′, ‘=SUM(C2:C4)’);
$objPHPExcel->getActiveSheet()->setCellValue(’B8′, ‘=MIN(B2:C5)’);

//合并单元格
$objPHPExcel->getActiveSheet()->mergeCells(’A18:E22′);

//PHP类phpExcel分离单元格
$objPHPExcel->getActiveSheet()->unmergeCells(’A28:B28′);

//保护cell
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); // Needs to be set to true in order to enable any worksheet protection!
$objPHPExcel->getActiveSheet()->protectCells(’A3:E13′, ‘PHPExcel’);

//设置格式
echo date(’H:i:s’) . ” Set cell number formatsn”;
$objPHPExcel->getActiveSheet()->getStyle(’E4′)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
$objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle(’E4′), ‘E5:E13′ );



//PHP类phpExcel设置宽width
$objPHPExcel->getActiveSheet()->getColumnDimension(’B’)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension(’D’)->setWidth(12);

//设置font
$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);
$objPHPExcel->getActiveSheet()->getStyle(’E1′)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle(’D13′)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle(’E13′)->getFont()->setBold(true);


//PHP类phpExcel设置align

$objPHPExcel->getActiveSheet()->getStyle(’D11′)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle(’D12′)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle(’D13′)->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);

//PHP类phpExcel设置column的border
$objPHPExcel->getActiveSheet()->getStyle(’A4′)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle(’B4′)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle(’C4′)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle(’D4′)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle(’E4′)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);



//设置border的color
$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()->getTop()->getColor()->setARGB(’FF993300′);
$objPHPExcel->getActiveSheet()->getStyle(’E13′)->getBorders()->getBottom()->getColor()->setARGB(’FF993300′);
$objPHPExcel->getActiveSheet()->getStyle(’E13′)->getBorders()->getRight()->getColor()->setARGB(’FF993300′);



//设置填充颜色
$objPHPExcel->getActiveSheet()->getStyle(’A1′)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle(’A1′)->getFill()->getStartColor()->setARGB(’FF808080′);
$objPHPExcel->getActiveSheet()->getStyle(’B1′)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle(’B1′)->getFill()->getStartColor()->setARGB(’FF808080′);



//加图片
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName(’Logo’);
$objDrawing->setDescription(’Logo’);
$objDrawing->setPath(’./images/officelogo.jpg’);
$objDrawing->setHeight(36);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName(’Paid’);
$objDrawing->setDescription(’Paid’);
$objDrawing->setPath(’./images/paid.png’);
$objDrawing->setCoordinates(’B15′);
$objDrawing->setOffsetX(110);
$objDrawing->setRotation(25);
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(45);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());