Phpexcel Chinese Use manual detailed

Hpexcel is a PHP class library that is used to manipulate office Excel documents, based on Microsoft's OPENXML Standard and PHP language. You can use it to read and write spreadsheets in different formats. Overall is a good Excel operation class.

Here are some instructions for this class.

Include ' phpexcel.php ';
Include ' phpexcel/writer/excel2007.php ';
Or include ' phpexcel/writer/excel5.php '; For the output. xls
Create an Excel
$objPHPExcel = new Phpexcel ();

Output Excel table to browser download
Header (' Content-type:application/vnd.ms-excel ');
Header (' Content-disposition:attachment;filename= "Abc.xls");
Header (' cache-control:max-age=0 ');
If you are serving to IE 9, then the following could be needed
Header (' cache-control:max-age=1 ');

If you are serving to IE over SSL, then the following could be needed
Header (' Expires:mon, June 1997 05:00:00 GMT '); Date in the past
Header (' last-modified: '. Gmdate (' d, D M Y h:i:s '). ' GMT '); Always modified
Header (' Cache-control:cache, Must-revalidate '); http/1.1
Header (' Pragma:public '); http/1.0

$objWriter = new Phpexcel_writer_excel5 ($objPHPExcel);
$objWriter->save (' php://output ');

——————————————————————————————————————–
to set properties for Excel:
Create a person
$objPHPExcel->getproperties ()->setcreator ("Maarten Balliauw");
Last modified person
$objPHPExcel->getproperties ()->setlastmodifiedby ("Maarten Balliauw");
Title
$objPHPExcel->getproperties ()->settitle ("Office 2007 XLSX Test Document");
Topic
$objPHPExcel->getproperties ()->setsubject ("Office 2007 XLSX Test Document");
Describe
$objPHPExcel->getproperties ()->setdescription ("Test document for Office 2007 XLSX, generated using PHP classes.");
Key words
$objPHPExcel->getproperties ()->setkeywords ("Office 2007 OPENXML PHP");
Kinds
$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 value of a cell
$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) ');

Merging cells
$objPHPExcel->getactivesheet ()->mergecells (' A18:e22 ');

Detach cells
$objPHPExcel->getactivesheet ()->unmergecells (' a28:b28 ');

Freeze window
$objPHPExcel->getactivesheet ()->freezepane (' A2 ');

Protect cell
$objPHPExcel->getactivesheet ()->getprotection ()->setsheet (true); Needs to is set to true in order to enable any worksheet protection!
$objPHPExcel->getactivesheet ()->protectcells (' A3:e13 ', ' phpexcel ');

Set format
Set Cell number formats
echo 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 wide width
Set column Widths
$objPHPExcel->getactivesheet ()->getcolumndimension (' B ')->setautosize (true);
$objPHPExcel->getactivesheet ()->getcolumndimension (' D ')->setwidth (12);

Set cell height
Default height for all cells
$objPHPExcel->getactivesheet ()->getdefaultrowdimension ()->setrowheight (15);
Default height of the first line
$objPHPExcel->getactivesheet ()->getrowdimension (' 1 ')->setrowheight (30);

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 (phpexcel_style_ Alignment::horizontal_right);
$objPHPExcel->getactivesheet ()->getstyle (' D12 ')->getalignment ()->sethorizontal (phpexcel_style_ Alignment::horizontal_right);
$objPHPExcel->getactivesheet ()->getstyle (' D13 ')->getalignment ()->sethorizontal (phpexcel_style_ Alignment::horizontal_right);
$objPHPExcel->getactivesheet ()->getstyle (' A18 ')->getalignment ()->sethorizontal (phpexcel_style_ Alignment::horizontal_justify);

Center vertically
$objPHPExcel->getactivesheet ()->getstyle (' A18 ')->getalignment ()->setvertical (phpexcel_style_ Alignment::vertical_center);

