第一步：安裝，使用composer安裝擴充。composer require phpoffice/phpexcel
第二步：封裝<?php
namespace ExcelService;
use think\Db;
use think\Exception;
use think\Loader;
use think\Log;
use PHPExcel_IOFactory;
use PHPExcel;
use PHPExcel_Worksheet_Drawing;
class ExcelService
{
protected $objPHPExcel;
public $xlsReader;
public static $instance;
protected $sheetNum=0;
protected $error;
protected $columnWidth;
protected $rowHeight=20;
protected $excelName;
protected $sheetTitle;
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"];
public function __construct()
{
$this->objPHPExcel= new PHPExcel();
if(!$this->isLoad){
//建立時删除預設頁面
$this->objPHPExcel->disconnectWorksheets();
}
}
public static function instance()
{
if (is_null(self::$instance)) {
self::$instance = new static();
}
return self::$instance;
}
static public function loadExcel($path="/test.xls",$ext =''){
if (is_null(self::$instance)) {
self::$instance = new static();
}
$excel = self::$instance;
try {
try {
if ($ext == "csv") {
$xlsReader = \PHPExcel_IOFactory::createReader('CSV')->setDelimiter(',')->setEnclosure('"')->setSheetIndex(0);
$xlsReader->setReadDataOnly(true); //
$xlsReader->setLoadSheetsOnly(true);
$xlsReader->setInputEncoding('GBK');
$excel->xlsReader=$xlsReader->load($path);
}else{
$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;
}
}
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;
}
}
public function setExcelName($name){
$this->excelName=$name;
return $this;
}
public function setSheetTitle($name)
{
$this->sheetTitle=$name;
return $this;
}
public function getSheetTitle()
{
return $this->sheetTitle ? $this->sheetTitle : "";
}
public function getExcelName()
{
return $this->excelName ? $this->excelName : "建立的資料表格";
}
public function createSheet($table,$field=[],$map=[]){
if (empty($table) ||empty($field)|| !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);
$sheet=$objPHPExcel->getActiveSheet();
if ($this->getSheetTitle()) {
$objPHPExcel->getActiveSheet()->setTitle($this->getSheetTitle());
}
//設定預設行高
$sheet->getDefaultRowDimension()->setRowHeight($this->rowHeight);
$titleStyleArray = [
'font' => [
'bold' => true
],
'alignment' => [
'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER
]
];
$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);
}
// p($table);
if (is_string($table)) {
$list = Db::table($table)->field($field)->where($map)->select();
}else if(is_array($table)){
$list = $table;
}else{
throw new Exception("Table錯誤：支援數組或資料表名稱");
return $this;
}
if ($list){
foreach($list as $item=>$value ){
$value=array_values($value);
foreach($value as $i=>$v){
$sheet->setCellValue($letter_array[$i].($item+2),$value[$i]);
}
}
}
$color='FFFF0000';
$width = count($field_title)+1;
$rows = count($list)+1;
//邊框樣式
$styleArray = [
'borders' => [
'allborders' => [
// 'style' => \PHPExcel_Style_Border::BORDER_THICK,//邊框是粗的
'style' => \PHPExcel_Style_Border::BORDER_THIN,//細邊框
// 'color' => array('argb' => $color),
],
],
];
$objPHPExcel->getActiveSheet()->getStyle("A1:{$this->letterArray[ $width ]}{$rows}")->applyFromArray($styleArray);
return $this;
}
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();
}
public function saveExcel($save_path=""){
$objWriter = \PHPExcel_IOFactory::createWriter($this->objPHPExcel, 'Excel5');
$save_name=$this->getExcelName();
$save_name = $save_name ? "$save_name.xls" : "demo.xls";
$save_path=$save_path?$save_path:ROOT_PATH.'runtime/excel/'.$save_name;
if(!is_dir(dirname ($save_path))){
mkdir(dirname ($save_path),0755,true);
}
$objWriter->save($save_path);
die();
}
protected function getNewSheetNum(){
$sheet_num=$this->sheetNum;
$this->sheetNum=$sheet_num+1;
return $sheet_num;
}
public function setColumnWidth($width){
if(is_integer($width)||is_array($width)){
$this->columnWidth=$width;
}
return $this;
}
public function setRowHeight($height){
if(is_numeric($height)){
$this->rowHeight=$height;
}
return $this;
}
public function __call($method, $args)
{
call_user_func_array([$this->objPHPExcel, $method], $args);
}
}
第三步：使用
1.導出資料到表格$excel=new ExcelService();
$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();
2.上傳檔案後導入資料，并解析資料結果$getExcelObject=Excel::loadExcel("test.xls");
$sheetName=$getExcelObject->getSheetNames();
dump($sheetName);
$sheet = $getExcelObject->getSheetByName($sheetName[0])->toArray();
dump($sheet);