php读取Excel

工作之中往往会碰到客户有需求要读取用户的Excel，每次都需要重写很麻烦，索性就记录下来大家一起分享

 

其中export方法是导出Excel，import方法是导入，命名空间自己填写

use PHPExcel;

/*
|--------------------------------------------------------------------------
| Excel操作类
|--------------------------------------------------------------------------
|
*/
class Excel
{
    public $data = [];
    
    public $thead = [];
    
    public $fieldname = [];
    
    public $exportfilename = '';
    
    public $importfilename = '';
    
    public $ismerge = '';
    
    public $mergearray = [];
    
    protected $squares = 0;
    
    protected $letters = ['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($data = [], $thead = [], $exportfilename = '', $importfilename = '', $fieldname = [], $ismerge = 0, $mergearray = []) {
        $this->data           = $data;
        $this->thead          = $thead;
        $this->exportfilename = $exportfilename;
        $this->importfilename = $importfilename;
        
        $this->fieldname      = $fieldname;
        $this->ismerge        = $ismerge;
        $this->mergearray     = $mergearray;
        
        if ( $this->thead ) {
            $this->squares = count($this->thead);
            
            if ( $this->squares > 26 ) {
                echo '{"code": 401, "message": "格数太多 请减少格数或者重新配置"}';
                exit;
            }
        }
    }
    
    /*
    |--------------------------------------------------------------------------
    | Excel导出
    |--------------------------------------------------------------------------
    |
    */
    public function export() {
        $objPHPExcel = new PHPExcel();
        
        $objPHPExcel->getProperties()->setCreator("wboll")
        ->setLastModifiedBy("wboll")
        ->setTitle("Office 2007 XLSX Document")
        ->setSubject("Office 2007 XLSX Document")
        ->setDescription("Document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
        ->setCategory("Test result file");
        
        if ( $this->squares > 0 ) {
            for ( $i = 0; $i < $this->squares; $i ++ ) {
                $objPHPExcel->getActiveSheet()->getColumnDimension($this->letters[$i])->setWidth(20);
                
                $objPHPExcel->getActiveSheet()->getStyle($this->letters[$i])->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            }
        } else {
            echo '{"code": 402, "message": "没有导出的数据"}';
            exit;
        }
        
        $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);
        $objPHPExcel->getDefaultStyle()->getFont()->setName(iconv('gbk', 'utf-8', '宋体'));
        
        $objPHPExcel->getActiveSheet()->getStyle('A1:'. $this->letters[$this->squares-1]."1")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        if ( $this->ismerge && $this->mergearray) {
            foreach ( $this->mergearray as $k => $v ) {
                $objPHPExcel->getActiveSheet()->mergeCells($k);
                
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue(explode(":", $k)[0], $v);
            }
        }
        
        $beginsquare = '1';
        if ( $this->ismerge ) {
            $beginsquare = '2';
        }
        
        if ( $this->thead ) {
            for ( $i = 0; $i < $this->squares; $i ++ ) {
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($this->letters[$i]. $beginsquare, $this->thead[$i]);
            }
        }
        
        for ( $i = 0; $i < count($this->data); $i ++ ) {
            for ( $j = 0; $j < $this->squares; $j ++ ) {
                $objPHPExcel->getActiveSheet(0)->setCellValue($this->letters[$j] . ($i + ($beginsquare + 1)), $this->data[$i][$this->fieldname[$j]]);
                $objPHPExcel->getActiveSheet(0)->getStyle($this->letters[$j] . ($i + ($beginsquare + 1)))->getAlignment()->setWrapText(true);
            }
        }
        
        $objPHPExcel->getActiveSheet()->setTitle($this->exportfilename);
        
        $objPHPExcel->setActiveSheetIndex(0);
        
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $this->exportfilename.date("YmdHis") . '.xls"');
        header('Cache-Control: max-age=0');
        
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
    }
    
    /*
    |--------------------------------------------------------------------------
    | Excel导入
    |--------------------------------------------------------------------------
    |
    */
    public function import() {
        
        $filetype = pathinfo($this->importfilename, PATHINFO_EXTENSION);
        
        if ($filetype == 'xls') {
            $reader = new \PHPExcel_Reader_Excel5();
        }
        if ($filetype == 'xlsx') {
            $reader = new \PHPExcel_Reader_Excel2007();
        }
        
        if ( !isset($reader) ) {
            echo '{"code": 403, "message": "上传文件格式错误"}';
        }
        
        // 读excel文件
        // 载入excel文件
        $PHPExcel = $reader->load($this->importfilename, 'utf-8');
        
        // 读取第一個工作表
        $sheet = $PHPExcel->getSheet(0);
        
        // 取得总行数
        $highestRow = $sheet->getHighestRow();
        
        // 取得总列数
        $highestColumm = $sheet->getHighestColumn();
        
        // 把Excel数据保存数组中
        $data = array();
        for ($rowIndex = 1; $rowIndex <= $highestRow; $rowIndex++) {
            for ($colIndex = 'A'; $colIndex <= $highestColumm; $colIndex++) {
                $addr = $colIndex . $rowIndex;
                $cell = $sheet->getCell($addr)->getValue();
                
                // 富文本转换字符串
                if ($cell instanceof \PHPExcel_RichText) {
                    $cell = $cell->__toString();
                }
                
                // if(is_object($cell))  $cell = $cell->__toString();
                
                $data[$rowIndex][$colIndex] = $cell;
            }
        }
        
        return $data;
    }
}

 使用时，先引用

use Excel 

然后调用方法

1.导出调用

//导出
$excel = new Excel($list, $thead, $exportfilename, '', $fieldname, $ismerge, $mergearray);

$excel->export();

2.导入调用，导入后返回的数据是一个二维数组，可用foreach循环进行之后的操作，很方便

//导入
$excel = new Excel([], [], '', $filename, '');

//$data => Array
$data = $excel->import();

 以上就是我个人的php对Excel的操作，希望帮助自己的同时，对其他人也有帮助