Phpexcel How to export an Excel method

This article mainly introduces phpexcel how to export Excel method, the interested friend's reference, hoped to have the help to everybody.

Set the Includepath of the Phpexcel class library

Set_include_path ('. '). Path_separator. ' D:\workspace\biznaligy_eh\dev_src\includes\PHPExcel '. Path_separator.get_include_path ());

Require_once ' phpexcel.php '; require_once ' phpexcel/writer/excel5.php ';//For other low versions xlsrequire_once ' PHPExcel/Writer/ Excel2007.php ';//For excel-2007 format

Create a Processing object instance

$objExcel =newphpexcel ();

Create a file format to write to an object instance, uncomment

$objWriter =newphpexcel_writer_excel5 ($objExcel);//For other version formats//or//$objWriter =newphpexcel_writer_excel2007 ($ OBJEXCEL)///For 2007 format//$objWriter->setoffice2003compatibility (TRUE);

Set document basic Properties

$objProps = $objExcel->getproperties (); $objProps->setcreator ("Zealli"); $objProps->setlastmodifiedby (" Zealli "), $objProps->settitle (" officexlstestdocument "); $objProps->setsubject (" Officexlstestdocument,demo ") ); $objProps->setdescription ("Testdocument,generatedbyphpexcel."); $objProps->setkeywords ("Officeexcelphpexcel"); $objProps->setcategory ("Test");

Sets the current sheet index for subsequent content operations.
It is generally only necessary to display a call when multiple sheet are used.
By default, Phpexcel automatically creates the first sheet to be set sheetindex=0

$objExcel->setactivesheetindex (0); $objActSheet = $objExcel->getactivesheet ();

Sets the name of the currently active sheet

$objActSheet->settitle (' Test sheet ');

Set cell contents automatically determine cell content type by phpexcel based on incoming content

$objActSheet->setcellvalue (' A1 ', ' string content ');//string Content $objactsheet->setcellvalue (' A2 ', 26);//Numeric $objactsheet- >setcellvalue (' A3 ', true);//Boolean Value $objactsheet->setcellvalue (' A4 ', ' =sum (A2:A2) ');//Formula

Explicitly specifying a content type

$objActSheet->setcellvalueexplicit (' A5 ', ' 8757584 ', phpexcel_cell_datatype::type_string);

Merge cells

$objActSheet->mergecells (' b1:c22 ');

Separating cells

$objActSheet->unmergecells (' b1:c22 ');

Set width

$objActSheet->getcolumndimension (' B ')->setautosize (true); $objActSheet->getcolumndimension (' A ')->setwidth (30);

