前言
excel作为办公环境中最常用的数据处理工具,使用的人群很广泛。但当你有一些比较复杂的处理场景时,仅靠一些表面的简单的操作肯定是满足不了要求的。这时如果让我临时学习vba编程,感觉有点难度,因为我只会C和PHP,于是借这个机会,我查阅了一些资料,发现PHP有开源的工具库PHPExcel,功能很强大,很实用。
以下介绍了如何使用PHPExcel实现excel读取和导出数据库表至excel里面,相信会对大家有所帮助。
一、PHPExcel库下载
点击进入如下GitHub地址,点击Code=>Download ZIP,下载PHPExcel-1.8.zip
二、Excel读取
1.引入库
代码如下(示例):
require_once 'PHPExcel.php';
require_once 'PHPExcel/IOFactory.php';
require_once 'PHPExcel/Reader/Excel2007.php';
require_once 'PHPExcel/Reader/Excel5.php';
require_once 'PHPExcel/Reader/CSV.php';
2.创建Reader
代码如下(示例):
if(strstr($shoolFile, ".xlsx"))
{
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
}
else if(strstr($shoolFile, ".xls"))
{
$objReader = PHPExcel_IOFactory::createReader('Excel5');
}
else if(strstr($shoolFile, ".csv"))
{
$csvReader = PHPExcel_IOFactory::createReader('CSV')->setDelimiter(',')->setInputEncoding('GBK');
}
3.Excel读取和遍历
代码如下(示例):
$objPHPExcel = $objReader->load($shoolFile);
$sheet = $objPHPExcel->getSheet(0);
$total_line = $sheet->getHighestRow(); // 取得总行数
for($row = 2; $row <= $total_line; $row++)
{
$name = trim($objPHPExcel->getActiveSheet()->getCell('D'.$row)->getValue());
$sex = trim($objPHPExcel->getActiveSheet()->getCell('E'.$row)->getValue());
$nature = trim($objPHPExcel->getActiveSheet()->getCell('F'.$row)->getValue());
}
三、导出数据至Excel
1.导出接口
代码如下(示例):
//导出接口
/*$data:数据库查询结果
$savefile:保存的文件名
$fileheader:表头
$sheetname:表单名
*/
function exportExcel($data, $savefile, $fileheader, $sheetname){
$excel = new PHPExcel();
$objActSheet = $excel->getActiveSheet();
$letter = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA');
$excel->setActiveSheetIndex(0);
$objActSheet->setTitle($sheetname);
for($i = 0;$i < count($fileheader);$i++) {
$objActSheet->setCellValue("$letter[$i]1",$fileheader[$i]);
}
for ($i = 2;$i <= count($data) + 1;$i++) {
$j = 0;
foreach ($data[$i - 2] as $key=>$value) {
$objActSheet->setCellValue("$letter[$j]$i",$value);
$j++;
}
}
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $savefile . '.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$objWriter->save('php://output');
}
2.接口调用
$connection= mysqli_connect('a.b.c.d','aaa','bbb','ccc');
if($connection)
{
if($query = mysqli_query($connection,"select *from aaa_info "))
{
$data = mysqli_fetch_all($query,MYSQLI_ASSOC);
$fileheader= array('a', 'b', 'c','d', 'e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','aa');
exportExcel($data,'调试信息',$fileheader,'Sheet1');
mysqli_free_result($query);
}
mysqli_close($connection);
}
总结
以上就是今天要讲的内容,本文仅仅介绍了使用php读取excel,并介绍了如何将数据库表导出至excel。