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;
    }

可以了

大海技术博客
请先登录后发表评论
  • latest comments
  • 总共0条评论