Sets the number format for the contents of the cell.
If you use Phpexcel_writer_excel5 to generate content,
It is important to note that the const variable in the Phpexcel_style_numberformat class defines the
In various custom formats, other types can be used normally, but when the Setformatcode
For Format_number, the actual effect was not formatted as "0". Need
Modify the GETXF ($style) method in the source code of the Phpexcel_writer_excel5_format class,
Add one in front of if ($this->_biff_version==0x0500) {(Near line No. 363)
Line code:
if ($ifmt = = = ' 0 ') $ifmt = 1;

Format as Phpexcel_style_numberformat::format_number to avoid some large numbers
is displayed using scientific notation, with the following Setautosize method to let the contents of each line
Are all displayed according to the original content.

Set font

$objFontA 5= $objStyleA 5->getfont (); $objFontA 5->setname (' couriernew '); $objFontA 5->setsize (10); $ Objfonta5->setbold (true); $objFontA 5->setunderline (Phpexcel_style_font::underline_single); $objFontA 5-> GetColor ()->setargb (' FFFF0000 '); $objFontA 5->getcolor ()->setargb (phpexcel_style_color::color_white);// $objFontA 5->getfont ()->setcolor (phpexcel_style_color::color_red);

Set alignment

$objAlignA 5= $objStyleA 5->getalignment (); $objAlignA 5->sethorizontal (Phpexcel_style_alignment::horizontal_ right); $objAlignA 5->setvertical (Phpexcel_style_alignment::vertical_center); $objAlignA 5->setwraptext (True );//Line wrap, if the value in the cell is a column wide, or if you write a \ n in the value

Set border

$objBorderA 5= $objStyleA 5->getborders (), $objBorderA 5->gettop ()->setborderstyle (Phpexcel_style_border:: Border_thin); $objBorderA 5->gettop ()->getcolor ()->setargb (' FFFF0000 ');//Border color$objbordera5-> Getbottom ()->setborderstyle (Phpexcel_style_border::border_thin), $objBorderA 5->getleft () Setborderstyle (Phpexcel_style_border::border_thin), $objBorderA 5->getright ()->setborderstyle (PHPExcel_ Style_border::border_thin);

Set cell fill Color

$objFillA 5= $objStyleA 5->getfill (); $objFillA 5->setfilltype (Phpexcel_style_fill::fill_solid); $objFillA 5- >getstartcolor ()->setargb (' ffeeeeee ');

Copies the style information from the specified cell.

$objActSheet->duplicatestyle ($objStyleA 5, ' b1:c22 ');

Add a picture

$objDrawing = Newphpexcel_worksheet_drawing (); $objDrawing->setname (' zealimg '); $objDrawing->setdescription ( ' Imageinsertedbyzeal '); $objDrawing->setpath ('./zeali.net.logo.gif '); $objDrawing->setheight (36); $ Objdrawing->setcoordinates (' C23 '); $objDrawing->setoffsetx; $objDrawing->setrotation (15); $ Objdrawing->getshadow ()->setvisible (true), $objDrawing->getshadow ()->setdirection (), $objDrawing- >setworksheet ($objActSheet);

Add a new worksheet

$objExcel->createsheet (); $objExcel->getsheet (1)->settitle (' Test 2 ');

Save, set Password

$objPHPExcel->getactivesheet ()->getprotection ()->setpassword (' Phpexcel ');

Protect cells

$objExcel->getsheet (1)->getprotection ()->setsheet (true); $objExcel->getsheet (1)->protectcells (' A1:c22 ', ' phpexcel ');

Display grid lines:

$objPHPExcel->getactivesheet ()->setshowgridlines (true);

Show hidden columns

$objPHPExcel->getactivesheet ()->getcolumndimension (' C ')->setvisible (true); $objPHPExcel Getactivesheet ()->getcolumndimension (' D ')->setvisible (false);

Show hidden rows

$objPHPExcel->getactivesheet ()->getrowdimension (' Ten ')->setvisible (false);

Default column width

$objPHPExcel->getactivesheet ()->getdefaultcolumndimension ()->setwidth (12);

Default line width

$objPHPExcel->getactivesheet ()->getdefaultrowdimension ()->setrowheight (15);

Worksheet default style settings (and default different settings required separately)

$objPHPExcel->getactivesheet ()->getdefaultstyle ()->getfont ()->setname (' Arial '); $objPHPExcel Getactivesheet ()->getdefaultstyle ()->getfont ()->setsize (8); $alignment = $objPHPExcel->getactivesheet ()->getdefaultstyle ()->getalignment (); $alignment->sethorizontal (Phpexcel_style_alignment::horizontal_ CENTER); $alignment->setvertical (Phpexcel_style_alignment::vertical_center);

Output content

$outputFileName = "Output.xls";

To file through file path again with Ajax no Refresh page

$objWriter->save ($outputFileName);

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 ("Last-modified:". Gmdate ("D,dmyh:i:s"). " GMT "), Header (" cache-control:must-revalidate,post-check=0,pre-check=0 "), Header (" Pragma:no-cache "), $objWriter- >save (' php://output '); $objWriter = Phpexcel_iofactory::createwriter ($objPHPExcel, ' PDF '), $objWriter->save (' a.pdf ') public function Getcellbycolumnandrow ($pColumn = 0, $pRow = 0) {return $this->getcell (Phpexcel_cell::stringfromcolumnindex ($pColum N). $pRow);} Print_r (phpexcel_cell::columnindexfromstring (' D ')); exit; Echo 4echo Phpexcel_cell::stringfromcolumnindex (4)//echo Efor ($c =phpexcel_cell::columnindexfromstring (' A '); $c <phpexcel_cell::columnindexfromstring (' J '); $c + +) {echo phpexcel_cell::stringfromcolumnindex ($c);} 

Phpexcel is not frozen by default, the following is a frozen column. Set two to set the whole

$sheet->freezepane (' A1 '); $sheet->freezepane (' B1 ');

Freeze rows

$sheet->freezepane (' D1 '); $sheet->freezepane (' D2 ');

A,B,C cannot be defined again otherwise column freezes are substituted

Public Function Freezepanebycolumnandrow ($pColumn = 0, $pRow = 0) {    $this->freezepane (Phpexcel_cell:: Stringfromcolumnindex ($pColumn). $pRow);} Public Function Unfreezepane () {   $this->freezepane (");} $worksheet->setinputencoding ("UTF-8");//$freeze = $sheet->getfreezepane ();

Format cells in batches with Phpexcel

Java code

$style _obj = new Phpexcel_style ();  $style _array = Array (' borders ' = = Array (' top ' = = Array (          ' style ' = Phpexcel_style_border::border_ THIN),          ' left ' = = Array (' style ' = Phpexcel_style_border::border_thin),          ' bottom ' = = Array (' style ' = > Phpexcel_style_border::border_thin),          ' right ' = = Array (' Style ' = Phpexcel_style_border::border_thin)      ),      ' alignment ' = = Array (          ' horizontal ' = Phpexcel_style_alignment::horizontal_center,          ' Vertical '   = phpexcel_style_alignment::vertical_center,          ' wrap '       = True      )  ;  $style _obj->applyfromarray ($style _array);  $sheet->setsharedstyle ($style _obj, "a1:o35");

Phpexcel Big Data export, data append (190,000 lines, 20 sheet, 10,000 lines is a sheet, first to 1 sheet data, other data append to Excel 19 times overwrite corresponding sheet)

First use Phpexcel to build the reader, and then load the file, so open the existing document, and then set up writer, the reader in the data are copied, and then use Phpexcel for data modification, and then the load file name overwrite save.

Java code

<?php  require_once ' phpexcel/iofactory.php ';  $reader = Phpexcel_iofactory::createreader (' Excel5 '); Read the old version of Excel file  $PHPExcel = $reader->load ("Y.xls");//file name  $sheet = $PHPExcel->getsheet (0);//Read the first worksheet ( Number starting from 0)  $highestRow = $sheet->gethighestrow ();//Get total number of columns for  ($row = 1; $row <= $highestRow; $row + +) {      for ($column = 1; $column <= 6; $column + +) {          $val = $sheet->getcellbycolumnandrow ($column, $row)->getvalue () ;      }  }  ? >

Related recommendations:

Phpexcel Export Excel File method in Yii2 framework

Yii2 Framework to implement PHPEXCEL export Excel file Sharing method

Phpexcel exporting Excel