PHP-使用插件-②形成Excel

最新推荐文章于 2024-06-29 09:51:21 发布
最新推荐文章于 2024-06-29 09:51:21 发布
阅读量1k 收藏
点赞数
分类专栏： php
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_41056807/article/details/84888686
版权

插件内容
PHPExcel.php完整代码

<?php
/**
 * PHPExcel
 *
 * Copyright (c) 2006 - 2014 PHPExcel
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 *
 * @category   PHPExcel
 * @package    PHPExcel
 * @copyright  Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt    LGPL
 * @version    1.8.0, 2014-03-02
 */


/** PHPExcel root directory */
if (!defined('PHPEXCEL_ROOT')) {
    define('PHPEXCEL_ROOT', dirname(__FILE__) . '/');
    require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
}


/**
 * PHPExcel
 *
 * @category   PHPExcel
 * @package    PHPExcel
 * @copyright  Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
 */
class PHPExcel
{
    /**
     * Unique ID
     *
     * @var string
     */
    private $_uniqueID;

    /**
     * Document properties
     *
     * @var PHPExcel_DocumentProperties
     */
    private $_properties;

    /**
     * Document security
     *
     * @var PHPExcel_DocumentSecurity
     */
    private $_security;

    /**
     * Collection of Worksheet objects
     *
     * @var PHPExcel_Worksheet[]
     */
    private $_workSheetCollection = array();

    /**
	 * Calculation Engine
	 *
	 * @var PHPExcel_Calculation
	 */
	private $_calculationEngine = NULL;

    /**
     * Active sheet index
     *
     * @var int
     */
    private $_activeSheetIndex = 0;

    /**
     * Named ranges
     *
     * @var PHPExcel_NamedRange[]
     */
    private $_namedRanges = array();

    /**
     * CellXf supervisor
     *
     * @var PHPExcel_Style
     */
    private $_cellXfSupervisor;

    /**
     * CellXf collection
     *
     * @var PHPExcel_Style[]
     */
    private $_cellXfCollection = array();

    /**
     * CellStyleXf collection
     *
     * @var PHPExcel_Style[]
     */
    private $_cellStyleXfCollection = array();

	/**
	* _hasMacros : this workbook have macros ?
	*
	* @var bool
	*/
	private $_hasMacros = FALSE;

	/**
	* _macrosCode : all macros code (the vbaProject.bin file, this include form, code,  etc.), NULL if no macro
	*
	* @var binary
	*/
	private $_macrosCode=NULL;
	/**
	* _macrosCertificate : if macros are signed, contains vbaProjectSignature.bin file, NULL if not signed
	*
	* @var binary
	*/
	private $_macrosCertificate=NULL;

	/**
	* _ribbonXMLData : NULL if workbook is'nt Excel 2007 or not contain a customized UI
	*
	* @var NULL|string
	*/
	private $_ribbonXMLData=NULL;

	/**
	* _ribbonBinObjects : NULL if workbook is'nt Excel 2007 or not contain embedded objects (picture(s)) for Ribbon Elements
	* ignored if $_ribbonXMLData is null
	*
	* @var NULL|array
	*/
	private $_ribbonBinObjects=NULL;

	/**
	* The workbook has macros ?
	*
	* @return true if workbook has macros, false if not
	*/
	public function hasMacros(){
		return $this->_hasMacros;
	}

	/**
	* Define if a workbook has macros
	*
	* @param true|false
	*/
	public function setHasMacros($hasMacros=false){
		$this->_hasMacros=(bool)$hasMacros;
	}

	/**
	* Set the macros code
	*
	* @param binary string|null
	*/
	public function setMacrosCode($MacrosCode){
		$this->_macrosCode=$MacrosCode;
		$this->setHasMacros(!is_null($MacrosCode));
	}

	/**
	* Return the macros code
	*
	* @return binary|null
	*/
	public function getMacrosCode(){
		return $this->_macrosCode;
	}

	/**
	* Set the macros certificate
	*
	* @param binary|null
	*/
	public function setMacrosCertificate($Certificate=NULL){
		$this->_macrosCertificate=$Certificate;
	}

	/**
	* Is the project signed ?
	*
	* @return true|false
	*/
	public function hasMacrosCertificate(){
		return !is_null($this->_macrosCertificate);
	}

	/**
	* Return the macros certificate
	*
	* @return binary|null
	*/
	public function getMacrosCertificate(){
		return $this->_macrosCertificate;
	}

	/**
	* Remove all macros, certificate from spreadsheet
	*
	* @param none
	* @return void
	*/
	public function discardMacros(){
		$this->_hasMacros=false;
		$this->_macrosCode=NULL;
		$this->_macrosCertificate=NULL;
	}

	/**
	* set ribbon XML data
	*
	*/
	public function setRibbonXMLData($Target=NULL, $XMLData=NULL){
		if(!is_null($Target) && !is_null($XMLData)){
			$this->_ribbonXMLData=array('target'=>$Target, 'data'=>$XMLData);
		}else{
			$this->_ribbonXMLData=NULL;
		}
	}

	/**
	* retrieve ribbon XML Data
	*
	* return string|null|array
	*/
	public function getRibbonXMLData($What='all'){//we need some constants here...
		$ReturnData=NULL;
		$What=strtolower($What);
		switch($What){
		case 'all':
			$ReturnData=$this->_ribbonXMLData;
			break;
		case 'target':
		case 'data':
			if(is_array($this->_ribbonXMLData) && array_key_exists($What,$this->_ribbonXMLData)){
				$ReturnData=$this->_ribbonXMLData[$What];
			}//else $ReturnData stay at null
			break;
		}//default: $ReturnData at null
		return $ReturnData;
	}