Set column 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 Fill Color
$objPHPExcel->getactivesheet ()->getstyle (' A1 ')->getfill ()->setfilltype (Phpexcel_style _fill::fill_solid);
$objPHPExcel->getactivesheet ()->getstyle (' A1 ')->getfill ()->getstartcolor ()-->setargb (" FF808080 ');
$objPHPExcel->getactivesheet ()->getstyle (' B1 ')->getfill ()->setfilltype (Phpexcel_style_fill:: Fill_solid);
$objPHPExcel->getactivesheet ()->getstyle (' B1 ')->getfill ()->getstartcolor ()-->setargb (" FF808080 ');

Add picture
/* Instantiate Insert Picture class * *
$objDrawing = new phpexcel_worksheet_drawing ();
/* Set the picture path to remember: only local pictures * *
$objDrawing->setpath ($img _val);
/* Set Picture Height * *
$objDrawing->setwidth (200);
$img _height[] = $objDrawing->getheight ();
/* Set the cell for which you want to insert the picture * *
$objDrawing->setcoordinates ($img _k[$j]. $i);
/* Set the format of the cell in which the picture is located.
$objDrawing->setoffsetx (10);
$objDrawing->setoffsety (10);
$objDrawing->setrotation (0);
$objDrawing->getshadow ()->setvisible (true);
$objDrawing->getshadow ()->setdirection (50);
$objDrawing->setworksheet ($objPHPExcel->getactivesheet ());
Export Excel Table Examples

<?php

$datas = Array (
Array (' Wang Cheng ', ' Men ', ' 18 ', ' 1997-03-13 ', ' 18948348924 '),
Array (' Li Fei Hong ', ' Men ', ' 21 ', ' 1994-06-13 ', ' 159481838924 '),
Array (' Wang Yun ', ' Women ', ' 18 ', ' 1997-03-13 ', ' 18648313924 '),
Array (' Guo Rui ', ' Men ', ' 17 ', ' 1998-04-13 ', ' 15543248924 '),
Array (' Li Xiaoxia ', ' Women ', ' 19 ', ' 1996-06-13 ', ' 18748348924 '),
);

Include ' phpexcel.php ';
Include ' phpexcel/writer/excel2007.php ';
Or include ' phpexcel/writer/excel5.php '; For the output. xls
Create an Excel
$objPHPExcel = new Phpexcel ();

Set Document Properties
$objPHPExcel->getproperties ()->setcreator ("Phpmarker")->setlastmodifiedby ("Phpmarker")->settitle (" Phpmarker ")->setsubject (" Phpmarker ")->setdescription (" Phpmarker ")->setkeywords (" Phpmarker ")-> Setcategory ("Phpmarker");

$objPHPExcel->setactivesheetindex (0)->setcellvalue (' A1 ', ' name ')->setcellvalue (' B1 ', ' gender ')-> Setcellvalue (' C1 ', ' age ')->setcellvalue (' D1 ', ' date of birth ')->setcellvalue (' E1 ', ' phone number ');

Rename Worksheet
$objPHPExcel->getactivesheet ()->settitle (' phpmarker-'. Date (' y-m-d '));

Set Active sheet Index to the "the" the "sheet", so Excel opens this as the
$objPHPExcel->setactivesheetindex (0);
$objPHPExcel->getactivesheet ()->getdefaultrowdimension ()->setrowheight (15);
$objPHPExcel->getactivesheet ()->freezepane (' A2 ');
$i = 2;
foreach ($datas as $data) {
$objPHPExcel->getactivesheet ()->setcellvalue (' A ' $i, $data [0])->getstyle (' a '. $i)->getalignment ()- >sethorizontal (Phpexcel_style_alignment::vertical_center);
$objPHPExcel->getactivesheet ()->setcellvalue (' B '. $i, $data [1]);
$objPHPExcel->getactivesheet ()->setcellvalue (' C '. $i, $data [2]);

$objPHPExcel->getactivesheet ()->setcellvalueexplicit (' D '. $i, $data [3],phpexcel_cell_datatype::type_string );
$objPHPExcel->getactivesheet ()->getstyle (' D '. $i)->getnumberformat ()->setformatcode ("@");

Set text formatting
$objPHPExcel->getactivesheet ()->setcellvalueexplicit (' E '. $i, $data [4],phpexcel_cell_datatype::type_string );
$objPHPExcel->getactivesheet ()->getstyle (' E '. $i)->getalignment ()->setwraptext (true);
$i + +;
}

$objActSheet = $objPHPExcel->getactivesheet ();

Set cell fill Color
$cell _fill = Array (
' A1 ',
' B1 ',
' C1 ',
' D1 ',
' E1 ',
);
foreach ($cell _fill as $cell _fill_val) {
$cellstyle = $objActSheet->getstyle ($cell _fill_val);
Background
$cellstyle->getfill ()->setfilltype (phpexcel_style_fill::fill_solid)->getstartcolor ()->setARGB (' Fafa00 ');
Set align
$cellstyle->getalignment ()->sethorizontal (phpexcel_style_alignment::horizontal_left)->setVertical ( Phpexcel_style_alignment::vertical_center);
Font
$cellstyle->getfont ()->setsize ()->setbold (true);
Border
$cellstyle->getborders ()->gettop ()->setborderstyle (Phpexcel_style_border::border_thin)->getColor () ->setargb (' FFFF0000 ');
$cellstyle->getborders ()->getbottom ()->setborderstyle (Phpexcel_style_border::border_thin)-> GetColor ()->setargb (' FFFF0000 ');
$cellstyle->getborders ()->getleft ()->setborderstyle (Phpexcel_style_border::border_thin)->getColor ( )->setargb (' FFFF0000 ');
$cellstyle->getborders ()->getright ()->setborderstyle (Phpexcel_style_border::border_thin)->getColor ()->setargb (' FFFF0000 ');
}

$objPHPExcel->getactivesheet ()->getrowdimension (' 1 ')->setrowheight (30);

$objActSheet->getcolumndimension (' A ')->setwidth (18.5);
$objActSheet->getcolumndimension (' B ')->setwidth (23.5);
$objActSheet->getcolumndimension (' C ')->setwidth (12);
$objActSheet->getcolumndimension (' D ')->setwidth (12);
$objActSheet->getcolumndimension (' E ')->setwidth (12);

$filename = ' 2015030423 ';
Ob_end_clean ()//clear buffer to avoid garbled
Header (' Content-type:application/vnd.ms-excel ');
Header (' Content-disposition:attachment;filename= '. $filename. ') XLS "');
Header (' cache-control:max-age=0 ');
If you are serving to IE 9, then the following could be needed
Header (' cache-control:max-age=1 ');

If you are serving to IE over SSL, then the following could be needed
Header (' Expires:mon, June 1997 05:00:00 GMT '); Date in the past
Header (' last-modified: '. Gmdate (' d, D M Y h:i:s '). ' GMT '); Always modified
Header (' Cache-control:cache, Must-revalidate '); http/1.1
Header (' Pragma:public '); http/1.0

$objWriter = new Phpexcel_writer_excel5 ($objPHPExcel);
$objWriter->save (' php://output ');
?>
Everywhere Chinese, will appear garbled, remember before header plus Ob_end_clean () clear buffer, avoid garbled