深入解读PHP类phpExcel应用

PHP语言在实际编写中经常会与数据库打上交道。下面我们就来一起看看PHP类phpExcel的具体使用方法。phpExcel，操作excel很方便，尤其是可以方便的加入图片，支持jpg gif png格式。
下面是总结的几个PHP类phpExcel使用方法

创建一个excel

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

$objWriter = new PHPExcel_Writer_Excel2007( $objPHPExcel );
$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 " );
PHP类phpExcel设置excel的属性：

创建人
$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 ”);

PHP类phpExcel设置当前的sheet
$objPHPExcel -> setActiveSheetIndex( 0 );

设置sheet的name
$objPHPExcel -> getActiveSheet() -> setTitle(’Simple’);

设置单元格的值
$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’);

设置格式
// Set cell number formats
echo date (’H : i : s’) . ” Set cell number formats\n”;
$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
// Set column widths
$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′);

PHP类phpExcel设置填充颜色
$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());

PHP类phpExcel在默认sheet后，创建一个worksheet
echo date (’H : i : s’) . ” Create new Worksheet object \n”;
$objPHPExcel -> createSheet();

$objWriter = PHPExcel_IOFactory :: createWriter( $objExcel , ‘Excel5′);
$objWriter - save(‘php : // output’);

http://www.pkwind.com/insight-into-application-of-php-classes-phpexcel/

转载于:https://www.cnblogs.com/xwblog/archive/2011/03/05/1971887.html