php excel 导入 导出

导入

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');//Xlxs是excel文件的格式，有的文件可能是Xls，根据实际情况来
$reader->setReadDataOnly(TRUE);//设置文件只读，可以去掉
$spreadsheet = $reader->load('文件路径'); //载入excel表格

$worksheet = $spreadsheet->getActiveSheet();
$highestRow = $worksheet->getHighestRow(); // 总行数
$highestColumn = $worksheet->getHighestColumn(); // 总列数
$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); 

$lines = $highestRow - 1;//这里的1是根据excel文件确定的，表头标题占几行，就要用总行数减去标题所占的行数
if ($lines <= 0) {
        $this->error('Excel表格中没有数据');
}
        Db::startTrans();
        try {
            for ($row = 1; $row <= $highestRow; ++$row) {
           //数据处理操作
                // $name = $worksheet->getCellByColumnAndRow(1, $row)->getValue(); //获取每行第1列的数据
                // $date = $worksheet->getCellByColumnAndRow(2, $row)->getValue(); //获取每行第2列的数据
        }
            Db::commit();
            $this->success('success');
        } catch (Exception $e) {
            Db::rollback();
            $this->error( $e->getMessage());
        }

导出

        $newExcel = new Spreadsheet();//创建一个新的excel文档
        $objSheet = $newExcel->getActiveSheet();//获取当前操作sheet的对象
    
        $objSheet->setTitle($name);//设置当前sheet的标题

        //样式设置 - 合并和拆分
        $objSheet->mergeCells('A1:C1'); //合并单元格 C根据实际表格的列数来确定
        //$sheet -> unmergeCells('C3:G3'); //拆分单元格
        $objSheet->setCellValue('A1', $name . '（' . $dateInfo['start'] . ' ~ ' . $dateInfo['end'] . '）');//合并单元格后设置表格标题名称

        //设置第一栏的中文标题
        $objSheet->setCellValue('A2', '序 号')
            ->setCellValue('B2', '编号')
            ->setCellValue('C2', '名称');

        //写入数据
        $dataCount = count($info);
        $k = 2;
        if ($dataCount != 0) {
            for ($i = 0; $i < $dataCount; $i++) {
                $k = $k + 1;
                $j = $i + 1;
                $objSheet->setCellValue('A' . $k, $j)
                    ->setCellValue('B' . $k, $info[$i]['code'])
                    ->setCellValue('C' . $k, $info[$i]['name']);
            }
        }
        //设定样式
        //所有sheet的表头样式 加粗
        $font = [
            'font' => [
                'bold' => true,
                'size' => 14,
            ],
        ];
        $objSheet->getStyle('A1:C1')->applyFromArray($font);

        //样式设置 - 水平、垂直居中
        $styleArray = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ],
        ];
        $objSheet->getStyle('A1:C2')->applyFromArray($styleArray);
        $objSheet->getStyle('A1:A' . $k)->applyFromArray($styleArray);//设置第一列样式
        //所有sheet的内容样式-加黑色边框
        $borders = [
            'borders' => [
                'outline' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => '000000'],
                ],
                'inside' => [
                    'borderStyle' => Border::BORDER_THIN,
                ]
            ],
        ];
        $objSheet->getStyle('A1:C' . $k)->applyFromArray($borders);
        $objSheet->getDefaultRowDimension()->setRowHeight(30);//设置行高

        //增加缩进量
        $objSheet->getStyle('B3:B' . $k)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT)->setIndent(1);
  
        //设置宽度
        $objSheet->getColumnDimension("A")->setWidth(10);
        $objSheet->getColumnDimension("B")->setWidth(20);
       
    
       
        // 目录不存在则创建目录
        if (!file_exists(ROOT_PATH . 'public' . $path)) { //$path为文件保存目录
            mkdir(ROOT_PATH . 'public' . $path, 0777, true);
        }
        $format = 'Xlsx'; //根据需要，可以设置Xls，Xlsx
        $savePath = $path . '/' . md5($name . time()) . '.' . strtolower($format);
        $filepath = ROOT_PATH . 'public/' . str_replace('/', DS, $savePath);
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Cache-Control: max-age=0');
        $objWriter = IOFactory::createWriter($newExcel, $format);
        $objWriter->save($filepath);