	/**
	* store binaries ribbon objects (pictures)
	*
	*/
	public function setRibbonBinObjects($BinObjectsNames=NULL, $BinObjectsData=NULL){
		if(!is_null($BinObjectsNames) && !is_null($BinObjectsData)){
			$this->_ribbonBinObjects=array('names'=>$BinObjectsNames, 'data'=>$BinObjectsData);
		}else{
			$this->_ribbonBinObjects=NULL;
		}
	}
	/**
	* return the extension of a filename. Internal use for a array_map callback (php<5.3 don't like lambda function)
	*
	*/
	private function _getExtensionOnly($ThePath){
		return pathinfo($ThePath, PATHINFO_EXTENSION);
	}

	/**
	* retrieve Binaries Ribbon Objects
	*
	*/
	public function getRibbonBinObjects($What='all'){
		$ReturnData=NULL;
		$What=strtolower($What);
		switch($What){
		case 'all':
			return $this->_ribbonBinObjects;
			break;
		case 'names':
		case 'data':
			if(is_array($this->_ribbonBinObjects) && array_key_exists($What, $this->_ribbonBinObjects)){
				$ReturnData=$this->_ribbonBinObjects[$What];
			}
			break;
		case 'types':
			if(is_array($this->_ribbonBinObjects) && array_key_exists('data', $this->_ribbonBinObjects) && is_array($this->_ribbonBinObjects['data'])){
				$tmpTypes=array_keys($this->_ribbonBinObjects['data']);
				$ReturnData=array_unique(array_map(array($this,'_getExtensionOnly'), $tmpTypes));
			}else
				$ReturnData=array();//the caller want an array... not null if empty
			break;
		}
		return $ReturnData;
	}

	/**
	* This workbook have a custom UI ?
	*
	* @return true|false
	*/
	public function hasRibbon(){
		return !is_null($this->_ribbonXMLData);
	}

	/**
	* This workbook have additionnal object for the ribbon ?
	*
	* @return true|false
	*/
	public function hasRibbonBinObjects(){
		return !is_null($this->_ribbonBinObjects);
	}

	/**
     * Check if a sheet with a specified code name already exists
     *
     * @param string $pSheetCodeName  Name of the worksheet to check
     * @return boolean
     */
    public function sheetCodeNameExists($pSheetCodeName)
    {
		return ($this->getSheetByCodeName($pSheetCodeName) !== NULL);
    }

	/**
	 * Get sheet by code name. Warning : sheet don't have always a code name !
	 *
	 * @param string $pName Sheet name
	 * @return PHPExcel_Worksheet
	 */
	public function getSheetByCodeName($pName = '')
	{
		$worksheetCount = count($this->_workSheetCollection);
		for ($i = 0; $i < $worksheetCount; ++$i) {
			if ($this->_workSheetCollection[$i]->getCodeName() == $pName) {
				return $this->_workSheetCollection[$i];
			}
		}

		return null;
	}

	 /**
	 * Create a new PHPExcel with one Worksheet
	 */
	public function __construct()
	{
		$this->_uniqueID = uniqid();
		$this->_calculationEngine	= PHPExcel_Calculation::getInstance($this);

		// Initialise worksheet collection and add one worksheet
		$this->_workSheetCollection = array();
		$this->_workSheetCollection[] = new PHPExcel_Worksheet($this);
		$this->_activeSheetIndex = 0;

        // Create document properties
        $this->_properties = new PHPExcel_DocumentProperties();

        // Create document security
        $this->_security = new PHPExcel_DocumentSecurity();

        // Set named ranges
        $this->_namedRanges = array();

        // Create the cellXf supervisor
        $this->_cellXfSupervisor = new PHPExcel_Style(true);
        $this->_cellXfSupervisor->bindParent($this);

        // Create the default style
        $this->addCellXf(new PHPExcel_Style);
        $this->addCellStyleXf(new PHPExcel_Style);
    }

    /**
     * Code to execute when this worksheet is unset()
     *
     */
    public function __destruct() {
        PHPExcel_Calculation::unsetInstance($this);
        $this->disconnectWorksheets();
    }    //    function __destruct()

    /**
     * Disconnect all worksheets from this PHPExcel workbook object,
     *    typically so that the PHPExcel object can be unset
     *
     */
    public function disconnectWorksheets()
    {
    	$worksheet = NULL;
        foreach($this->_workSheetCollection as $k => &$worksheet) {
            $worksheet->disconnectCells();
            $this->_workSheetCollection[$k] = null;
        }
        unset($worksheet);
        $this->_workSheetCollection = array();
    }

	/**
	 * Return the calculation engine for this worksheet
	 *
	 * @return PHPExcel_Calculation
	 */
	public function getCalculationEngine()
	{
		return $this->_calculationEngine;
	}	//	function getCellCacheController()

    /**
     * Get properties
     *
     * @return PHPExcel_DocumentProperties
     */
    public function getProperties()
    {
        return $this->_properties;
    }

    /**
     * Set properties
     *
     * @param PHPExcel_DocumentProperties    $pValue
     */
    public function setProperties(PHPExcel_DocumentProperties $pValue)
    {
        $this->_properties = $pValue;
    }

    /**
     * Get security
     *
     * @return PHPExcel_DocumentSecurity
     */
    public function getSecurity()
    {
        return $this->_security;
    }

    /**
     * Set security
     *
     * @param PHPExcel_DocumentSecurity    $pValue
     */
    public function setSecurity(PHPExcel_DocumentSecurity $pValue)
    {
        $this->_security = $pValue;
    }

