php使用PhpSpreadsheet，Excel导出公共方法

一、安装PhpSpreadsheet

composer require phpoffice/phpspreadsheet

二、公共方法（支持单元格合并、单元格特殊样式设置）

1、引入

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

2、方法

/**
 * @param string $title 导出名称
 * @param array $head 标题[['标题'],['第二行第一个','第二行第二个']]
 * @param array $list 数据
 * @param array $list_field 那一列用那个字段,示例['id','name','age']
 * @param array $merge 合并,示例['A1:C1','A2:F2']
 * @param array $styles 样式,示例['A1'=>[...],'B2:F2'=>[...]]
 * @param array $decimals 保留两位小数的字段,与$list_field类似：['age']
 * @param boolean $isTotal 是否合计
 * @param array $text_field 那些字段需要转为文本,示例['id','name','age']
 */
function excelExport($title = "", $head = [], $list = [], $list_field = [], $merge = [], $styles = [], $decimals = [], $isTotal = true, $text_field = [])
{
    try {
        // 实例化Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        $sheetMain = $spreadsheet->getsheet(0); // 设置主sheet
        $sheetMain->setTitle($title); // 设置sheet的名称
        $sheetMain->getPageSetup()->setHorizontalCentered(true);
        $sheetMain->getPageSetup()->setVerticalCentered(false);
        //合并
        foreach ($merge as $key => $val) {
            $sheetMain->mergeCells($val);
        }
        //设置头部标题
        $rowNum = 1;//行数
        $maxRank = 0;//最大列
        $widths = [];
        foreach ($head as $key => $val) {//第几行
            foreach ($val as $k => $v) {//第几列
                if ($maxRank < $k) {
                    $maxRank = $k;
                }

                $y = Coordinate::stringFromColumnIndex(bcadd($k, 1, 0));//根据列计算 列名  A B C D
                $sheetMain->setCellValue($y . $rowNum, $v);
                //设置标题样式
                $styleArray = [                        'font' => ['bold' => true, 'size' => 18],
                    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
                ];
                $sheetMain->getStyle($y . $rowNum)->applyFromArray($styleArray);
                //获取每列最大宽度,当有两行标题，第一行标题不计算宽度
                if (count($head) >= 2 && $k == 0 && count($list) > 0) {
                    continue;
                }

                if (isset($widths[$k]) && $widths[$k] < (strlen(iconv('utf-8', 'GB2312//IGNORE', $v)) + 6)) {
                    $widths[$k] = strlen(iconv('utf-8', 'GB2312//IGNORE', $v)) + 6;
                } else if (!isset($widths[$k])) {
                    $widths[$k] = strlen(iconv('utf-8', 'GB2312//IGNORE', $v)) + 6;
                }
            }
            $rowNum++;
        }
        $decimals_key = [];//合计对应的列
        $options = "//TRANSLIT"; //字段值为：增值税 – 附加税 导出报错 iconv(): Detected an illegal character in input string，解决办法
        //设置数据
        foreach ($list as $key => $val) {
            if (count($list_field) > 0) {
                foreach ($list_field as $k => $v) {
                    if (isset($val[$v])) {
                        if ($maxRank < $k) {
                            $maxRank = $k;
                        }
                        //获取每列最大宽度
                        if (isset($widths[$k]) && $widths[$k] < (strlen(iconv('utf-8', 'GB2312//IGNORE', $val[$v])) + 8)) {
                            $widths[$k] = strlen(iconv('utf-8', 'GB2312//IGNORE', $val[$v])) + 8;
                        } else if (!isset($widths[$k])) {
                            $widths[$k] = strlen(iconv('utf-8', 'GB2312//IGNORE', $val[$v])) + 8;
                        }
                        $y = Coordinate::stringFromColumnIndex(bcadd($k, 1, 0));//根据列计算 列名  A B C D
                        //设置文本
                        if (in_array($v, $text_field)) {
                            $sheetMain->getStyle($y . $rowNum)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_TEXT);
                            $sheetMain->setCellValueExplicit($y . $rowNum, $val[$v], DataType::TYPE_STRING);
                        } else {
                            $sheetMain->setCellValue($y . $rowNum, $val[$v]);
                        }

                        //设置两位小数
                        if (in_array($v, $decimals)) {
                            $sheetMain->getStyle($y . $rowNum)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
                            $decimals_key[$v] = $k;
                        }

                    }
                }
            } else {
                if ($maxRank < $key) {
                    $maxRank = $key;
                }
                //获取每列最大宽度
                if (isset($widths[$key]) && $widths[$key] < (strlen(iconv('utf-8', 'GB2312//IGNORE', $val)) + 8)) {
                    $widths[$key] = strlen(iconv('utf-8', 'GB2312//IGNORE', $val)) + 8;
                } else if (!isset($widths[$key])) {
                    $widths[$key] = strlen(iconv('utf-8', 'GB2312//IGNORE', $val)) + 8;
                }
                //当一维数组处理
                $y = Coordinate::stringFromColumnIndex(bcadd($key, 1, 0));//根据列计算 列名  A B C D
                $sheetMain->setCellValue($y . $rowNum, $val);
            }
            $rowNum++;
        }

        if (count($decimals) <= 0 || !$isTotal) {
            $rowNum -= 1;
        } else {
            //设置合计列
            $is_one = false;//A列是否有合计数据
            foreach ($decimals_key as $key => $val) {
                if ($val == 0) {
                    $is_one = true;
                }
                $y = Coordinate::stringFromColumnIndex(bcadd($val, 1, 0));//根据列计算 列名  A B C D
                $sheetMain->setCellValue($y . $rowNum, '=sum(' . $y . (count($head) + 1) . ':' . $y . ($rowNum - 1) . ')');
                $sheetMain->getStyle($y . $rowNum)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
            }
            if (!$is_one && count($decimals_key) > 0) {
                $sheetMain->setCellValue('A' . $rowNum, 'TOTAL：');
                $sheetMain->getStyle('A' . $rowNum)->applyFromArray(['alignment' => ['horizontal' => Alignment::HORIZONTAL_RIGHT]]);
            }
        }
        //设置公共样式
        $maxRankStr = Coordinate::stringFromColumnIndex(bcadd($maxRank, 1, 0));
        $sheetMain->getStyle('A1:' . $maxRankStr . $rowNum)->getAlignment()->setWrapText(true)->setVertical(Alignment::VERTICAL_CENTER);//自动换行+垂直居中
        $sheetMain->getStyle('A2:' . $maxRankStr . $rowNum)->getFont()->setName('SimSun')->setSize(11);
        //设置边框
        $borderStyle = array(
            'borders' => array(
                'allBorders' => [                        'borderStyle' => Border::BORDER_THIN
                ]
            ),
        );
        $sheetMain->getStyle('A1:' . $maxRankStr . $rowNum)->applyFromArray($borderStyle);
        // 设置要自动调整宽度的行
        foreach ($widths as $key => $val) {
            $y = Coordinate::stringFromColumnIndex(bcadd($key, 1, 0));//根据列计算 列名  A B C D
            $sheetMain->getColumnDimension($y)->setWidth($val);
        }
        //设置行高
        for ($i = 1; $i <= $rowNum; $i++) {
            //只有一行标题
            if (count($head) == 1 && $i == 1) {
                $sheetMain->getRowDimension($i)->setRowHeight(30);
            } else if (count($head) >= 2) {
                if ($i == 1) {
                    $sheetMain->getRowDimension($i)->setRowHeight(30);
                } else if ($i <= count($head)) {
                    $sheetMain->getRowDimension($i)->setRowHeight(25);
                } else {
                    $sheetMain->getRowDimension($i)->setRowHeight(20);
                }
            } else {
                $sheetMain->getRowDimension($i)->setRowHeight(20);
            }
        }
        //设置样式
        foreach ($styles as $key => $val) {
            $sheetMain->getStyle($key)->applyFromArray($val);
        }

        //定义文件名称，需要带有定义的后缀名
        $filename = $title . '.xlsx';
        ob_end_clean(); //清除缓冲区,避免乱码
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        // 创建临时文件并将数据写入其中
        $tempFile = tempnam(sys_get_temp_dir(), 'excel_');
        $writer->save($tempFile);

        // 构建响应对象
        $response = Response::create(file_get_contents($tempFile), 'html', 200)
            ->contentType('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
            ->header([
                'Content-Disposition' => 'attachment; filename="' . urlencode($filename) . '"',
                'Cache-Control' => 'max-age=0;filename="' . urlencode($filename)  . '"'
            ]);

        // 删除临时文件
        unlink($tempFile);

        return $response;
    } catch (\Exception $e) {
        throw new Exception($e->getMessage());
    }
}

