首页 > > 网络编程 > Php编程 >

phpExcel操作

2019-05-22 06:36:11来源：博客园阅读 ()

下载PHPExcel资源

https://github.com/PHPOffice/PHPExcel

引入PHPExcel资源

include "/lib/PHPExcel/Classes/PHPExcel/IOFactory.php";//读取Excel所需文件
include "/lib/PHPExcel/Classes/PHPExcel.php"; //写入excel所需的类文件

构造myExcelUtil类

private $fileName = null;
private $sheet = 0;

/**
 * myExcelUtil构造函数，构造参数为文件路径
 *
 * @param String $fileName
 */
public function __construct($fileName = null)
{
    $this->fileName = $fileName;
}

读出一个sheet表

/**
 * 读取一个sheet表，默认第一个
 *
 */
public function readSheet()
{
    $this->readSheetBySheet(0);
}

/**
 * 读取一个sheet表
 * @param String|int $sheetIndex,表名或索引
 * @return array,二维数组
 */
public function readSheetBySheet($sheetIndex = 0)
{
    //$filename = 'xuehua04.xlsx';
    date_default_timezone_set('PRC');
    // 读取excel文件
    try {
        $inputFileType = PHPExcel_IOFactory::identify($this->fileName);
        $objReader = PHPExcel_IOFactory::createReader($inputFileType);
        $objPHPExcel = $objReader->load($this->fileName);
    } catch (Exception $e) {
        die("加载文件发生错误：" . pathinfo($this->fileName, PATHINFO_BASENAME) . ":" . $e->getMessage());
    }

    // 确定要读取的sheet
    try {
        $sheet = $objPHPExcel->getSheet($sheetIndex);
        $this->setSheet($sheet->getTitle());
    } catch (PHPExcel_Exception $e) {
    }
    $highestRow = $sheet->getHighestRow();
    $highestColumn = $sheet->getHighestColumn();

    $outputArray = array();
    // 获取一行的数据
    for ($row = 1; $row <= $highestRow; $row++) {
        // Read a row of data into an array
        $rowData = $sheet->rangeToArray("A" . $row . ":" . $highestColumn . $row, NULL, TRUE, FALSE);
        //这里得到的rowData都是一行的数据，得到数据后自行处理，我们这里只打出来看看效果
        $outputArray[] = $rowData[0];
        //print_r($rowData);
    }
    return $outputArray;
}

插入一条数据

/**
 * 向指定表指定位置插入数据
 * @param String|int $sheetIndex,表名或索引
 * @param int $row,行
 * @param int $col,列
 * @param string $value,值
 */
public function addCellValueByRC($sheetIndex = 0, $row = 0, $col = 0, $value = "")
{
    $inputArray = $this->readSheetBySheet(0);
    $inputArray[$row - 1][$col - 1] = $value;

    $objPHPExcel = new PHPExcel();                     //实例化一个PHPExcel()对象
    try {
        $objSheet = $objPHPExcel->getActiveSheet();
    } catch (PHPExcel_Exception $e) {
    }        //选取当前的sheet对象

    $objSheet->setTitle($this->getSheet());      //对当前sheet对象命名
    //常规方式：利用setCellValue()填充数据
    //$objSheet->setCellValue("A1", "张三")->setCellValue("B1", "李四");   //利用setCellValues()填充数据
    /*$arrayLength = count($inputArray);
    $basic = 'A';
    for($i = 0;$i<$arrayLength;$i++){
        foreach ($inputArray[$i] as $key => $d){
            $objSheet->setCellValue($basic.($i+1), $d);
            $basic++;
        }
        $basic = 'A';
    }*/
    //取巧模式：利用fromArray()填充数据
    try {
        $objSheet->fromArray($inputArray);
    } catch (PHPExcel_Exception $e) {
    }  //利用fromArray()直接一次性填充数据*/
    try {
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    } catch (PHPExcel_Reader_Exception $e) {
    }   //设定写入excel的类型
    try {
        $objWriter->save($this->getFileName());
    } catch (PHPExcel_Writer_Exception $e) {
    }       //保存文件
}