[BD2.0] Excel导出, 待完善

主要由BD模块市场的导出改写

web\src\api\controllerUrls.ts

import { useAdminInfo } from "../stores/adminInfo"
import { getUrl } from '/@/utils/axios'
const adminInfo = useAdminInfo()
const url = getUrl()
const token = adminInfo.getToken()

export const exportGoods = url + '/admin/goods/exportGoods?server=1'+ '&batoken=' + token // 导出

前端页面

<TableHeader
    :buttons="['refresh', 'add', 'edit', 'delete', 'comSearch', 'quickSearch', 'columnDisplay']"
    :quick-search-placeholder="t('Quick search placeholder', { fields: t('goods.quick Search Fields') })"
>
    <el-button style="margin-left:10px" class="table-header-operate" type="success" @click="exportExcel">
        <span class="table-header-operate-text">导出</span>
    </el-button>
</TableHeader>


<script setup lang="ts">
import { goodsCategory, exportGoods } from '/@/api/controllerUrls'

// 导出列表数据
const exportExcel = () => {
    window.location.href = exportGoods
}
</script>            

控制器代码(重点, 但是有个问题,ide提示getCellByColumnAndRow is deprecated)

use ba\Filesystem;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\facade\Db;
use Throwable;
use app\common\controller\Backend;
use app\admin\model\Member;

public function exportGoods()
{
    $headers = ['name'=>"姓名", 'age'=>"年龄", 'city'=>"城市"];
    $data = [['name'=>"Alice", 'age'=>"23", 'city'=>"shanghai"], ['name'=>"BAlice", 'age'=>"63", 'city'=>"shanghai"] ];
    $this->export($headers, $data, '测试.xlsx');
}

/**
 * 导出关联数组数据为Excel
 *
 * @param array  $headers  关联数组作为表头
 * @param array  $data     关联数组的二维数组作为表格内容
 * @param string $filename 导出的文件名
 */
public function export(array $headers, array $data, string $filename)
{
    $spreadsheet = new Spreadsheet();
    $worksheet = $spreadsheet->getActiveSheet();

    // 设置表头
    $headerRow = 1;
    $columnIndex = 1;
    foreach ($headers as $headerKey => $headerLabel) {
        $cell = $worksheet->getCellByColumnAndRow($columnIndex, $headerRow);
        $cell->setValue($headerLabel);
        $columnIndex++;
    }

    // 设置表格内容
    $dataRow = $headerRow + 1;
    foreach ($data as $rowData) {
        $columnIndex = 1;
        foreach ($headers as $headerKey => $headerLabel) {
            $cell = $worksheet->getCellByColumnAndRow($columnIndex, $dataRow);
            $cell->setValue($rowData[$headerKey] ?? ''); // Use the value if it exists, otherwise use an empty string
            $columnIndex++;
        }
        $dataRow++;
    }

    // 直接下载
    ob_end_clean();
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
    header("Content-Type: application/force-download");
    header("Content-Type: application/vnd.ms-execl");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");

    $encodedFilename = urlencode($filename);
    $ua = $_SERVER["HTTP_USER_AGENT"];
    if (str_contains($ua, "MSIE")) {
        header('Content-Disposition: attachment; filename="' . $encodedFilename . '"');
    } else if (str_contains($ua, "Firefox")) {
        header('Content-Disposition: attachment; filename*="utf8''' . $filename . '"');
    } else {
        header('Content-Disposition: attachment; filename="' . $filename . '"');
    }
    header("Content-Transfer-Encoding: binary");
    header('Cache-Control: max-age=0');

    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
    $writer->save('php://output');
    $spreadsheet->disconnectWorksheets();
    unset($spreadsheet);
}