report/app/common.php

<?php

// 应用公共文件

/**
 * 
 * @param type $algo
 * @param type $password
 * @param type $salt
 * @param type $hash_iterations
 * @return type
 */
function simple_hash($algo = 'md5', $password = '', $salt = '', $hash_iterations = 2) {
    $res = '';
    $pass = $salt . $password;
    $encoded = hash($algo, $pass, true);
    $iteration = $hash_iterations - 1;
    if ($iteration > 0) {
        for ($i = 0; $i < $iteration; $i++) {
            $encoded = hash($algo, $encoded, true);
        }
    }
    $tmp = unpack('H*', $encoded);
    if (!empty($tmp) && !empty($tmp[1])) {
        $res = $tmp[1];
    }
    return $res;
}

/**
 * 检查权限
 * @param type $url
 * @param type $old_url
 * @return type
 */
function chkCommission($url, $old_url) {
    return app\common\api\MenuApi::chkPermission($url, $old_url);
}

/**
 * 随机字符ID
 * @return type
 */
function getStringId() {
    $day = random_int(10, 30);
    $time = strtotime("-4 years -6 months -" . $day . " days");
    $randnum = random_int(100000000, 999999999);
    $randnum = str_shuffle($randnum);
    return $time . $randnum;
}

function isNullOrEmpty($obj) {
    if (!$obj || $obj == "" || !isset($obj))
        return "";
    return $obj;
}

function getTreeList($array, $id_field = "id", $pid_field = "pid", $value = "0") {
    static $result = [];
    foreach ($array as $key => $item) {
        if ($value == $item[$pid_field]) {
            $result[] = $item;
            unset($array[$key]);
            getTreeList($array, $id_field, $pid_field, $item[$id_field]);
        }
    }
    return $result;
}

/**
 * 读取excel
 * @param type $filepath
 * @param type $sheetIndex
 * @return type
 */
function getExcelDatas($filepath, $sheetIndex = 0) {
    $reader_type = \PHPExcel_IOFactory::identify($filepath);
    $reader = \PHPExcel_IOFactory::createReader($reader_type);
    $phpexcel = $reader->load($filepath);
    $sheet = $phpexcel->getSheet($sheetIndex);
    return $sheet->toArray();
}

/**
 * 导出excel
 * @param type $columns 列标题
 * @param type $rows 内容
 * @param string $filename 文件名
 * @param string $settings 样式批设置
 * @param type $sheetname sheet标题
 * @param type $saveurl 保存位置
 * @param type $author 作者
 */
