php composer phpexcel

#用于excel表格
function getColKeys($index)
{
    $index = (int)$index;
    if ($index <= 0) return; //输入检测
    $dimension = ceil(log(25 * $index + 26, 26)) - 1;  //算结果一共有几位，实际算的是位数减1，记住是26进制的位数
    $n = $index - 26 * (pow(26, $dimension - 1) - 1) / 25; //算结果在所在位数总数中排第几个
    $n--; //转化为索引

    return str_pad(
        str_replace(
            array_merge(range(0, 9), range('a', 'p')),
            range('A', 'Z'),
            base_convert($n, 10, 26)
        ),
        $dimension,
        'A',
        STR_PAD_LEFT
    ); //翻译加补齐
}

composer require phpoffice/phpexcel 1.8.2  导入插件

 

require_once($_SERVER['DOCUMENT_ROOT'] . '/../vendor/phpoffice/phpexcel/Classes/PHPExcel.php');
require_once($_SERVER['DOCUMENT_ROOT'] . '/../vendor/phpoffice/phpexcel/Classes/PHPExcel/IOFactory.php');
$objPHPExcel = new \PHPExcel();

// 实例化完了之后就先把数据库里面的数据查出来
$PHPExcel = $objPHPExcel->setActiveSheetIndex(0);

$paramsColumn = [
    "account"=>'卡号',
    "card_type"=>'类型',
    "user_name"=>'会员姓名',
    "phone"=>'会员手机号',
    "create_time"=>'交易时间',
    "sollerName"=>'销售人员',
    "buy_hour"=>'购买课时',
    "actual_payment"=>'实际付款',
    "TopUpTxt"=>'交易类型',
    "PayMentTermTxt"=>'支付方式',
    "CardTypeTxt"=>'卡种'
];
$getColumn = array_keys($paramsColumn);
$columnNum = 1;
foreach ($paramsColumn as $k=>$v) {
    $col = getColKeys($columnNum);
    $PHPExcel = $PHPExcel->setCellValue($col . '1', $v);
    $columnNum += 1;
}

$line = 1;
foreach ($data as $k => $v) {
    $getColumnKey = 1;
    foreach ($getColumn as $val) {
        $col = getColKeys($getColumnKey);
        $objPHPExcel->getActiveSheet()->setCellValue($col . (string)($line + 1), $v[$val]);
        $getColumnKey += 1;
    }
    $line++;
}



$objPHPExcel->getActiveSheet()->setTitle('productaccess');      //设置sheet的名称
$objPHPExcel->setActiveSheetIndex(0);                   //设置sheet的起始位置
$PHPWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");
ob_start();
$PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件
$buf1 = ob_get_contents();
ob_end_clean();
return base64_encode($buf1);

 

 

                        let str = "base65字符串"
                        var blob = dataURLtoBlob(str);
                        var downloadUrl = window.URL.createObjectURL(blob);
                        var anchor = document.createElement("a");
                        anchor.href = downloadUrl;
                        anchor.download = decodeURI('交易记录.xlsx');
                        anchor.click();
   
                    function dataURLtoBlob(base64Str) {
                        var bstr = atob(base64Str), n = bstr.length, u8arr = new Uint8Array(n);
                        while (n--) {
                            u8arr[n] = bstr.charCodeAt(n);
                        }
                        return new Blob([u8arr], { type: "application/vnd.ms-excel" });
                    }