PhpSpreadsheet 是用来操作excel文件。
使用升级到最新版本的 composer 安装 PhpSpreadsheet,见前一篇博客 discuz3.4定制开发: 将composer更新到最新版本。
打开命令行窗口,进入项目目录 D:\discuz_study\www 。执行命令:
D:\phpstudy_pro\Extensions\composer1.8.5\composer require phpoffice/phpspreadsheet
运行结果:
结果在www目录中生成1个文件夹和2个文件:
修改一个以前生成的php文件 D:\discuz_study\www\source\module\cat\cat_dd_crud_list_edit.php :
<?php
//namespace app\admin\service;
// mod文件只能被入口文件引用,不能直接访问
if(!defined('IN_DISCUZ')) {
exit('Access Denied');
}
require_once './vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = new Spreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
// 批量赋值
$sheet->setCellValue('A3','ID');
$sheet->setCellValue('B3','name');
$sheet->setCellValue('C3','years');
$sheet->setCellValue('D3','high');
$sheet->fromArray(
[
[1,'tom','18','188cm'],
[2,'bill','17','165cm'],
[3,'dog','21','180cm']
],
NULL,
'A4'
);
// 合并单元格 合并后,赋值只能给A1,开始的坐标。
//$sheet->mergeCells('A1:B5');
//$sheet->getCell('A1')->setValue('欧阳克');
# Xlsx类 将电子表格保存到文件
$writer = new Xlsx($spreadsheet);
$writer->save('1.xls');
// 客户端文件下载
header('Content-Type:application/vnd.ms-excel');
header('Content-Disposition:attachment;filename=1.xls');
header('Cache-Control:max-age=0');
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
$writer->save('php://output');
//初始化当前页码
$page = empty($_GET['page'])?1:intval($_GET['page']);
if($page<1) $page=1;
//分页
$perpage = 20;
$start = ($page-1)*$perpage;
//获取当前页的留言数据
//$cat_dd_list = array();
//$query = DB::query("SELECT * FROM ".DB::table('cat_dd')." WHERE 1 ORDER BY ID DESC LIMIT $start, $perpage");
$gtmj = DB::fetch($query);
//while($cat_dd = DB::fetch($query)) {
$gtmj['ID'] = dgmdate($mood['dateline'], 'u');
//$cat_dd_list[] = $cat_dd;
//}
//print_r($gtmj_list);
//print(count($gtmj_list));
//die();
//获得一个简单的分页,只有上一页和下一页,这个不需要count()数据表中的所有记录
//$multi = simplepage(count($gtmj_list), $perpage, $page, 'gtmj.php?mod=gtmj_list');
//数据准备完毕,引入相应的模板,准备输出
//include_once template("cat/cat_dd_crud_list");
//include_once template("cat/list_test");
if ($_GET['page']){
$page = empty($_GET['page']) ? 1 : intval($_GET['page']);
if($page<1) $page=1;
$perpage = empty($_GET['rows']) ? 20 : intval($_GET['rows']);
$start = ($page-1)*$perpage;
// 多个查询条件
$m_conds = '';
$get_json = C::t('cat_dd')->fetch_json_by_sql($start, $perpage, $m_conds);
echo $get_json;
exit;
}
elseif ($_GET['ac']=='exc'){
//$data_arr = C::t('cat_dd')->fetch_all_by_sql(1, 10);
//$excel_tool = new Tool;
//$excel_tool->outdata($data=$data_arr);
//$excel_tool->outdata();
//echo $data_arr;
exit;
}
elseif ($_GET['ac']=='del'){
$json_del_id = $_GET['del_id'];
$del_res = C::t('cat_dd')->delete_by_mult_id($json_del_id);
echo $del_res;
exit;
}
elseif ($_GET['ac']=='upd'){
$upd_data = $_GET['upd_data'];
//$del_res = C::t('cat_dd')->delete_by_mult_id($json_del_id);
$upd_res = C::t('cat_dd')->update_mult_rows($upd_data);
echo $upd_res;
exit;
}
elseif ($_GET['ac']=='ins'){
$ins_data = $_GET['ins_data'];
//$del_res = C::t('cat_dd')->delete_by_mult_id($json_del_id);
$ins_res = C::t('cat_dd')->insert_mult_rows($ins_data);
echo $ins_res;
exit;
}
else{
//print_r($gtmj_list);
//print(count($gtmj_list));
//die();
//获得一个简单的分页,只有上一页和下一页,这个不需要count()数据表中的所有记录
//$multi = simplepage(count($gtmj_list), $perpage, $page, 'gtmj.php?mod=gtmj_list');
//数据准备完毕,引入相应的模板,准备输出
//if ($_GET['ac']=='sh'){
//include_once template("cat/cat_dd_crud_list_show");
//}elseif ($_GET['ac']=='ed'){
include_once template("cat/cat_dd_crud_list_edit");
//}
//include_once template("cat/list_test");
}
?>
浏览器调用: http://localhost/cat_cat_dd_crud_entr_edit.php?ac=exc
打开这个文件:
成功了。具体操作,应该还有许多需要晚上的地方。