PhpSpreadsheet 导入、导出数据

先引入

use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

导出数据

/**
* 导出Excel
 * @param $datas    导出数据
 * @param $param    导出标题及每列对应的数据键名 ['数据键名' => '列标题']
 * @param $commonField  每列公共的数据
 * @param string $fileName  文件名称--保存目录为空时有效
 * @param string $options    保存目录--为空时直接唤醒浏览器下载
 * @return bool|mixed|string    失败则
 */
function exportExcel($datas, $param, $commonField, $fileName = '', $options = '')
{
    $commonColumn = []; //字段名称数组
    try {
        if (empty($datas)) {
            return false;
        }
        if (empty($param)) {
            return false;
        }
        //列名  字段名有多少个  列名就有多少
        $rows = array(
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
            'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ',
            'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV', 'BW', 'BX', 'BY', 'BZ'
        );
        set_time_limit(50);

        /** @var Spreadsheet $objSpreadsheet */
        $objSpreadsheet = new Spreadsheet();
        //设置默认文字居左，上下居中
        $styleArray = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_LEFT,
                'vertical'   => Alignment::VERTICAL_CENTER,
            ],
        ];
        $objSpreadsheet->getDefaultStyle()->applyFromArray($styleArray);
        //设置Excel Sheet
        $activeSheet = $objSpreadsheet->setActiveSheetIndex(0);
        $startRow = 2;
        //默认文本格式
        $pDataType = DataType::TYPE_STRING;
        $sort_id = 1;
        //行数据处理
        foreach ($datas as $sKey => $sItem) {
            $starColumn = 0;
            foreach ($param as $key => $value) {
                $activeSheet->setCellValueExplicit($rows[$starColumn] .$startRow, $val, $pDataType);
                $starColumn ++;
            }
            $startRow++;
        }
        $titleRow = 1;
        $titleColumn = 0;
        $pDataType = DataType::TYPE_STRING2;
        // 设置标题
        foreach ($param as $key => $title) {
            $activeSheet->setCellValueExplicit($rows[$titleColumn] . $titleRow, $title, $pDataType );
            if ($key == 'address') {
                $activeSheet->getColumnDimension($rows[$titleColumn])->setWidth(70);
            } else {
                $activeSheet->getColumnDimension($rows[$titleColumn])->setWidth(30);
            }
            $titleColumn ++;
        }
        unset($datas);
        $fileName = !empty($fileName) ? $fileName : (date('YmdHis') . '.xls');
        if (empty($options)) {
            header("Content-Type: application/force-download");
            header("Content-Type: application/octet-stream");
            header("Content-Type: application/download");
            header('Content-Disposition:inline;filename="'.$fileName.'"');
            header("Content-Transfer-Encoding: binary");
            header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
            header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
            header("Pragma: no-cache");
            $savePath = 'php://output';
        } else {
            $savePath = $options . $fileName;
        }
        ob_clean();
        ob_start();
        $objWriter = IOFactory::createWriter($objSpreadsheet, 'Xls');
        $objWriter->save($savePath);
        //释放内存
        $objSpreadsheet->disconnectWorksheets();
        unset($objSpreadsheet);
        ob_end_flush();
        return $savePath;
    } catch (Exception $e) {
        returnError($e->getMessage());
    }
}

导入数据 公共处理

/**
 * 导入Excel表取出需要的内容
 * @param $excelPath    excel表路径
 * @param $param    每列对应数据键名及标题 ['A' => ['key' => 'A',title => '标题名称']] 标题名为空则不验证
 * @param $startRow 内容开始的行
 * @return array    返回数据内容 [['A' => 'content']];
 * @throws \PhpOffice\PhpSpreadsheet\Calculation\Exception
 * @throws \PhpOffice\PhpSpreadsheet\Exception
 * @throws \think\Exception
 */
function importExcel($excelPath, $param, $startRow) {
    $excelObj = IOFactory::load($excelPath);
    if (!$excelObj) {
        returnError('加载Excel表失败，请检查Excel内容');
    }
    $excelWorkSheet = $excelObj->getActiveSheet();
    $rowCount = $excelWorkSheet->getHighestRow();
    if ($rowCount <= 0) {
        returnError('Excel表内容为空。');
    }
    //验证标题
    foreach ($param as $column => $content) {
        $item = $excelWorkSheet->getCell($column . ($startRow - 1))->getCalculatedValue();
        if ($item != $content['title'] && !empty($content['title'])) {
            returnError('请检查模板标题是否正确。');
        }
    }
    $excelData = array();
    for ($row = $startRow; $row <= $rowCount; $row++ ) {
        $rowData = array();
        foreach ($param as $column => $content) {
            $item = $excelWorkSheet->getCell($column . $row)->getCalculatedValue();
            $rowData[$content['key']] = $item;
        }
        if(!implode('',$rowData)){
            continue;//删除空行
        }
        $excelData[] = $rowData;
    }
    return $excelData;
}

进行导入数据处理

public function importData(){
    $name = $_FILES['file']["name"];//上传文件的文件名
    $size = $_FILES['file']["size"];//上传文件的大小
    $tmp_name = $_FILES['file']["tmp_name"];//上传文件的临时存放路径
    if ($size <= 0){
        returnError('文件内容读取失败');
    }

    $exts = strtolower(strstr($name,'.'));
    if ($exts != ".xls" && $exts != ".xlsx") {
        returnError('文件格式错误，请上传EXCEL文件');
    }

    $filename = time().$exts;
    $path = dirname(__FILE__) . '/../../../public/uploads/' . date('ymd');
    if (!is_dir($path)) {
        mkdir( $path, 0777, true);
    }
    $excelPath = $path . '/' . $filename;
    move_uploaded_file($tmp_name, $excelPath);

    $startRow = 2;
    $param = [
        'A' => ['key' => '数据库字段名', 'title' => '导入的excel字段名'],
        'B' => ['key' => '数据库字段名', 'title' => '导入的excel字段名'],
        'C' => ['key' => '数据库字段名', 'title' => '导入的excel字段名'],
    ];
    
    $excelData = $this->importExcel($excelPath, $param, $startRow);
    if(count($excelData)==0){
        returnError('获取数据失败');
    }
    $file_path = 'uploads/' . date('ymd') . '/' . $filename;
    
    unlink($excelPath);	
	
	//接下来进行 数据库读取数据的操作
}