Analysis of common instructions used in Phpexcel and introduction of Phpexcel integration into CI framework

Analysis of common instructions used in Phpexcel and introduction of Phpexcel integration into CI framework _php skills

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

Write and build operations for Excel:

Copy Code code 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 ');

Directly generate files

$objWriterr->save (' filename ');

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 ');

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 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 border for 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") ;

$objPHPExcel->getactivesheet ()->getstyle (' B1 ')->getfill ()->setfilltype (Phpexcel_style_fill::fill_ SOLID);

$objPHPExcel->getactivesheet ()->getstyle (' B1 ')->getfill ()->getstartcolor ("->setargb") ;

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 problems

You need to convert the string to UTF-8 encoding for normal output, otherwise the Chinese character will output as blank, as follows:

$str = Iconv (' gb2312 ', ' utf-8 ', $str);

Or you can write a function to handle the Chinese string specifically:

function ConvertUTF8 ($STR)

{

if (empty ($STR)) return ";

Return Iconv (' gb2312 ', ' utf-8 ', $str);

}

Reading Excel
1. The easiest way to import an Excel is to use the Phpexel IO Factory to invoke 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 Code code as follows:

Load Factory class
Include ' phpexcel/iofactory.php ';
The XLS file path to read
$inputFileName = './sampledata/example1.xls ';
/** uses Phpexcel_iofactory Load method to get Excel operation object **/
$objPHPExcel = Phpexcel_iofactory::load ($inputFileName);
Get the current active table, call the ToArray method, and 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 create unpredictable errors.

Copy Code code 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 data for the table 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 knowing the format of the file you want to read.

Copy Code code 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 data for the table as a two-dimensional array
$sheetData = $curSheet->toarray (null,true,true,true);
Var_dump ($sheetData);

If the file format is unknown before the file is read, you can get the file type through the Iofactory identify () method, and then use the Createreader () method to go through the reader.

Copy Code code as follows:

$inputFileName = './sampledata/example1.xls ';
/** determines the format of the input file **/
$inputFileType = Phpexcel_iofactory::identify ($inputFileName);
/** Reader **/
$objReader = Phpexcel_iofactory::createreader ($inputFileType);
/** load the file to read **/
$objPHPExcel = $objReader->load ($inputFileName);

2. Set the read options for Excel
Before loading a file using the load () method, you can set the read options to control the behavior of the load.

2.1.ReadingOnly Data from a spreadsheet File
Setreaddataonly () method, configure the reader to not care about the data type of the table data, all returned in string format

Copy Code code as follows:

$inputFileType = ' Excel5 ';
$inputFileName = './sampledata/example1.xls ';
/** Create A new Reader of the type defined in $inputFileType **/
$objReader = Phpexcel_iofactory::createreader ($inputFileType);
/** 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 Code code 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 (a) "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 "December 1960"

["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

}

}

return if not set:
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 (), set the worksheet to read, and accept the worksheet name as an argument.

Copy 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 ' <hr/> ';

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 multiple worksheet, you can pass an array

Copy Code code 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 ().

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More

Analysis of common instructions used in Phpexcel and introduction of Phpexcel integration into CI framework _php skills

Contact Us

What's Trending

Top 10 Tags

Top 10 Keywords

A Free Trial That Lets You Build Big!

Sales Support

After-Sales Support

Analysis of common instructions used in Phpexcel and introduction of Phpexcel integration into CI framework _php skills

Contact Us

What's Trending

Top 10 Tags

Top 10 Keywords

Trending Topic

A Free Trial That Lets You Build Big!

Sales Support

After-Sales Support