PHPExcel 是用来操作Office Excel 文档的一个PHP类库，功能强大，操作简单，但是官方没有提供英文的帮助手册，这叫我们这些英文盲为难了，以下是本人工作中整理的常用的几个使用方法的中文说明，希望对你有用。
官网：http://phpexcel.codeplex.com/
下面是总结的几个使用方法，
include &＃39;PHPExcel.php&＃39;; include &＃39;PHPExcel/Writer/Excel2007.php&＃39;; //或者include &＃39;PHPExcel/Writer/Excel5.php&＃39;; 用于输出.xls的
创建一个excel
$objPHPExcel = new PHPExcel();
保存excel-2007格式
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); //或者$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 非2007格式 $objWriter->save(&＃39;xxx.xlsx&＃39;);
直接输出到浏览器
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); header(&＃39;Pragma: public&＃39;); header(&＃39;Expires: 0&＃39;); header(&＃39;Cache-Control:must-revalidate, post-check=0, pre-check=0&＃39;); header(&＃39;Content-Type:application/force-download&＃39;); header(&＃39;Content-Type:application/vnd.ms-execl&＃39;); header(&＃39;Content-Type:application/octet-stream&＃39;); header(&＃39;Content-Type:application/download&＃39;);; header(&＃39;Content-Disposition:attachment;filename=&＃39;resume.xls&＃39;&＃39;); header(&＃39;Content-Transfer-Encoding:binary&＃39;); $objWriter->save(&＃39;php://output&＃39;);
--------------------------------------
phpExcel中文手册
设置excel的属性：
创建人
$objPHPExcel->getProperties()->setCreator(&＃39;Maarten Balliauw&＃39;);
最后修改人
$objPHPExcel->getProperties()->setLastModifiedBy(&＃39;Maarten Balliauw&＃39;);
标题
$objPHPExcel->getProperties()->setTitle(&＃39;Office 2007 XLSX Test Document&＃39;);
题目
$objPHPExcel->getProperties()->setSubject(&＃39;Office 2007 XLSX Test Document&＃39;);
描述
$objPHPExcel->getProperties()->setDescription(&＃39;Test document for Office 2007 XLSX, generated using PHP classes.&＃39;);
关键字
$objPHPExcel->getProperties()->setKeywords(&＃39;office 2007 openxml php&＃39;);
种类
$objPHPExcel->getProperties()->setCategory(&＃39;Test result file&＃39;);
--------------------------------------
设置当前的sheet
$objPHPExcel->setActiveSheetIndex(0);
设置sheet的name
$objPHPExcel->getActiveSheet()->setTitle(&＃39;Simple&＃39;);
设置单元格的值
$objPHPExcel->getActiveSheet()->setCellValue(&＃39;A1&＃39;, &＃39;String&＃39;); $objPHPExcel->getActiveSheet()->setCellValue(&＃39;A2&＃39;, 12); $objPHPExcel->getActiveSheet()->setCellValue(&＃39;A3&＃39;, true); $objPHPExcel->getActiveSheet()->setCellValue(&＃39;C5&＃39;, &＃39;=SUM(C2:C4)&＃39;); $objPHPExcel->getActiveSheet()->setCellValue(&＃39;B8&＃39;, &＃39;=MIN(B2:C5)&＃39;);
合并单元格
$objPHPExcel->getActiveSheet()->mergeCells(&＃39;A18:E22&＃39;);
分离单元格
$objPHPExcel->getActiveSheet()->unmergeCells(&＃39;A28:B28&＃39;); 保护cell $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); // Needs to be set to true in order to enable any worksheet protection! $objPHPExcel->getActiveSheet()->protectCells(&＃39;A3:E13&＃39;, &＃39;PHPExcel&＃39;);
设置格式
// Set cell number formats echo date(&＃39;H:i:s&＃39;) . &＃39; Set cell number formatsn&＃39;; $objPHPExcel->getActiveSheet()->getStyle(&＃39;E4&＃39;)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE); $objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle(&＃39;E4&＃39;), &＃39;E5:E13&＃39; );
设置宽width
// Set column widths $objPHPExcel->getActiveSheet()->getColumnDimension(&＃39;B&＃39;)->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension(&＃39;D&＃39;)->setWidth(12);
设置font
$objPHPExcel->getActiveSheet()->getStyle(&＃39;B1&＃39;)->getFont()->setName(&＃39;Candara&＃39;); $objPHPExcel->getActiveSheet()->getStyle(&＃39;B1&＃39;)->getFont()->setSize(20); $objPHPExcel->getActiveSheet()->getStyle(&＃39;B1&＃39;)->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle(&＃39;B1&＃39;)->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE); $objPHPExcel->getActiveSheet()->getStyle(&＃39;B1&＃39;)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE); $objPHPExcel->getActiveSheet()->getStyle(&＃39;E1&＃39;)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE); $objPHPExcel->getActiveSheet()->getStyle(&＃39;D13&＃39;)->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle(&＃39;E13&＃39;)->getFont()->setBold(true);
设置align
$objPHPExcel->getActiveSheet()->getStyle(&＃39;D11&＃39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle(&＃39;D12&＃39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle(&＃39;D13&＃39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle(&＃39;A18&＃39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
//垂直居中
$objPHPExcel->getActiveSheet()->getStyle(&＃39;A18&＃39;)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
设置column的border
$objPHPExcel->getActiveSheet()->getStyle(&＃39;A4&＃39;)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getStyle(&＃39;B4&＃39;)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getStyle(&＃39;C4&＃39;)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getStyle(&＃39;D4&＃39;)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getStyle(&＃39;E4&＃39;)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
设置border的color
$objPHPExcel->getActiveSheet()->getStyle(&＃39;D13&＃39;)->getBorders()->getLeft()->getColor()->setARGB(&＃39;FF993300&＃39;); $objPHPExcel->getActiveSheet()->getStyle(&＃39;D13&＃39;)->getBorders()->getTop()->getColor()->setARGB(&＃39;FF993300&＃39;); $objPHPExcel->getActiveSheet()->getStyle(&＃39;D13&＃39;)->getBorders()->getBottom()->getColor()->setARGB(&＃39;FF993300&＃39;); $objPHPExcel->getActiveSheet()->getStyle(&＃39;E13&＃39;)->getBorders()->getTop()->getColor()->setARGB(&＃39;FF993300&＃39;); $objPHPExcel->getActiveSheet()->getStyle(&＃39;E13&＃39;)->getBorders()->getBottom()->getColor()->setARGB(&＃39;FF993300&＃39;); $objPHPExcel->getActiveSheet()->getStyle(&＃39;E13&＃39;)->getBorders()->getRight()->getColor()->setARGB(&＃39;FF993300&＃39;);
设置填充颜色
$objPHPExcel->getActiveSheet()->getStyle(&＃39;A1&＃39;)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle(&＃39;A1&＃39;)->getFill()->getStartColor()->setARGB(&＃39;FF808080&＃39;); $objPHPExcel->getActiveSheet()->getStyle(&＃39;B1&＃39;)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle(&＃39;B1&＃39;)->getFill()->getStartColor()->setARGB(&＃39;FF808080&＃39;);
加图片
$objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName(&＃39;Logo&＃39;); $objDrawing->setDescription(&＃39;Logo&＃39;); $objDrawing->setPath(&＃39;./images/officelogo.jpg&＃39;); $objDrawing->setHeight(36); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName(&＃39;Paid&＃39;); $objDrawing->setDescription(&＃39;Paid&＃39;); $objDrawing->setPath(&＃39;./images/paid.png&＃39;); $objDrawing->setCoordinates(&＃39;B15&＃39;); $objDrawing->setOffsetX(110); $objDrawing->setRotation(25); $objDrawing->getShadow()->setVisible(true); $objDrawing->getShadow()->setDirection(45); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
//处理中文输出问题
需要将字符串转化为UTF-8编码，才能正常输出，否则中文字符将输出为空白，如下处理：
$str = iconv(&＃39;gb2312&＃39;, &＃39;utf-8&＃39;, $str);
或者你可以写一个函数专门处理中文字符串：
function convertUTF8($str)
{
if(empty($str)) return &＃39;;
return iconv(&＃39;gb2312&＃39;, &＃39;utf-8&＃39;, $str);
}//从数据库输出数据处理方式
从数据库读取数据如：
$db = new Mysql($dbconfig);
$sql = &＃39;SELECT * FROM 表名&＃39;;
$row = $db->GetAll($sql); // $row 为二维数组
$count = count($row);
for ($i = 2; $i <= $count+1; $i++) {
$objPHPExcel->getActiveSheet()->setCellValue(&＃39;A&＃39; . $i, convertUTF8($row[$i-2][1]));
$objPHPExcel->getActiveSheet()->setCellValue(&＃39;B&＃39; . $i, convertUTF8($row[$i-2][2]));
$objPHPExcel->getActiveSheet()->setCellValue(&＃39;C&＃39; . $i, convertUTF8($row[$i-2][3]));
$objPHPExcel->getActiveSheet()->setCellValue(&＃39;D&＃39; . $i, convertUTF8($row[$i-2][4]));
$objPHPExcel->getActiveSheet()->setCellValue(&＃39;E&＃39; . $i, convertUTF8(date(&＃39;Y-m-d&＃39;, $row[$i-2][5])));
$objPHPExcel->getActiveSheet()->setCellValue(&＃39;F&＃39; . $i, convertUTF8($row[$i-2][6]));
$objPHPExcel->getActiveSheet()->setCellValue(&＃39;G&＃39; . $i, convertUTF8($row[$i-2][7]));
$objPHPExcel->getActiveSheet()->setCellValue(&＃39;H&＃39; . $i, convertUTF8($row[$i-2][8]));
}在默认sheet后，创建一个worksheet
echo date(&＃39;H:i:s&＃39;) . &＃39; Create new Worksheet objectn&＃39;; $objPHPExcel->createSheet(); $objWriter = PHPExcel_IOFactory::createWriter($objExcel, &＃39;Excel5&＃39;); $objWriter-save(&＃39;php://output&＃39;);
教程地址：
欢迎转载！但请带上文章地址^^
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有 