    /**
     * Get active sheet
     *
     * @return PHPExcel_Worksheet
     */
    public function getActiveSheet()
    {
        return $this->_workSheetCollection[$this->_activeSheetIndex];
    }

    /**
     * Create sheet and add it to this workbook
     *
     * @param  int|null $iSheetIndex Index where sheet should go (0,1,..., or null for last)
     * @return PHPExcel_Worksheet
     * @throws PHPExcel_Exception
     */
    public function createSheet($iSheetIndex = NULL)
    {
        $newSheet = new PHPExcel_Worksheet($this);
        $this->addSheet($newSheet, $iSheetIndex);
        return $newSheet;
    }

    /**
     * Check if a sheet with a specified name already exists
     *
     * @param  string $pSheetName  Name of the worksheet to check
     * @return boolean
     */
    public function sheetNameExists($pSheetName)
    {
        return ($this->getSheetByName($pSheetName) !== NULL);
    }

    /**
     * Add sheet
     *
     * @param  PHPExcel_Worksheet $pSheet
     * @param  int|null $iSheetIndex Index where sheet should go (0,1,..., or null for last)
     * @return PHPExcel_Worksheet
     * @throws PHPExcel_Exception
     */
    public function addSheet(PHPExcel_Worksheet $pSheet, $iSheetIndex = NULL)
    {
        if ($this->sheetNameExists($pSheet->getTitle())) {
            throw new PHPExcel_Exception(
            	"Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename this worksheet first."
            );
        }

        if($iSheetIndex === NULL) {
            if ($this->_activeSheetIndex < 0) {
                $this->_activeSheetIndex = 0;
            }
            $this->_workSheetCollection[] = $pSheet;
        } else {
            // Insert the sheet at the requested index
            array_splice(
                $this->_workSheetCollection,
                $iSheetIndex,
                0,
                array($pSheet)
                );

            // Adjust active sheet index if necessary
            if ($this->_activeSheetIndex >= $iSheetIndex) {
                ++$this->_activeSheetIndex;
            }
        }

        if ($pSheet->getParent() === null) {
            $pSheet->rebindParent($this);
        }

        return $pSheet;
    }

    /**
     * Remove sheet by index
     *
     * @param  int $pIndex Active sheet index
     * @throws PHPExcel_Exception
     */
    public function removeSheetByIndex($pIndex = 0)
    {

        $numSheets = count($this->_workSheetCollection);

        if ($pIndex > $numSheets - 1) {
            throw new PHPExcel_Exception(
            	"You tried to remove a sheet by the out of bounds index: {$pIndex}. The actual number of sheets is {$numSheets}."
            );
        } else {
            array_splice($this->_workSheetCollection, $pIndex, 1);
        }
        // Adjust active sheet index if necessary
        if (($this->_activeSheetIndex >= $pIndex) &&
            ($pIndex > count($this->_workSheetCollection) - 1)) {
            --$this->_activeSheetIndex;
        }

    }

    /**
     * Get sheet by index
     *
     * @param  int $pIndex Sheet index
     * @return PHPExcel_Worksheet
     * @throws PHPExcel_Exception
     */
    public function getSheet($pIndex = 0)
    {

        $numSheets = count($this->_workSheetCollection);

        if ($pIndex > $numSheets - 1) {
            throw new PHPExcel_Exception(
            	"Your requested sheet index: {$pIndex} is out of bounds. The actual number of sheets is {$numSheets}."
           	);
        } else {
            return $this->_workSheetCollection[$pIndex];
        }
    }

    /**
     * Get all sheets
     *
     * @return PHPExcel_Worksheet[]
     */
    public function getAllSheets()
    {
        return $this->_workSheetCollection;
    }

    /**
     * Get sheet by name
     *
     * @param  string $pName Sheet name
     * @return PHPExcel_Worksheet
     */
    public function getSheetByName($pName = '')
    {
        $worksheetCount = count($this->_workSheetCollection);
        for ($i = 0; $i < $worksheetCount; ++$i) {
            if ($this->_workSheetCollection[$i]->getTitle() === $pName) {
                return $this->_workSheetCollection[$i];
            }
        }

        return NULL;
    }

    /**
     * Get index for sheet
     *
     * @param  PHPExcel_Worksheet $pSheet
     * @return Sheet index
     * @throws PHPExcel_Exception
     */
    public function getIndex(PHPExcel_Worksheet $pSheet)
    {
        foreach ($this->_workSheetCollection as $key => $value) {
            if ($value->getHashCode() == $pSheet->getHashCode()) {
                return $key;
            }
        }

        throw new PHPExcel_Exception("Sheet does not exist.");
    }

    /**
     * Set index for sheet by sheet name.
     *
     * @param  string $sheetName Sheet name to modify index for
     * @param  int $newIndex New index for the sheet
     * @return New sheet index
     * @throws PHPExcel_Exception
     */
    public function setIndexByName($sheetName, $newIndex)
    {
        $oldIndex = $this->getIndex($this->getSheetByName($sheetName));
        $pSheet = array_splice(
            $this->_workSheetCollection,
            $oldIndex,
            1
        );
        array_splice(
            $this->_workSheetCollection,
            $newIndex,
            0,
            $pSheet
        );
        return $newIndex;
    }

    /**
     * Get sheet count
     *
     * @return int
     */
    public function getSheetCount()
    {
        return count($this->_workSheetCollection);
    }

    /**
     * Get active sheet index
     *
     * @return int Active sheet index
     */
    public function getActiveSheetIndex()
    {
        return $this->_activeSheetIndex;
    }

