操作Excel的PHP类库phpExcel中文帮助手册指南

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;);

教程地址：

欢迎转载！但请带上文章地址^^