tp6使用phpexcel导出和保存到服务器

安装

composer require phpoffice/phpexcel

使用导出

<?php
declare (strict_types = 1);

namespace app\admin\controller;

use think\Request;
use PHPExcel_IOFactory;

/**
 * 报表
 * */
class ReportFormExcel
{
    /**
     *订单
     * */
    public function orderExcel(){
        $objPHPExcel = new \PHPExcel();
        $objPHPExcel->getProperties()->setCreator("ctos")
            ->setLastModifiedBy("ctos")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Test result file");
        //设置各列宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(16);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(19);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(18);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(55);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(43);
        //设置列名
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '订单编号')
            ->setCellValue('B1', '收件人')
            ->setCellValue('C1', '时间')
            ->setCellValue('D1', '手机')
            ->setCellValue('E1', '地址')
            ->setCellValue('F1', '发货信息');
        //给数据
        for ($i=0;$i<30;$i++){
            $num = $i + 2;
            $objPHPExcel->setActiveSheetIndex(0)//Excel的第A列，uid是你查出数组的键值，下面以此类推
            ->setCellValue('A'.$num, "5468565549844565454\t")//数据太长\t不乱码
            ->setCellValue('B'.$num, 2)
            ->setCellValue('C'.$num, "2021-10-12")
            ->setCellValue('D'.$num, 3)
            ->setCellValue('E'.$num, 5)
            ->setCellValue('F'.$num, 6);
        }
        //导出文件名
        $filename = date('Y-m-d',time()).'报表.xlsx';
        ob_end_clean();
        header('Content-Type: applicationnd.ms-excel');
        header('Content-Disposition: attachment;filename='.$filename);
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  //excel5为xls格式，excel2007为xlsx格式
        $objWriter->save('php://output');
    }

}

保存到服务器，把导出最后一点改为下面就可以

//导出文件名
$filename = date('Y-m-d',time()).'房产车位储藏室商铺.xlsx';
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  //excel5为xls格式，excel2007为xlsx格式
//获得tp6路径
$Absolute_Path=$_SERVER['SCRIPT_FILENAME'];
$user_path = substr($Absolute_Path,0,-9).'/storage/xsc/';//保存路径
$urls = $objWriter->save($user_path.$filename);//保存excle文件
return json([$user_path,$urls]);

//获得tp6路径
        $date = date("Ymd");
        $Absolute_Path=$_SERVER['SCRIPT_FILENAME'];
        $user_path = substr($Absolute_Path,0,-9)."/storage/xsc/{$date}/";//保存路径
        $user_paths = substr($Absolute_Path,0,-9)."/storage/xsc/{$date}";
        if(!is_dir($user_paths)){
            mkdir($user_paths,0777,true);
        }
        $urls = $objWriter->save($user_path.$filename);//保存excle文件

 

居中

//所有单元格居中
$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

//单个单元格居中
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

设置表格行高

//设置行高
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20);

设置背景颜色

//设置填充的样式和背景色
$objPHPExcel->getActiveSheet()->getStyle('A1:I1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FF999999');

合并单元格

//合并单元格
$objPHPExcel->getActiveSheet()->mergeCells('A1:E1');

固定表头

//固定表头
 $objPHPExcel->getActiveSheet()->freezePane('A2');