PHPExcel导出报表封装函数(单元格合并、设置填充色)

一、PHPExcel 是什么？

PHPExcel 是用来操作Office Excel 文档的一个PHP类库，它基于微软的OpenXML标准和PHP语言。可以使用它来读取、写入不同格式的电子表格。

二、导出报表

获取数据

数据来源可以是从mysql数据库汇总获取，也可以直接通过爬虫获取API传递过来的数据，本例从第三方API获取数据

    //电能报表;
    case "exReport1";
        require "libs/power.export.php";
        $filename = get_param("filename");
        $member_id = get_param("member_id");
        $filesName = $filename . date("Y.m.d");
        $row = $db->fetch('member', 'member_depart,energyid', array('member_id' => $member_id), 'member_id DESC');
        $group_id = $row['energyid'];
        $member_depart = $row['member_depart'];
        if ($group_id && $group_id != "0") {
            $url = curlIotAPI($CONF["api_iot_pipe"], '1', '1000', $CONF["api_user_key"], $group_id);
            $reArr = json_decode($url, true);
            $data_int = $reArr["data"];
            $arr = getIotPower($data_int["data"]);
            expExcel($arr, $filesName, $member_depart);
            //die(json_encode_lockdata($res));
        } else {
            die("无数据");
        }
        break;

2.封装函数

libs/power.export.php

function expExcel($arr, $name, $title)
{
//核心代码
}

表格属性设置

实例化表格，设置表格属性

$objPHPExcel = new PHPExcel();
    $objPHPExcel->getProperties()->setCreator("电能报表")
        ->setLastModifiedBy("电能报表")
        ->setTitle('电能报表数据导出')
        ->setSubject('电能报表数据导出')
        ->setDescription('导出数据')
        ->setKeywords("excel")
        ->setCategory("result file");

标题设置

可以将要导出的表格表头提前设置好，按照标准模版进行代码的合并和设置。

 //一级标题行
    $objPHPExcel->setActiveSheetIndex(0);
    $objPHPExcel->getActiveSheet()
        ->setCellValue('A1', $title . '电能报表')
        ->setCellValue('A2', '生成时间：' . date("Y-m-d H:i:s"))
        ->setCellValue('A3', '')
        ->setCellValue('C3', '日电量 Kwh')
        ->setCellValue('F3', '月电量 Kwh')
        ->setCellValue('L3', '年电量 Kwh')
        ->setCellValue('A4', '排序')
        ->setCellValue('B4', '位置')
        ->setCellValue('C4', date("Y-m-d"))
        ->setCellValue('D4', date("Y-m-d", strtotime("-1 days")))
        ->setCellValue('E4', date("Y-m-d", strtotime("-2 days")))
        ->setCellValue('F4', date("Y-m"))
        ->setCellValue('G4', date("Y-m", strtotime("-1 months")))
        ->setCellValue('H4', date("Y-m", strtotime("-2 months")))
        ->setCellValue('I4', date("Y-m", strtotime("-3 months")))
        ->setCellValue('J4', date("Y-m", strtotime("-4 months")))
        ->setCellValue('k4', date("Y-m", strtotime("-5 months")))
        ->setCellValue('L4', date("Y"))
        ->setCellValue('M4', date("Y", strtotime("-1 years")))
        ->setCellValue('N4', date("Y", strtotime("-2 years")))
        ->getStyle('A4:N4')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);

合并单元格

    //合并单元格
    $objPHPExcel->getActiveSheet()->mergeCells('A1:N1');
    $objPHPExcel->getActiveSheet()->mergeCells('A2:N2');
    $objPHPExcel->getActiveSheet()->mergeCells('A3:B3');
    $objPHPExcel->getActiveSheet()->mergeCells('C3:E3');
    $objPHPExcel->getActiveSheet()->mergeCells('F3:K3');
    $objPHPExcel->getActiveSheet()->mergeCells('L3:N3');