3、调用案例

public function download()
{
    $title = '支付宝转账'.date('Y年m月d日H时i分');
    $data = [];//自己组装数据，可以有其它字段，使用字段根据下面配置字段来
    return excelExport(
            $title,
            [[$title],['订单ID','收款方支付宝账号','收款方姓名','金额','备注']],
            $data,
            ['id','member_alipay_account','member_name','principal','mer_refund_msg'],
            ['A1:E1'],
            [],
            ['principal'],
            false,
            ['member_alipay_account','member_name','mer_refund_msg']
        );
}

4、前端request.js

import axios from 'axios'
import { useSettingsStoreHook } from '@/store/modules/settings'
import { useUserStoreHook } from '@/store/modules/user'
import { useAppStoreHook } from '@/store/modules/app'

// 创建axios实例
const service = axios.create({
  baseURL: import.meta.env.VITE_APP_BASE_URL, // 接口baseURL
  timeout: 60000, // 请求超时时间
  headers: {},
  params: {},
  data: {}
})

// 请求拦截器
service.interceptors.request.use(
  // 请求配置
  (config) => {
    const appStore = useAppStoreHook()
    const userStore = useUserStoreHook()
    const settingsStore = useSettingsStoreHook()
    const language = appStore.language
    const tokenValue = userStore.token
    const tokenType = settingsStore.tokenType
    // 设置Token
    if (tokenValue) {
      const tokenName = settingsStore.tokenName
      if (tokenType === 'header') {
        // 请求头部token
        config.headers[tokenName] = tokenValue
      } else {
        // 请求参数token
        if (config.method === 'get') {
          config.params = { ...config?.params, [tokenName]: tokenValue }
        } else {
          config.data = { ...config?.data, [tokenName]: tokenValue }
        }
      }
    }
    // 设置语言
    if (tokenType === 'header') {
      config.headers['think-lang'] = language
    } else {
      config.params.lang = language
    }
    return config
  },
  // 请求错误
  (error) => {
    if (import.meta.env.DEV) {
      console.log(error)
    }
    return Promise.reject(error)
  }
)

