phpexcel,excel导入导出封装类

安装包 ​​composer require phpoffice/phpexcel​​

导出,分步走文件名/header/内容写入

class OfficeExcel
{
    public $obj;
    public $k = 2;

    public function __construct($author='',$title='',$theme='',$remark='')
    {
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()->setCreator($author);//创建人
        $objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");//最后修改人
        $objPHPExcel->getProperties()->setTitle($title);//标题
        $objPHPExcel->getProperties()->setSubject($theme);//主题
        $objPHPExcel->getProperties()->setDescription($remark);//描述
        $objPHPExcel->getProperties()->setKeywords("office 2007 openxml php"); //设置标记
        $objPHPExcel->getProperties()->setCategory("Test result file"); //设置类别
        $this->obj = $objPHPExcel;
    }

/**
* 第一栏头部
**/
    public function header($data)
    {
        $i = 'A';
        foreach ($data as $k => $v) {
            $this->obj->setActiveSheetIndex(0)->setCellValue($i . '1', $v);
            $i++;
        }
    }

/**
*写入表格
**/
    public function write($data)
    {
        $i = 'A';
        foreach ($data as $k => $v) {
            $this->obj->setActiveSheetIndex(0)->setCellValueExplicit($i . $this->k, $v);
            $i++;
        }
        $this->k++;
    }

/**
*关闭资源/给导出文件重命名
**/
    public function close($filename)
    {
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename=' . $filename);
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($this->obj, 'Excel2007');
        $objWriter->save($filename);
    }


}

 

或者,一次性走完

/**
 * 导出
 * @param array $column
 * @param array $data
 * @param string $filename 文件
 * @param string $savefile 是否保存成文件在服务器
 * @param bool $debug
 * @return string
 */
public static function export($column, $data, $filename)
{
    $objPHPExcel = new PHPExcel();    //实例化

    $i = 'A';
    foreach ($column as $k => $v) {
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($i . '1', $v);
        $i++;
    }

    if (!empty($data)) {
        foreach ($data as $k => $v) {
            $j = 'A';
            foreach ($v as $a => $b) {
                $objPHPExcel->setActiveSheetIndex(0)->setCellValueExplicit($j . ($k + 2), $b);
                $j++;
            }
        }
    }

    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename=' . $filename);
    header('Cache-Control: max-age=0');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save('php://output');
}

 

导入

/**
    * 导入excel
    * Path 文件地址
    */
   public static function import($Path)
   {
       $ext = strtolower(pathinfo(array_values($_FILES)[0]['name'], PATHINFO_EXTENSION));
       if ($ext =='xlsx') {
           $PHPReader = new PHPExcel_Reader_Excel2007();
           $PHPExcel = $PHPReader->load($Path);
       } else if ($ext =='xls') {
      //use excel2007 for 2007 format
           $objReader = PHPExcel_IOFactory::createReader('Excel5');
         //$filename可以是上传的文件，或者是指定的文件
           $PHPExcel = $objReader->load($Path);
       } else {
           return [];
       }
      //读取excel文件中的第一个工作表
       $currentSheet = $PHPExcel->getSheet(0);
      //取得最大的列号
       $allColumn = $currentSheet->getHighestColumn();
      //取得一共有多少行
       $allRow = $currentSheet->getHighestRow();
       /**从第二行开始输出，因为excel表中第一行为列名*/
       for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
           for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
               $address = $currentColumn . $currentRow;
               //读取到的数据
               $value = $currentSheet->getCell($address)->getValue();
               //如果是对象则转化为字符串
               if (is_object($value)) {
                   $value = $value->__toString();
               }
               //保存到数组$arr中
               $info[$currentRow-1][] = $value;
               /**如果输出汉字有乱码，则需将输出内容用iconv函数进行编码转换，如下将gb2312编码转为utf-8编码输出*/
               //echo iconv('utf-8','gb2312', $val)."\t";
           }
       }
       return $info;
   }

 

可以了