fastadmin的PhpOffice或PHPExcel导出数据
实例代码:
根据实际业务自行修改
根据实际业务自行修改
PhpOffice方式
<?php
namespace app\api\controller\v1;
use app\common\controller\Api;
use app\common\library\Curl;
use think\Db;
use app\common\model\Goods;
//
use PhpOffice\PhpSpreadsheet\Helper\Sample;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
/**
* 物品登记
*/
class Index extends Api
{
protected $noNeedLogin = ['download'];
protected $noNeedRight = ['*'];
//导出数据
public function download(){
// if ($this->request->isPost()){
set_time_limit(0);//设置时间限制
$ids = $this->request->post('ids'); //获取数据的所有id
$newExcel = new Spreadsheet();
///
$sheet = $newExcel->getActiveSheet();
$sheetPHPExcel = $newExcel->setActiveSheetIndex(0);
$char_index = range('A', 'Z');
///
//创建一个新的excel文档
$objSheet = $newExcel->getActiveSheet();
//获取当前操作sheet的对象
$objSheet->setTitle('固定资产盘点表');
//设置当前sheet的标题
//$newExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true)->setWidth(50);
$newExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
$newExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$newExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
$newExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
$newExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
$newExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
$newExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
$newExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
$newExcel->getActiveSheet()->getColumnDimension('I')->setWidth(15);
$objSheet->setCellValue('A1', '资产编号')
->setCellValue('B1', '品牌')
->setCellValue('C1', '资产名称')
->setCellValue('D1', '规格型号')
->setCellValue('E1', '资产现状')
->setCellValue('F1', '使用部门')
->setCellValue('G1', '保管负责人')
->setCellValue('H1', '备注')
->setCellValue('I1', '盘点情况');
$sql=model('goods')->select();
$sql = collection($sql)->toArray();
/*--------------开始从数据库提取信息插入Excel表中------------------*/
$i=2;
//定义一个i变量，目的是在循环输出数据是控制行数
$count = count($sql);
//计算有多少条数据
for ($i = 2; $i <= $count+1; $i++) {
$row = $i-2;
$objSheet->setCellValue('A' . $i, $sql[$row]['number'])
->setCellValue('B' . $i, $sql[$row]['category'])
->setCellValue('C' . $i, $sql[$row]['brand'])
->setCellValue('D' . $i, $sql[$row]['specification'])
->setCellValue('E' . $i, $sql[$row]['status'] == 1?'使用中':'闲置')
->setCellValue('F' . $i, $sql[$row]['section'])
->setCellValue('G' . $i, $sql[$row]['name'])
->setCellValue('H' . $i, $sql[$row]['remark'])
->setCellValue('I' . $i, '');
//渲染图片
// $isImg = ROOT_PATH.'public'.'/wxcode/wxcode_0.png';
// $orImg = pathinfo($isImg);
// if (isset($orImg['extension']) && (($orImg['extension'] == 'jpg') || ($orImg['extension'] == 'png') || ($orImg['extension'] == 'gif'))){
// $drawing[$row] = new Drawing();
// $drawing[$row]->setName( 'Logo');
// $drawing[$row]->setDescription( 'Logo');
// $drawing[$row]->setPath($isImg);
// $drawing[$row]->setPath($isImg);
// $drawing[$row]->setWidth(80);
// $drawing[$row]->setHeight(80);
// $drawing[$row]->setCoordinates('I' . $i);
// $drawing[$row]->setOffsetX(12);
// $drawing[$row]->setOffsetY(12);
// $drawing[$row]->setWorksheet($newExcel->getActiveSheet());
// }
// $sheet->getRowDimension($i)->setRowHeight(80);
//
}
/*--------------下面是设置其他信息------------------*/
$title = date("Ymd-固定资产盘点表");
// header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
// header('Content-Disposition: attachment;filename="' . $title . '.xlsx"');
// header('Cache-Control: max-age=0');
$objWriter = IOFactory::createWriter($newExcel, 'Xlsx');
//直接下载
// return $objWriter->save('php://output');
//保存本地
$path = '/xlsx/'.$title.'.xlsx';
$objWriter->save(ROOT_PATH.'public'.$path);
$this->success('获取成功!',['path'=>request()->domain().$path]);
//以下为需要用到IE时候设置
// 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
exit;
return;
// }
}
}
PHPExcel方式
<?php
namespace app\admin\controller;
use app\admin\common\Base;
use think\Db;
use think\Request;
use think\Session;
class Ziliao extends Base
{
//导出
public function leading(){
$ResultInfo = Db::name("xinxi")->field('id,zs_name,xm_name,zs_num,name,sex,id_card,education,time,grade,phone,px_name')->where(['zs_num'=>['<>','null'],'name'=>['<>','null']])->select();
ob_end_clean();
vendor("PHPExcel.PHPExcel"); //方法一
$excel =new \PHPExcel();
$excel->getActiveSheet()->setTitle('报名列表');
// 设置单元格高度
// 所有单元格默认高度
$excel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(25);
// 第一行的默认高度
$excel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
//Excel表格式
$letter = [
'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N',
'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'
];
//设置表头
// $tableheader = array('序号','证书名称','项目名称','证书编号','姓名','性别','身份证号','文化程度','结业时间','成绩','电话','培训单位','承办单位');
$tableheader = array('序号','证书名称','项目名称','证书编号','姓名','性别','身份证号','文化程度','结业时间','成绩','电话','培训单位');
//设置表头表格宽度
$tablestyle = array(
array('width'=>'5'), //序号
array('width'=>'15'), //证书名称
array('width'=>'25'), //项目名称
array('width'=>'20'), //证书编号
array('width'=>'20'), //姓名
array('width'=>'5'), //性别
array('width'=>'25'), //身份证号
array('width'=>'15'), //文化程度
array('width'=>'15'), //结业时间
array('width'=>'10'), //成绩
array('width'=>'20'), //电话
array('width'=>'20'), //培训单位
// array('width'=>'50'), //承办单位
);
//填充表头信息
for($i = 0;$i < count($tableheader);$i++) {
$excel->getActiveSheet()->setCellValue("$letter[$i]1","$tableheader[$i]");
$excel->getActiveSheet()->getColumnDimension($letter[$i])->setWidth($tablestyle[$i]['width']);
}
//填充表格信息
for ($i = 2;$i <= count($ResultInfo) + 1;$i++) {
$j = 0;
foreach ($ResultInfo[$i - 2] as $key=>$value) {
$excel->getActiveSheet()->setCellValue("$letter[$j]$i","$value");
$j++;
}
}
$filename = "./".date('Y-m-d',time())."-".rand(1111,9999).".xls";
//直接下载的代码
$write = new \PHPExcel_Writer_Excel5($excel);
//$write->save($filename);
header("Pragma: public");
header("Expires: 0");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl;charset=utf-8");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header('Content-Disposition:attachment;filename='.$filename);
header("Content-Transfer-Encoding:binary");
$write->save('php://output');
}
/**
* excel导入网点
*/
public function insert(){
if(request() -> isPost())
{
vendor("PHPExcel.PHPExcel"); //方法一
$objPHPExcel =new \PHPExcel();
//获取表单上传文件
$file = request()->file('file');
$info = $file->validate(['ext' => 'xlsx,xls'])->move(ROOT_PATH . 'public'); //上传验证后缀名,以及上传之后移动的地址 E:\wamp\www\bick\public
if($info)
{
$exclePath = $info->getSaveName(); //获取文件名
$file_name = ROOT_PATH . 'public' . DS . $exclePath;//上传文件的地址
$file_types = explode ( ".", $_FILES ['file'] ['name'] );
$file_type = $file_types [count ( $file_types ) - 1];
if($file_type=='xls'){
$objReader =\PHPExcel_IOFactory::createReader("Excel5");
}
else if($file_type=='xlsx'){
$objReader =\PHPExcel_IOFactory::createReader("Excel2007");
}
$obj_PHPExcel =$objReader->load($file_name, $encode = 'utf-8'); //加载文件内容,编码utf-8
$excel_array=$obj_PHPExcel->getSheet(0)->toArray(); //转换为数组格式
array_shift($excel_array); //删除第一个数组(标题);
$data = [];
$i=0;
foreach($excel_array as $k=>$v) {
$data[$k]['zs_name'] = $v[1];
$data[$k]['xm_name'] = $v[2];
$data[$k]['zs_num'] = $v[3];
$data[$k]['name'] = $v[4];
$data[$k]['sex'] = $v[5];
$data[$k]['id_card'] = $v[6];
$data[$k]['education'] = $v[7];
$data[$k]['time'] = $v[8];
$data[$k]['grade'] = $v[9];
$data[$k]['phone'] = $v[10];
$data[$k]['px_name'] = $v[11];
// $data[$k]['cb_name'] = $v[9];
$i++;
}
// foreach($excel_array as $k=>$v) {
// $data[$k]['zs_name'] = $v[10];
// $data[$k]['xm_name'] = $v[11];
// $data[$k]['zs_num'] = $v[12];
// $data[$k]['name'] = $v[1];
// $data[$k]['sex'] = $v[2];
// $data[$k]['id_card'] = $v[3];
// $data[$k]['education'] = $v[4];
// $data[$k]['time'] = $v[5];
// $data[$k]['grade'] = $v[6];
// $data[$k]['phone'] = $v[7];
// $data[$k]['px_name'] = $v[8];
// // $data[$k]['cb_name'] = $v[9];
// $i++;
// }
// foreach($excel_array as $k=>$v) {
// $data[$k]['zs_name'] = $v[1];
// $data[$k]['xm_name'] = $v[2];
// $data[$k]['zs_num'] = $v[3];
// $data[$k]['name'] = $v[4];
// $data[$k]['sex'] = $v[5];
// $data[$k]['id_card'] = $v[6];
// $data[$k]['education'] = $v[7];
// $data[$k]['time'] = $v[8];
// $data[$k]['grade'] = $v[9];
// $data[$k]['phone'] = $v[10];
// $data[$k]['px_name'] = $v[11];
// $data[$k]['cb_name'] = $v[12];
// $i++;
// }
// foreach($data as $k=>$v)
// {
// $time=explode('/',$v['time']);
// $data[$k]['time']=$time[2].'-'.$time[0].'-'.$time[1];
// }
$re=Db::name("xinxi")->insertAll($data);
if($re){
$msg=[
'code'=>1,
'msg'=>'已获取信息',
];
return $msg;
}
else{
$msg=[
'code'=>0,
'msg'=>'获取信息失败',
];
return $msg;
}
}else
{
echo $file->getError();
}
}
return $this->redirect("index");
}
}
原创文章，转载请注明：fastadmin的PhpOffice或PHPExcel导出数据 | 知识改变命运
转载请注明出处： 知识改变命运 » fastadmin的PhpOffice或PHPExcel导出数据
