类库：
"phpoffice/phpspreadsheet": "1.12",1 导入excel/csv，结果array数组
private function importFromExcel($filePath){ $ext = pathinfo($filePath, PATHINFO_EXTENSION); if (!in_array($ext, ['csv', 'xls', 'xlsx'])) { $this->error(__('Unknown data format')); } if ($ext === 'csv') { $file = fopen($filePath, 'r'); $filePath = tempnam(sys_get_temp_dir(), 'import_csv'); $fp = fopen($filePath, "w"); $n = 0; while ($line = fgets($file)) { $line = rtrim($line, "\n\r\0"); $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']); if ($encoding != 'utf-8') { $line = mb_convert_encoding($line, 'utf-8', $encoding); } if ($n == 0 || preg_match('/^".*"$/', $line)) { fwrite($fp, $line . "\n"); } else { fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n"); } $n++; } fclose($file) || fclose($fp); $reader = new Csv(); } elseif ($ext === 'xls') { $reader = new Xls(); } else { $reader = new Xlsx(); } $insert = []; try { if (!$PHPExcel = $reader->load($filePath)) { $this->error(__('Unknown data format')); } $currentSheet = $PHPExcel->getSheet(0); //读取文件中的第一个工作表 $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号 $allRow = $currentSheet->getHighestRow(); //取得一共有多少行 $maxColumnNumber = Coordinate::columnIndexFromString($allColumn); $fields = []; for ($currentRow = 1; $currentRow <= 1; $currentRow++) { for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) { $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue(); $fields[] = $val; } } $insert[] = $fields; for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) { $values = []; for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) { $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue(); $values[] = is_null($val) ? '' : $val; } $flag = false; foreach ($values as $v) { if (!empty($v)) $flag = true; } if ($flag) $insert[] = $values; } } catch (\Exception $exception) { $this->error($exception->getMessage()); } if (!$insert) { $this->error(__('No rows were updated')); } return $insert; }2 导出CSV，不依赖库
function exportToCvs($fileName = '', $headArr = [], $data = []) { ini_set('memory_limit', '1024M'); //设置程序运行的内存 ini_set('max_execution_time', 0); //设置程序的执行时间,0为无上限 ob_end_clean(); //清除内存 ob_start(); header("Content-Type: text/csv"); header("Content-Disposition:filename=" . $fileName . '.csv'); $fp = fopen('php://output', 'w'); fwrite($fp, chr(0xEF) . chr(0xBB) . chr(0xBF)); fputcsv($fp, $headArr); $index = 0; foreach ($data as $item) { if ($index == 1000) { //每次写入1000条数据清除内存 $index = 0; ob_flush();//清除内存 flush(); } $index++; fputcsv($fp, $item); } ob_flush(); flush(); ob_end_clean(); exit(); }3 导出excel
public function exportToExcel($title, $headers=[], $datas){ // Create new Spreadsheet object $spreadsheet = new Spreadsheet(); // Set document properties $spreadsheet->getProperties()->setCreator('syx') ->setLastModifiedBy('syx') ->setTitle($title) ->setSubject($title) ->setDescription('') ->setKeywords('') ->setCategory(''); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $worksheet = $spreadsheet->setActiveSheetIndex(0); $worksheet->setTitle("数据"); $styleArray = array( 'font' => array( 'bold' => false, 'color' => array('rgb' => '000000'), 'size' => 12, 'name' => 'Verdana' )); // Add some data 1 // $spreadsheet->setActiveSheetIndex(0) // ->setCellValue('A1', 'Hello') // ->setCellValue('B2', 'world!') // ->setCellValue('C1', 'Hello') // ->setCellValue('D2', 'world!'); // Add some data 2 // $worksheet->setCellValueByColumnAndRow(1, 1, 'Hello'); $line = 0; if (!empty($headers)) { $line++; $col = 1; foreach ($headers as $field => $value) { $value = ' '.$value; $worksheet->setCellValueByColumnAndRow($col, $line, $value); $worksheet->getCellByColumnAndRow($col, $line)->getStyle()->applyFromArray($styleArray); $col++; } } foreach ($datas as $index => $item) { $line++; $col = 1; foreach ($item as $field => $value) { $value = ' '.$value; $worksheet->setCellValueByColumnAndRow($col, $line, $value); $worksheet->getCellByColumnAndRow($col, $line)->getStyle()->applyFromArray($styleArray); $col++; } } // Redirect output to a client’s web browser (Xlsx) header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="'.$title.'.xlsx"'); header('Cache-Control: max-age=0'); // If you're serving to IE 9, then the following may be needed header('Cache-Control: max-age=1'); // If you're serving to IE over SSL, then the following may be needed header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified header('Cache-Control: cache, must-revalidate'); // HTTP/1.1 header('Pragma: public'); // HTTP/1.0 $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); $writer->save('php://output'); }over