phpExcel操作

下载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) {
    }       //保存文件
}