PHPExcel及大哥PhpSpreadsheet

在 上张贴 由 fkiwi, fkiwi发表回复

 

总有那么人,放着美丽的管理平台不用,偏要你做导入导出Excel, 返璞归真吗 ?!!

 

GitHub地址

PHPExcel: 

https://github.com/PHPOffice/PHPExcel

PhpSpreadsheet: 

https://github.com/PHPOffice/PhpSpreadsheet

 

 

PHPExcel读excel表

    public function exportNews(){

        //文件上传代码略...
        $splInfo = null;
        if ($splInfo) {
            $filePath = $splInfo->getRealPath() ?: $splInfo->getPathname();
        
            //创建excel读对象
            $reader = \PHPExcel_IOFactory::createReader('Excel5');
            //得到excel操作对象
            $excel = \PHPExcel_IOFactory::load($filePath);

            //放入数组
            $data = array();

            //获取所有工作表名
            $SheetNamas = $excel->getSheetNames();

            //遍历工作表
            for($i = 0; $i < count($SheetNamas); $i++){
                //获取当前工作表名
                $SheetName = $SheetNamas[$i];
                //根据表名切换当前工作表
                $excel->setActiveSheetIndexByName($SheetName);
                //得到当前工作表对象
                $curSheet = $excel->getActiveSheet();
                //获取当前工作表最大行数
                $rows = $curSheet->getHighestRow();
                //获取当前工作表最大列数,返回的是最大的列名，如：B
                $cols = $curSheet->getHighestColumn();

                //将当前工作表名当键，内容为值存入数组
                $data[$SheetName] = array();

                //大写字母A的ASCII值是65 A-Z对应65-90
                for($j = 'A'; $j <= $cols; $j++ ){
                    for($k = 1; $k <= $rows; $k++){
                        $key = $j.$k;
                        $value = $curSheet->getCell($key)->getValue();
                        $data[$SheetName][$key] = $value;
                    }
                }

            }
            print_r($data);exit;

            $this->success(__('Upload successful'), null, [
                'url' => $uploadDir . $splInfo->getSaveName()
            ]);
        } else {
            // 上传失败获取错误信息
            $this->error($file->getError());
        }

    }

 

PHPExcel写Excel

//数据插入到excel
public function pushExcel($ids = null){


    $data = Db::table('fa_news')->field('id,title,content,status,sort,img,create_time,update_time')->select();
    //1.文件的创建
    //创建PHPExcel对象
    $objPHPExcel = new \PHPExcel();

    //写入excel操作
    //使用工厂的方式创建excel写入对象
    //第二个参数可以写'excel2007'或者 ''Excel5''
    // $writer = PHPExcel_IOFactory::createWriter($obj, 'Excel5');
    //$writer = \PHPExcel_IOFactory::createWriter($obj, 'excel2007');
    //或如下方式
    //$writer = new PHPExcel_Writer_Excel5($obj);

    $letter = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H');
    //表头数组
    $tableheader = array('ID', '标题', '内筒', '状态值', '排序', 'img', '创建时间','更新时间');

    //填充表头信息
    for ($i = 0; $i < count($tableheader); $i++) {
        $objPHPExcel->getActiveSheet()->setCellValue("$letter[$i]1", "$tableheader[$i]");
    }

    $excelName = date('Y-m-d') . '-' .mt_rand(100, 999);

    /*以下就是对处理Excel里的数据，横着取数据，主要是这一步，其他基本都不要改*/
    foreach ($data as $k => $v) {
        $num = $k + 1 + 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A' . $num, $v['id'])
            ->setCellValue('B' . $num, $v['title'])
            ->setCellValue('C' . $num, $v['content'])
            ->setCellValue('D' . $num, ($v['status'] == 1) ? '显示' : '隐藏')
            ->setCellValue('E' . $num, $v['sort'])
            ->setCellValue('F' . $num, $v['img'])
            ->setCellValue('G' . $num, $v['create_time'])
            ->setCellValue('H' . $num, $v['update_time']);
    }

    $objPHPExcel->getActiveSheet()->setTitle('测试');
    $objPHPExcel->setActiveSheetIndex(0);

    header('Content-Type: application/vnd.ms-excel;charset=UTF-8"');
    header('Content-Disposition: attachment;filename="' . $excelName . '.xls"');
    header('Cache-Control: max-age=0');
    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');
    exit;

}

 

 

PhpSpreadsheet 写 excel:

function export()
{
    //require_once __DIR__ . '/vendor/autoload.php';

    $data = [
        ['title1' => '111', 'title2' => '222'],
        ['title1' => '111', 'title2' => '222'],
        ['title1' => '111', 'title2' => '222']
    ];
    $title = ['第一行标题', '第二行标题'];

    // Create new Spreadsheet object
    $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();

    // 方法一，使用 setCellValueByColumnAndRow
    //表头
    //设置单元格内容
    foreach ($title as $key => $value) {
        // 单元格内容写入
        $sheet->setCellValueByColumnAndRow($key + 1, 1, $value);
    }
    $row = 2; // 从第二行开始
    foreach ($data as $item) {
        $column = 1;
        foreach ($item as $value) {
            // 单元格内容写入
            $sheet->setCellValueByColumnAndRow($column, $row, $value);
            $column++;
        }
        $row++;
    }

    // 方法二，使用 setCellValue
    //表头
    //设置单元格内容
    $titCol = 'A';
    foreach ($title as $key => $value) {
        // 单元格内容写入
        $sheet->setCellValue($titCol . '1', $value);
        $titCol++;
    }
    $row = 2; // 从第二行开始
    foreach ($data as $item) {
        $dataCol = 'A';
        foreach ($item as $value) {
            // 单元格内容写入
            $sheet->setCellValue($dataCol . $row, $value);
            $dataCol++;
        }
        $row++;
    }

    // Redirect output to a client’s web browser (Xlsx)
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="01simple.xlsx"');
    header('Cache-Control: max-age=0');
    // If you're serving to IE 9, then the following may be needed
    header('Cache-Control: max-age=1');

    // If you're serving to IE over SSL, then the following may be needed
    header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
    header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
    header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
    header('Pragma: public'); // HTTP/1.0

    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('php://output');
    exit;
}

 

PhpSpreadsheet 读excel:

   public function readExcel(){
        $inputFileName = ROOT_PATH . 'public/01simple.xlsx';
//        $inputFileName = ROOT_PATH . 'public/222.xls';
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);
        $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
        print_r($sheetData);
        exit;
    }

 

PHPExcel mark:

https://www.cnblogs.com/52lnamp/category/1231737.html

https://www.cnblogs.com/makalochen/p/12834440.html

 

 

PhpSpreadsheet mark:

https://www.cnblogs.com/woods1815/p/11372007.html