搜索到
1
篇与
PhpSpreadsheet
的结果
-
PhpSpreadsheet实现导入&导出功能 前文导入excel的文本内容如下代码演示composer安装PhpSpreadsheet (这是新的Excel类库)，不用旧的PHPExcel的类库，旧的已被官网弃用不维护了！！前端代码<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Title</title> </head> <body> <form action="./index5.php" method="post" enctype="multipart/form-data"> <input type="file" name="fileUpload" /> <input type="submit" value="上传文件" /> </form> </body> </html>后端代码 require_once 'vendor/autoload.php'; $filePath = $_FILES['fileUpload']['tmp_name']; $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx'); $reader->setReadDataOnly(TRUE); $spreadsheet = $reader->load($filePath); //载入excel表格 $worksheet = $spreadsheet->getActiveSheet(); $highestRow = $worksheet->getHighestRow(); // 总行数 $highestColumn = $worksheet->getHighestColumn(); // 总列数 $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // e.g. 5 $lines = $highestRow - 1; if ($lines <= 0) { exit('Excel表格中没有数据'); } $import_data = []; for ($i = 2; $i <= $highestRow; $i++) { $import_data[$i]['id'] = $spreadsheet->getActiveSheet()->getCell("A" . $i)->getValue(); $import_data[$i]['nick_name'] = $spreadsheet->getActiveSheet()->getCell("B" . $i)->getValue(); $import_data[$i]['number'] = $spreadsheet->getActiveSheet()->getCell("C" . $i)->getValue(); $import_data[$i]['vip_number'] = $spreadsheet->getActiveSheet()->getCell("D" . $i)->getValue(); $import_data[$i]['card'] = $spreadsheet->getActiveSheet()->getCell("E" . $i)->getValue(); } $import_data = array_values($import_data);//重置索引 echo "<pre>"; print_r($import_data);exit; exit; 第二种方式不用$spreadsheet读取的文件的变量句柄，用 $worksheet获取所有的表格行数，先获取某一个sheet。 require_once 'vendor/autoload.php'; $filePath = $_FILES['fileUpload']['tmp_name']; $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx'); $reader->setReadDataOnly(TRUE); $spreadsheet = $reader->load($filePath); //载入excel表格 $worksheet = $spreadsheet->getActiveSheet(); $highestRow = $worksheet->getHighestRow(); // 总行数 $highestColumn = $worksheet->getHighestColumn(); // 总列数 $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // e.g. 5 $lines = $highestRow - 1; if ($lines <= 0) { exit('Excel表格中没有数据'); } for ($row = 2; $row <= $highestRow; ++$row) { $temp = array( 'id' => $worksheet->getCellByColumnAndRow('1', $row)->getValue(), 'nick_name' => $worksheet->getCellByColumnAndRow('2', $row)->getValue(), 'number' => $worksheet->getCellByColumnAndRow('3', $row)->getValue(), 'vip_number' => $worksheet->getCellByColumnAndRow('4', $row)->getValue(), 'card' => $worksheet->getCellByColumnAndRow('5', $row)->getValue(), 'other' => $worksheet->getCellByColumnAndRow('6', $row)->getValue(), 'addr' => $worksheet->getCellByColumnAndRow('7', $row)->getValue(), ); $list[] = $temp; } echo "<pre>"; print_r($list);exit;这样就可以根据数据入数据库了导出 require_once 'vendor/autoload.php'; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet; use PhpOffice\PhpSpreadsheet\Cell\DataValidation; use PhpOffice\PhpSpreadsheet\IOFactory; function exportExcel(){ //定义一个excel的header表头 $header = ['A1' => '序号', 'B1' => '姓名', 'C1' => '证件号', 'D1' => '张数', 'E1' => '证件类型', 'F1' => '省', 'G1' => '市', 'H1' => '区/镇', 'I1' => '类别', 'J1' => '设计人', 'K1' => '完成时间', 'L1' => '备注']; $list = [ ['id'=>1,'name'=>'黎明强','card'=>'13800','number'=>10], ['id'=>2,'name'=>'黎明','card'=>'13811','number'=>100], ['id'=>3,'name'=>'黎明强森','card'=>'13822','number'=>200], ['id'=>4,'name'=>'黎明强2','card'=>'13833','number'=>300], ['id'=>5,'name'=>'黎明强3','card'=>'13844','number'=>400], ]; $spreadsheet = new Spreadsheet(); $sheetMain = $spreadsheet -> getsheet(0);//主sheet $sheetMain -> setTitle('人员信息');//设置sheet的名称 $sheetMain -> getPageSetup() -> setHorizontalCentered(true); $sheetMain -> getPageSetup() -> setVerticalCentered(false); foreach ($header as $key => $value) { $sheetMain -> setCellValue($key, $value); } $row = 2; //从第二行开始 foreach ($list as $item) { $column = 1; foreach ($item as $value) { $sheetMain->setCellValueByColumnAndRow($column, $row, $value); $column++; } $row++; } //定义文件名称，需要带有定义的后缀名 $filename = 'test.xlsx'; ob_end_clean(); //清除缓冲区,避免乱码 //将输出重定向到客户端的web浏览器 header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="' . $filename . '"'); header('Cache-Control: max-age=0'); //如果浏览器为IE9 header('Cache-Control: max-age=1'); //如果通过SSL向IE提供服务 header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); header('Cache-Control: cache, must-revalidate');//HTTP/1.1 header('Pragma: public');//HTTP/1.0 $writer = IOFactory ::createWriter($spreadsheet, 'Xlsx'); //指定格式 $writer -> save('php://output'); exit; }运行exportExcel();
