PHP处理Excel插件PHPExcel常用方法详解

2.PHPExcel
开发包Tests目录有详细使用实例，支持中文，注意文件编码，文件保存为utf-8。
写excel

//Include classrequire_once('Classes/PHPExcel.php');require_once('Classes/PHPExcel/Writer/Excel2007.php');$objPHPExcel = new PHPExcel();

//设置文件属性$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");

//添加数据$objPHPExcel->setActiveSheetIndex(0);$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Hello');//可以指定位置$objPHPExcel->getActiveSheet()->setCellValue('A2', true);$objPHPExcel->getActiveSheet()->setCellValue('A3', false);$objPHPExcel->getActiveSheet()->setCellValue('B2', 'world!');$objPHPExcel->getActiveSheet()->setCellValue('B3', 2);$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Hello');$objPHPExcel->getActiveSheet()->setCellValue('D2', 'world!');

//循环for($i = 1;$i<200;$i++) { $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $i); $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, 'Test value');}

//日期格式化$objPHPExcel->getActiveSheet()->setCellValue('D1', time());$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE

//添加注释$objPHPExcel->getActiveSheet()->getComment('E11')->setAuthor('PHPExcel');$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun('PHPExcel:');$objCommentRichText->getFont()->setBold(true);$objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun("\r\n");$objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun('Total amount on the current invoice, excluding VAT.');//添加文字 可设置样式$objRichText = new PHPExcel_RichText( $objPHPExcel->getActiveSheet()->getCell('A18') );$objRichText->createText('This invoice is ');$objPayable = $objRichText->createTextRun('payable within thirty days after the end of the month');$objPayable->getFont()->setBold(true);$objPayable->getFont()->setItalic(true);$objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN ) );$objRichText->createText(', unless specified otherwise on the invoice.');

//合并分离单元格$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');$objPHPExcel->getActiveSheet()->unmergeCells('A18:E22');

//保护单元格$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);$objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel');

//数字格式化$objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);$objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('E4'), 'E5:E13' );

//设置列宽度$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
//设置字体$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('D11')->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);$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setWrapText(true);
//设置列边框$objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);$objPHPExcel->getActiveSheet()->getStyle('A10')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);$objPHPExcel->getActiveSheet()->getStyle('E10')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);

//添加链接$objPHPExcel->getActiveSheet()->setCellValue('E26', 'http://www.phpexcel.net');$objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl('http://www.phpexcel.net');$objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setTooltip('Navigate to website');$objPHPExcel->getActiveSheet()->getStyle('E26')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
//添加图片$objDrawing = new PHPExcel_Worksheet_Drawing();$objDrawing->setName('Logo');$objDrawing->setDescription('Logo');$objDrawing->setPath('./images/officelogo.jpg');$objDrawing->setHeight(36);$objDrawing->setCoordinates('B15');$objDrawing->setOffsetX(110);$objDrawing->setRotation(25);$objDrawing->getShadow()->setVisible(true);$objDrawing->getShadow()->setDirection(45);$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());