Home > Article > Backend Development > php excel class phpExcel usage introduction, excelphpexcel_PHP tutorial
phpExcel operation excel can easily add pictures, supporting jpg gif png format.
Download address: http://www.codeplex.com/PHPExcel
The following are summarized several usage methods
include 'PHPExcel.php';
include 'PHPExcel/Writer/Excel2007.php';
//or include 'PHPExcel/Writer/ Excel5.php';
Create an excel for output .xls
$objPHPExcel = new PHPExcel();
Save excel—2007 format
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
//Or $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); Non-2007 format
$objWriter->save("xxx.xlsx");
Output directly to the browser
$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');
——
Friends who are not familiar with the phpexcel class library can read the content in the "phpexcel Chinese Help Manual". For specific examples, you can find relevant examples in the phpexcel Rapid Development Guide.
Set excel properties:
Creator
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
Last modified by
$objPHPExcel->getProperties ()->setLastModifiedBy("Maarten Balliauw");
Title
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
Title
$ objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
Description
$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
Keywords
$objPHPExcel->getProperties()->setKeywords("office 2007 openxml php");
Category
$objPHPExcel->getProperties( )->setCategory("Test result file");
———————————————————————————————— ————–
Set the current sheet
$objPHPExcel->setActiveSheetIndex(0);
Set the name of the sheet
$objPHPExcel->getActiveSheet()->setTitle('Simple ');
Set the value of the cell
$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)');
Merge cells
$objPHPExcel->getActiveSheet()->mergeCells('A18:E22′);
Detached cells
$objPHPExcel->getActiveSheet()->unmergeCells('A28:B28′ );
Protect 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 format
// Set cell number formats
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′ );
Set width
// Set column widths
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D' )->setWidth(12);
Set 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);
Set 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);
//Vertically centered
$objPHPExcel->getActiveSheet()->getStyle('A18′)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
设置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′);
>>> 更多php教程内容,请关注本站php编程栏目。
加图片
$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());
在默认sheet后,创建一个worksheet
echo date('H:i:s') . " Create new Worksheet objectn";
$objPHPExcel->createSheet();
$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
$objWriter-save('php://output');
Summary php export Excel php import Excel PhpExcel instructions PhpExcel user manual 2009/03/06 02:37 AM Method 1: Features, simplicity, worry-free,
header( "Content-type:application/vnd.ms-excel");
header("Content-Disposition:attachment;filename=test_data.xls");
$tx='header';
echo $tx."\n\n";
//The output content is as follows:
echo "Name"."\t";
echo "Age"."\t";
echo "Education"."\t";
echo "\n";
echo "Zhang San"."\t";
echo "25"."\t";
echo "undergraduate"."\t";
?>
Method 2: Quote the small class library recommended in Google Code (roughly the same as method 1, more complicated)
code.google.com/p/php-excel/downloads/list
Method 3: PHPEXCEL class library, powerful, supports win Excel2003, Win Excel2007.
www.codeplex.com/ PHPExcel
//Set the include path of the PHPExcel class library
set_include_path('.'. PATH_SEPARATOR .
'D:\Zeal\PHP_LIBS' . PATH_SEPARATOR .
get_include_path());
/**
* The following are usage examples. There are different options for lines starting with ////. Please
* open the corresponding line according to actual needs. annotation.
* If using Excel5, the output content should be GBK encoded.
*/
require_once 'PHPExcel.php';
// uncomment
////require_once 'PHPExcel/Writer/Excel5.ph...the rest of the text>>
phpexcel PHPExcel is a very powerful MS Office Excel document generation library. When you need to output data in a more complex format, PHPExcel is a good choice. However, its use is relatively complicated. List them to remember.
-- PHP Code --
//Set the include path of the PHPExcel class library
set_include_path('.'. PATH_SEPARATOR .
'D:\Zeal\PHP_LIBS' . PATH_SEPARATOR .
get_include_path());
/**
* The following are usage examples. There are different optional methods for lines starting with ////. Please
* open according to actual needs. Comments for corresponding lines.
* If using Excel5, the output content should be GBK encoded.
*/
require_once 'PHPExcel.php';
// uncomment
////require_once 'PHPExcel/Writer/Excel5.php'; // For other lower versions of xls
// or
////require_once 'PHPExcel/Writer/Excel2007.php'; // For excel-2007 format
// Create a processing object instance
$objExcel = new PHPExcel();
// Create file format writing object instance, uncomment
////$objWriter = new PHPExcel_Writer_Excel5($objExcel); // Used for other version formats
// or
/// /$objWriter = new PHPExcel_Writer_Excel2007($objExcel); // For 2007 format
//$objWriter->setOffice2003Compatibility(true);
//************* **********************
//Set the basic properties of the document
$objProps = $objExcel->getProperties();
$objProps->setCreator("Zeal Li"); //Set the author
$objProps->setLastModifiedBy("Zeal Li"); //Set the last modified person
$objProps->setTitle ("Office XLS Test Document"); //Set the title
$objProps->setSubject("Office XLS Test Document, Demo"); //Set the theme
$objProps->setDescription("Test document , generated by ......the rest of the text>>