PHP导出Excel，设置表格样式，填充颜色等较为复杂样式

// 注:只是在此做下记录,有兴趣的可以参考,不做实际教程文档
<?php

//引入Li类对数据进行操作
include_once('./Li.php');
//引入Excel类库对对数据进行操作
include_once('./Excel/PHPExcel.php');

//开起error all机制

error_reporting(E_ALL);
//操作数据
$Li = new Li;
$whe = "1=1";
if ($_GET) {
    if (isset($_GET['sou'])) {
        $sou = $_GET['sou'];
        $whe = "CONCAT(`id`,`nr`,`jl`,`bm`,`qrsj`,`bz`) LIKE '%$sou%'";
    }
}
$sql = "select * from check_s where $whe ORDER BY id";
$data = $Li->sel("$sql");
//实例化Excel类
date_default_timezone_set('Europe/London');
$objPHPExcel = new PHPExcel();

/*设置导出信息*/
$objPHPExcel->getProperties()->setCreator("MTN")
    ->setLastModifiedBy("MTN")
    ->setTitle("数据EXCEL导出")
    ->setSubject("数据EXCEL导出")
    ->setDescription("备份数据")
    ->setKeywords("excel")
    ->setCategory("result file");
//设置表头名
$objPHPExcel->setActiveSheetIndex(0)
//Excel的第A列，uid是你查出数组的键值，下面以此类推
    ->setCellValue('A5', '序号')
    ->setCellValue('B5', '检查内容')
    ->setCellValue('E5', '检查记录')
    ->setCellValue('F5', '被检查部门')
    ->setCellValue('G5', '被检查人确认/时间')
    ->setCellValue('H5', '备注');
//合并
$objPHPExcel->getActiveSheet()->mergeCells('A1:C3');
//给单元格设置值
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'LOGO');
$objPHPExcel->getActiveSheet()->mergeCells('D1:E2');
$objPHPExcel->getActiveSheet()->setCellValue('D1', '文件执行检查表');
$objPHPExcel->getActiveSheet()->mergeCells('F1:G1');
$objPHPExcel->getActiveSheet()->setCellValue('F1', '表格编号:BZY047-2015');
$objPHPExcel->getActiveSheet()->setCellValue('H1', '版次:B');
$objPHPExcel->getActiveSheet()->mergeCells('D3:E3');
$objPHPExcel->getActiveSheet()->setCellValue('D3', '表格编制部门：考管部     使用部门：           ');
$objPHPExcel->getActiveSheet()->mergeCells('F2:G2');
$objPHPExcel->getActiveSheet()->setCellValue('F2', '实施日期:2015.10.18');
$objPHPExcel->getActiveSheet()->setCellValue('H2', '编号:');
$objPHPExcel->getActiveSheet()->mergeCells('F3:H3');
$objPHPExcel->getActiveSheet()->setCellValue('F3', '代替:BZY047-2010（A版）2010.06.24发布');
$objPHPExcel->getActiveSheet()->mergeCells('A4:H4');
$objPHPExcel->getActiveSheet()->setCellValue('A4', ' 文件名称：工作现场管理办法                文件编号：BY/GLZ6.4-01-2016       检查日期：       年    月     日       检查人：');
$objPHPExcel->getActiveSheet()->mergeCells('B5:D5');
//设置列宽
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(3);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(23);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(50);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(9);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(9);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
//设置行高
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
$objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
$objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(20);
$objPHPExcel->getActiveSheet()->getRowDimension('4')->setRowHeight(20);
//设置字体大小加粗
$objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->setName('宋体')->setSize(24)->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('F1:F3')->getFont()->setName('宋体')->setSize(8);
$objPHPExcel->getActiveSheet()->getStyle('H1:H2')->getFont()->setName('宋体')->setSize(8);

//遍历数据，准备导出
$i = 6;
foreach ($data as $key => $val) {
    //数据内容居中
    $objPHPExcel->getActiveSheet()->getStyle('A1:H' . $i)->applyFromArray(
        array(
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER
            )
        )
    );
    //配置边框样式
    $styleArray = array(
        'borders' => array(
            'allborders' => array(
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框
            ),
        ),
    );
    //执行边框样式
    $objPHPExcel->getActiveSheet()->getStyle('A1:H' . $i)->applyFromArray($styleArray);
//数据内容垂直居中
    $objPHPExcel->getActiveSheet()->getStyle('A1:H' . $i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//内容过长自动换行
    $objPHPExcel->getActiveSheet()->getStyle('A1:H' . $i)->getAlignment()->setWrapText(true);

    //设置合并
    $objPHPExcel->getActiveSheet()->mergeCells('B' . $i . ':' . 'D' . $i);
    //设置好单元格横坐标
    $clos = ++$key + 5;
    //进行导出操作
    $objPHPExcel->setActiveSheetIndex(0)
        //Excel的第A列，uid是你查出数组的键值，下面以此类推
        ->setCellValue('A' . $clos, $val['id'])
        ->setCellValue('B' . $clos, $val['nr'])
        ->setCellValue('E' . $clos, $val['jl'])
        ->setCellValue('F' . $clos, $val['bm'])
        ->setCellValue('G' . $clos, $val['qrsj'])
        ->setCellValue('H' . $clos, $val['bz']);
    $i++;
}

//设置sheet1操作表名
$objPHPExcel->getActiveSheet()->setTitle('文件执行检查');

$objPHPExcel->setActiveSheetIndex(0);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="文件执行检查.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
巴特西

PHP导出Excel，设置表格样式，填充颜色等较为复杂样式

最新文章

热门文章
