phpoffice下载安装
不请求额外信息,客户端a标签直接下载就行。
excel 服务端
配置文件
return [
'stage'=>[
'savePath' => ROOT_PATH . 'public/resource/stage/', // 保存路径
'rootPath' => '/resource/stage/' // url访问的根路径
],
]
class Excel extends Controller
{
// 将数据导出至Excel
// @param $fileType 文件类型
public function exportExcel($arr_data = [],$fileType = "xlsx")
{
// 文件名
$fileName = "驿站财务数据";
// 模拟获取数据
// $data = $arr_data;
$data = self::getData();
// phpexcel 官方停止维护了
// $obj = new \PHPExcel();
$obj = new Spreadsheet();
// 以下内容是excel文件的信息描述信息
$obj->getProperties()->setCreator(''); //设置创建者
$obj->getProperties()->setLastModifiedBy(''); //设置修改者
$obj->getProperties()->setTitle(''); //设置标题
$obj->getProperties()->setSubject(''); //设置主题
$obj->getProperties()->setDescription(''); //设置描述
$obj->getProperties()->setKeywords('');//设置关键词
$obj->getProperties()->setCategory('');//设置类型
// 设置当前sheet
$obj->setActiveSheetIndex(0);
// 设置当前sheet的名称
$obj->getActiveSheet()->setTitle($fileName);
// 列标
$list = ['A', 'B', 'C','D','E','F','G','H','I','J'];
// 填充第一行数据
$obj->getActiveSheet()
->setCellValue($list[0] . '1', '序号')
->setCellValue($list[1] . '1', '驿站名称')
->setCellValue($list[2] . '1', '驿站地址')
->setCellValue($list[3] . '1', '手机号')
->setCellValue($list[4] . '1', '累计入库')
->setCellValue($list[5] . '1', '累计出库')
->setCellValue($list[6] . '1', '累计入账')
->setCellValue($list[7] . '1', '驿站余额')
->setCellValue($list[8] . '1', '短信充值金额')
->setCellValue($list[9] . '1', '驿站收费信息');
// 填充第n(n>=2, n∈N*)行数据
$length = count($data);
foreach ($data as $key=>$val){
$obj->getActiveSheet()->setCellValue($list[0] . ($key + 2), $val['stage_id'], \PHPExcel_Cell_DataType::TYPE_STRING);//将其设置为文本格式
$obj->getActiveSheet()->setCellValue($list[1] . ($key + 2), $val['name']);
$obj->getActiveSheet()->setCellValue($list[2] . ($key + 2), $val['address']);
$obj->getActiveSheet()->setCellValue($list[3] . ($key + 2), $val['phone']);
$obj->getActiveSheet()->setCellValue($list[4] . ($key + 2), $val['ruku']);
$obj->getActiveSheet()->setCellValue($list[5] . ($key + 2), $val['chuku']);
$obj->getActiveSheet()->setCellValue($list[6] . ($key + 2), $val['ruzhang']);
$obj->getActiveSheet()->setCellValue($list[7] . ($key + 2), $val['amount']);
$obj->getActiveSheet()->setCellValue($list[8] . ($key + 2), $val['duanxin']);
$obj->getActiveSheet()->setCellValue($list[9] . ($key + 2), $val['stage_express']);
}
// 设置加粗和左对齐
foreach ($list as $col) {
// 设置第一行加粗
$obj->getActiveSheet()->getStyle($col . '1')->getFont()->setBold(true);
// 设置第1-n行,左对齐
for ($i = 1; $i <= $length + 1; $i++) {
$obj->getActiveSheet()
->getStyle($col . $i)
->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
}
}
// 设置列宽
$obj->getActiveSheet()->getColumnDimension('A')->setWidth(20);
$obj->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$obj->getActiveSheet()->getColumnDimension('C')->setWidth(40);
$obj->getActiveSheet()->getColumnDimension('D')->setWidth(15);
$obj->getActiveSheet()->getColumnDimension('E')->setWidth(15);
$obj->getActiveSheet()->getColumnDimension('F')->setWidth(15);
$obj->getActiveSheet()->getColumnDimension('G')->setWidth(15);
$obj->getActiveSheet()->getColumnDimension('H')->setWidth(15);
$obj->getActiveSheet()->getColumnDimension('I')->setWidth(20);
$obj->getActiveSheet()->getColumnDimension('J')->setWidth(80);
// 导出
ob_clean();
if ($fileType == 'xls') {
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $fileName . '.xls');
header('Cache-Control: max-age=1');
$objWriter = new \PHPExcel_Writer_Excel5($obj);
$objWriter->save('php://output');
exit;
} elseif ($fileType == 'xlsx') {
// 直接下载输出, 返回文件流形式
// header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
// header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx');
// header('Cache-Control: max-age=1');
// $objWriter = \PHPExcel_IOFactory::createWriter($obj, 'Excel2007');
//
// // 文件流形式输出
// $objWriter->save('php://output');
// 返回文件路径
// $objWriter = \PHPExcel_IOFactory::createWriter($obj, 'Excel2007');
$objWriter = new Xlsx($obj);
$save_path = config('upload.stage'); //配置下载路径
$filename = $fileName . '.xlsx'; //文件名
$abs_filepath = $save_path['savePath'] . $filename; // 文件绝对路径
$show_path = $save_path['rootPath'] . $filename; // 下载相对路径
$objWriter->save($abs_filepath); //在该路径下保存生成好的表格文件
return [
'down_path' => getcwd().'/'.$show_path,
];
}
}
// 准备数据
protected function getData()
{
$studentList = [
[
'stuNo' => '20190101',
'name' => 'student01',
'class' => '1班'
], [
'stuNo' => '20190102',
'name' => 'student02',
'class' => '1班'
], [
'stuNo' => '20190103',
'name' => 'student03',
'class' => '1班'
]
];
return $studentList;
}
}
客户端接收
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<a onclick="down()" >点击下载</a>
</body>
</html>
<script typet="text/javascript" src="http://libs.baidu.com/jquery/1.9.1/jquery.min.js"></script>
<script src="https://unpkg.com/axios/dist/axios.min.js"></script>
<script>
function down(){
axios({
url: '/index/test/index', // 请求服务端接口
headers:{}, // 发送请求头
method: 'post',
data: { // 请求参数
}
}).then(res => {
console.log('请求结果:', res);
download() // 解析文件流信息 并下载文件
});
}
</script>
<script type="text/javascript">
function download(){
var oReq = new XMLHttpRequest();
oReq.open("GET", "/index/test/index", true);
oReq.responseType = "blob";
oReq.onload = function (oEvent) {
var content = oReq.response;
var elink = document.createElement('a');
elink.download = "xxxx.xlsx"; // 下载文件名字
elink.style.display = 'none';
var blob = new Blob([content]);
elink.href = URL.createObjectURL(blob);
document.body.appendChild(elink);
elink.click();
document.body.removeChild(elink);
};
oReq.send();
}
</script>