Shulou(Shulou.com)06/03 Report--

Editor to share with you how to use PHPExcel, I hope you will get something after reading this article. Let's discuss it together.

Write and generate operations of excel:

The copy code is as follows:

Include 'PHPExcel.php'

Include 'PHPExcel/Writer/Excel2007.php'

/ / or include 'PHPExcel/Writer/Excel5.php'; is used to 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)

/ / can also be used

/ / $objWriter = PHPExcel_IOFactory::createWriter ($objPHPExcel, "Excel2007")

/ / or $objWriter = new PHPExcel_Writer_Excel5 ($objPHPExcel); non-2007 format

$objWriter- > save ("xxx.xlsx")

/ / output directly to the 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')

/ / set the properties of excel:

/ / creator

$objPHPExcel- > getProperties ()-> setCreator ("Maarten Balliauw")

/ / the last modifier

$objPHPExcel- > getProperties ()-> setLastModifiedBy ("Maarten Balliauw")

/ / title

$objPHPExcel- > getProperties ()-> setTitle ("Office 2007 XLSX Test Document")

/ / title

$objPHPExcel- > getProperties ()-> setSubject ("Office 2007 XLSX Test Document")

/ / description

ObjPHPExcel- > getProperties ()-> setDescription ("Test document for Office 2007 XLSX, generated using PHP classes.")

/ / keyword

$objPHPExcel- > getProperties ()-> setKeywords ("office 2007 openxml php")

/ / Type

$objPHPExcel- > getProperties ()-> setCategory ("Test result file")

/ / set the current sheet

$objPHPExcel- > setActiveSheetIndex (0)

/ / set the name of sheet

$objPHPExcel- > getActiveSheet ()-> setTitle ('Simple')

/ / set the value of the cell

$objPHPExcel- > getActiveSheet ()-> setCellValue ('A1Qing,' String')

$objPHPExcel- > getActiveSheet ()-> setCellValue ('A2clients, 12)

$objPHPExcel- > getActiveSheet ()-> setCellValue ('A3clients, true)

$objPHPExcel- > getActiveSheet ()-> setCellValue ('C5neighbors,' = SUM (C2:C4)')

$objPHPExcel- > getActiveSheet ()-> setCellValue ('B8neighbors,' = MIN (B2:C5)')

/ / merge cells

$objPHPExcel- > getActiveSheet ()-> mergeCells ('A18mergeCells E22')

/ / detach cells

$objPHPExcel- > getActiveSheet ()-> unmergeCells ('A28Suzhou B28')

/ / protect cell

$objPHPExcel- > getActiveSheet ()-> getProtection ()-> setSheet (true); / / Needs to be set to true in order to enable any worksheet protection!

$objPHPExcel- > getActiveSheet ()-> protectCells ('A3Vera E13mm,' PHPExcel')

/ / set the format

/ / Set cell number formats

Echo date ('Hpuri'). "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'),' E5VR 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 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 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 pictures

$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

$objDrawing- > setRotation (25)

$objDrawing- > getShadow ()-> setVisible (true)

$objDrawing- > getShadow ()-> setDirection (45)

$objDrawing- > setWorksheet ($objPHPExcel- > getActiveSheet ())

/ / deal with Chinese output problem

/ / the string needs to be converted to UTF-8 encoding before it can be output normally, otherwise the Chinese characters will be output as blank, as follows:

$str = iconv ('gb2312',' utf-8', $str)

Or you can write a function that specifically deals with Chinese strings:

Function convertUTF8 ($str)

{

If (empty ($str)) return''

Return iconv ('gb2312',' utf-8', $str)

}

Read excel

1. The easiest way to import an Excel is to use PHPExel's IOFactory and call the static method load of the PHPExcel_IOFactory class, which automatically recognizes the document format, including Excel2007, Excel2003XML, OOCalcSYLK, Gnumeric, and CSV. Returns an instance of PHPExcel.

The copy code is as follows:

/ / load factory class

Include'PHPExcel/IOFactory.php'

/ / path of xls file to be read

$inputFileName ='. / sampleData/example1.xls'

/ * * get the excel Operand using the load method of PHPExcel_IOFactory * * /

$objPHPExcel = PHPExcel_IOFactory::load ($inputFileName)

/ / get the currently 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 an 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 to be read. But unpredictable errors can occur if you load the wrong document type.

The copy 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 the currently active sheet

$curSheet = $objPHPExcel- > getActiveSheet ()

/ / return the data of the table as a two-dimensional array

$sheetData = $curSheet- > toArray (null,true,true,true)

Var_dump ($sheetData)

You can also use PHPExcel_IOFactory 's createReader method to get a Reader object without knowing the format of the file to be read.

The copy 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 the currently active sheet

$curSheet = $objPHPExcel- > getActiveSheet ()

/ / return the data of the table 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 through the identify () method of IOFactory, and then use the createReader () method to get through the reader.

The copy code is as follows:

$inputFileName ='. / sampleData/example1.xls'

/ * * determine the format of the input file * /

$inputFileType = PHPExcel_IOFactory::identify ($inputFileName)

/ * wear the corresponding reader * /

$objReader = PHPExcel_IOFactory::createReader ($inputFileType)

/ * * load the file to be read * /

$objPHPExcel = $objReader- > load ($inputFileName)

two。 Set read options for Excel

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

2.1.ReadingOnly Data from a Spreadsheet File

The setReadDataOnly () method, which configures the reader to pay no attention to the data type of the table data, which is returned in string format

The copy 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)

/ * * 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:

The copy code is as follows:

Array (8) {

[1] = >

Array (6) {

["A"] = >

String (15) "Integer Numbers"

["B"] = >

String (3) "123"

["C"] = >

String (3)

["D"] = >

String (4) "- 345"

["E"] = >

String (3) "456"

["F"] = >

NULL

}

[2] = >

Array (6) {

["A"] = >

String (22) "Floating PointNumbers"

["B"] = >

String (4) "1.23"

["C"] = >

String (5) "23.45"

["D"] = >

String (10) "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 (16) "19 December 1960"

["C"] = >

String (15) "10 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) "# Ngamma A"

["C"] = >

String (7) "# DIV/0!"

["D"] = >

NULL

["E"] = >

NULL

["F"] = >

NULL

}

}

If it is not set, it returns:

Array (8) {

[1] = >

Array (6) {

["A"] = >

String (15) "Integer Numbers"

["B"] = >

Float (123)

["C"] = >

Float (234)

["D"] = >

Float (- 345)

["E"] = >

Float (456)

["F"] = >

NULL

}

[2] = >

Array (6) {

["A"] = >

String (22) "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) "# Ngamma 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 (), which sets the worksheet to be read, and accepts the name of the worksheet as a parameter.

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

The copy code is as follows:

$inputFileType = 'Excel5'

$inputFileName ='. / sampleData/example1.xls'

$sheetnames = array ('Data Sheet # 1 recording 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 the worksheet, you can call setLoadAllSheets ().

After reading this article, I believe you have a certain understanding of "how to use PHPExcel". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.