    /**
     * Set active sheet index
     *
     * @param  int $pIndex Active sheet index
     * @throws PHPExcel_Exception
     * @return PHPExcel_Worksheet
     */
    public function setActiveSheetIndex($pIndex = 0)
    {
    		$numSheets = count($this->_workSheetCollection);

        if ($pIndex > $numSheets - 1) {
            throw new PHPExcel_Exception(
            	"You tried to set a sheet active by the out of bounds index: {$pIndex}. The actual number of sheets is {$numSheets}."
            );
        } else {
            $this->_activeSheetIndex = $pIndex;
        }
        return $this->getActiveSheet();
    }

    /**
     * Set active sheet index by name
     *
     * @param  string $pValue Sheet title
     * @return PHPExcel_Worksheet
     * @throws PHPExcel_Exception
     */
    public function setActiveSheetIndexByName($pValue = '')
    {
        if (($worksheet = $this->getSheetByName($pValue)) instanceof PHPExcel_Worksheet) {
            $this->setActiveSheetIndex($this->getIndex($worksheet));
            return $worksheet;
        }

        throw new PHPExcel_Exception('Workbook does not contain sheet:' . $pValue);
    }

    /**
     * Get sheet names
     *
     * @return string[]
     */
    public function getSheetNames()
    {
        $returnValue = array();
        $worksheetCount = $this->getSheetCount();
        for ($i = 0; $i < $worksheetCount; ++$i) {
            $returnValue[] = $this->getSheet($i)->getTitle();
        }

        return $returnValue;
    }

    /**
     * Add external sheet
     *
     * @param  PHPExcel_Worksheet $pSheet External sheet to add
     * @param  int|null $iSheetIndex Index where sheet should go (0,1,..., or null for last)
     * @throws PHPExcel_Exception
     * @return PHPExcel_Worksheet
     */
    public function addExternalSheet(PHPExcel_Worksheet $pSheet, $iSheetIndex = null) {
        if ($this->sheetNameExists($pSheet->getTitle())) {
            throw new PHPExcel_Exception("Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename the external sheet first.");
        }

        // count how many cellXfs there are in this workbook currently, we will need this below
        $countCellXfs = count($this->_cellXfCollection);

        // copy all the shared cellXfs from the external workbook and append them to the current
        foreach ($pSheet->getParent()->getCellXfCollection() as $cellXf) {
            $this->addCellXf(clone $cellXf);
        }

        // move sheet to this workbook
        $pSheet->rebindParent($this);

        // update the cellXfs
        foreach ($pSheet->getCellCollection(false) as $cellID) {
            $cell = $pSheet->getCell($cellID);
            $cell->setXfIndex( $cell->getXfIndex() + $countCellXfs );
        }

        return $this->addSheet($pSheet, $iSheetIndex);
    }

    /**
     * Get named ranges
     *
     * @return PHPExcel_NamedRange[]
     */
    public function getNamedRanges() {
        return $this->_namedRanges;
    }

    /**
     * Add named range
     *
     * @param  PHPExcel_NamedRange $namedRange
     * @return PHPExcel
     */
    public function addNamedRange(PHPExcel_NamedRange $namedRange) {
        if ($namedRange->getScope() == null) {
            // global scope
            $this->_namedRanges[$namedRange->getName()] = $namedRange;
        } else {
            // local scope
            $this->_namedRanges[$namedRange->getScope()->getTitle().'!'.$namedRange->getName()] = $namedRange;
        }
        return true;
    }

