PHPExcel help manual | PHPExcel usage,

PHPExcel help manual | 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 the excel-2007 format $ objWriter = new PHPExcel_Writer_Excel2007 ($ objPHPExcel); // or $ objWriter = new PHPExcel_Writer_Excel5 ($ 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-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 the excel attributes: Creator $ objPHPExcel-> getProperties () -> setCreator ("Maarten Balliauw"); last modifier $ 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-> getPro Perties ()-> setDescription ("Test document for Office 2007 XLSX, generated using 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 name of the sheet $ 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'); separate cells $ objPHPExcel-> getActiveSheet ()-> unmergeCell S ('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 ');