PHPExcel 生成excel表格(附带图表)

最新推荐文章于 2024-04-27 14:59:16 发布

小宝二号

最新推荐文章于 2024-04-27 14:59:16 发布

阅读量421

点赞数

分类专栏： PHP 文章标签： php

本文链接：https://blog.csdn.net/Black_phoenix/article/details/127313177

版权

PHP 专栏收录该内容

5 篇文章 1 订阅

订阅专栏

`PHPExcel` 生成excel表格

文章目录

___`PHPExcel`___ 生成excel表格

excel文件内表格的生成

<?php
    // 引入配置文件
    require_once('./../inc/init.inc.php');
    include('./../lib/PHPExcel/PHPExcel.php');
    include('./../lib/PHPExcel/PHPExcel/Writer/Excel2007.php');
	// 命名要生成的excel文件
	$filename = 'XXXXXXXXXX.xlsx'
    
###########    // 初始化设置      start  ######################################
    $objPHPExcel = new PHPExcel();
    $objPHPExcel->setActiveSheetIndex(0);
    $objExcel = $objPHPExcel->getActiveSheet();
	// 设置字体
    $objPHPExcel->getDefaultStyle()->getFont()->setName('Calibri');
	// 设置水平居中
    $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	// 设置垂直居中
    $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

############    // 初始化设置       end     ########################################

############    设置内容填充    start   ##################################
#  表格头
// 定义第一列的列宽
$objExcel->getColumnDimension('A')->setWidth(18);
// 合并单元格
$objExcel->mergeCells('B2:L2');
// 设置单元格内容
$objExcel->setCellValue('B2', 'TOTAL');
// 字体加粗
$objExcel->getStyle('B2')->getFont()->setBold(true);
// 字体大小
$objExcel->getStyle('B2')->getFont()->setSize(12);
// 设置行高
$objExcel->getRowDimension('1')->setRowHeight(26); 
// 设置颜色填充
$objPHPExcel->getActiveSheet()->getStyle('A2:V2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A2:V2')->getFill()->getStartColor()->setARGB('FFCCCCCC');
// 设置文本旋转
$objExcel->getStyle('A'.$secondLine)->getAlignment()->setTextRotation(90);
// 设置自动换行
$objExcel->getStyle('A'.$secondLine)->getAlignment()->setWrapText(true);
//  边框线加粗
$styleThinBlackBorderInnerline = array(
    'borders' => array(
        'allborders' => array( 
            'style' => \PHPExcel_Style_Border::BORDER_THIN 
        ),

    ),
);
$styleThinBlackBorderOutline = array(
    'borders' => array(
        'outline' => array( 
            'style' => \PHPExcel_Style_Border::BORDER_THICK 
        ),

    ),
);
$objPHPExcel->getActiveSheet()->getStyle( 'A2:V'.($startLine+$num-1))->applyFromArray($styleThinBlackBorderInnerline);
$objPHPExcel->getActiveSheet()->getStyle( 'A2:V'.($startLine+$num-1))->applyFromArray($styleThinBlackBorderOutline);
// 设置工作表标题
$objPHPExcel->getActiveSheet()->setTitle('RESUME');

############    设置内容填充     end    ###################################

注意:getStyle的值可以是单个单元格也可以是从其实单元格到终止单元格(这样子的表示方法意味着这中间的全部单元格的样式统一)

excel文件内图表的生成(我的是柱状图)

<?php
    /* 
     * $num 多少列
     *
     */
     // X 轴刻度
    $xLabels = array(
        new \PHPExcel_Chart_DataSeriesValues('String','CLIENT!$A$4:$A$'.($startLine+$num-1),null,$num),
    );
    $data = array();
    for($i=0;$i<$last;$i++) {
        // Y轴刻度
        $labels[$i] = new \PHPExcel_Chart_DataSeriesValues('String','RESUME!$B$'.($line+$i),null,1); // tab
		// 获取数据
        $data[$i] = new \PHPExcel_Chart_DataSeriesValues('Number', 'RESUME!$C$'.($line+$i).':$N$'.($line + $i), null, 12);

    }
	// 构建图表框架
    $series =  new \PHPExcel_Chart_DataSeries(
        PHPExcel_Chart_DataSeries::TYPE_BARCHART,
        PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED,
        range(0, count($labels)-1),
        $labels,
        $xLabels,
        $data
    );
	// 设置绘图方向
    $series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_VERTICAL);// 垂直
	// 在框架中设置绘图区域
    $areas = new \PHPExcel_Chart_PlotArea(NULL,array($series));
	// 设置图表图例走向
    $legend = new \PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT,NULL,false);// 向右延申
	// 生成图表
    $chart = new \PHPExcel_Chart(
        $name,
        null,
        $legend,
        $areas,
        true,
        false,
        null,
        null
    );
	// 图表定位
    $chart->setTopLeftPosition('A'.($fourth+$last+2));
    $chart->setBottomRightPosition('O'.($fourth+$last+22));
    $chart->setBottomRightCell('P'.($fourth+$last+22));
	// 将图表插入excel表格中
    $objExcel->addChart($chart);

输出图表

<?php
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->setIncludeCharts(true);
    $objWriter->setOffice2003Compatibility(true);
    header("Content-disposition: attachment; filename=".$filename."");
    header("Content-Transfer-Encoding: application/vnd.ms-excel\n");
    // header("Content-Transfer-Encoding: binary");
    header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    header("Content-Type: application/force-download");
    // header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");
    header("Pragma: no-cache");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");

    // $objWriter->save($filename);
    $objWriter->save('php://output');