一、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;
总结
@漏刻有时