// 响应拦截器
service.interceptors.response.use(
  /**
   * 通过接口返回码确定返回状态
   * 还可以通过HTTP状态代码来判断请求状态
   */
  (response) => {
    // 响应数据
    const res = response.data
    // 检查响应的内容类型
    const contentType = response.headers['content-type'];
    if (contentType.includes('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')) {
      // 文件下载
      const blob = new Blob([response.data], { type: contentType });
      const downloadUrl = window.URL.createObjectURL(blob);
      const a = document.createElement('a');
      a.href = downloadUrl;
      // 从响应头中获取文件名
      // const contentDisposition = response.headers['Content-Disposition'];
      const contentDisposition = response.headers['cache-control'];
      let filename = 'download.xlsx';
      if (contentDisposition) {
        const matches = /filename="([^"]*)"/.exec(contentDisposition);
        if (matches !== null && matches[1]) {
          filename = decodeURIComponent(matches[1]);
        }
      }
      a.download = filename;
      document.body.appendChild(a);
      a.click();
      document.body.removeChild(a);
      window.URL.revokeObjectURL(downloadUrl);
      return response.data;
    }else if (response.data && response.config.responseType === 'blob') {
      // 文件下载
      if (response.data.type === 'application/json') {
        const reader = new FileReader()
        reader.readAsText(response.data, 'utf-8')
        reader.onload = () => {
          const result = JSON.parse(reader.result)
          responseHandle(result)
          return Promise.reject(new Error(result.msg || 'Server error'))
        }
        return Promise.reject()
      } else {
        return response.data
      }
    } else {
      // 返回码200：成功
      if (res.code === 200) {
        return res
      } else {
        responseHandle(res)
        return Promise.reject(new Error(res.msg || 'Server error'))
      }
    }
  },
  (error) => {
    // 响应错误
    const res = error.response.data
    responseHandle(res)
    if (import.meta.env.DEV) {
      console.log(error.response)
    }
    return Promise.reject(error)
  }
)

// 响应处理
function responseHandle(res) {
  // 返回码 401：Token 无效
  if (res.code === 401) {
    ElMessageBox.confirm(res.msg, '提示', {
      confirmButtonText: '重新登录',
      cancelButtonText: '取消',
      type: 'warning'
    })
      .then(() => {
        const userStore = useUserStoreHook()
        userStore.resetToken().then(() => {
          location.reload()
        })
      })
      .catch(() => {})
  } else {
    ElMessage({
      showClose: true,
      message: res.msg || 'Server error',
      type: 'error',
      duration: 5000
    })
  }
}

export default service

5、简单效果图