php office连接打印机,【PHP】使用PHPoffice实现普通的导出功能

使用composer安装： composer require phpoffice/phpspreadsheet<?php

namespace app\index\controller;

use app\index\controller\Comm;

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

use think\Exception;

use think\Request;

class Importsheet extends comm{

/**

* @var Excel文件名字

*/

private $sheet_filename;

/**

* @var Excel中的sheet的名字

*/

private $sheet_name;

/**

* @var array 输出Excel的表头

*/

private $sheet_firstline = [];

/**

* @var array 表的内容

*/

private $sheet_info = [];

/**

* Importsheet constructor.

* @param $filename 文件名

* @param $name sheet名

* @param $firstline 表头

* @param $info 表内容

*/

public function __construct($filename,$name,$firstline = [],$info = [])

{

parent::__construct();

$this->sheet_filename = $filename;

$this->sheet_name = $name;

$this->sheet_firstline = $firstline;

$this->sheet_info = $info;

}

/**

* @Author: 小破孩嫩

* @Email: 3584685883@qq.com

* @Time: 2020/12/23 16:08

* @param int $column_num

* @return mixed

* @Description：获取表格列数的字母

*/

public function getMaxColumn(int $column_num)

{

try{

if(empty($column_num)){

throw new Exception('column_num:列数为空~');

}

if(!is_int($column_num)){

throw new Exception('column_num:参数类型错误~');

}

if($column_num > 26*26 || $column_num < 0){

throw new Exception('最大列数：676列,最小列数：1列');

}

$column_word = ['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'];

//生成循环次数

$num = ceil($column_num/26);

for($c = 0; $c < $num; $c++) {

$first_word = $column_word[$c-1];

foreach($column_word as $key => $val){

if($c >= 1){

$word = $first_word.$column_word[$key];

}else{

$word = $column_word[$key];

}

$column[] = $word;

}

}

for($a = 0; $a < $column_num; $a++){

$new_column[] = $column[$a];

}

return $new_column;

}catch (Exception $e){

returnResponse(100,$e->getMessage());

}

}

/**

* @Author: 小破孩嫩

* @Email: 3584685883@qq.com

* @Time: 2020/12/23 17:54

* @Description：输出表

*/

public function outputSheet()

{

try{

$spreadsheet = new Spreadsheet();

$sheet = $spreadsheet->getActiveSheet();

//设置sheet的名字

$sheet->setTitle($this->sheet_name);

//默认表头第一行

$k = 1;

//生成列的个数，根据表头个数来定

$column_num = count($this->sheet_firstline);

$info_field_num = count($this->sheet_info[0]);

if($column_num != $info_field_num){

throw new Exception('结果集列数和表头列数不一致~');

}

//生成表头上方的字母(最大676，最小1)

$column_word = $this->getMaxColumn($column_num);

//设置表头

for($i=0;$i

$sheet->setCellValue($column_word[$i].$k, $this->sheet_firstline[$i]);

}

//第二行开始插入数据

$k = 2;

//插入表格数据

foreach ($this->sheet_info as $key => $value) {

$b = 0;

for($a = 0; $a < $column_num; $a++){

$getvalbykey = array_values($value);

$sheet->setCellValue($column_word[$a].$k, $getvalbykey[$b]);

$b++;

}

$k++;

}

//文件名

$file_name = date('Y-m-d H:i:s', time()).'-'.rand(1000, 9999).'_'. $this->sheet_filename . ".xlsx";

//下载

header('Content-Type: application/vnd.ms-excel');

header('Content-Disposition: attachment;filename="'.$file_name.'"');

header('Cache-Control: max-age=0');

$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');

$writer->save('php://output');

}catch (Exception $e){

returnResponse(100,$e->getMessage());

}

}

}