PHPEXCEL批量导出

最新推荐文章于 2021-03-18 19:50:48 发布

原创最新推荐文章于 2021-03-18 19:50:48 发布 · 390 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍如何利用PHPExcel库在PHP中生成包含商品销售明细的Excel文件，包括设置表格样式、填充数据及导出文件的具体步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

// 引入phpexcel核心类文件
    require_once ROOT_PATH . '/includes/phpexcel/Classes/PHPExcel.php';
    // 实例化excel类
    $objPHPExcel = new PHPExcel();
    // 操作第一个工作表
    $objPHPExcel->setActiveSheetIndex(0);
    // 设置sheet名
    $objPHPExcel->getActiveSheet()->setTitle('商品销售明细');
    // 设置表格宽度
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(60);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
    // 列名表头文字加粗
    $objPHPExcel->getActiveSheet()->getStyle('A1:E1')->getFont()->setBold(true);
    // 列表头文字居中
    $objPHPExcel->getActiveSheet()->getStyle('A1:E1')->getAlignment()
        ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    // 列名赋值
    $objPHPExcel->getActiveSheet()->setCellValue('A1', '商品名称');
    $objPHPExcel->getActiveSheet()->setCellValue('B1', '订单号');
    $objPHPExcel->getActiveSheet()->setCellValue('C1', '销售数量');
    $objPHPExcel->getActiveSheet()->setCellValue('D1', '销售价格');
    $objPHPExcel->getActiveSheet()->setCellValue('E1', '下单日期');

    // 数据起始行
    $row_num = 2;
    // 向每行单元格插入数据
    foreach($result as $value)
    {
        // 设置所有垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('A' . $row_num . ':' . 'E' . $row_num)->getAlignment()
            ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        // 设置价格为数字格式
        $objPHPExcel->getActiveSheet()->getStyle('D' . $row_num)->getNumberFormat()
            ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);

        // 设置单元格数值
        $objPHPExcel->getActiveSheet()->setCellValue('A' . $row_num, $value['goods_name']);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('B' . $row_num, $value['order_sn'], PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValue('C' . $row_num, $value['sells_count']);
        $objPHPExcel->getActiveSheet()->setCellValue('D' . $row_num, $value['goods_price']);
        $objPHPExcel->getActiveSheet()->setCellValue('E' . $row_num, $value['add_time']);
        $row_num++;
    }
    $outputFileName = '商品销售明细_' . time() . '.xls';
    $xlsWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");
    header('Content-Disposition:inline;filename="' . $outputFileName . '"');
    header("Content-Transfer-Encoding: binary");
    header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
    header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Pragma: no-cache");
    $xlsWriter->save("php://output");
    echo file_get_contents($outputFileName);

/***param $result 查询的数据

***/