    /**
     * Get named range
     *
     * @param  string $namedRange
     * @param  PHPExcel_Worksheet|null $pSheet Scope. Use null for global scope
     * @return PHPExcel_NamedRange|null
     */
    public function getNamedRange($namedRange, PHPExcel_Worksheet $pSheet = null) {
        $returnValue = null;

        if ($namedRange != '' && ($namedRange !== NULL)) {
            // first look for global defined name
            if (isset($this->_namedRanges[$namedRange])) {
                $returnValue = $this->_namedRanges[$namedRange];
            }

            // then look for local defined name (has priority over global defined name if both names exist)
            if (($pSheet !== NULL) && isset($this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange])) {
                $returnValue = $this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange];
            }
        }

        return $returnValue;
    }

    /**
     * Remove named range
     *
     * @param  string  $namedRange
     * @param  PHPExcel_Worksheet|null  $pSheet  Scope: use null for global scope.
     * @return PHPExcel
     */
    public function removeNamedRange($namedRange, PHPExcel_Worksheet $pSheet = null) {
        if ($pSheet === NULL) {
            if (isset($this->_namedRanges[$namedRange])) {
                unset($this->_namedRanges[$namedRange]);
            }
        } else {
            if (isset($this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange])) {
                unset($this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange]);
            }
        }
        return $this;
    }

    /**
     * Get worksheet iterator
     *
     * @return PHPExcel_WorksheetIterator
     */
    public function getWorksheetIterator() {
        return new PHPExcel_WorksheetIterator($this);
    }

    /**
     * Copy workbook (!= clone!)
     *
     * @return PHPExcel
     */
    public function copy() {
        $copied = clone $this;

        $worksheetCount = count($this->_workSheetCollection);
        for ($i = 0; $i < $worksheetCount; ++$i) {
            $this->_workSheetCollection[$i] = $this->_workSheetCollection[$i]->copy();
            $this->_workSheetCollection[$i]->rebindParent($this);
        }

        return $copied;
    }

    /**
     * Implement PHP __clone to create a deep clone, not just a shallow copy.
     */
    public function __clone() {
        foreach($this as $key => $val) {
            if (is_object($val) || (is_array($val))) {
                $this->{$key} = unserialize(serialize($val));
            }
        }
    }

    /**
     * Get the workbook collection of cellXfs
     *
     * @return PHPExcel_Style[]
     */
    public function getCellXfCollection()
    {
        return $this->_cellXfCollection;
    }

    /**
     * Get cellXf by index
     *
     * @param  int $pIndex
     * @return PHPExcel_Style
     */
    public function getCellXfByIndex($pIndex = 0)
    {
        return $this->_cellXfCollection[$pIndex];
    }

    /**
     * Get cellXf by hash code
     *
     * @param  string $pValue
     * @return PHPExcel_Style|false
     */
    public function getCellXfByHashCode($pValue = '')
    {
        foreach ($this->_cellXfCollection as $cellXf) {
            if ($cellXf->getHashCode() == $pValue) {
                return $cellXf;
            }
        }
        return false;
    }

    /**
     * Check if style exists in style collection
     *
     * @param  PHPExcel_Style $pCellStyle
     * @return boolean
     */
    public function cellXfExists($pCellStyle = null)
    {
        return in_array($pCellStyle, $this->_cellXfCollection, true);
    }

    /**
     * Get default style
     *
     * @return PHPExcel_Style
     * @throws PHPExcel_Exception
     */
    public function getDefaultStyle()
    {
        if (isset($this->_cellXfCollection[0])) {
            return $this->_cellXfCollection[0];
        }
        throw new PHPExcel_Exception('No default style found for this workbook');
    }

    /**
     * Add a cellXf to the workbook
     *
     * @param PHPExcel_Style $style
     */
    public function addCellXf(PHPExcel_Style $style)
    {
        $this->_cellXfCollection[] = $style;
        $style->setIndex(count($this->_cellXfCollection) - 1);
    }

    /**
     * Remove cellXf by index. It is ensured that all cells get their xf index updated.
     *
     * @param  int $pIndex Index to cellXf
     * @throws PHPExcel_Exception
     */
    public function removeCellXfByIndex($pIndex = 0)
    {
        if ($pIndex > count($this->_cellXfCollection) - 1) {
            throw new PHPExcel_Exception("CellXf index is out of bounds.");
        } else {
            // first remove the cellXf
            array_splice($this->_cellXfCollection, $pIndex, 1);

            // then update cellXf indexes for cells
            foreach ($this->_workSheetCollection as $worksheet) {
                foreach ($worksheet->getCellCollection(false) as $cellID) {
                    $cell = $worksheet->getCell($cellID);
                    $xfIndex = $cell->getXfIndex();
                    if ($xfIndex > $pIndex ) {
                        // decrease xf index by 1
                        $cell->setXfIndex($xfIndex - 1);
                    } else if ($xfIndex == $pIndex) {
                        // set to default xf index 0
                        $cell->setXfIndex(0);
                    }
                }
            }
        }
    }

    /**
     * Get the cellXf supervisor
     *
     * @return PHPExcel_Style
     */
    public function getCellXfSupervisor()
    {
        return $this->_cellXfSupervisor;
    }

    /**
     * Get the workbook collection of cellStyleXfs
     *
     * @return PHPExcel_Style[]
     */
    public function getCellStyleXfCollection()
    {
        return $this->_cellStyleXfCollection;
    }

    /**
     * Get cellStyleXf by index
     *
     * @param  int $pIndex
     * @return PHPExcel_Style
     */
    public function getCellStyleXfByIndex($pIndex = 0)
    {
        return $this->_cellStyleXfCollection[$pIndex];
    }

    /**
     * Get cellStyleXf by hash code
     *
     * @param  string $pValue
     * @return PHPExcel_Style|false
     */
    public function getCellStyleXfByHashCode($pValue = '')
    {
        foreach ($this->_cellXfStyleCollection as $cellStyleXf) {
            if ($cellStyleXf->getHashCode() == $pValue) {
                return $cellStyleXf;
            }
        }
        return false;
    }

    /**
     * Add a cellStyleXf to the workbook
     *
     * @param PHPExcel_Style $pStyle
     */
    public function addCellStyleXf(PHPExcel_Style $pStyle)
    {
        $this->_cellStyleXfCollection[] = $pStyle;
        $pStyle->setIndex(count($this->_cellStyleXfCollection) - 1);
    }

    /**
     * Remove cellStyleXf by index
     *
     * @param int $pIndex
     * @throws PHPExcel_Exception
     */
    public function removeCellStyleXfByIndex($pIndex = 0)
    {
        if ($pIndex > count($this->_cellStyleXfCollection) - 1) {
            throw new PHPExcel_Exception("CellStyleXf index is out of bounds.");
        } else {
            array_splice($this->_cellStyleXfCollection, $pIndex, 1);
        }
    }

    /**
     * Eliminate all unneeded cellXf and afterwards update the xfIndex for all cells
     * and columns in the workbook
     */
    public function garbageCollect()
    {
        // how many references are there to each cellXf ?
        $countReferencesCellXf = array();
        foreach ($this->_cellXfCollection as $index => $cellXf) {
            $countReferencesCellXf[$index] = 0;
        }

        foreach ($this->getWorksheetIterator() as $sheet) {

            // from cells
            foreach ($sheet->getCellCollection(false) as $cellID) {
                $cell = $sheet->getCell($cellID);
                ++$countReferencesCellXf[$cell->getXfIndex()];
            }

            // from row dimensions
            foreach ($sheet->getRowDimensions() as $rowDimension) {
                if ($rowDimension->getXfIndex() !== null) {
                    ++$countReferencesCellXf[$rowDimension->getXfIndex()];
                }
            }

            // from column dimensions
            foreach ($sheet->getColumnDimensions() as $columnDimension) {
                ++$countReferencesCellXf[$columnDimension->getXfIndex()];
            }
        }

        // remove cellXfs without references and create mapping so we can update xfIndex
        // for all cells and columns
        $countNeededCellXfs = 0;
        foreach ($this->_cellXfCollection as $index => $cellXf) {
            if ($countReferencesCellXf[$index] > 0 || $index == 0) { // we must never remove the first cellXf
                ++$countNeededCellXfs;
            } else {
                unset($this->_cellXfCollection[$index]);
            }
            $map[$index] = $countNeededCellXfs - 1;
        }
        $this->_cellXfCollection = array_values($this->_cellXfCollection);

        // update the index for all cellXfs
        foreach ($this->_cellXfCollection as $i => $cellXf) {
            $cellXf->setIndex($i);
        }

        // make sure there is always at least one cellXf (there should be)
        if (empty($this->_cellXfCollection)) {
            $this->_cellXfCollection[] = new PHPExcel_Style();
        }

        // update the xfIndex for all cells, row dimensions, column dimensions
        foreach ($this->getWorksheetIterator() as $sheet) {

            // for all cells
            foreach ($sheet->getCellCollection(false) as $cellID) {
                $cell = $sheet->getCell($cellID);
                $cell->setXfIndex( $map[$cell->getXfIndex()] );
            }

            // for all row dimensions
            foreach ($sheet->getRowDimensions() as $rowDimension) {
                if ($rowDimension->getXfIndex() !== null) {
                    $rowDimension->setXfIndex( $map[$rowDimension->getXfIndex()] );
                }
            }

            // for all column dimensions
            foreach ($sheet->getColumnDimensions() as $columnDimension) {
                $columnDimension->setXfIndex( $map[$columnDimension->getXfIndex()] );
            }

			// also do garbage collection for all the sheets
            $sheet->garbageCollect();
        }
    }

    /**
     * Return the unique ID value assigned to this spreadsheet workbook
     *
     * @return string
     */
    public function getID() {
        return $this->_uniqueID;
    }

}

