Method 2: phpExcel

Method 2: phpExcel

Require_once '. /phpexcel/PHPExcel. php '; // first create a new object PHPExcel object $ objPHPExcel = new PHPExcel (); // set some attributes of the file, in the xls file --> properties --> details, you can see these values. In the xml table, $ objPHPExcel-> getProperties () // gets the file property object, set the resource-> setCreator ("Maarten Balliauw") // set the creator of the file-> setLastModifiedBy ("Maarten Balliauw ") // set the last modifier-> setTitle ("Office 2007 XLSX Test Document") // set the title-> setSubject ("Office 2007 XLSX Test Document ") // Set topic-> setDescription ("Test document for Office 2007 XLSX, generated using PHP classes. ") // set remarks-> setKeywords (" office 2007 openxml php ") // set the tag-> setCategory (" Test result file "); // set category // location aaa * provide an anchor for the following code location // Add data to the table $ objPHPExcel-> setActiveSheetIndex (0) // set the first built-in table (multiple tables can exist in an xls file) as active-> setCellValue ('a1', 'Hello ') // set data for cells in the table-> setCellValue ('b2', 'World! ') // The data format can be string-> setCellValue ('c1', 12) // number type-> setCellValue ('d2 ', 12) //-> setCellValue ('d3 ', true) // Boolean-> setCellValue ('d4', '= SUM (C1: D2 )'); // formula // obtain the table of the current activity. Note that $ objActSheet = $ objPHPExcel-> getActiveSheet () is often used in the following tutorial (); // position bbb * provide an anchor for the following code position // set the name of the currently active table $ objActSheet-> setTitle ('simple222222'); the Code is not over yet, you can copy the following code to determine what we will do. What we will do is 1. directly generate a file $ objWriter = PHPExcel_IOFactory: createWriter ($ obj PHPExcel, 'excel2007 '); $ objWriter-> save('myexchel.xlsx'); 2. the system prompts you to download the file excel 2003. xls // generate the xls file header ('content-Type: application/vnd. ms-excel '); header ('content-Disposition: attachment; filename = "01simple.xls"'); header ('cache-Control: max-age = 0 '); $ objWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'excel5'); $ objWriter-> save ('php: // output'); exit; excel 2007. xlsx // generate xlsx file in 2007excel format h Eader ('content-Type: application/vnd. openxmlformats-officedocument.spreadsheetml.sheet '); header ('content-Disposition: attachment; filename = "01simple.xlsx"'); header ('cache-Control: max-age = 0 '); $ objWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'excel2007 '); $ objWriter-> save ('php: // output'); exit; pdf file // download a pdf file header ('content-Type: application/pdf '); header ('content-Disposition: attachmen T; filename = "01simpleexample" '); header ('cache-Control: max-age = 0'); $ objWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'pdf '); $ objWriter-> save ('php: // output'); exit; // generate a PDF file $ objWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'pdf '); $ objWriter-> save('a.pdf'); CSV file $ objWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'csv')-> setDelimiter (',') // set the separator-> setEnclosure ('"') // set the package Break-> setLineEnding ("\ r \ n") // set the row separator-> setSheetIndex (0) // set the activity table-> save (str_replace ('. php ', '.csv', _ FILE _); html file $ objWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'html '); // convert $ objPHPEcel to $ objWriter-> setSheetIndex (0) in html format; // set the activity table // $ objWriter-> setImagesRoot ('HTTP: // www.example.com '); $ objWriter-> save (str_replace ('. php ', '.htm', _ FILE _); // save the FILE and set the table style and data format. Set the default font and text size. Anchor: aaa $ obj PHPExcel-> getDefaultStyle ()-> getFont ()-> setName ('arial'); $ objPHPExcel-> getDefaultStyle ()-> getFont ()-> setSize (20 ); date Format anchor: bbb // get the second value variable $ dateTimeNow = time (); // set the three tables to the current actual date format, time format, date, and time format. // set the cell value to PHPExcel_Shared_Date :: the PHPToExcel method converts the value in the excel format, and then uses the numeric style in the style of the cell to set the display format $ objActSheet-> setCellValue ('c9 ', PHPExcel_Shared_Date :: PHPToExcel ($ dateTimeNow); $ objActSheet-> getStyle ('c9 ')-> getNumber Format ()-> setFormatCode (encoding: FORMAT_DATE_YYYYMMDD2); $ objActSheet-> setCellValue ('c10', PHPExcel_Shared_Date: PHPToExcel ($ dateTimeNow )); $ objActSheet-> getStyle ('c10')-> getNumberFormat ()-> setFormatCode (encoding: FORMAT_DATE_TIME4); $ objActSheet-> setCellValue ('c10', PHPExcel_Shared_Date :: PHPToExcel ($ dateTimeNow); $ objActSheet-> getStyle ('c10')-> getNumber Format ()-> setFormatCode (PHPExcel_Style_NumberFormat: FORMAT_DATE_TIME4); // set the number Format from E4 to E13 to EUR $ objPHPExcel-> getActiveSheet ()-> getStyle ('e4: e13 ')-> getNumberFormat ()-> setFormatCode (PHPExcel_Style_NumberFormat: FORMAT_CURRENCY_EUR_SIMPLE); set the column width anchor: bbb $ objActSheet-> getColumnDimension (' B ') -> setAutoSize (true); // adaptive content $ objActSheet-> getColumnDimension ('A')-> setWidth (30 ); // 30-width setting of the print header and footer. Anchor: bbb // set the page for printing. The & * in the eyebrow footer (you can print the preview to see the effect after setting) in the string seems to be a variable $ objActSheet-> getHeaderFooter () -> setOddHeader ('& L & G & C & HPlease treat this document as confidential! '); $ ObjActSheet-> getHeaderFooter ()-> setOddFooter (' & L & B '. $ objPHPExcel-> getProperties ()-> getTitle (). '& RPage & P of & n'); set the direction and size of the page text. Anchor: bbb $ objPHPExcel-> getActiveSheet ()-> getPageSetup ()-> setOrientation (PHPExcel_Worksheet_PageSetup:: ORIENTATION_LANDSCAPE); $ objPHPExcel-> getActiveSheet ()-> getPageSetup ()-> setPaperSize (PHPExcel_Worksheet_PageSetup: PAPERSIZE_A4 ); // A4 paper size: Invalid anchor in valid wps in the Image office added to the header: Bbb $ objDrawing = new PHPExcel_Worksheet_HeaderFooterDrawing (); $ objDrawing-> setName ('phpexcel logo '); $ objDrawing-> setPath ('. /images/phpexcel_logo.gif '); $ objDrawing-> setHeight (36); $ objPHPExcel-> getActiveSheet ()-> getHeaderFooter ()-> addImage ($ objDrawing, identifier :: IMAGE_HEADER_LEFT); set the cell annotation anchor: bbb // Add annotation to the cell $ objPHPExcel-> getActiveSheet ()-> getComment ('e13')-> setAuthor ('phpexcel' ); // Set the author $ objCommentRichText = $ objPHPExcel-> getActiveSheet ()-> getComment ('e13')-> getText ()-> createTextRun ('phpexcel :'); // Add annotation $ objCommentRichText-> getFont ()-> setBold (true); // bold existing annotation $ objPHPExcel-> getActiveSheet ()-> getComment ('e13 ') -> getText ()-> createTextRun ("\ r \ n"); // Add more comments $ objPHPExcel-> getActiveSheet ()-> getComment ('e13 ') -> getText ()-> createTextRun ('total amount on the current invoice, incl Uding VAT. '); $ objPHPExcel-> getActiveSheet ()-> getComment ('e13')-> setWidth ('100pt'); // you can specify the width and height of an annotation, invalid in office valid in wps $ objPHPExcel-> getActiveSheet ()-> getComment ('e13')-> setHeight ('100pt '); $ objPHPExcel-> getActiveSheet () -> getComment ('e13')-> setMarginLeft ('150pt'); $ objPHPExcel-> getActiveSheet ()-> getComment ('e13')-> getFillColor () -> setRGB ('eeeeeee'); // sets the background color, which is invalid in wps in office. Adding text blocks to office is invalid in wps. Anchor: bbb // probably the translation creates a rich text box office valid wps invalid $ objRichText = new PHPExcel_RichText (); $ objRichText-> createText ('this invoice is '); // write text // Add text and set the bold italic and text color of the text $ objPayable = $ objRichText-> createTextRun ('payable within thirty days after the end of the Month '); $ objPayable-> getFont ()-> setBold (true); $ objPayable-> getFont ()-> setItalic (true); $ objPayable-> getFont () -> setColor (new PHPExcel_Style_Color (PHPExcel_Styl E_Color: COLOR_DARKGREEN); $ objRichText-> createText (', unless specified otherwise on the invoice. '); // write the text to cell A18 $ objPHPExcel-> getActiveSheet ()-> getCell ('a18')-> setValue ($ objRichText ); PHPExcel <wbr> merge learning notes and split cells. Anchor: bbb $ objPHPExcel-> getActiveSheet ()-> mergeCells ('a28: b28'); // A28: b28 merge $ objPHPExcel-> getActiveSheet ()-> unmergeCells ('a28: b28'); // A28: B28 then split the cell password to protect the anchor: bbb // cell password protection is not allowed to be modified $ objPHPEx Cel-> getActiveSheet ()-> getProtection ()-> setSheet (true); // to protect any table, set it to $ objPHPExcel-> getActiveSheet () -> protectCells ('a3: e13', 'phpexcel '); // set A3 to E13 to protect the encrypted password from PHPExcel $ objPHPExcel-> getActiveSheet () -> getStyle ('b1 ')-> getProtection ()-> setLocked (PHPExcel_Style_Protection: PROTECTION_UNPROTECTED); // remove the Protection Setting cell font anchor: bbb // set the text font of B1 to Candara, and the bold underline on the 20th contains the background color $ 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_WH ITE); text alignment: bbb $ objPHPExcel-> getActiveSheet ()-> getStyle ('d11')-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_RIGHT ); // align horizontally $ objPHPExcel-> getActiveSheet ()-> getStyle ('a18')-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_JUSTIFY ); // align the two ends horizontally. $ objPHPExcel-> getActiveSheet ()-> getStyle ('a18')-> getAlignment ()-> setVertical (PHPExcel_Style_Alignment: VERTICAL _ CENTER); // set the cell border anchor in the CENTER of the vertical direction: bbb $ styleThinBlackBorderOutline = array ('borders '=> array ('outline' => array ('style' => PHPExcel_Style_Border: BORDER_THIN, // set the border style // 'style' => PHPExcel_Style_Border: BORDER_THICK, another style 'color' => array ('arg' => 'ff000000 '), // set the border color),),); $ objPHPExcel-> getActiveSheet ()-> getStyle ('a4: e10')-> applyFromArray ($ styleThinBlackBorderOutline ); background fill color anchor: bbb // Set the filled style and background color $ objPHPExcel-> getActiveSheet ()-> getStyle ('a1: E1 ')-> getFill ()-> setFillType (PHPExcel_Style_Fill: FILL_SOLID ); $ objPHPExcel-> getActiveSheet ()-> getStyle ('a1: E1 ')-> getFill ()-> getStartColor ()-> setARGB ('ff80808080 '); comprehensive setting example $ objPHPExcel-> getActiveSheet ()-> getStyle ('a3: E3 ') -> applyFromArray (array ('font' => array ('bold '=> true), 'alignment' => array ('horizontal '=> PHPExcel_Style_Alignmen T: HORIZONTAL_RIGHT,), 'borders '=> array ('top' => array ('style' => PHPExcel_Style_Border: BORDER_THIN )), 'fill' => array ('type' => PHPExcel_Style_Fill: FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array ('arg' => 'ffa0a0 '), 'endcolor' => array ('arg' => 'ffffff ')))); PHPExcel <wbr> set the url hyperlink to the cell content. Anchor: bbb $ objActSheet-> getCell ('e26')-> getHyperlink ()-> setUrl (' Http://www.phpexcel.net '); // hyperlink url $ objActSheet-> getCell ('e26')-> getHyperlink ()-> setTooltip ('navigate to website '); // Add the image anchor to the table by moving the cursor over the connection prompt: bbb $ objDrawing = new PHPExcel_Worksheet_Drawing (); $ objDrawing-> setName ('paid '); $ objDrawing-> setDescription ('paid'); $ objDrawing-> setPath ('. /images/paid.png '); // image import position $ objDrawing-> setCoordinates ('b15'); // Add the image position $ objDrawing-> setOffsetX (210 ); $ objDrawing-> setRot Ation (25); $ objDrawing-> setHeight (36); $ objDrawing-> getShadow ()-> setVisible (true); $ objDrawing-> getShadow () -> setDirection (45); $ objDrawing-> setWorksheet ($ objPHPExcel-> getActiveSheet (); // You can also add images produced by the gd library, for details, see create a new worksheet and set the worksheet tag color. Anchor: bbb $ objExcel-> createSheet (); $ objPHPExcel-> setActiveSheetIndex (1 ); // set 2nd tables as active tables and provide the operation handle $ objExcel-> getSheet (1)-> setTitle ('test 2 '); // directly obtain the second table and rename the worksheet as test 2 $ objPHPExcel-> GetActiveSheet ()-> getTabColor ()-> setARGB ('ff0094ff '); // set the label color to add or delete rows and column anchors: bbb $ objPHPExcel-> getActiveSheet () -> insertNewRowBefore (6, 10); // Add 10 rows before Row 6 $ objPHPExcel-> getActiveSheet ()-> removeRow (6, 10 ); // Delete 10 rows from row 3 to $ objPHPExcel-> getActiveSheet ()-> insertNewColumnBefore ('E', 5 ); // Add 5 classes before column E $ objPHPExcel-> getActiveSheet ()-> removeColumn ('E', 5 ); // delete five columns from column E to hide and display a column of Anchor: bbb $ objPHPExcel-> getActiveSheet ()-> ge TColumnDimension ('C')-> setVisible (false); // hide $ objPHPExcel-> getActiveSheet ()-> getColumnDimension ('D')-> setVisible (true ); // display the Tag Name of the table for renaming the activity. Anchor: bbb $ objPHPExcel-> getActiveSheet ()-> setTitle ('invoice '); set worksheet security $ objPHPExcel-> getActiveSheet ()-> getProtection ()-> setPassword ('phpexcel '); $ objPHPExcel-> getActiveSheet ()-> getProtection () -> setSheet (true); // This shocould be enabled in order to enable any of t He following! $ ObjPHPExcel-> getActiveSheet ()-> getProtection ()-> setSort (true); $ objPHPExcel-> getActiveSheet ()-> getProtection ()-> setInsertRows (true ); $ objPHPExcel-> getActiveSheet ()-> getProtection ()-> setFormatCells (true); set the document security anchor: bbb $ objPHPExcel-> getSecurity ()-> setLockWindows (true ); $ objPHPExcel-> getSecurity ()-> setLockStructure (true); $ objPHPExcel-> getSecurity ()-> setWorkbookPassword ("PHPExcel"); // set the password style to copy the anchor: bbb/ /Copy the B2 style to B7 $ objPHPExcel-> getActiveSheet ()-> duplicateConditionalStyle ($ objPHPExcel-> getActiveSheet ()-> getStyle ('b2 ') -> getConditionalStyles (), 'b3: B7 '); Add conditional formatting anchor: bbbecho date ('H: I: s'), "Add conditional formatting", PHP_EOL; $ objConditional1 = new PHPExcel_Style_Conditional (); $ objConditional1-> setConditionType (PHPExcel_Style_Conditional: CONDITION_CELLIS); $ objCondition Al1-> setOperatorType (usage: OPERATOR_BETWEEN); $ objConditional1-> addCondition ('000000'); $ objConditional1-> addCondition ('20140901'); Set pagination (mainly for printing) anchor: bbb // set A cell to the end of the page $ objPHPExcel-> getActiveSheet ()-> setBreak ('A '. $ I, PHPExcel_Worksheet: BREAK_ROW); fill the table anchor with an array: bbb // the content of the bar array starts from A2 to fill $ dataArray = array ("2010 ", "Q1", "United States", 790), array ("2010", "Q2", "United States", 730),); $ objP HPExcel-> getActiveSheet ()-> fromArray ($ dataArray, NULL, 'a2 '); Set automatic filter anchor: bbb $ objPHPExcel-> getActiveSheet () -> setAutoFilter ($ objPHPExcel-> getActiveSheet ()-> calculateWorksheetDimension (); // $ objPHPExcel-> getActiveSheet ()-> calculateWorksheetDimension ().... get all the formula $ objCalc = PHPExcel_Calculation: getInstance (); print_r ($ objCalc-> listFunctionNames () printed from all the content of row A1 ()) set the range of cell values. Anchor: bbb $ objValidation = $ objPHPExcel-> GetActiveSheet ()-> getCell ('b3')-> getDataValidation (); $ objValidation-> setType (PHPExcel_Cell_DataValidation: TYPE_WHOLE); $ objValidation-> setErrorStyle (rules :: STYLE_STOP); $ objValidation-> setAllowBlank (true); $ objValidation-> setShowInputMessage (true); // set the display prompt information $ objValidation-> setShowErrorMessage (true ); // set the Display error message $ objValidation-> setErrorTitle ('input error'); // error title // $ ob JValidation-> setShowDropDown (true); $ objValidation-> setError ('only numbers between 10 and 20 are allowed! '); // Error content $ objValidation-> setPromptTitle ('allowed input'); // you can specify the title $ objValidation-> setPrompt ('only numbers between 10 and 20 are Allowed. '); // prompt content $ objValidation-> setFormula1 (10); // set the maximum value $ objValidation-> setFormula2 (120 ); // set the minimum value // or set $ objValidation-> setFormula2 (,); set the value to one of, and other $ objPHPExcel-> getActiveSheet () -> getStyle ('b5 ')-> getAlignment ()-> setShrinkToFit (true); // whether to automatically wrap the line when the length is insufficient. $ obj PHPExcel-> getActiveSheet ()-> getStyle ('b5 ')-> getAlignment ()-> setShrinkToFit (true); // automatically converts and displays the font size, enable the content to display $ objPHPExcel-> getActiveSheet ()-> getCell (B14)-> getValue (); // get the value, the formula $ objPHPExcel-> getActiveSheet ()-> getCell (B14)-> getCalculatedValue (); // obtain the calculated value to import or read the file // use PHPExcel_IOFactory:: load Method to load a file. load will automatically judge the file extension name to import the corresponding processing class, the read format contains xlsx/xls/xlsm/ods/slk/csv/xml/gnumericrequire_once '.. /Classes/PHPExcel/I OFactory. php '; $ objPHPExcel = PHPExcel_IOFactory: load (// Default table of files loaded by the bar) use the toArray method to return a multi-dimensional array $ dataArray = $ objPHPExcel-> getActiveSheet ()-> toArray (); // write $ objWriter = PHPExcel_IOFactory in an xlsx file after reading :: createWriter ($ objPHPExcel, 'excel2007 '); // $ objPHPExcel is the resource read in the previous article $ objWriter-> save (str_replace ('. php ', '.xlsx', _ FILE _); read the xml FILE $ objReader = PHPExcel_IOFactory: createReader ('excel2003xml'); $ objPHPExc El = $ objReader-> load ("Excel2003XMLTest. xml "); read ods files $ objReader = PHPExcel_IOFactory: createReader ('oocalc'); $ objPHPExcel = $ objReader-> load (" OOCalcTest. ods "); read the numeric File $ objReader = PHPExcel_IOFactory: createReader ('gnumeric '); $ objPHPExcel = $ objReader-> load (" GnumericTest. gnumeric "); read the slk File $ objPHPExcel = PHPExcel_IOFactory: load (" SylkTest. slk "); cyclically traversing data $ objReader = PHPExcel_IOFactory: cr EateReader ('excel2007 '); // create a 2007 read object $ objPHPExcel = $ objReader-> load ("05featuredemo.xlsx "); // read an xlsx file foreach ($ objPHPExcel-> getWorksheetIterator () as $ worksheet) {// traverse the Worksheet echo 'worksheet-', $ worksheet-> getTitle (), PHP_EOL; foreach ($ worksheet-> getRowIterator () as $ row) {// traverse rows echo 'row number-', $ Row-> getRowIndex (), PHP_EOL; $ cellIterator = $ row-> getCellIterator (); // get all columns $ cel LIterator-> setIterateOnlyExistingCells (false); // Loop all cells, even if it is not set foreach ($ cellIterator as $ cell) {// traverse the column if (! Is_null ($ cell) {// If the column is not empty, obtain its coordinates and calculated values echo 'cell-', $ Cell-> getCoordinate (), '-', $ cell-> getCalculatedValue (), PHP_EOL ;}}}} 3 rows of data in the table/inserted into the array $ data = array ('title' => 'excel for dummies ', 'price' => 17.99, 'quantity '=> 2), array ('title' => 'php for dummies', 'price' => 15.99, 'quantity' => 1 ), array ('title' => 'inside OOP ', 'price' => 12.95, 'quantity' => 1); $ baseRow = 5; // specify the foreach ($ data as $ r =>$ dataRow) {$ row = $ baseRow + $ r after the row is inserted to 5th; // $ row is the row number of the cyclic operation line $ objPHPExcel-> getActiveSheet ()-> insertNewRowBefore ($ row, 1); // Add an empty row before the number of the operation line, the row number of this empty row is changed to the current row number. The corresponding data and number $ objPHPExcel-> getActiveSheet ()-> setCellValue ('A '. $ row, $ r + 1); $ objPHPExcel-> getActiveSheet ()-> setCellValue ('B '. $ row, $ dataRow ['title']); $ objPHPExcel-> getActiveSheet ()-> setCellValue ('C '. $ row, $ dataRow ['price']); $ objPHPExcel-> getActiveSheet ()-> setCellValue ('D '. $ row, $ dataRow ['quantity ']); $ objPHPExcel-> getActiveSheet ()-> setCellValue ('E '. $ row, '= C '. $ row. '* d '. $ row) ;}$ objPHPExcel-> getActiveSheet ()-> removeRow ($ baseRow-1, 1); // finally Delete row 4th, which is required by the example, where you can delete instances