PhpExcel usage-php Tutorial

PhpExcel usage

The following are the methods to use 'phpexcel. php '; include 'phpexcel/Writer/excel2007.php'; // or include 'phpexcel/Writer/excel5.php'; create an excel $ objPHPExcel = new PHPExcel () in the output .xls file; save excel? 2007 format $ objWriter = new PHPExcel_Writer_Excel2007 ($ objPHPExcel); // or $ objWriter = new preview ($ objPHPExcel); non-2007 format $ objWriter-> save ("xxx.xlsx "); directly output 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-Ty Pe: 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: Creator $ objPHPExcel-> getProperties ()-> setCreator ("Maarten Balliauw"); last modifier $ 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 us Ing PHP classes. "); keyword $ objPHPExcel-> getProperties ()-> setKeywords (" office 2007 openxml php "); type $ objPHPExcel-> getProperties () -> setCategory ("Test result file ");????????????????????????????? ?????????? Set the current sheet $ objPHPExcel-> setActiveSheetIndex (0); set the sheet name $ objPHPExcel-> getActiveSheet ()-> setTitle ('simple '); set the cell value $ 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 '); split 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 '); format // Set cell number formatsecho 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 '); 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 (metadata: HORIZONTAL_RIGHT); $ objPHPExcel-> getActiveSheet () -> getStyle ('d12')-> getAlignment ()-> setHorizontal (metadata: HORIZONTAL_RIGHT); $ objPHPExcel-> getActiveSheet ()-> getStyle ('d13 ') -> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_RIGHT); $ objPHPExcel-> getActiveSheet ()-> getStyle ('a18')-> getAlignment () -> setHorizontal (ALIGN: HORIZONTAL_JUSTIFY); // vertically centered $ objPHPExcel-> getActiveSheet ()-> getStyle ('a18')-> getAlignment ()-> setVertical (ALIGN:: VERTICAL_CENTER); set 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 ); set 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 '); set the fill color $ objPHPExcel-> getActiveSheet ()-> getStyle ('A1')-> getFill ()-> setFillType (PHPExcel_Style_Fill :: FILL_SOLID); $ objPHPExcel-> getActiveSheet ()-> getStyle ('A1')-> getFill ()-> getStartColor ()-> setARGB ('ff80808080 '); $ objPHPExcel-> getActiveSheet ()-> getStyle ('b1 ')-> getFill ()-> setFillType (PHPExcel_Style_Fill: FILL_SOLID); $ objPHPExcel-> getActiveSheet () -> getStyle ('b1 ')-> getFill ()-> getStartColor ()-> setARGB ('ff808080'); add image $ 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 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 (); // to handle Chinese output problems, you need to convert the string to UTF-8 encoding for normal output, otherwise, the Chinese characters will be output as blank and processed as follows: $ str = iconv ('gb2312', 'utf-8', $ str ); alternatively, you can write a function to process a Chinese string: function convertUTF8 ($ str) {if (empty ($ str) return ''; return iconv ('gb2312 ', 'utf-8', $ str);} // read data from the database using the data processing method output from the database, for example, $ db = new Mysql ($ dbconfig ); $ SQL = "SELECT * FROM table name"; $ row = $ db-> GetAll ($ SQL); // $ row is a two-dimensional array $ count = count ($ row ); for ($ I = 2; $ I <= $ count + 1; $ I ++) {$ objPHPExcel-> getActiveSheet ()-> setCellValue ('A '. $ I, convertUTF8 ($ row [$ i-2] [1]); $ objPHPExcel-> getActiveSheet ()-> setCellValue ('B '. $ I, convertUTF8 ($ row [$ i-2] [2]); $ objPHPExcel-> getActiveSheet ()-> setCellValue ('C '. $ I, convertUTF8 ($ row [$ i-2] [3]); $ objPHPExcel-> getActiveSheet ()-> setCellValue ('D '. $ I, convertUTF8 ($ row [$ i-2] [4]); $ objPHPExcel-> getActiveSheet ()-> setCellValue ('E '. $ I, convertUTF8 (date ("Y-m-d", $ row [$ i-2] [5]); $ objPHPExcel-> getActiveSheet () -> setCellValue ('F '. $ I, convertUTF8 ($ row [$ i-2] [6]); $ objPHPExcel-> getActiveSheet ()-> setCellValue ('g '. $ I, convertUTF8 ($ row [$ i-2] [7]); $ objPHPExcel-> getActiveSheet ()-> setCellValue ('H '. $ I, convertUTF8 ($ row [$ i-2] [8]);} after the default sheet, create a worksheetecho date ('H: I: s '). "Create new Worksheet object \ n"; $ objPHPExcel-> createSheet (); $ objWriter = PHPExcel_IOFactory: createWriter ($ objExcel, 'excel5 '); $ objWriter-save ('php: // output ');