phpexcel是一个用来生成excel的php插件,他可以很方便的对excel数据进行操作,如:生成excel,修改excel数据等等.
一、PHPEXCEL简介
PHPEXCEL提供了一系列的API,能够解析与生成excel,pdf之类的文档.
PHPEXCEL虽然强大,不过使用起来相对有些繁锁,如果需要输出较为复杂格式时,是一个不错的选择,可以到官方下载到源码.
二、PHPEXCEL部分函数
立即学习“PHP免费学习笔记（深入）”；
设置当前的工作簿,返回该工作簿对象:
$excelSheet = $excel->setActiveSheetIndex(0);
合并单元格,返回该单元格对象,以下示例即合并A列第一行与第二行所在单元格:
$excelSheet->mergeCells('A1:A2');
设置单元格的值,参数:单元格名称,值:
$excelSheet->setCellValue('A1', '字符串内容');
$excelSheet->setCellValue('A2', 26); //数值
$excelSheet->setCellValue('A3', true); //布尔值
$excelSheet->setCellValue('A4', '=SUM(A2:A2)'); //公式phpexcel用法介绍,代码如下:
include 'PHPExcel.php'; include 'PHPExcel/Writer/Excel2007.php'; //或者include 'PHPExcel/Writer/Excel5.php'; 用于输出.xls的
创建一个excel
$objPHPExcel = new PHPExcel();
保存excel–2007格式
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
//或者$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 非2007格式
$objWriter->save("xxx.xlsx");
直接输出到浏览器
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");;
header('Content-Disposition:attachment;filename="resume.xls"');
header("Content-Transfer-Encoding:binary");
$objWriter->save('php://output');
–––––––––––––––––––––––––––––––––––––––
设置excel的属性：
getProperties()->setCreator("Maarten Balliauw");
最后修改人
$objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
标题
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
题目
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
描述
$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
关键字
$objPHPExcel->getProperties()->setKeywords("office 2007 openxml php");
种类
$objPHPExcel->getProperties()->setCategory("Test result file");
–––––––––––––––––––––––––––––––––––––––
设置当前的sheet
$objPHPExcel->setActiveSheetIndex(0);
设置sheet的name
$objPHPExcel->getActiveSheet()->setTitle('Simple');
设置单元格的值
$objPHPExcel->getActiveSheet()->setCellValue("A1", 'String');
$objPHPExcel->getActiveSheet()->setCellValue("A2", 12);
$objPHPExcel->getActiveSheet()->setCellValue("A3", true);
$objPHPExcel->getActiveSheet()->setCellValue("C5", '=SUM(C2:C4)');
$objPHPExcel->getActiveSheet()->setCellValue("B8", '=MIN(B2:C5)');
合并单元格
$objPHPExcel->getActiveSheet()->mergeCells("A18:E22");
分离单元格
$objPHPExcel->getActiveSheet()->unmergeCells("A28:B28");
?>三、PHPEXCEL举例应用
整个代码如下,值得注意的是表头用了$orderCellData记录了每个商户编号的顺序,为了在表体把对应的数据取出,代码如下:
getProperties()->setCreator("office 2003 excel");
$objExcel->getProperties()->setLastModifiedBy("office 2003 excel");
$objExcel->getProperties()->setTitle("Office 2003 XLS Test Document");
$objExcel->getProperties()->setSubject("Office 2003 XLS Test Document");
$objExcel->getProperties()->setDescription("Test document for Office 2003 XLS, generated using PHP classes.");
$objExcel->getProperties()->setKeywords("office 2003 openxml php");
$objExcel->getProperties()->setCategory("Test result file");
//开始处理数据(索引从0开始)
$objExcel->setActiveSheetIndex(0);
$conn = mssql_connect($config['mssql']['host'], $config['mssql']['user'], $config['mssql']['password']);
mssql_select_db($config['mssql']['dbname'], $conn);
$tm = $_REQUEST['tm'];
$sql = "exec HNow05_getTTSpace '','" . $tm . "','',1";
$sql = mb_convert_encoding($sql, 'GBK', 'UTF-8');
$res = mssql_query($sql);
$i = 0;
$k = array(
'站码',
'站名',
'河系',
'来报时间',
'水位',
'水势'
);
$count = count($k);
$arrs = array(
'A',
'B',
'C',
'D',
'E',
'F'
);
//添加表头
for ($i = 0; $i < $count; $i++) {
$objExcel->getActiveSheet()->setCellValue($arrs[$i] . "1", "$k[$i]");
}
/*--------从数据库读取数据-------*/
$i = 0;
while ($arr = mssql_fetch_array($res)) {
$stcd = $arr["STCD"];
$stnm = $arr["STNM"];
$rvnm = $arr["RVNM"];
$tm = $arr["TM"];
$tdz = $arr["TDZ"];
$tdptn = $arr["TDPTN"];
if ($tdptn == '6') {
$tdptn = '平';
} else if ($tdptn == '5') {
$tdptn = '涨';
} else if ($tdptn == '4') {
$tdptn = '落';
}
$u1 = $i + 2;
$stnm = iconv("GBK", "utf-8", $stnm);
$rvnm = iconv("GBK", "utf-8", $rvnm);
$tm = iconv("GBK", "utf-8", $tm);
/*----------写入内容-------------*/
$objExcel->getActiveSheet()->setCellValue('a' . $u1, "$stcd");
$objExcel->getActiveSheet()->setCellValue('b' . $u1, "$stnm");
$objExcel->getActiveSheet()->setCellValue('c' . $u1, "$rvnm");
$objExcel->getActiveSheet()->setCellValue('d' . $u1, "$tm");
$objExcel->getActiveSheet()->setCellValue('e' . $u1, "$tdz");
$objExcel->getActiveSheet()->setCellValue('f' . $u1, "$tdptn");
$i++;
}
/*----------设置单元格边框和颜色-------------*/
$rows = mssql_num_rows($res);
for ($i = 0; $i < ($rows + 1); $i++) {
for ($j = 0; $j < $count; $j++) {
$a = $i + 1;
$objExcel->getActiveSheet()->getStyle($arrs[$j] . $a)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objExcel->getActiveSheet()->getStyle($arrs[$j] . $a)->getBorders()->getAllBorders()->getColor()->setARGB('FF00BBcc');
//水平居中
$objExcel->getActiveSheet()->getStyle($arrs[$j] . $a)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
}
// 高置列的宽度
$objExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
$objExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
$objExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
$objExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
$objExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);
$objExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
// 设置页眉和页脚。如果没有不同的标题奇数/即使是使用单头假定.
$objExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&BPersonal cash register&RPrinted on &D');
$objExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objExcel->getProperties()->getTitle() . '&RPage &P of &N');
// 设置页方向和规模
$objExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
$objExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
// 重命名表
$objExcel->getActiveSheet()->setTitle('实时潮汐情况');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objExcel->setActiveSheetIndex(0);
// Redirect output to a client's web browser (Excel5)保存为excel2003格式
//设置Excel的名字
$excelName = '实时潮汐情况(' . $tm . ')';
//$excelName = 'Excel_'.date("YmdHis");
header('Content-Type: application/vnd.ms-excel');
header('Cache-Control: max-age=0');
header('Content-Disposition: attachment; filename=' . iconv("utf-8", "GBK", $excelName) . '.xls');
$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
$objWriter->save('php://output');
exit;
?>教程地址：
欢迎转载！但请带上文章地址^^
