PHPExcel——生成扣款报表（字体大小、颜色、居中、换行设置，单元格高宽、边框、背景、合并设置）

这个小功能包含：

1、设置字体加粗、字体大小、字体颜色；

2、设置单元格背景色、高度、宽度、边框、单元格的合并；

3、设置字体水平居中、垂直居中、自动换行

// 导出扣款报表
function exportDeduction($data, $datafield, $fileName, $title=''){
    // 引入 PHPExcel 文件
    include ROOT_PATH.'phpexcel/PHPExcel.php';
    include_once ROOT_PATH.'phpexcel/PHPExcel/Writer/Excel2007.php';
    include_once ROOT_PATH.'phpexcel/PHPExcel/IOFactory.php';

    // 设置PHPExcel缓存机制
    $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_wincache;
    $cacheSettings = array( 'cacheTime'  => 600 );
    PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
    //创建一个excel对象
    $objPHPExcel = new PHPExcel();

    //默认第一个sheet为当前的sheet
    $objPHPExcel->setActiveSheetIndex(0);
    $sheettmp = $objPHPExcel->getActiveSheet();

    // 设置表格第一行的大标题
    $sheettmp->setCellValue('A1', '测试 '.$fileName);
    $objPHPExcel -> getActiveSheet() -> getStyle('A1') -> getFont() -> setBold(true);
    // 合并单元格
    $sheettmp->mergeCells('A1:Q1');
    // 设置表格目录
    $sheettmp->setCellValue('A2', '序号');$sheettmp->setCellValue('B2', '部门');$sheettmp->setCellValue('C2', '姓名');$sheettmp->setCellValue('D2', '事假');
    $sheettmp->setCellValue('H2', '病假');$sheettmp->setCellValue('L2', '迟到');$sheettmp->setCellValue('N2', '忘打卡');$sheettmp->setCellValue('P2', '合计');
    $sheettmp->setCellValue('Q2', '备注');$sheettmp->setCellValue('D3', '1-3天 （30元)');$sheettmp->setCellValue('E3', '3-7天 （50元)');$sheettmp->setCellValue('F3', '7天以上（100元）');
    $sheettmp->setCellValue('G3', '合计');$sheettmp->setCellValue('H3', '1-3天 （30元)');$sheettmp->setCellValue('I3', '3-7天 （40元)');$sheettmp->setCellValue('J3', '7天以上 （50元）');
    $sheettmp->setCellValue('K3', '合计');$sheettmp->setCellValue('L3', '次数       （30元/次）');$sheettmp->setCellValue('M3', '合计');
    $sheettmp->setCellValue('N3', '次数       （30元/次）');$sheettmp->setCellValue('O3', '合计');
    $sheettmp->mergeCells('A2:A3');$sheettmp->mergeCells('B2:B3');$sheettmp->mergeCells('C2:C3');$sheettmp->mergeCells('D2:G2');$sheettmp->mergeCells('H2:K2');
    $sheettmp->mergeCells('L2:M2');$sheettmp->mergeCells('N2:O2');$sheettmp->mergeCells('P2:P3');$sheettmp->mergeCells('Q2:Q3');
   // 设置单元格字体加粗
    $objPHPExcel -> getActiveSheet() -> getStyle('A1') -> getFont() -> setBold(true);
    $objPHPExcel -> getActiveSheet() -> getStyle('A2:Q2') -> getFont() -> setBold(true);
    // 设置默认字体大小
    $objPHPExcel -> getActiveSheet() -> getDefaultStyle() -> getFont() -> setSize(10);
    // 设置单元格字体大小
    $objPHPExcel -> getActiveSheet() -> getStyle('A1') -> getFont() -> setSize(14);
    // 设置单元格字体颜色
    $objPHPExcel -> getActiveSheet() -> getStyle('A1') -> getFont()->getColor()->setRGB('FF0000');
    // 设置单元格背景色
    $objPHPExcel -> getActiveSheet() -> getStyle('A1') -> getFill() -> setFillType(PHPExcel_Style_Fill::FILL_SOLID);
    $objPHPExcel -> getActiveSheet() -> getStyle('A1') -> getFill() -> getStartColor() -> setRGB('99CCFF');
    //设置单元格宽度
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(6.5);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(13);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(9);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D:E')->setWidth(8);
    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
    $objPHPExcel->getActiveSheet()->getColumnDimension('G:K')->setWidth(8);
    $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(11);
    $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(8);
    $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(11);
    $objPHPExcel->getActiveSheet()->getColumnDimension('0')->setWidth(8);
    $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(8);
    $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(27);

    // 设置单元格高度
    $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);   // 设置默认
    $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);      // 设置指定行
    $objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(30);

    // 设置水平居中
    $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('A2:Q2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

    // 设置垂直居中
    $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('A2:Q2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

    // 设置自动换行
    $objPHPExcel->getDefaultStyle()->getAlignment()->setWrapText(true);

    // 设置单元格边框
    $styleThinBlackBorderOutline = array(
        'borders' => array(
            'allborders' => array( //设置全部边框
                'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
            ),

        ),
    );
    $length = sizeof($data);
    $objPHPExcel->getActiveSheet()->getStyle( 'A2:Q'.($length+4))->applyFromArray($styleThinBlackBorderOutline);

    // 引入数据
    foreach ($data as $index => $row){
        $i = $index+4;
        $colIndex='A';
        // 列循环
        foreach ($datafield as $field => $name)
        {
            $sheettmp->setCellValue($colIndex . $i, $row[$field]);
            $colIndex++;
        }
    }
     // 删除多余的最后一列
     //$objPHPExcel->getActiveSheet()->removeColumn(++$colIndex,1);

    $sheettmp -> setTitle($title);
    $objPHPExcel-> setActiveSheetIndex(0);
    $fileName = iconv("utf-8", "gb2312", $fileName);
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save(ROOT_PATH.'upload/file/'.$fileName.'.xlsx'); // 脚本方式运行，保存在在服务器上传目录

    return iconv("gb2312", "utf-8",  ROOT_URL.'upload/file/'.$fileName.'.xlsx');

 

导出Excel：就不放数据了