phpexcel导出
<?php
namespace lib;
/**
* Description of Excel
*
* @author lsf
*/
use think\Exception;
use think\Loader;
class Excel {
/**
* TP5 Excel专用类库
* $excel=new Excel();
* $table_name="mk_material_list_edit";
* $field=["id"=>"序号","guid"=>"项目代码","name"=>"项目名称"];
* $map=["status"=>1];
* $map2=["status"=>-1];
* $excel->setExcelName("下载装修项目")
* ->createSheet("装修项目",$table_name,$field,$map)
* ->createSheet("已删除装修项目",$table_name,$field,$map2)
* ->downloadExcel();
* @var \PHPExcel
*/
/**例子
use lib\Excel;
可以导出多个excel
$excel = new Excel();
$excel->setExcelName("下载装修项目")
->createSheet("装修项目", array(array(1,2,3)),array("序号","项目代码","项目名称"))
->createSheet("已删除装修项目",二维数组,一维title)
->downloadExcel();
一个表导出一个excel
*
$excel->setExcelName("已删除装修项目")->createSheet("已删除装修项目",二维数组,一维title)
/->downloadExcel();
*/
protected $objPHPExcel;
public $xlsReader;
public static $instance;
protected $sheetNum = 0;
protected $error;
protected $columnWidth;
protected $rowHeight = 20;
protected $excelName;
protected $isLoad = false;
//如果你的字段列数超过26字母 会报错
protected $letterArray = ["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"];
/**
* 项目初始化
* Excel constructor.
*/
public function __construct() {
Loader::import("excel/PHPExcel", EXTEND_PATH);
$this->objPHPExcel = new \PHPExcel();
if (!$this->isLoad) {
//新建时删除默认页面
$this->objPHPExcel->disconnectWorksheets();
}
}
public function importData($file_name){
try {
try {
$xlsReader = \PHPExcel_IOFactory::createReader("Excel2007");
$xlsReader->setReadDataOnly(true);
$xlsReader->setLoadSheetsOnly(true);
$xlsReader = $xlsReader->load($file_name);
} catch (Exception $e) {
$xlsReader = \PHPExcel_IOFactory::createReader("Excel5");
$xlsReader->setReadDataOnly(true); //
$xlsReader->setLoadSheetsOnly(true);
$xlsReader = $xlsReader->load($file_name);
}
} catch (Exception $e) {
throw new Exception("读取EXCEL失败");
}
$excel_array = $xlsReader->getSheet(0)->toArray(); //转换为数组格式
array_shift($excel_array); //删除第一个数组(标题);
return $excel_array;
}
/**
* @param string $path
* @return static
* @throws Exception
* @throws \PHPExcel_Reader_Exception
*/
static public function loadExcel($path = "/weihom.xls") {
if (is_null(self::$instance)) {
self::$instance = new static();
}
$excel = self::$instance;
try {
try {
$xlsReader = \PHPExcel_IOFactory::createReader("Excel2007");
$xlsReader->setReadDataOnly(true); //
$xlsReader->setLoadSheetsOnly(true);
$excel->xlsReader = $xlsReader->load($path);
} catch (Exception $e) {
$xlsReader = \PHPExcel_IOFactory::createReader("Excel5");
$xlsReader->setReadDataOnly(true); //
$xlsReader->setLoadSheetsOnly(true);
$excel->xlsReader = $xlsReader->load($path);
}
} catch (Exception $e) {
throw new Exception("读取EXCEL失败");
}
return $excel;
}
public function getSheetByName($name) {
if (isset($this->xlsReader)) {
return $this->xlsReader->getSheetByName($name);
} else {
return false;
}
}
public function getSheetNames() {
if (isset($this->xlsReader)) {
return $this->xlsReader->getSheetNames();
} else {
return false;
}
}
/**
* @return mixed
*/
public function getExcelObject() {
return $this->xlsReader;
}
public function getAllSheets() {
if (isset($this->xlsReader)) {
return $this->xlsReader->getAllSheets();
} else {
return false;
}
}
public function getSheetCount() {
if (isset($this->xlsReader)) {
return $this->xlsReader->getSheetCount();
} else {
return false;
}
}
public function getSheetArrayByIndex($index = 0) {
if (isset($this->xlsReader)) {
return $this->xlsReader->getSheet($index)->toArray();
} else {
return false;
}
}
/**
* 设置下载的Excel名称
* @param $name
* @return $this
*/
public function setExcelName($name) {
$this->excelName = $name;
return $this;
}
/**
* 返回EXCEL名称
* @return string
*/
public function getExcelName() {
return $this->excelName ? $this->excelName : "新建的数据表格";
}
/**
* 创建新的Sheet 支持链式操作
* @param string $sheet_title
* @param string $table 数据库表名称
* @param array $field 要导出的字段
* @param array $map 查询参数
* @return $this
* @throws Exception
* @throws \PHPExcel_Exception
*/
public function createSheet($sheet_title = "sheet", $data, $field) {
$sheet_num = $this->getNewSheetNum();
$objPHPExcel = $this->objPHPExcel;
$objPHPExcel->createSheet($sheet_num);
$objPHPExcel->setActiveSheetIndex($sheet_num);
$objPHPExcel->getActiveSheet()->setTitle($sheet_title);
//设置默认行高
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight($this->rowHeight);
$sheet = $objPHPExcel->getActiveSheet();
$field_title = array_values($field);
$letter_array = $this->letterArray;
foreach ($field_title as $item => $value) {
if (isset($this->columnWidth)) {
if (is_array($this->columnWidth) && count($field) == count($this->columnWidth)) {
$sheet->getColumnDimension($letter_array[$item])->setWidth($this->columnWidth[$item]);
} elseif (is_integer($this->columnWidth)) {
$sheet->getColumnDimension($letter_array[$item])->setWidth($this->columnWidth);
} else {
$sheet->getColumnDimension($letter_array[$item])->setAutoSize(true);
}
} else {
$sheet->getColumnDimension($letter_array[$item])->setAutoSize(true);
}
//标题加粗
$sheet->getStyle($letter_array[$item] . "1")->getFont()->setBold(true);
$sheet->setCellValue($letter_array[$item] . "1", $value);
}
if ($data) {
foreach ($data as $item => $value) {
$value = array_values($value);
foreach ($value as $i => $v)
$sheet->setCellValue($letter_array[$i] . ($item + 2), $value[$i]);
}
}
return $this;
}
/**
* 通过model生成Excel 获取器生效
* @param string $sheet_title
* @param string $model_name
* @param array $field
* @param array $map
* @return $this
* @throws Exception
* @throws \PHPExcel_Exception
*/
public function createSheetByModel($sheet_title = "sheet", $model_name = "", $field = [], $map = []) {
if (empty($model_name) || empty($field) || !is_string($model_name) || !is_array($field)) {
$this->error = "生成Excel的[table]或[field]参数不正确";
throw new Exception("生成Excel的[table]或[field]参数不正确");
return $this;
}
$sheet_num = $this->getNewSheetNum();
$objPHPExcel = $this->objPHPExcel;
$objPHPExcel->createSheet($sheet_num);
$objPHPExcel->setActiveSheetIndex($sheet_num);
$objPHPExcel->getActiveSheet()->setTitle($sheet_title);
//设置默认行高
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight($this->rowHeight);
$sheet = $objPHPExcel->getActiveSheet();
$field_title = array_values($field);
$letter_array = $this->letterArray;
foreach ($field_title as $item => $value) {
if (isset($this->columnWidth)) {
if (is_array($this->columnWidth) && count($field) == count($this->columnWidth)) {
$sheet->getColumnDimension($letter_array[$item])->setWidth($this->columnWidth[$item]);
} elseif (is_integer($this->columnWidth)) {
$sheet->getColumnDimension($letter_array[$item])->setWidth($this->columnWidth);
} else {
$sheet->getColumnDimension($letter_array[$item])->setAutoSize(true);
}
} else {
$sheet->getColumnDimension($letter_array[$item])->setAutoSize(true);
}
//标题加粗
$sheet->getStyle($letter_array[$item] . "1")->getFont()->setBold(true);
$sheet->setCellValue($letter_array[$item] . "1", $value);
}
$field = array_values(array_flip($field));
$list = Loader::model($model_name)->field($field)->where($map)->select();
if ($list) {
foreach ($list as $item => $value) {
$value = array_values($value->toArray());
foreach ($value as $i => $v)
if (is_array($v)) {
$sheet->setCellValue($letter_array[$i] . ($item + 2), implode("--", $v));
} else {
$sheet->setCellValue($letter_array[$i] . ($item + 2), $v);
}
}
}
return $this;
}
/**
* 下载当前的EXCEL
* @param string $save_name
* @throws \PHPExcel_Reader_Exception
*/
public function downloadExcel($save_name = "") {
ob_start();
//最后通过浏览器输出
$save_name = $this->getExcelName();
$save_name = $save_name ? "$save_name.xls" : "导出信息.xls";
header('Content-Type: application/vnd.ms-excel; charset=utf-8');
header("Content-Disposition: attachment;filename=$save_name");
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($this->objPHPExcel, 'Excel5');
$objWriter->save('php://output');
ob_end_flush(); //输出全部内容到浏览器
die();
}
/**
* 获取新的Sheet编号
* @return int
*/
protected function getNewSheetNum() {
$sheet_num = $this->sheetNum;
$this->sheetNum = $sheet_num + 1;
return $sheet_num;
}
/**
* 设置行宽 未设置时候默认为自动
* @param $width
* @return $this
*/
public function setColumnWidth($width) {
if (is_numeric($width) || is_array($width)) {
$this->columnWidth = $width;
}
return $this;
}
/**
* 设置默认行高
* @param $height
* @return $this
*/
public function setRowHeight($height) {
if (is_integer($height)) {
$this->rowHeight = $height;
}
return $this;
}
/**
* 魔术方法 有不存在的操作的时候执行
* @access public
* @param string $method 方法名
* @param array $args 参数
* @return mixed
*/
public function __call($method, $args) {
call_user_func_array([$this->objPHPExcel, $method], $args);
}
}
