Introduction to the steps of php processing Excel

Introduction to the steps of php processing Excel
Encountered problems
In daily work, there is often a need to export a database table to Excel or import Excel into a database table. This requirement has been realized long ago. In order to facilitate import and export, Brothers www.lampbrother.net has divided it into two methods as records.

Code implementation
Reference to phpexcel class library
phpexcel has powerful Excel processing capabilities and has been downloaded millions of times on packagist. However, to be honest, the processing speed of excel is still very slow when the amount of data is large. Use with caution. After downloading from packagist or directly using composer require phpoffice/phpexcel, you can use phpexcel.

Export to Excel
In most cases, exporting excel is actually converting a two-digit array into a table.

use namespace PHPExcel;
/**
​​*/
function exportExcel($name, $ret_data){
$objPHPExcel = new PHPExcel();
//Set up the table
$objPHPExcel->get Properties()-> setCreator($name) ->setLastModifiedBy($name) ->setTitle("Office 2007 XLSX Test Document") ->setSubject("Office 2007 XLSX Test Document")
                         ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
                                                                                                                                                                             _data as $key => $row ) {
                                                                      use using use using . $objPHPExcel->setActiveSheetIndex( 0)->setCellValue( Cell::stringFromColumnIndex($i). ($num), $value2);
                                                                            jPHPExcel->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; Must 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);//总列数 
        $row = 1; 
        $curr = array(); 
        while ($row             for ($col = 0; $col                 $value = str_replace(array("n", "nr", "r"), "", $objWorksheet->getCellByColumnAndRow($col, $row)->getValue()); 
                $curr[$row][] = $value; 
            } 
            $row++; 
        } 
        array_shift($curr);//第一行一般是字段名（Excel中列的标题），导入时要移除 
        return $curr; 
} 

其他 
导出时保存的格式是xlsx，想要改成其他格式需要传入不同的参数。 
导入时如果有多个sheet时需要在上次打开时在要导入的sheet页（以保证当前sheet为activeSheet）关闭，或者根据sheet名在程序中选择sheet。