function export($columns, $rows, $filename = "jjrcw", $settings = [], $sheetname = "sheet1", $saveurl = "php://output", $author = "晋江人才网") {
    $datatype = new \PhpOffice\PhpSpreadsheet\Cell\DataType;
    $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
    $spreadsheet->getProperties()->setCreator($author)
            ->setLastModifiedBy($author)
            ->setTitle($filename)
            ->setSubject($filename);
    $spreadsheet->setActiveSheetIndex(0);
    $objPHPExcel = $spreadsheet->getActiveSheet();
    $objPHPExcel->setTitle($sheetname);

    $filename .= "_" . time();
    $titleStartLine = 1;
    $rowStartLine = $titleStartLine + 1;
    $colCount = count($columns);
    $rowCount = count($rows);
    //设置表头
    for ($i = 0; $i < $colCount; $i++) {
        $objPHPExcel->setCellValue(getExcelColumnByIndex($i) . $titleStartLine, $columns[$i]);
    }
    //设置正文内容
    for ($i = 0; $i < $rowCount; $i++) {
        for ($n = 0; $n < $colCount; $n++) {
            if (is_numeric($rows[$i][$n]) && strlen($rows[$i][$n]) > 17) {
                //超过17位的数字转成文本方以免被转化为科学数
                $objPHPExcel->setCellValueExplicit(getExcelColumnByIndex($n) . ($rowStartLine + $i), $rows[$i][$n], $datatype::TYPE_STRING);
            } else {
                $objPHPExcel->setCellValue(getExcelColumnByIndex($n) . ($rowStartLine + $i), $rows[$i][$n]);
            }
        }
    }
    $objPHPExcel->getDefaultColumnDimension()->setWidth(16); //默认列宽
    $objPHPExcel->getDefaultRowDimension()->setRowHeight(30); //默认列高
    //样式设置
    $settings = $settings ?: getCommonExcelSetting($colCount, $rowCount);
    foreach ($settings as $type => $cfg) {
        switch ($type) {
            case "width":
                for ($i = 0; $i < count($cfg); $i++) {
                    $objPHPExcel->getColumnDimension($cfg[$i][0])->setWidth($cfg[$i][1]);
                }
                break;
            case "height":
                for ($i = 0; $i < count($cfg); $i++) {
                    $objPHPExcel->getRowDimension($cfg[$i][0])->setRowHeight($cfg[$i][1]);
                }
                break;
            case "background-color":
                for ($i = 0; $i < count($cfg); $i++) {
                    $objPHPExcel->getStyle($cfg[$i][0])->getFill()->setFillType(PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor($cfg[$i][1])->setARGB($cfg[$i][1]);
                }
                break;
            case "border":
                $objPHPExcel->getStyle($cfg)->applyFromArray(array(
                    "borders" => array(
                        "allBorders" => array(
                            'borderStyle' => PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
                        )
                    )
                ));
                break;
            case "align":
                for ($i = 0; $i < count($cfg); $i++) {
                    switch ($cfg[$i][1]) {
                        case "left":
                            $hAlign = PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT;
                            break;
                        case "right":
                            $hAlign = PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT;
                            break;
                        case "hCenter":
                            $hAlign = PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER;
                            break;
                        case "hJustify":
                            $hAlign = PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_JUSTIFY;
                            break;
                        case "top":
                            $vAlign = PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP;
                            break;
                        case "bottom":
                            $vAlign = PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_BOTTOM;
                            break;
                        case "vCenter":
                            $vAlign = PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER;
                            break;
                        case "vJustify":
                            $vAlign = PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_JUSTIFY;
                            break;
                    }
                    $align = [];
                    if ($hAlign) {
                        $align["horizontal"] = $hAlign;
                    }
                    if ($vAlign) {
                        $align["vertical"] = $vAlign;
                    }
                    $objPHPExcel->getStyle($cfg[$i][0])->applyFromArray([
                        "alignment" => $align
                    ]);
                }
                break;
            case "color":
                for ($i = 0; $i < count($cfg); $i++) {
                    $objPHPExcel->getStyle($cfg[$i][0])->getFont()->getColor()->setARGB($cfg[$i][1]);
                }
                break;
            case "wrap":
                for ($i = 0; $i < count($cfg); $i++) {
                    $objPHPExcel->getStyle($cfg[$i])->getAlignment()->setWrapText(true); //cellvalue包含\n设为自动换行
                }
                break;
        }
    }

    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="' . $filename . '"');
    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xls($spreadsheet);
    $writer->save($saveurl);
    //删除临时的sheet
    $spreadsheet->disconnectWorksheets();
    unset($spreadsheet);
    exit;
}

/**
 * 根据传入的数值（游标），从26个英文字母的数组中查询，返回excel列标
 * @param int $index 游标从0开始
 * @return string 返回列标
 */
function getExcelColumnByIndex(int $index) {
    $letters = 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');
    if ($letters[$index])
        return $letters[$index];
    $rowIndex = floor($index / 26) - 1;
    $colIndex = $index % 26;
    return $letters[$rowIndex] . $letters[$colIndex];
}

/**
 * 公共的excel设置
 * @return type
 */
function getCommonExcelSetting($columns, $rows) {
    return $settings = [
        "background-color" => [[sprintf("A1:%s1", getExcelColumnByIndex($columns - 1)), "0066CC"]],
        "color" => [[sprintf("A1:%s1", getExcelColumnByIndex($columns - 1)), "FFFFFF"]],
        "border" => sprintf("A1:%s%d", getExcelColumnByIndex($columns - 1), $rows + 1),
        "wrap" => [sprintf("A2:%s%d", getExcelColumnByIndex($columns - 1), $rows + 1)],
        "align" => [[sprintf("A1:%s%d", getExcelColumnByIndex($columns - 1), $rows + 1), "hCenter"], [sprintf("A1:%s%d", getExcelColumnByIndex($columns - 1), $rows + 1), "vCenter"]]
    ];
}

/**
 * 检查是不是excel格式，不确定是否都是可用文件，主要还是第一和最后一个比较常见
 * @param type $mime
 * @return type
 */
function isExcelFile($mime) {
    return in_array($mime, [
        "application/vnd.ms-excel",
        "application/msexcel",
        "application/x-msexcel",
        "application/x-ms-excel",
        "application/x-excel",
        "application/x-dos_ms_excel",
        "application/xls",
        "application/x-xls",
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    ]);
}

/**
 * 获得上传文件的路径
 * @param $path
 * @return string
 */
function getStoragePath($path) {
    if ($path) {
        return "/storage/{$path}";
    }
    return "";
}

function getFileView($path) {
    $complete_path = "https://report.jinjianghc.com/" . getStoragePath($path);
    return "https://fileview.jinjianghc.com/onlinePreview?url=" . base64_encode($complete_path);
}