设置对齐方式

  //对齐方式
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('C3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('F3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('L3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

设置数据

 //设置填充
    $objPHPExcel->getActiveSheet()->getStyle('C3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
    $objPHPExcel->getActiveSheet()->getStyle('C3')->getFill()->getStartColor()->setARGB('FF808080');
    $objPHPExcel->getActiveSheet()->getStyle('L3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
    $objPHPExcel->getActiveSheet()->getStyle('L3')->getFill()->getStartColor()->setARGB('FF808080');

  $key = 4;
    for ($i = 0; $i < count($arr); $i++) {
        $dlVal = explode(",", $arr[$i]['data']['propertyType']);
        $sdVal = explode(",", $arr[$i]['sdata']);
        $yesterday_val = explode(",", $arr[$i]['odata']['yesterday_val']);
        $b_yesterday_val = explode(",", $arr[$i]['odata']['b_yesterday_val']);
        $three_days_ago_val = explode(",", $arr[$i]['odata']['three_days_ago_val']);
        $last_month_val = explode(",", $arr[$i]['odata']['last_month_val']);
        $b_last_month_val = explode(",", $arr[$i]['odata']['b_last_month_val']);
        $three_month_ago_val = explode(",", $arr[$i]['odata']['three_month_ago_val']);
        $last_year_val = explode(",", $arr[$i]['odata']['last_year_val']);
        $b_last_year_val = explode(",", $arr[$i]['odata']['b_last_year_val']);
        $three_years_ago_val = explode(",", $arr[$i]['odata']['three_years_ago_val']);
        $four_month_ago_val = explode(",", $arr[$i]['odata']['four_month_ago_val']);
        $five_month_ago_val = explode(",", $arr[$i]['odata']['five_month_ago_val']);
        $six_month_ago_val = explode(",", $arr[$i]['odata']['six_month_ago_val']);
        $sdVal_p = array();

        for ($j = 0; $j < count($dlVal); $j++) {
            if ($dlVal[$j] == "电量") {
                //日
                $sdVal_p[0] = getPositive($sdVal[$j] - $yesterday_val[$j]);
                $sdVal_p[1] = getPositive($yesterday_val[$j] - $b_yesterday_val[$j]);
                $sdVal_p[2] = getPositive($b_yesterday_val[$j] - $three_days_ago_val[$j]);
                //月
                $sdVal_p[3] = getPositive($sdVal[$j] - $last_month_val[$j]);
                $sdVal_p[4] = getPositive($last_month_val[$j] - $b_last_month_val[$j]);
                $sdVal_p[5] = getPositive($b_last_month_val[$j] - $three_month_ago_val[$j]);
                $sdVal_p[9] = getPositive($three_month_ago_val[$j] - $four_month_ago_val[$j]);
                $sdVal_p[10] = getPositive($four_month_ago_val[$j] - $five_month_ago_val[$j]);
                $sdVal_p[11] = getPositive($five_month_ago_val[$j] - $six_month_ago_val[$j]);
                //年
                $sdVal_p[6] = getPositive($sdVal[$j] - $last_year_val[$j]);
                $sdVal_p[7] = getPositive($last_year_val[$j] - $b_last_year_val[$j]);
                $sdVal_p[8] = getPositive($b_last_year_val[$j] - $three_years_ago_val[$j]);
            }
        }

        $key++;
        $objPHPExcel->getActiveSheet()
            ->setCellValue('A' . $key, $arr[$i]["device_seq"])
            ->setCellValue('B' . $key, $arr[$i]["device"])
            ->setCellValue('C' . $key, $sdVal_p[0])
            ->setCellValue('D' . $key, $sdVal_p[1])
            ->setCellValue('E' . $key, $sdVal_p[2])
            ->setCellValue('F' . $key, $sdVal_p[3])
            ->setCellValue('G' . $key, $sdVal_p[4])
            ->setCellValue('H' . $key, $sdVal_p[5])
            ->setCellValue('I' . $key, $sdVal_p[9])
            ->setCellValue('J' . $key, $sdVal_p[10])
            ->setCellValue('K' . $key, $sdVal_p[11])
            ->setCellValue('L' . $key, $sdVal_p[6])
            ->setCellValue('M' . $key, $sdVal_p[7])
            ->setCellValue('N' . $key, $sdVal_p[8]);
    }

生成表格

  //设置当前的表格
    $objPHPExcel->setActiveSheetIndex(0);
    //ob_end_clean();

    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="' . $name . '.xls');
    header('Cache-Control: max-age=0');
    header('Content-Type: text/html; charset=utf-8');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');
    exit;

---

总结

@漏刻有时