Common instructions for parsing PHPExcel and an introduction to integrating PHPExcel into the CI framework
- 2020-06-19 09:53:20
- OfStack
Write and generate operation of excel:
include 'PHPExcel.php';
include 'PHPExcel/Writer/Excel2007.php';
// or include 'PHPExcel/Writer/Excel5.php'; Used for output .xls the
include 'PHPExcel/IOFactory.php';//phpexcel The factory class
// create 1 a 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");
// 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');
// Direct generation file
$objWriterr->save( 'the name of the file ');
// Set up the excel Properties:
// founder
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
// Last reviser
$objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
// The title
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
// The title
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
// describe
$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
// The keyword
$objPHPExcel->getProperties()->setKeywords("office 2007 openxml php");
// species
$objPHPExcel->getProperties()->setCategory("Test result file");
// Set the current sheet
$objPHPExcel->setActiveSheetIndex(0);
// Set up the sheet the name
$objPHPExcel->getActiveSheet()->setTitle('Simple');
// Sets the value of the 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)');
// Merged cell
$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');
// Detached cell
$objPHPExcel->getActiveSheet()->unmergeCells('A28:B28');
// To protect the cell
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); // Needs to be set to true in order to enable any worksheet protection!
$objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel');
// Set the 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 the width width
// Set column widths
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
// Set up the 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 up the 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);
// Vertical center
$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
Set up the column the 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 up the border the 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 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(110);
$objDrawing->setRotation(25);
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(45);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
// Handle Chinese output problems
// You need to convert the string to UTF-8 Otherwise, Chinese characters will be output as blank, as follows:
$str = iconv('gb2312', 'utf-8', $str);
Or you could write 1 A function that 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 1 Excel is to use IO Factory of PHPExel and call the static method load of PHPExcel_IOFactory class, which can automatically recognize the document format, including Excel2007, Excel2003XML, OOCalcSYLK, Gnumeric, CSV. Returns 1 instance of PHPExcel.
// Load factory class
include'PHPExcel/IOFactory.php';
// To read the xls The file path
$inputFileName = './sampleData/example1.xls';
/** with PHPExcel_IOFactory the load method excel Action object **/
$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
// Gets the current active table, called toArray Method to get the table 2 Dimensional array
$sheetData =$objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
var_dump($sheetData);
1. Create 1 ExcelReader to load 1 Excel document
If you know the format of the Excel document, you can create a corresponding Reader to load the Excel document to be read. But if you load the wrong document type, you can get unpredictable errors.
$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();
// In order to 2 Returns the data of the table as a dimensional array
$sheetData = $curSheet->toArray(null,true,true,true);
var_dump($sheetData);
You can also use the createReader method of PHPExcel_IOFactory to get an Reader object without knowing the format of the file to read.
$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();
// In order to 2 Returns the data of the table as a 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 to the reader.
$inputFileName = './sampleData/example1.xls';
/** Determine the format of the input file **/
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
/** Wear a corresponding reader **/
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
/** Loads the file to read **/
$objPHPExcel = $objReader->load($inputFileName);
2. Set read options for Excel
Before loading a file using the load() method, you can set read options to control the behavior of load.
2.1.ReadingOnly Data from a Spreadsheet File
The setReadDataOnly() method, where the configuration reader does not care about the data type of the table data, is returned in string format
$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:
array(8) {
[1]=>
array(6) {
["A"]=>
string(15) "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(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) "#N/A"
["C"]=>
string(7) "#DIV/0!"
["D"]=>
NULL
["E"]=>
NULL
["F"]=>
NULL
}
}
If not set, return:
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) "#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(), which sets worksheet to read, takes the name of worksheet as a parameter.
/** 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,'<br />';
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
echo 'Loading Sheet"',$sheetname,'" only<br />';
$objReader->setLoadSheetsOnly($sheetname);
$objPHPExcel =$objReader->load($inputFileName);
echo '<hr />';
echo$objPHPExcel->getSheetCount(),' worksheet',(($objPHPExcel->getSheetCount()== 1) ? '' : 's'),' loaded<br /><br />';
$loadedSheetNames =$objPHPExcel->getSheetNames();
foreach($loadedSheetNames as$sheetIndex => $loadedSheetName) {
echo $sheetIndex,' -> ',$loadedSheetName,'<br />';
}
If you want to read multiple worksheet, you can pass one array
$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().