使用PhpSpreadsheet怎么导入导出Excel

这篇文章给大家介绍使用PHPSpreadsheet怎么导入导出excel，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。

phpspreadsheet 引入

由于PHPExcel早就停止更新维护，所以适用phpspreadsheet。不知道如何通过composer拉取项目包的同学，可以查看Composer学习一文。引入方法：

composer require phpoffice/phpspreadsheet

引入命名空间

由于本人项目中需要居中、背景、单元格格式等各种操作，所以引入较多，大家使用的时候，可以根据自己实际需要引入。

use PhpOffice\PhpSpreadsheet\Reader\Xlsx;use PhpOffice\PhpSpreadsheet\Reader\Xls;use PhpOffice\PhpSpreadsheet\ioFactory;use PhpOffice\PhpSpreadsheet\Cell\Coordinate;use PhpOffice\PhpSpreadsheet\Spreadsheet;use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;use PhpOffice\PhpSpreadsheet\Cell\DataType;use PhpOffice\PhpSpreadsheet\Style\Fill;use PhpOffice\PhpSpreadsheet\Style\Color;use PhpOffice\PhpSpreadsheet\Style\Alignment;use PhpOffice\PhpSpreadsheet\Style\Border;use PhpOffice\PhpSpreadsheet\Style\NumberFORMat;

Excel导入操作(importExcel)

除了单纯的处理Excel数据外，还可以将Excel中的合并项、公式项、单元格格式提取，提取后可根据业务需求做对应处理后存储起来，以便后续的各种操作。

 */function importExecl(string $file = '', int $sheet = 0, int $columnCnt = 0, &$options = []){    try {                $file = iconv("utf-8", "gb2312", $file);        if (empty($file) OR !file_exists($file)) {            throw new \Exception('文件不存在!');        }                $objRead = IOFactory::createReader('Xlsx');        if (!$objRead->canRead($file)) {                        $objRead = IOFactory::createReader('Xls');            if (!$objRead->canRead($file)) {                throw new \Exception('只支持导入Excel文件！');            }        }                empty($options) && $objRead->setReadDataOnly(true);                $obj = $objRead->load($file);                $currSheet = $obj->getSheet($sheet);        if (isset($options['mergeCells'])) {                        $options['mergeCells'] = $currSheet->getMergeCells();        }        if (0 == $columnCnt) {                        $columnH = $currSheet->getHighestColumn();                        $columnCnt = Coordinate::columnIndexFromString($columnH);        }                $rowCnt = $currSheet->getHighestRow();        $data   = [];                for ($_row = 1; $_row <= $rowCnt; $_row++) {            $isNull = true;            for ($_column = 1; $_column <= $columnCnt; $_column++) {                $cellName = Coordinate::stringFromColumnIndex($_column);                $cellId   = $cellName . $_row;                $cell     = $currSheet->getCell($cellId);                if (isset($options['format'])) {                                        $format = $cell->getStyle()->getNumberFormat()->getFormatCode();                                        $options['format'][$_row][$cellName] = $format;                }                if (isset($options['formula'])) {                                        $formula = $currSheet->getCell($cellId)->getValue();                    if (0 === strpos($formula, '=')) {                        $options['formula'][$cellName . $_row] = $formula;                    }                }                if (isset($format) && 'm/d/yyyy' == $format) {                                        $cell->getStyle()->getNumberFormat()->setFormatCode('yyyy/mm/dd');                }                $data[$_row][$cellName] = trim($currSheet->getCell($cellId)->getFormattedValue());                if (!empty($data[$_row][$cellName])) {                    $isNull = false;                }            }                        if ($isNull) {                unset($data[$_row]);            }        }        return $data;    } catch (\Exception $e) {        throw $e;    }}

将数据处理好后，可以通过额外配置，将导出的Excel做各种不同的配置，例如打印样式、锁定行、背景色、宽度等。

Excel导出操作(exportExcel)

