Php excel operations phpExcel usage

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

?>