新建一个testExcel.php
在这里插入图片描述

testExcel.php完整代码：

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<?php
    require_once ("PHPExcel.php");//包含一次
    $objPHPExcel = new PHPExcel();

    $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
        ->setLastModifiedBy("alice88")
        ->setTitle("Friday")
        ->setSubject("Having weenkends")
        ->setDescription("Hello world!")
        ->setKeywords("kw")
        ->setCategory("日期");

    $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', 'Hello')
        ->setCellValue('B2', 'world!')
        ->setCellValue('C1', 'Hello')
        ->setCellValue('D2', 'world!');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save("1.xlsx");

?>
</body>
</html>

在这里插入图片描述
之后在该目录下会生成1.xlsx文件是我们之前想保存文件到的位置
在这里插入图片描述

表格内容
在这里插入图片描述

把表格的这个插件可以用到导出这个功能
在这里插入图片描述
import.php（导入文件）

  • header(“content-type:text/html;charset=utf-8”);//设置网页头信息（网页重定向，发送文件格式）test/plain
  • $con=mysqli_connect(“localhost”,“root”,"",“neuvideo”);//连接数据库
  • mysqli_query($con,“set names utf8”);//设置编码方式
  • $sql=“select * from users”;
  • $rs=mysqli_query($con,$sql);//取出结果集
  • require_once ("…/common/PHPExcel.php");//包含一次
<?php
header("content-type:text/html;charset=utf-8");//设置网页头信息（网页重定向，发送文件格式）test/plain
$con=mysqli_connect("localhost","root","","neuvideo");//连接数据库
mysqli_query($con,"set names utf8");//设置编码方式
$sql="select * from users";
$rs=mysqli_query($con,$sql);
//取出结果集
require_once ("../common/PHPExcel.php");//包含一次
$objPHPExcel = new PHPExcel();

$objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue('A1', '用户名：')
    ->setCellValue('B1', '性别：')
    ->setCellValue('C1', '邮箱：')
    ->setCellValue('D1', '头像：');

$i=2;
while ($row=mysqli_fetch_array($rs)){
    $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue("A$i", $row["uname"])
        ->setCellValue("B$i", $row["gender"])
        ->setCellValue("C$i", $row["email"])
        ->setCellValue("D$i", $row["pic"]);
    $i++;
}
//生成
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save("1.xlsx");
?>

在这里插入图片描述

在这里插入图片描述

userlist.php
在这里插入图片描述
完整

<?php
  require_once('tpl/header.php');
?>

  <form  action="">
    请输入用户名：
    <input type="text" name="key">
    <input type="submit" value="搜索">
  </form>
<?php
//调用dbConn.php
调用连接数据库函数,返回值是连接号
$con=connect();
$sql="select * from users ORDER BY uid DESC ";//sql语句

//搜索
if(isset($_GET["key"])){
    $k=$_GET["key"];
    $sql="select * from users where uname like '%$k%' order by uid desc";//sql语句
}
//删除_删除每列后的选项——是同一个文件——判断是删除的哪个用户，超链后面的key值来区分——主见
$rs=mysqli_query($con,$sql);
$num=mysqli_num_rows($rs);//总记录数
$pageSize=3;//每个页显示多少条记录
$pageNum=ceil($num/$pageSize); //一共多少页,向上取整10/3
if(isset($_GET["page"])&&$_GET["page"]!="")
    $page=$_GET["page"];//当前的页号

