Introduction to PHP processing Excel steps

PHP handles Excel Step Introduction

Encounter problems

often at work, there is a need to export a database table as Excel or import Excel into a database table. This demand has been realized early, in order to facilitate the import and export, brother Lian www.lampbrother.net it into two methods as a record.

Code implementation

Reference to the Phpexcel class library

Phpexcel has a powerful Excel processing power, already has millions of downloads on the packagist, but to be honest, Excel processing speed is still very slow, the amount of data is used cautiously. Phpexcel can be used after downloading it on packagist or directly using composer require phpoffice/phpexcel.

Export as Excel

In most cases, exporting Excel is actually converting a two-bit array into a table.

Use namespace Phpexcel;

/**

* @param $name string to save the name of Excel

* @param $ret _data converted to a two-dimensional array of tables

* @throws phpexcel_exception

* @throws phpexcel_reader_exception

*/

function Exportexcel ($name, $ret _data) {

$objPHPExcel = new Phpexcel ();

Setup table

$objPHPExcel->getproperties ()->setcreator ($name)

->setlastmodifiedby ($name)

->settitle ("Office" XLSX Test Document ")

->setsubject ("Office" XLSX Test Document ")

->setdescription ("Test document for Office" XLSX, generated using PHP classes. ")

->setkeywords ("Office openxml PHP")

->setcategory ("Test result file");

Populating data

foreach ($ret _data as $key = + $row) {

$num = $key + 1;

$row = Array_values ($row);

$i = 0;

foreach ($row as $key 2 = $value 2) {

$objPHPExcel->setactivesheetindex (0)->setcellvalue (Cell::stringfromcolumnindex ($i). ($num), $value 2);

$i + +;

}

}

Set the table and output

$objPHPExcel->getactivesheet ()->settitle ($name);

Header (' Content-type:application/vnd.ms-excel ');

Header ("content-disposition:attachment;filename={$name}.xls");

Header (' cache-control:max-age=0 ');

Header (' cache-control:max-age=1 ');

Header (' last-modified: '. Gmdate (' d, D M Y h:i:s '). ' GMT ');

Header (' Cache-control:cache, Must-revalidate ');

Header (' Pragma:public '); http/1.0

$objWriter = Phpexcel_iofactory::createwriter ($objPHPExcel, ' Excel5 ');

$objWriter->save (' php://output ');

Exit

}

Import Excel

Similarly, importing Excel is actually converting Excel data into a two-dimensional array, which requires Excel to conform to the format.

function GetRows ($inputFileName)

{

if (!file_exists ($inputFileName)) {

throw new Exception ("File not existed");

}

$inputFileType = Phpexcel_iofactory::identify ($inputFileName);

$objReader = Phpexcel_iofactory::createreader ($inputFileType);

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

$objWorksheet = $objPHPExcel->getactivesheet ();

$highestRow = $objWorksheet->gethighestrow ();

$highestColumn = $objWorksheet->gethighestcolumn ();

$highestColumnIndex = phpexcel_cell::columnindexfromstring ($highestColumn);//Total number of columns

$row = 1;

$curr = Array ();

while ($row <= $highestRow) {

for ($col = 0; $col < $highestColumnIndex; $col + +) {

$value = Str_replace (Array ("\ n", "\n\r", "\ R"), "", $objWorksheet->getcellbycolumnandrow ($col, $row)->getvalue ( ));

$curr [$row] = $value;

}

$row + +;

}

Array_shift ($curr);//The first line is typically the field name (the title of the column in Excel), to be removed when importing

return $curr;

}

Other

The format saved on export is xlsx, and you need to pass in different parameters to change to another format.

If you have more than one sheet on import, you need to close the sheet page you are importing (to ensure that the current sheet is ActiveSheet) when you last open, or select sheet in your program based on the sheet name.

Introduction to PHP processing Excel steps