function exportExcel(array $datas, string $fileName = '', array $options = []): bool{    try {        if (empty($datas)) {            return false;        }        set_time_limit(0);                $objspreadsheet = app(Spreadsheet::class);                $styleArray = [            'alignment' => [                'horizontal' => Alignment::HORIZONTAL_LEFT,                'vertical'   => Alignment::VERTICAL_CENTER,            ],        ];        $objSpreadsheet->getDefaultStyle()->applyFromArray($styleArray);                $activeSheet = $objSpreadsheet->setActiveSheetIndex(0);                if (isset($options['print']) && $options['print']) {                        $activeSheet->getPageSetup()->setPaperSize(PageSetup:: PAPERSIZE_A4);                        $pValue = 1 / 2.54;            $activeSheet->getPageMargins()->setTop($pValue / 2);            $activeSheet->getPageMargins()->setBottom($pValue * 2);            $activeSheet->getPageMargins()->setLeft($pValue / 2);            $activeSheet->getPageMargins()->setRight($pValue / 2);        }                foreach ($datas as $sKey => $sItem) {                        $pDataType = DataType::TYPE_STRING;                        if (isset($options['format']) && !empty($options['format'])) {                $colRow = Coordinate::coordinateFromString($sKey);                                if (isset($options['format'][$colRow[0]]) &&                    NumberFormat::FORMAT_GENERAL != $options['format'][$colRow[0]]) {                    $activeSheet->getStyle($sKey)->getNumberFormat()                        ->setFormatCode($options['format'][$colRow[0]]);                    if (false !== strpos($options['format'][$colRow[0]], '0.00') &&                        is_numeric(str_replace(['￥', ','], '', $sItem))) {                                                $pDataType = DataType::TYPE_NUMERIC;                        $sItem     = str_replace(['￥', ','], '', $sItem);                    }                } elseif (is_int($sItem)) {                    $pDataType = DataType::TYPE_NUMERIC;                }            }            $activeSheet->setCellValueExplicit($sKey, $sItem, $pDataType);                        if (false !== strstr($sKey, ":")) {                $options['mergeCells'][$sKey] = $sKey;            }        }        unset($datas);                if (isset($options['freezePane']) && !empty($options['freezePane'])) {            $activeSheet->freezePane($options['freezePane']);            unset($options['freezePane']);        }                if (isset($options['setWidth']) && !empty($options['setWidth'])) {            foreach ($options['setWidth'] as $swKey => $swItem) {                $activeSheet->getColumnDimension($swKey)->setWidth($swItem);            }            unset($options['setWidth']);        }                if (isset($options['setARGB']) && !empty($options['setARGB'])) {            foreach ($options['setARGB'] as $sItem) {                $activeSheet->getStyle($sItem)                    ->getFill()->setFillType(Fill::FILL_SOLID)                    ->getStartColor()->setARGB(Color::COLOR_YELLOW);            }            unset($options['setARGB']);        }                if (isset($options['formula']) && !empty($options['formula'])) {            foreach ($options['formula'] as $fKey => $fItem) {                $activeSheet->setCellValue($fKey, $fItem);            }            unset($options['formula']);        }                if (isset($options['mergeCells']) && !empty($options['mergeCells'])) {            $activeSheet->setMergeCells($options['mergeCells']);            unset($options['mergeCells']);        }                if (isset($options['alignCenter']) && !empty($options['alignCenter'])) {            $styleArray = [                'alignment' => [                    'horizontal' => Alignment::HORIZONTAL_CENTER,                    'vertical'   => Alignment::VERTICAL_CENTER,                ],            ];            foreach ($options['alignCenter'] as $acItem) {                $activeSheet->getStyle($acItem)->applyFromArray($styleArray);            }            unset($options['alignCenter']);        }                if (isset($options['bold']) && !empty($options['bold'])) {            foreach ($options['bold'] as $bItem) {                $activeSheet->getStyle($bItem)->getFont()->setBold(true);            }            unset($options['bold']);        }                if (isset($options['setBorder']) && $options['setBorder']) {            $border    = [                'borders' => [                    'allBorders' => [                        'borderStyle' => Border::BORDER_THIN, // 设置border样式                        'color'       => ['argb' => 'FF000000'], // 设置border颜色                    ],                ],            ];            $setBorder = 'A1:' . $activeSheet->getHighestColumn() . $activeSheet->getHighestRow();            $activeSheet->getStyle($setBorder)->applyFromArray($border);            unset($options['setBorder']);        }        $fileName = !empty($fileName) ? $fileName : (date('YmdHis') . '.xlsx');        if (!isset($options['savePath'])) {                        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');            header(                "Content-Disposition:attachment;filename=" . iconv(                    "utf-8", "GB2312//TRANSLIT", $fileName                )            );            header('Cache-Control: max-age=0');//禁止缓存            $savePath = 'php://output';        } else {            $savePath = $options['savePath'];        }        ob_clean();        ob_start();        $objWriter = IOFactory::createWriter($objSpreadsheet, 'Xlsx');        $objWriter->save($savePath);                $objSpreadsheet->disconnectWorksheets();        unset($objSpreadsheet);        ob_end_flush();        return true;    } catch (Exception $e) {        return false;    }}

关于使用PhpSpreadsheet怎么导入导出Excel就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。