Write and build operations for Excel:
Copy CodeThe code is as follows:
Include ' phpexcel.php ';
Include ' phpexcel/writer/excel2007.php ';
Or include ' phpexcel/writer/excel5.php '; For the output. xls
Include ' phpexcel/iofactory.php ';//phpexcel factory class
Create an Excel
$objPHPExcel = new Phpexcel ();
Save excel-2007 Format
$objWriter = new phpexcel_writer_excel2007 ($objPHPExcel);
You can also use
$objWriter = Phpexcel_iofactory::createwriter ($objPHPExcel, "Excel2007");
or $objwriter = new Phpexcel_writer_excel5 ($objPHPExcel); Non-2007 format
$objWriter->save ("xxx.xlsx");
Direct output to 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 ');
Generate files directly
$objWriterr->save (' filename ');
To set properties for Excel:
Create person
$objPHPExcel->getproperties ()->setcreator ("Maarten Balliauw");
Last modified person
$objPHPExcel->getproperties ()->setlastmodifiedby ("Maarten Balliauw");
Title
$objPHPExcel->getproperties ()->settitle ("Office" XLSX Test Document ");
Topic
$objPHPExcel->getproperties ()->setsubject ("Office" XLSX Test Document ");
Describe
$objPHPExcel->getproperties ()->setdescription ("Test document for Office-XLSX, generated using PHP classes.");
Key words
$objPHPExcel->getproperties ()->setkeywords ("Office 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) ');
Merge cells
$objPHPExcel->getactivesheet ()->mergecells (' A18:e22 ');
Separating cells
$objPHPExcel->getactivesheet ()->unmergecells (' a28:b28 ');
Protect cell
$objPHPExcel->getactivesheet ()->getprotection ()->setsheet (true); Needs to BES 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 ');
Setting width 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 (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 the border of column
$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 the color of the border
$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
$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 phpexcel_worksheet_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 ());
Handling Chinese output issues
You need to convert the string to UTF-8 encoding for normal output, otherwise the Chinese characters will be output blank, as follows:
$str = Iconv (' gb2312 ', ' utf-8 ', $str);
Or you can write a function that specifically handles Chinese strings:
function ConvertUTF8 ($STR)
{
if (empty ($STR)) return ';
Return Iconv (' gb2312 ', ' utf-8 ', $str);
}
Read Excel
1. The simplest way to import an Excel is to use the Phpexel IO Factory, which calls the static load of the Phpexcel_iofactory class, which automatically identifies the document format, including Excel2007, Excel2003xml, Oocalcsylk, Gnumeric, CSV. Returns an instance of a phpexcel.
Copy CodeThe code is as follows:
Load Factory class
Include ' phpexcel/iofactory.php ';
The XLS file path to read
$inputFileName = './sampledata/example1.xls ';
/** uses the Phpexcel_iofactory load method to get the Excel object **/
$objPHPExcel = Phpexcel_iofactory::load ($inputFileName);
Get the current active table, call the ToArray method, get the two-dimensional array of the table
$sheetData = $objPHPExcel->getactivesheet ()->toarray (null,true,true,true);
Var_dump ($sheetData);
1. Create a excelreader to load an Excel document
If you know the format of this Excel document, you can create a corresponding reader to load the Excel document you want to read. But if you load the wrong document type, you can produce unpredictable errors.
Copy CodeThe code is as follows:
$inputFileName = './sampledata/example1.xls ';
/** Create a new Excel5 Reader **/
$objReader = new Phpexcel_reader_excel5 ();
$objReader = new phpexcel_reader_excel2007 ();
$objReader = new Phpexcel_reader_excel2003xml ();
$objReader = new Phpexcel_reader_oocalc ();
$objReader = new Phpexcel_reader_sylk ();
$objReader = new Phpexcel_reader_gnumeric ();
$objReader = new Phpexcel_reader_csv ();
/** Load $inputFileName to a phpexcel Object **/
$objPHPExcel = $objReader->load ($inputFileName);
Get Current Activity sheet
$curSheet = $objPHPExcel->getactivesheet ();
Returns the table's data as a two-dimensional array
$sheetData = $curSheet->toarray (null,true,true,true);
Var_dump ($sheetData);
You can also use the Phpexcel_iofactory Createreader method to get a reader object without needing to know the format of the file to be read.
Copy CodeThe code is as follows:
$inputFileType = ' Excel5 ';
$inputFileType = ' Excel2007 ';
$inputFileType = ' excel2003xml ';
$inputFileType = ' Oocalc ';
$inputFileType = ' SYLK ';
$inputFileType = ' Gnumeric ';
$inputFileType = ' CSV ';
$inputFileName = './sampledata/example1.xls ';
/** Create A new Reader of the type defined in $inputFileType **/
$objReader = Phpexcel_iofactory::createreader ($inputFileType);
/** Load $inputFileName to a phpexcel Object **/
$objPHPExcel = $objReader->load ($inputFileName);
Get Current Activity sheet
$curSheet = $objPHPExcel->getactivesheet ();
Returns the table's data as a two-dimensional array
$sheetData = $curSheet->toarray (null,true,true,true);
Var_dump ($sheetData);
If the file format is unknown before reading the file, you can get the file type by Iofactory's identify () method and then go through the reader via the Createreader () method.
Copy CodeThe code is as follows:
$inputFileName = './sampledata/example1.xls ';
/** determine the format of the input file **/
$inputFileType = Phpexcel_iofactory::identify ($inputFileName);
/** reader **/for wearing pieces
$objReader = Phpexcel_iofactory::createreader ($inputFileType);
/** load the file to be read **/
$objPHPExcel = $objReader->load ($inputFileName);
2. Set reading options for Excel
Before loading a file using the load () method, you can set the Read option to control the behavior of load.
2.1.ReadingOnly Data from a Spreadsheet File
The Setreaddataonly () method, which configures the reader to not care about the data type of the tabular data, is returned in string format
Copy CodeThe code is as follows:
$inputFileType = ' Excel5 ';
$inputFileName = './sampledata/example1.xls ';
/** Create A new Reader of the type defined in $inputFileType **/
$objReader = Phpexcel_iofactory::createreader ($inputFileType);
/** the configuration cell data is returned as a string **/
$objReader->setreaddataonly (TRUE);
/** Load $inputFileName to a phpexcel Object **/
$objPHPExcel = $objReader->load ($inputFileName);
$sheetData = $objPHPExcel->getactivesheet ()->toarray (null,true,true,true);
Var_dump ($sheetData);
Return Data:
Copy CodeThe code is as follows:
Array (8) {
[1]=>
Array (6) {
["A"]=>
String ("Integer Numbers")
["B"]=>
String (3) "123"
["C"]=>
String (3) "234"
["D"]=>
String (4) "-345"
["E"]=>
String (3) "456"
["F"]=>
Null
}
[2]=>
Array (6) {
["A"]=>
String ("Floating pointnumbers")
["B"]=>
String (4) "1.23"
["C"]=>
String (5) "23.45"
["D"]=>
String (Ten) "0.00e+0.00"
["E"]=>
String (6) "-45.68"
["F"]=>
String (7) "£56.78"
}
[3]=>
Array (6) {
["A"]=>
String (7) "Strings"
["B"]=>
String (5) "Hello"
["C"]=>
String (5) "World"
["D"]=>
Null
["E"]=>
String (8) "Phpexcel"
["F"]=>
Null
}
[4]=>
Array (6) {
["A"]=>
String (8) "Booleans"
["B"]=>
BOOL (TRUE)
["C"]=>
BOOL (FALSE)
["D"]=>
Null
["E"]=>
Null
["F"]=>
Null
}
[5]=>
Array (6) {
["A"]=>
String (5) "Dates"
["B"]=>
String (1960) "December"
["C"]=>
String (October 2010)
["D"]=>
Null
["E"]=>
Null
["F"]=>
Null
}
[6]=>
Array (6) {
["A"]=>
String (5) "Times"
["B"]=>
String (4) "9:30"
["C"]=>
String (5) "23:59"
["D"]=>
Null
["E"]=>
Null
["F"]=>
Null
}
[7]=>
Array (6) {
["A"]=>
String (8) "Formulae"
["B"]=>
String (3) "468"
["C"]=>
String (7) "-20.998"
["D"]=>
Null
["E"]=>
Null
["F"]=>
Null
}
[8]=>
Array (6) {
["A"]=>
String (6) "Errors"
["B"]=>
String (4) "#N/A"
["C"]=>
String (7) "#DIV/0!"
["D"]=>
Null
["E"]=>
Null
["F"]=>
Null
}
}
if not set then return:
Array (8) {
[1]=>
Array (6) {
["A"]=>
String ("Integer Numbers")
["B"]=>
Float (123)
["C"]=>
Float (234)
["D"]=>
Float (-345)
["E"]=>
Float (456)
["F"]=>
Null
}
[2]=>
Array (6) {
["A"]=>
String "Floating point Numbers"
["B"]=>
Float (1.23)
["C"]=>
Float (23.45)
["D"]=>
Float (3.45E-6)
["E"]=>
Float (-45.678)
["F"]=>
Float (56.78)
}
[3]=>
Array (6) {
["A"]=>
String (7) "Strings"
["B"]=>
String (5) "Hello"
["C"]=>
String (5) "World"
["D"]=>
Null
["E"]=>
String (8) "Phpexcel"
["F"]=>
Null
}
[4]=>
Array (6) {
["A"]=>
String (8) "Booleans"
["B"]=>
BOOL (TRUE)
["C"]=>
BOOL (FALSE)
["D"]=>
Null
["E"]=>
Null
["F"]=>
Null
}
[5]=>
Array (6) {
["A"]=>
String (5) "Dates"
["B"]=>
Float (22269)
["C"]=>
Float (40461)
["D"]=>
Null
["E"]=>
Null
["F"]=>
Null
}
[6]=>
Array (6) {
["A"]=>
String (5) "Times"
["B"]=>
Float (0.39583333333333)
["C"]=>
Float (0.99930555555556)
["D"]=>
Null
["E"]=>
Null
["F"]=>
Null
}
[7]=>
Array (6) {
["A"]=>
String (8) "Formulae"
["B"]=>
Float (468)
["C"]=>
Float (-20.99799655)
["D"]=>
Null
["E"]=>
Null
["F"]=>
Null
}
[8]=>
Array (6) {
["A"]=>
String (6) "Errors"
["B"]=>
String (4) "#N/A"
["C"]=>
String (7) "#DIV/0!"
["D"]=>
Null
["E"]=>
Null
["F"]=>
Null
}
}
Reading only Data from a spreadsheetfile applies to Readers:
Excel2007 Yes Excel5 Yes Excel2003xml Yes
Oocalc Yes SYLK NO Gnumeric Yes
CSV NO
2.2.ReadingOnly Named worksheets from a File
Setloadsheetsonly (), sets the worksheet to be read, accepts the name of the worksheet as a parameter.
Copy the Code code as follows:
/** Phpexcel_iofactory */
Include ' phpexcel/iofactory.php ';
$inputFileType = ' Excel5 ';
$inputFileType = ' Excel2007 ';
$inputFileType = ' excel2003xml ';
$inputFileType = ' Oocalc ';
$inputFileType = ' Gnumeric ';
$inputFileName = './sampledata/example1.xls ';
$sheetname = ' Data Sheet #2 ';
Echo ' Loading file ', PathInfo ($inputFileName, pathinfo_basename), ' using Iofactory with a definedreader type of ', $ Inputfiletype, '
';
$objReader = Phpexcel_iofactory::createreader ($inputFileType);
Echo ' Loading Sheet ', $sheetname, ' "only
';
$objReader->setloadsheetsonly ($sheetname);
$objPHPExcel = $objReader->load ($inputFileName);
Echo '
';
Echo$objphpexcel->getsheetcount (), ' Worksheet ', (($objPHPExcel->getsheetcount () = = 1)? ': ' s '), ' loaded
';
$loadedSheetNames = $objPHPExcel->getsheetnames ();
foreach ($loadedSheetNames as$sheetindex = $loadedSheetName) {
Echo $sheetIndex, '--', $loadedSheetName, '
';
}
If you want to read more than one worksheet, you can pass an array
Copy CodeThe code is as follows:
$inputFileType = ' Excel5 ';
$inputFileName = './sampledata/example1.xls ';
$sheetnames = Array (' Data Sheet #1 ', ' Data Sheet #3 ');
/** Create A new Reader of the type defined in $inputFileType **/
$objReader = Phpexcel_iofactory::createreader ($inputFileType);
/** Advise the Reader of which worksheets we want to load **/
$objReader->setloadsheetsonly ($sheetnames);
/** Load $inputFileName to a phpexcel Object **/
$objPHPExcel = $objReader->load ($inputFileName);
If you want to read all worksheet, you can call Setloadallsheets ().
http://www.bkjia.com/PHPjc/327797.html www.bkjia.com true http://www.bkjia.com/PHPjc/327797.html techarticle write and build operations for Excel: Copy the code as follows: Include ' phpexcel.php '; include ' phpexcel/writer/excel2007.php ';//or include ' phpexcel/ Writer/excel5.php '; For output ...
