Php excel operations phpExcel usage

Source: Internet
Author: User
Tags getcolor php excel

The following are the usage methods

Include 'phpexcel. Php ';

Include 'phpexcel/Writer/excel2007.php ';

// Or include 'phpexcel/Writer/excel5.php'; for output. Xls

Create an excel file

$ ObjPHPExcel = new PHPExcel ();

Save the 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 & Prime ;);

Header ("Cache-Control: must-revalidate, post-check = 0, pre-check = 0 & Prime ;);

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

---------------------------------------

Set excel attributes:

Created

$ ObjPHPExcel-> getProperties ()-> setCreator ("Maarten Balliauw ");

Last modified

$ ObjPHPExcel-> getProperties ()-> setLastModifiedBy ("Maarten Balliauw ");

Title

$ ObjPHPExcel-> getProperties ()-> setTitle ("Office 2007 XLSX Test Document ");

Question

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

Type

$ ObjPHPExcel-> getProperties ()-> setCategory ("Test result file ");

---------------------------------------

Set the current sheet

$ ObjPHPExcel-> setActiveSheetIndex (0 );

Set sheet name

$ ObjPHPExcel-> getActiveSheet ()-> setTitle ('simple ');

Set the cell value

$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('A1 & prime;, 'string ');

$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('A2 & prime;, 12 );

$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('A3 & prime;, true );

$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('c5 & prime;, '= SUM (C2: C4 )');

$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('b8 & prime;, '= MIN (B2: C5 )');

Merge cells

$ ObjPHPExcel-> getActiveSheet ()-> mergeCells ('a18: E22 & prime ;);

Separate cells

$ ObjPHPExcel-> getActiveSheet ()-> unmergeCells ('a28: B28 & prime ;);

Common Excel generation methods

<?
// Set the include path of the PHPExcel class library
Set_include_path ('.'. PATH_SEPARATOR.
'D: ZealPHP_LIBS '. PATH_SEPARATOR.
Get_include_path ());


Require_once 'phpexcel. Php ';


// Uncomment
/// Require_once 'phpexcel/Writer/excel5.php'; // used for other earlier versions of xls
// Or
/// Require_once 'phpexcel/Writer/excel2007.php'; // used for excel-2007 format

// Create a processing object instance
$ ObjExcel = new PHPExcel ();

// Create a file format to write the object instance, uncomment
//// $ ObjWriter = new PHPExcel_Writer_Excel5 ($ objExcel); // used for other version formats
// Or
//// $ ObjWriter = new PHPExcel_Writer_Excel2007 ($ objExcel); // used in 2007 format
// $ ObjWriter-> setOffice2003Compatibility (true );

//*************************************
// Set the basic attributes of the document
$ ObjProps = $ objExcel-> getProperties ();
$ ObjProps-> setCreator ("Zeal Li ");
$ ObjProps-> setLastModifiedBy ("Zeal Li ");
$ ObjProps-> setTitle ("Office XLS Test Document ");
$ ObjProps-> setSubject ("Office XLS Test Document, Demo ");
$ ObjProps-> setDescription ("Test document, generated by PHPExcel .");
$ ObjProps-> setKeywords ("office excel PHPExcel ");
$ ObjProps-> setCategory ("Test ");

//*************************************
// Set the current sheet index for subsequent content operations.
// Display the call only when multiple sheets are used.
// By default, PHPExcel will automatically create the first sheet and set SheetIndex = 0
$ ObjExcel-> setActiveSheetIndex (0 );


$ ObjActSheet = $ objExcel-> getActiveSheet ();

// Set the name of the current active sheet
$ ObjActSheet-> setTitle ('test Sheet ');

//*************************************
// Set the cell content
//
// PHPExcel automatically determines the cell content type based on the input content
$ ObjActSheet-> setCellValue ('A1', 'string content'); // String content
$ ObjActSheet-> setCellValue ('A2 ', 26); // value
$ ObjActSheet-> setCellValue ('A3 ', true); // boolean value
$ ObjActSheet-> setCellValue ('A4 ',' = SUM (A2: A2) '); // formula

// Explicitly specify the content type
$ ObjActSheet-> setCellValueExplicit ('a5 ', '123 ',
PHPExcel_Cell_DataType: TYPE_STRING );

// Merge cells
$ ObjActSheet-> mergeCells ('b1: c22 ');

// Separate cells
$ ObjActSheet-> unmergeCells ('b1: c22 ');

//*************************************
// Set the cell style
//

// Set the width
$ ObjActSheet-> getColumnDimension ('B')-> setAutoSize (true );
$ ObjActSheet-> getColumnDimension ('A')-> setWidth (30 );

$ ObjStyleA5 = $ objActSheet-> getStyle ('a5 ');

// Set the numeric format of the cell content.
//
// If PHPExcel_Writer_Excel5 is used to generate the content,
// Note that the const variable defined in the PHPExcel_Style_NumberFormat class
// Other types of custom formatting methods can be used normally, but when setFormatCode
// When FORMAT_NUMBER is used, the actual effect is not set to "0 ". Yes
// Modify the getXf ($ style) method in the source code of the PHPExcel_Writer_Excel5_Format class,
// Add one before if ($ this-> _ BIFF_version = 0x0500) {(near row 363rd)
// Line of code:
// If ($ ifmt = '0') $ ifmt = 1;
//
// Set the format to PHPExcel_Style_NumberFormat: FORMAT_NUMBER to avoid large numbers.
// The data is displayed in scientific notation. The following setAutoSize method can be used to display the content of each row.
// All are displayed based on the original content.
$ ObjStyleA5
-> GetNumberFormat ()
-> SetFormatCode (PHPExcel_Style_NumberFormat: FORMAT_NUMBER );

// Set the font
$ ObjFontA5 = $ objStyleA5-> getFont ();
$ ObjFontA5-> setName ('courier new ');
$ ObjFontA5-> setSize (10 );
$ ObjFontA5-> setBold (true );
$ ObjFontA5-> setUnderline (PHPExcel_Style_Font: UNDERLINE_SINGLE );
$ ObjFontA5-> getColor ()-> setARGB ('ff9999999999 ');

// Set alignment
$ ObjAlignA5 = $ objStyleA5-> getAlignment ();
$ ObjAlignA5-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_RIGHT );
$ ObjAlignA5-> setVertical (PHPExcel_Style_Alignment: VERTICAL_CENTER );

// Set the border
$ ObjBorderA5 = $ objStyleA5-> getBorders ();
$ ObjBorderA5-> getTop ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
$ ObjBorderA5-> getTop ()-> getColor ()-> setARGB ('ffff000000'); // color
$ ObjBorderA5-> getBottom ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
$ ObjBorderA5-> getLeft ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
$ ObjBorderA5-> getRight ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );

// Set the fill color
$ ObjFillA5 = $ objStyleA5-> getFill ();
$ ObjFillA5-> setFillType (PHPExcel_Style_Fill: FILL_SOLID );
$ ObjFillA5-> getStartColor ()-> setARGB ('ffeeeeeee ');

// Copy the style information from the specified cell.
$ ObjActSheet-> duplicateStyle ($ objStyleA5, 'b1: C22 ');


//*************************************
// Add an image
$ ObjDrawing = new PHPExcel_Worksheet_Drawing ();
$ ObjDrawing-> setName ('zealimg ');
$ ObjDrawing-> setDescription ('image inserted by zeal ');
$ ObjDrawing-> setPath ('./zeali.net.logo.gif ');
$ ObjDrawing-> setHeight (36 );
$ ObjDrawing-> setCoordinates ('c23 ');
$ ObjDrawing-> setOffsetX (10 );
$ ObjDrawing-> setRotation (15 );
$ ObjDrawing-> getShadow ()-> setVisible (true );
$ ObjDrawing-> getShadow ()-> setDirection (36 );
$ ObjDrawing-> setWorksheet ($ objActSheet );


// Add a new worksheet
$ ObjExcel-> createSheet ();
$ ObjExcel-> getSheet (1)-> setTitle ('test 2 ');

// Protect cells
$ ObjExcel-> getSheet (1)-> getProtection ()-> setSheet (true );
$ ObjExcel-> getSheet (1)-> protectCells ('A1: c22', 'phpexcel ');


//*************************************
// Output Content
//
$ OutputFileName = "output.xls ";
// To the file
/// $ ObjWriter-> save ($ outputFileName );
// Or
// Go to the browser
/// Header ("Content-Type: application/force-download ");
/// Header ("Content-Type: application/octet-stream ");
/// Header ("Content-Type: application/download ");
/// Header ('content-Disposition: inline; filename = "'. $ outputFileName .'"');
/// Header ("Content-Transfer-Encoding: binary ");
/// Header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT ");
/// Header ("Last-Modified:". gmdate ("D, d m y h: I: s"). "GMT ");
/// Header ("Cache-Control: must-revalidate, post-check = 0, pre-check = 0 ");
/// Header ("Pragma: no-cache ");
//// $ ObjWriter-> save ('php: // output ');

?>

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.