PHPExcel文件的导入和导出
前言
- 文件导入和导出在web开发的应用场景十分广泛，本文就探讨如何使用phpExcel实现简单的导入和导出文件
- 项目注意说明：
json_decode和json_encode是对json数据的转换
ob_end_clean(); //清空(擦除)缓冲区并关闭输出缓冲,否则导出的文件可能是乱码
echo “<\pre>”;//格式化 配合print_r()有利于调试
有关于session一定要在首航session_start();开启
文件上传的注意：enctype=“multipart/form-data” method=post,<input type=“file” name=“file” >
设置编码： header(“Content-Type:text/html;charset=utf-8”); //设置编码
php跳转：header(“Location:/index.php”);//跳转到首页
自动转换分割符：DIRECTORY_SEPARATOR，使用DIRECTORY_SEPARATOR常量来添加正确的目录分隔符（在不同的操作系统中可能不同）。然后我们使用realpath()函数来获取文件的绝对路径。
一、phpExcel下载
下载PHPExcel插件：https://github.com/PHPOffice/PHPExcel/archive/1.8.1.zip
慕课网学习：http://www.imooc.com/view/433
二、使用步骤
1.准备
下载好插件后，找到classes目录，这里我改名为PHPExcel,然后单独提取出来，放到你的项目目录。
2.文件下载
代码如下（示例）：
function upload($path)
{
if ($_FILES["file"]["error"] > 0) {
echo "Error: " . $_FILES["file"]["error"] . "<br />";
} else {
echo "上传文件名: " . $_FILES["file"]["name"] . "<br />";
echo "文件类型: " . $_FILES["file"]["type"] . "<br />";
echo "文件大小: " . ($_FILES["file"]["size"] / 1024) . " Kb<br />";
echo "临时文件名: " . $_FILES["file"]["tmp_name"] . "<br />";
$dir = dirname(__DIR__);//获取当前文件的父路径
if (move_uploaded_file($_FILES["file"]["tmp_name"], "$path/" . $_FILES['file']['name'])) {//从暂存区域中将上传的文件下载到本地
echo "上传成功";
}
}
}
3.文件导入到数据库
以下是代码：
//文件导入
function import($path)
{
header("Content-Type:text/html;charset=utf-8"); //设置编码
global $usql, $dir; //声明全局变量，$usql操作数据库的，$dir：父目录
require_once("$dir/PHPExcel/PHPExcel/IOFactory.php"); //引入文件
//下载文件
upload($path); //下载文件到本地
//读取的文件路径
$file = $path . "/" . $_FILES["file"]["name"];
//清空数据库
// $usql->clear();
//读取文件
$sheet = \PHPExcel_IOFactory::load($file);
//保存到数据库
// echo "保存数据库中....<br>";
foreach ($sheet->getWorksheetIterator() as $sheetUnit) { //遍历sheet
foreach ($sheetUnit->getRowIterator() as $rowUnit) { //遍历sheet中的行
if ($rowUnit->getRowIndex() == 1) { //排查第一行，我这里第一行是标题
continue;
}
$arr = array(); //存储每一行的数据
foreach ($rowUnit->getCellIterator() as $cellUnit) { //遍历每一列
$data = $cellUnit->getValue();
$arr[] = $data;
}
$usql->importInsert($arr[1], $arr[2], $arr[3]); //插入数据
// echo "<br>";
}
// echo "<br>";
}
}
将数据库中数据导出
以下是导出到浏览器的代码
//导出到浏览器的设置
function browser_export($type, $filename)
{
ob_end_clean(); //清空(擦除)缓冲区并关闭输出缓冲,否则导出的文件可能是乱码
if ($type == "Excel5") {
// Redirect output to a client’s web browser (Excel5) 告诉浏览器将要输出excel03文件
header('Content-Type: application/vnd.ms-excel;charset=utf-8');
} elseif ($type == "Excel2007E") {
// Redirect output to a client’s web browser (Excel2007) 告诉浏览器将要输出excel07文件
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
}
//告诉浏览器将输出文件的名称
header('Content-Disposition: attachment;filename="' . $filename . '"');
//禁止缓存
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');
*/
}
//导出到浏览器
function exportBrowerse()
{
header("Content-type:text/html;charset=utf-8"); //设置编码
global $usql, $dir; //声明全局变量，$usql操作数据库的，$dir：父目录
//创建excel
$excel = new \PHPExcel();
//指定当前sheet
$excel->createSheet();
$excel->setActiveSheetIndex(1);
$sheet = $excel->getActiveSheet();
$sheet->setTitle("留言板信息");
//填充数据
$sheet->setCellValue("A1", "id")->setCellValue("B1", "标题")->setCellValue("C1", "内容")
->setCellValue("D1", "创建时间");
//获取数据库中所有数据
$list = json_decode($usql->list());
echo "<pre>";//格式化
//填充数据
foreach ($list as $k => $v) {
$k++;
// echo $k."::". $v->id." ".$v->name."<br>";//对象
$sheet->setCellValue("A" . ($k + 1), $v->id)->setCellValue("B" . ($k + 1), $v->name)
->setCellValue("C" . ($k + 1), $v->title)->setCellValue("D" . ($k + 1), $v->time);
}
echo "<hr>";
//保存xlsx文件，excel5是xls格式的
$writerObj = \PHPExcel_IOFactory::createWriter($excel, "Excel5");
// $writerObj->save("$dir/file/excel/liuyan.xlsx");
browser_export('Excel5', 'b.xls'); //浏览器设置
$writerObj->save('php://output');
}
完整代码
mysql.php的代码
<?php
class USql
{
public $username;
public $password;
public $dbname;
public $host;
public $conn;
public function __construct($username, $password, $dbname, $host)
{
$this->username = $username;
$this->password = $password;
$this->dbname = $dbname;
$this->host = $host;
try {
$pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
// 设置PDO错误模式为异常
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// echo "成功连接到数据库";
$this->conn = $pdo;
} catch (PDOException $e) {
echo "数据库连接失败: " . $e->getMessage();
}
}
//返回全部数据，以json格式返回
public function list()
{
$sql = "select * from user order by create_date desc;";
$stmt = $this->conn->query($sql);
$arr = array();
$i = 0;
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$arr[$i++] = ["id" => $row['id'], "name" => $row['name'], "title" => $row["title"], "time" => $row['create_date']];
}
return json_encode($arr, JSON_UNESCAPED_UNICODE);
}
//插入
public function insert($name, $title)
{
$sql = "insert into user(name,title)values('$name','$title');";
$this->conn->exec($sql);
}
//清空数据库
public function clear()
{
$this->conn->exec('delete from user;');
}
//导入文件的插入
function importInsert($name, $title, $time)
{
$sql = "insert into user(name,title,create_date)values('$name','$title','$time')";
$this->conn->exec($sql);
}
}
?>
excel.php的代码：
<?php
namespace excel;
$dir = dirname(__DIR__);
// echo $dir."<br>";
require_once("$dir/PHPExcel/PHPExcel.php");
//文件导出，导出到固定的文件夹
function export()
{
global $usql, $dir;
//创建excel
$excel = new \PHPExcel();
//创建sheet并且指定当前sheet
$excel->createSheet(); //创建sheet
$excel->setActiveSheetIndex(1); //指定sheet，这里指定第二个sheet
$sheet = $excel->getActiveSheet(); //获取sheet
$sheet->setTitle("留言板信息"); //设置sheet标题
//填充标题数据
$sheet->setCellValue("A1", "id")->setCellValue("B1", "标题")->setCellValue("C1", "内容")
->setCellValue("D1", "创建时间");
//获取数据库中所有数据
$list = json_decode($usql->list());
echo "<pre>";//格式化
//填充数据
foreach ($list as $k => $v) {
$k++;
// echo $k."::". $v->id." ".$v->name."<br>";//对象
$sheet->setCellValue("A" . ($k + 1), $v->id)->setCellValue("B" . ($k + 1), $v->name)
->setCellValue("C" . ($k + 1), $v->title)->setCellValue("D" . ($k + 1), $v->time);
}
echo "<hr>";
//保存xlsx文件，excel5是xls格式的
$writerObj = \PHPExcel_IOFactory::createWriter($excel, "Excel2007");
$writerObj->save("$dir/file/excel/liuyan.xlsx"); //保存到本地
}
//导出到浏览器的设置
function browser_export($type, $filename)
{
ob_end_clean(); //清空(擦除)缓冲区并关闭输出缓冲,否则导出的文件可能是乱码
if ($type == "Excel5") {
// Redirect output to a client’s web browser (Excel5) 告诉浏览器将要输出excel03文件
header('Content-Type: application/vnd.ms-excel;charset=utf-8');
} elseif ($type == "Excel2007E") {
// Redirect output to a client’s web browser (Excel2007) 告诉浏览器将要输出excel07文件
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
}
//告诉浏览器将输出文件的名称
header('Content-Disposition: attachment;filename="' . $filename . '"');
//禁止缓存
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');
*/
}
//导出到浏览器
function exportBrowerse()
{
header("Content-type:text/html;charset=utf-8"); //设置编码
global $usql, $dir; //声明全局变量，$usql操作数据库的，$dir：父目录
//创建excel
$excel = new \PHPExcel();
//指定当前sheet
$excel->createSheet();
$excel->setActiveSheetIndex(1);
$sheet = $excel->getActiveSheet();
$sheet->setTitle("留言板信息");
//填充数据
$sheet->setCellValue("A1", "id")->setCellValue("B1", "标题")->setCellValue("C1", "内容")
->setCellValue("D1", "创建时间");
//获取数据库中所有数据
$list = json_decode($usql->list());
echo "<pre>";//格式化
//填充数据
foreach ($list as $k => $v) {
$k++;
// echo $k."::". $v->id." ".$v->name."<br>";//对象
$sheet->setCellValue("A" . ($k + 1), $v->id)->setCellValue("B" . ($k + 1), $v->name)
->setCellValue("C" . ($k + 1), $v->title)->setCellValue("D" . ($k + 1), $v->time);
}
echo "<hr>";
//保存xlsx文件，excel5是xls格式的
$writerObj = \PHPExcel_IOFactory::createWriter($excel, "Excel5");
// $writerObj->save("$dir/file/excel/liuyan.xlsx");
browser_export('Excel5', 'b.xls'); //浏览器设置
$writerObj->save('php://output');
}
//文件导入
function import($path)
{
header("Content-Type:text/html;charset=utf-8"); //设置编码
global $usql, $dir; //声明全局变量，$usql操作数据库的，$dir：父目录
require_once("$dir/PHPExcel/PHPExcel/IOFactory.php"); //引入文件
//下载文件
upload($path); //下载文件到本地
//读取的文件路径
$file = $path . "/" . $_FILES["file"]["name"];
//清空数据库
// $usql->clear();
//读取文件
$sheet = \PHPExcel_IOFactory::load($file);
//保存到数据库
// echo "保存数据库中....<br>";
foreach ($sheet->getWorksheetIterator() as $sheetUnit) { //遍历sheet
foreach ($sheetUnit->getRowIterator() as $rowUnit) { //遍历sheet中的行
if ($rowUnit->getRowIndex() == 1) { //排查第一行，我这里第一行是标题
continue;
}
$arr = array(); //存储每一行的数据
foreach ($rowUnit->getCellIterator() as $cellUnit) { //遍历每一列
$data = $cellUnit->getValue();
$arr[] = $data;
}
$usql->importInsert($arr[1], $arr[2], $arr[3]); //插入数据
// echo "<br>";
}
// echo "<br>";
}
}
function upload($path)
{
if ($_FILES["file"]["error"] > 0) {
echo "Error: " . $_FILES["file"]["error"] . "<br />";
} else {
echo "上传文件名: " . $_FILES["file"]["name"] . "<br />";
echo "文件类型: " . $_FILES["file"]["type"] . "<br />";
echo "文件大小: " . ($_FILES["file"]["size"] / 1024) . " Kb<br />";
echo "临时文件名: " . $_FILES["file"]["tmp_name"] . "<br />";
// echo dirname(__DIR__); //获取当前文件的父路径
if (move_uploaded_file($_FILES["file"]["tmp_name"], "$path/" . $_FILES['file']['name'])) { //从暂存区域中将上传的文件下载到本地
echo "上传成功";
$GLOBALS['img'] = $_FILES['file']['name'];
}
}
}
// upload($dir.'/file/excel');
// upload($dir.'/file/img');
// exportBrowerse();
//export();
?>
submit.php代码：
<?php session_start();
require_once(dirname(__FILE__) . "/mysql.php");
$usql = new USql("root", "root", "phptest", "localhost");
require_once(dirname(__FILE__) . "/excel.php");
$action = $_REQUEST['action'];
// print_r($_SERVER);
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
// echo "action的值为" . $action;
// print_r($_REQUEST);
switch ($action) {
case 'submit':
$name = $_POST['name'];
$title = $_POST['title'];
$usql->insert($name, $title);
break;
case 'import':
// echo $dir . "=》" . "进入了导入<br>";
\excel\import($dir . "/file/excel");
break;
case "upload":
// echo $dir . "=》" . "进入了文件上传<br>";
\excel\upload($dir . "/file/img");
$_SESSION['img'] = $GLOBALS['img'];
break;
}
} else {
if ($action == 'export') {
\excel\exportBrowerse();
}
}
header("Location:/index.php");//跳转到首页
?>
前端index.php代码，不包含css
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>留言板</title>
<link rel="stylesheet" href="./file/bootstrap.min.css">
<link rel="stylesheet" href="./css/index.css">
</head>
<body>
<div class="container">
<h1 class="text-center mb-4">留言板</h1>
<div class="d-flex align-items-beetween align-items-center justify-content-center">
<form class="text-center" action="./api/submit.php?action=import" method="post"
enctype="multipart/form-data">
<div class="form-group" style="max-width: 210px;">
<input type="file" name="file" class="form-control-file" id="importFile">
</div>
<button type="submit" class="btn btn-primary">导入</button>
</form>
<form class="text-center" action="./api/submit.php?action=upload" method="post"
enctype="multipart/form-data">
<div class="form-group" style="max-width: 210px;">
<input type="file" name="file" class="form-control-file" id="exportFile">
</div>
<button type="submit" class="btn btn-primary">文件上传</button>
</form>
<img style="height:100px;width:100px;border:1px solid red;" src="<?php session_start();
echo "./file/img/" . ($_SESSION['img']??"1.jpg"); ?>" alt="">
<a href="./api/submit.php?action=export" style="position:relative;margin-left:20px;">导出数据</a>
</div>
<form id="messageForm" action="./api/submit.php?action=submit" method="post">
<div class="form-group">
<label for="title">标题:</label>
<input type="text" class="form-control" name="name" id="title" placeholder="请输入标题" required>
</div>
<div class="form-group">
<label for="message">留言:</label>
<textarea class="form-control" id="message" name="title" rows="5" required></textarea>
</div>
<input type="submit" class="btn btn-primary w-100">
</form>
<ul id="messageList" class="list-group mt-3">
</ul>
</div>
<script src="./file/jquery.slim.min.js"></script>
<!-- // <script src="https://code.jquery.com/jquery-3.5.1.slim.min.js"></script> -->
<script src="./file/bootstrap.min.js"></script>
<?php
include("./api/mysql.php");
$usql = new USql("root", "root", "phptest", "localhost");
?>
<script>
$(document).ready(function () {
//渲染函数
function list() {
//查询列表数据
let list = <?php echo $usql->list(); ?>;
let msgList = document.querySelector('#messageList');
// console.log(msgList);
let listItem = '';
//遍历封装li标签
list.forEach(element => {
listItem += `
<li class="list-group-item">
<strong>${element.name}</strong>
<p>${element.title}</p>
<p>${element.time}</p>
</li>
`;
});
msgList.innerHTML = listItem;
}
list();
});
</script>
</body>
</html>
2446
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