else $page=1;//当前是哪一页
$start=($page-1)*$pageSize;//起始页号
$sql="select * from users order by uid desc limit $start,$pageSize";
$rs=mysqli_query($con,$sql);//执行
?>



    <a href="userlist.php?page=1">首页</a>
<?php
if ($page!=1) {
    ?>
    <a href="userlist.php?page=<?php echo($page - 1); ?>">上一页</a>
        <?php
    }
    if ($page!=$pageNum) {
        ?>

    <a href="userlist.php?page=<?php echo($page + 1); ?>">下一页</a>
    <?php
}
    ?>
<!--    如果没有任何记录，$pageNum=0，尾页的超链page=0；在尾页点击上一页，page=-1-->
    <a href="userlist.php?page=<?php echo $pageNum;?>">尾页</a>

    <form action="">
<!--        跳转文本框只填写数字-->
        <input type="text" name="page" size="3">
        <input type="button" value="提交">
    </form>


<table class="table table-hover">
    <caption>共有 <?php
    echo $num;
    ?>
        名用户(<a href="import.php">导出Excel</a>)(<a href="import1.php">导出Excel1</a>)</caption>
    <tr>
      <th>用户编号</th>
      <th>用户名</th>
      <th>性别</th>
      <th>生日</th>
      <th width='80'>头像</th>
      <th>电子邮件</th>
      <th>操作</th>
    </tr>
  <?php
    $i=0;
    while ($row = mysqli_fetch_array($rs)){//


    ?>
    <tr>
      <td><?php echo ++$i;//先加 后打印?></td>
      <td><?php echo $row["uname"];?></td>
      <td><?php
           echo ($row["gender"]==0)?"男":"女"
          ?></td>
      <td>
          <?php
          echo $row["birthdate"] ?></td>
      <td><img src="../images/<?php echo $row["pic"];?>" width=60 height=60 title= alt=""></td>
      <td><?php
          echo $row["email"]
          ?></td>
      <td><a href="userEdit.php?uid=<?php echo $row["uid"]; ?>">修改</a>
          | <a href="userDelete.php?uid=<?php echo $row["uid"]; ?>">删除</a></td>
    </tr>

<?php
    } ?>


</table>

<?php
  require_once('tpl/footer.php');
?>

在浏览器中打开
在这里插入图片描述
在这里插入图片描述
import1.php

<?php
header("content-type:text/html;charset=utf-8");
$con=mysqli_connect("localhost","root","","neuvideo");
mysqli_query($con,"set names utf8");
$sql="select * from users";
$rs=mysqli_query($con,$sql);
require_once("../common/PHPExcel.php");
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue('A1', '用户名')
    ->setCellValue('B1', '性别')
    ->setCellValue('C1', '邮箱')
    ->setCellValue('D1', '头像');
