https://www.zzwws.cn/tag/Excel%E4%B8%8A%E4%BC%A0/ https://www.zzwws.cn/archives/4971/ 2020-12-12T15:50:00+08:00 1、生成Excel并下载<?php include "./PHPExcel-1.8/Classes/PHPExcel/IOFactory.php"; // Create new PHPExcel object $objPHPExcel = new PHPExcel(); // Set document properties $objPHPExcel->getProperties()->setCreator("Maarten Balliauw") ->setLastModifiedBy("Maarten Balliauw") ->setTitle("Office 2007 XLSX Test Document") ->setSubject("Office 2007 XLSX Test Document") ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") ->setKeywords("office 2007 openxml php") ->setCategory("Test result file"); // Add some data $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'ID'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1', '微信OpenID'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C1', '昵称'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D1', '性别'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E1', '国家'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F1', '省份'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G1', '城市'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H1', '场景'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I1', '标签'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J1', '头像'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K1', '关注时间'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A2', 1111); /*for ($i = 0; $i < count($result); $i++) { $j = $i + 2; $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $j, $result[$i]['id']); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . $j, $result[$i]['openid']); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . $j, $result[$i]['nickname']); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . $j, $result[$i]['sex']); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . $j, $result[$i]['country']); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F' . $j, $result[$i]['province']); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G' . $j, $result[$i]['city']); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H' . $j, $result[$i]['scene']); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . $j, $result[$i]['tagid']); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J' . $j, $result[$i]['headimgurl']); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K' . $j, date("Y-m-d H:i:s", $result[$i]['subscribe'])); }*/ // Rename worksheet $objPHPExcel->getActiveSheet()->setTitle('userlist'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); ob_end_clean(); //解决乱码核心 // Redirect output to a client’s web browser (Excel2007) $filename = date("YmdHis", time()); header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="' . $filename . '.xls"'); 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 // $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output');// $objWriter->save($filename.'.xls');// 保存到本地，上面的header()可以不用了 2、上传Excel文件<?php header("Content-type: text/html; charset=utf-8"); include "./PHPExcel-1.8/Classes/PHPExcel.php"; // var_dump($_FILES); if (@is_uploaded_file($_FILES['upfile']['tmp_name'])) { $upfile = $_FILES["upfile"]; //获取数组里面的值 $name = $upfile["name"]; //上传文件的文件名 $type = $upfile["type"]; //上传文件的类型 $size = $upfile["size"]; //上传文件的大小 $tmp_name = $upfile["tmp_name"]; //上传文件的临时存放路径 $dirname = 'uploads/'; if(!is_dir($dirname)){//判断有没有这个文件夹 mkdir($dirname);//新建文件夹 } $file_name = __DIR__ . '/uploads/' . $name; move_uploaded_file($tmp_name, $file_name); //将上传到服务器临时文件夹的文件重新移动到新位置 $error = $upfile["error"]; //上传后系统返回的值 if ($error == 0) { echo "文件上传成功啦！<br>"; } else { echo "上传失败"; } // print($file_name); $objReader = \PHPExcel_IOFactory::createReader('Excel5'); //创建读取实例 /* * log()//方法参数 * $file_name excel文件的保存路径 */ $objPHPExcel = $objReader->load($file_name, $encode = 'utf-8'); //加载文件 $sheet = $objPHPExcel->getSheet(0); //取得sheet(0)表 $highestRow = $sheet->getHighestRow(); // 取得总行数 $highestColumn = $sheet->getHighestColumn(); // 取得总列数 // 获取excel文件的数据，$row=1代表从第一行开始获取数据 for ($row = 1; $row <= $highestRow; $row++) { // Read a row of data into an array $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, null, true, false); //这里得到的rowData都是一行的数据，得到数据后自行处理，我们这里只打出来看看效果 echo '<pre>'; print_r($rowData); } unlink($file_name);//删除文件 echo '<button onclick="location.href = \'./\'" style="margin-top: 10px">重新上传</button>'; }else{ ?> <html lang="zh-cn"> <head> <meta charset="utf-8"/> </head> <body> <form action="" enctype="multipart/form-data" method="post"> 上传文件：<input type="file" name="upfile"/><br><!--上传多个文件：<input type="file" name="upfile[]" multiple/>--> <input type="submit" value="上传"/> </form> </body> <?php }?> PHPExcel下载地址：https://zhizun.lanzouy.com/irlxN0a371bi