$i=2;
while($row=mysqli_fetch_array($rs)){

    $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue("A$i", $row["uname"])
        ->setCellValue("B$i", $row["gender"])
        ->setCellValue("C$i", $row["email"])
        ->setCellValue("D$i", $row["pic"]);

    $i++;
}

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
// $objWriter->save("1.xlsx");
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename=1.xlsx');
header('Cache-Control: max-age=0');
// $objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel2007');
$objWriter->save('php://output');
// exit;
?>
宝拉不想努力了
关注
专栏目录
PHPExecl 插件
04-19
PHPExcel是国外人开发的一个php处理excel插件了，这个插件没有中文的说明了，只有英文说明了，
php-excel读取插件
03-23
本文将详细介绍如何使用php-excel读取插件以及相关的技术要点。 首先，我们要明确的是，"SpreadsheetReader"是PHP中一个流行的开源库，用于读取Excel文件。这个库支持Biff8（Excel 97-2003格式）和OOXML（Excel ...
php报表插件,excel插件
weixin_42102713的博客
03-10 382
excel插件是非常好用的东西，它可以帮助你提高效率，简化繁琐的操作，下面为大家介绍几款。方方格子，简称FFCell今天给大家分享的是一款超级好用的excel插件，是一款基于Excel的工具箱软件(Com加载项)，极大的增强了Excel程序的功能，提高用户工作效率。插件采用VSTO开发，支持32位和64位Office，支持Excel2007至2016各版本。不支持Excel2003、不支持WPS。...
php---excel
最新发布
qq_57700056的博客
06-29 357
在 PHP 中导出 Excel 文件可以使用多种库，其中最流行的是。这个库是由PHPExcel演变而来的，功能强大且适用于各种 Excel 操作。下面是一个简单的示例，展示如何使用库来创建和导出一个 Excel 文件。
phpexcel最新的插件
爱打echo的猴子
10-22 864
https://packagist.org/packages/senman/phpoffice
PHP使用第三方插件PHPExcel
坚持，努力，奋斗不止
08-20 1213
<?php //载入PHPExcel类 include(dirname(__FILE__).'/phpexcel/PHPExcel.php'); $Obj = new PHPExcel_Reader_Excel5(); $Obj->setReadDataOnly(true); //读取demo.xls文件 $phpExcel = $Obj->load(dirname(__FIL...
tablepress php,优美、强大的wordpress表格插件：TablePress
weixin_36417344的博客
03-21 562
作为一个数据分析师，博客怎能少了表格插件，这里推荐本博使用的一款优美的、功能强大、支持中文的wordpress表格插件。先看本博使用效果：示例页面：文章页效果截图：插件介绍(引用官方文档)：TablePress 使您能够通过 WordPress 管理后台创建和管理表格。 表格可以包含文本、数字，甚至 HTML 代码(例如，插入图片和超链接)。 然后，您就可以使用简码将表格显示在文章、静态页，或者文...
php不使用插件导出excel的简单方法
12-19
在PHP中，有时候我们需要在没有使用任何第三方插件的情况下生成Excel文件来导出数据，这通常是为了节省资源或者简化项目依赖。本篇文章将详细介绍一种不使用插件导出Excel的简单方法。 首先，我们创建一个名为`$...
基于PHP的excel插件支持7.4(操作Excel实现导入导出).zip
07-20
"基于PHP的excel插件支持7.4(操作Excel实现导入导出)"是一个专门针对这些需求的工具，它允许开发者通过PHP来操作Microsoft Excel文件，从而实现数据的导入和导出功能。 首先，我们需要理解PHP如何与Excel文件交互。...
Makro Tools-crx插件
04-04
v.2编辑，提取产品价格和SKU简单数据提取工具，价格，价格，销售，评论此Chrome Extension允许您以可编辑的Excel格式提取BIGC网页详细信息。现在，您可以使用此扩展将产品列表分开竞争对手。产品规格中的产品。特定...
bootstrap表格插件php,分享一个很棒的插件--Bootstrap table
weixin_35801556的博客
03-22 437
这篇文章主要总结介绍了Bootstrap table的使用方法，服务器分页、客户端分页的转换，table刷新，具有一定的参考价值，感兴趣的小伙伴们可以参考一下最近接触一个很棒的插件，Bootstrap table 没做过前端的表示对table的印象还只停留在html的table标签那一套，用过bootstrap table之后不得不说真是牛。构造方式1 、HTML//定义一系列工具栏...data...
php 表格控件
10-19
php绘制折线图，条形图
PHP+jqGrid表格插件实现增删改查
12-21
无刷新增删改查，网上都有免费下载，所以就不设置积分了。
phpexcel
流星雨的博客
06-27 2189
利用PHPExcel 实现excel数据的导入导出（源码实现） 在开发过程中，经常会遇到导入导出的需求，利用phpexcel类实现起来也是比较容易的，下面，我们一步一步实现 提前将phpexcel类下载，并放在扩展目录中，如图所示 一、Excel导出 导出功能，相对复杂，复杂就复杂在设置导出的excel文件的样式以及内容如何循环赋值，而且导出之后的处理结果，一般是直接在通过浏览器...
php表格datagrid插件,jQuery表格插件bs_grid
weixin_39527078的博客
03-21 136
插件描述：bs_grid是一个jQuery DataGrid插件，基于Bootstrap。先进行选择，排序和过滤，分页。完全可定制的，灵活的网络设计，定位。特点引导创建(bootstrap 2支持)响应的网页设计完全可配置的获得JSON格式的数据(使用Ajax的任何服务器端技术)一个PHP类设置为服务器端操作更改列顺序显示/隐藏列列式简单的点击排序的列灵活的数据排序(多栏)单个或多个行选择强大的分...
php表格datagrid插件,让datagrid表格
weixin_42378973的博客
03-21 327
EasyUI - DataGrid 去右边空白滚动条列在网上查了很长时间没有找到解决方法，干脆自已查看一下代码来解决吧，随是压缩过的，不过也还是好查的，工夫不负有心人，终于解决了，方法如下：一、我们先让表格自适应宽度文章天伟2013-11-24717浏览量【我们一起写框架】MVVM的WPF框架(五)—完结篇前言这篇文章是WPF框架系列的最后一篇，在这里我想阐述一下我对框架设计的理解。我对框架设计的...
php jquery grid,Jqgrid之强大的表格插件应用
weixin_28854085的博客
03-10 196
jqGrid是一款基于jQuery的功能强大的表格插件，使用jqGrid可以轻松实现前端页面与后台数据进行ajax异步通信，jqGrid运行速度相当快，可以很好的应用在一些后台管理系统来管理大量数据的场合jqGrid是一款基于jQuery的功能强大的表格插件，使用jqGrid可以轻松实现前端页面与后台数据进行ajax异步通信，jqGrid运行速度相当快，可以很好的应用在一些后台管理系统来管理大量数...
php中excel控件导出时查询字段及备注
专注企业信息化
05-08 765
一、在ThinkPHP中excel导出控件需要字段名及备注 SELECT COLUMN_NAME, COLUMN_COMMENT  AS  `备注` FROM information_schema.COLUMNS WHERE TABLE_NAME='xy_eventmanage' 二、查询出用editplus批量替换转换 public function outxls() { $mod
list grid php,mmGrid一款让你上瘾的表格插件，绝对值得拥有
weixin_29658015的博客
03-18 351
欢迎关注支持。前言在遇到表格插件之前，开发后台表格的时候，一直是手写table，th，tr，td，或者不停的复制粘贴，不胜其烦，自从用了mmGrid来创建表格或者列表，在也没有烦恼了，今天我们一起来看看mmGrid插件为什么让后台开发工程师上瘾的。内容概要1、mmGrid简介2、mmGrid常用功能介绍3、mmGrid实践一、mmGrid简介mmGrid基于jQuery的表格插件，样式简洁，修改容...
php导出数据到excel表格的插件
12-09
PHP导出数据到Excel表格有很多插件可供选择，以下是其中几个常用的插件： 1. PHPExcel： ...这些插件都可以帮助开发者在PHP中实现将数据导出到Excel表格的功能，可以根据具体需求选择合适的插件来使用。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值