phpexcel读写excel表格详解

phpexcel代码地址：https://code.csdn.net/u013372487/phpexcel/tree/master

代码实例：
数据导入excel表格：

可以通过命令直接导出：

<?php/** * php 输出excel文件到固定文件夹中   * 注意输出的是 xxx.xlsx文件， * 若想输出 .xls文件，将下面语句替换为   * include './PHPExcel/Writer/Excel5.php'; * $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); */include './PHPExcel.php';//用于输出.xlsx文件include './PHPExcel/Writer/Excel2007.php';//创建一个excel$objPHPExcel = new PHPExcel();//设置excel的属性：//创建人$objPHPExcel->getProperties()->setCreator("wml");//最后修改人$objPHPExcel->getProperties()->setLastModifiedBy("wml");//标题$objPHPExcel->getProperties()->setTitle("wml_excel");//题目$objPHPExcel->getProperties()->setSubject("wml_subject");//备注$objPHPExcel->getProperties()->setDescription("wml_des");//标记$objPHPExcel->getProperties()->setKeywords("wml_key");//类别$objPHPExcel->getProperties()->setCategory("wml_kind");//设置当前的sheet$objPHPExcel->setActiveSheetIndex(0);//设置sheet的name$objPHPExcel->getActiveSheet()->setTitle('test1');//设置单元格的值$objPHPExcel->getActiveSheet()->setCellValue('A1', 'numben1');$objPHPExcel->getActiveSheet()->setCellValue('B1', 'numben2');$objPHPExcel->getActiveSheet()->setCellValue('C1', 'numben3');$objPHPExcel->getActiveSheet()->setCellValue('D1', 'numben4');$objPHPExcel->getActiveSheet()->setCellValue('E1', 'numben5');$objPHPExcel->getActiveSheet()->setCellValue('F1', 'numben5');$objPHPExcel->getActiveSheet()->setCellValue('G1', 'numben5');$objPHPExcel->getActiveSheet()->setCellValue('H1', 'numben5');//从第二行开始设置记录值for ($i = 2; $i <= 4; $i++) {    $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, convertGBK('地方'));    $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, convertGBK('002'));    $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, convertGBK('003'));    $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, convertGBK('004'));    $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, convertGBK('005'));    $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, convertGBK('006'));    $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, convertGBK('007'));    $objPHPExcel->getActiveSheet()->setCellValue('H' . $i, convertGBK('008'));}//合并单元格,两个值分别是要合并范围的对角线值$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');//分离单元格$objPHPExcel->getActiveSheet()->unmergeCells('A18:E22');//设置列宽width//自适应宽度$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);//固定宽度$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);//设置文字大小$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);$objPHPExcel->getActiveSheet()->getStyle('B3')->getFont()->setSize(20);//设置align,水平垂直对齐设置//水平对齐$objPHPExcel->getActiveSheet()->getStyle('D1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);$objPHPExcel->getActiveSheet()->getStyle('D2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);$objPHPExcel->getActiveSheet()->getStyle('D3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//垂直对齐$objPHPExcel->getActiveSheet()->getStyle('D1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_BOTTOM);$objPHPExcel->getActiveSheet()->getStyle('D2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);$objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//设置填充颜色（背景色）$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080');$objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);$objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->getStartColor()->setARGB('FF808080');//添加图片$objDrawing = new PHPExcel_Worksheet_Drawing();$objDrawing->setName('Logo');$objDrawing->setDescription('Logo');$objDrawing->setPath('./image/sample.png');$objDrawing->setHeight(36);$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());$objDrawing = new PHPExcel_Worksheet_Drawing();$objDrawing->setName('Paid');$objDrawing->setDescription('Paid');$objDrawing->setPath('./image/sample1.png');$objDrawing->setCoordinates('B15');$objDrawing->setOffsetX(110);//旋转$objDrawing->setRotation(25);$objDrawing->getShadow()->setVisible(true);$objDrawing->getShadow()->setDirection(45);$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());//多sheet表格设置//在默认sheet后，创建一个worksheet$objPHPExcel->createSheet();//设置新的的sheet$objPHPExcel->setActiveSheetIndex(1);//设置sheet的name$objPHPExcel->getActiveSheet()->setTitle('test12');//设置单元格的值（字段值）$objPHPExcel->getActiveSheet()->setCellValue('A1', 'numben12');$objPHPExcel->getActiveSheet()->setCellValue('B1', 'numben22');$objPHPExcel->getActiveSheet()->setCellValue('C1', 'numben32');$objPHPExcel->getActiveSheet()->setCellValue('D1', 'numben42');$objPHPExcel->getActiveSheet()->setCellValue('E1', 'numben52');$objPHPExcel->getActiveSheet()->setCellValue('F1', 'numben52');$objPHPExcel->getActiveSheet()->setCellValue('G1', 'numben52');$objPHPExcel->getActiveSheet()->setCellValue('H1', 'numben52');for ($i = 2; $i <= 14; $i++) {    $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, convertGBK('0012'));    $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, convertGBK('0022'));    $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, '32');    $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, convertGBK('0042'));    $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, convertGBK('0052'));    $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, convertGBK('0062'));    $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, convertGBK('0072'));    $objPHPExcel->getActiveSheet()->setCellValue('H' . $i, convertGBK('0082'));    //注意 表格中也可以插入公式，会自动计算    $objPHPExcel->getActiveSheet()->setCellValue('I' . $i, '=SUM(C2:C4)');}//保存excel—2007格式$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);//保存文件到具体路径$objWriter->save("./excel/2016test.xlsx");//可选择性对导出数据进行转码function convertGBK($str){    if(empty($str)) return '';    //return  iconv('utf-8', 'GBK', $str);    //return  iconv('GBK', 'utf-8', $str);    return $str;}

通过浏览器导出：

<?php/** * php 输出excel文件到浏览器中   * 注意输出的是 xxx.xlsx文件， * 若想输出 .xls文件，将下面语句替换为   * include './PHPExcel/Writer/Excel5.php'; * $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); */include 'PHPExcel.php';include 'PHPExcel/Writer/Excel2007.php';//创建一个excel$objPHPExcel = new PHPExcel();//设置excel的属性：//创建人$objPHPExcel->getProperties()->setCreator("wml");//最后修改人$objPHPExcel->getProperties()->setLastModifiedBy("wml");//标题$objPHPExcel->getProperties()->setTitle("wml_excel");//题目$objPHPExcel->getProperties()->setSubject("wml_subject");//备注$objPHPExcel->getProperties()->setDescription("wml_des");//标记$objPHPExcel->getProperties()->setKeywords("wml_key");//类别$objPHPExcel->getProperties()->setCategory("wml_kind");//设置当前的sheet$objPHPExcel->setActiveSheetIndex(0);//设置sheet的name$objPHPExcel->getActiveSheet()->setTitle('test1');//设置单元格的值$objPHPExcel->getActiveSheet()->setCellValue('A1', 'numben1');$objPHPExcel->getActiveSheet()->setCellValue('B1', 'numben2');$objPHPExcel->getActiveSheet()->setCellValue('C1', 'numben3');$objPHPExcel->getActiveSheet()->setCellValue('D1', 'numben4');$objPHPExcel->getActiveSheet()->setCellValue('E1', 'numben5');$objPHPExcel->getActiveSheet()->setCellValue('F1', 'numben5');$objPHPExcel->getActiveSheet()->setCellValue('G1', 'numben5');$objPHPExcel->getActiveSheet()->setCellValue('H1', 'numben5');//从第二行开始设置记录值for ($i = 2; $i <= 4; $i++) {    $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, convertGBK('地方'));    $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, convertGBK('002'));    $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, convertGBK('003'));    $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, convertGBK('004'));    $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, convertGBK('005'));    $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, convertGBK('006'));    $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, convertGBK('007'));    $objPHPExcel->getActiveSheet()->setCellValue('H' . $i, convertGBK('008'));}//合并单元格,两个值分别是要合并范围的对角线值$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');//分离单元格$objPHPExcel->getActiveSheet()->unmergeCells('A18:E22');//设置列宽width//自适应宽度$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);//固定宽度$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);//设置文字大小$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);$objPHPExcel->getActiveSheet()->getStyle('B3')->getFont()->setSize(20);//设置align,水平垂直对齐设置//水平对齐$objPHPExcel->getActiveSheet()->getStyle('D1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);$objPHPExcel->getActiveSheet()->getStyle('D2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);$objPHPExcel->getActiveSheet()->getStyle('D3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//垂直对齐$objPHPExcel->getActiveSheet()->getStyle('D1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_BOTTOM);$objPHPExcel->getActiveSheet()->getStyle('D2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);$objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//设置填充颜色（背景色）$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080');$objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);$objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->getStartColor()->setARGB('FF808080');//添加图片$objDrawing = new PHPExcel_Worksheet_Drawing();$objDrawing->setName('Logo');$objDrawing->setDescription('Logo');$objDrawing->setPath('./image/sample.png');$objDrawing->setHeight(36);$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());$objDrawing = new PHPExcel_Worksheet_Drawing();$objDrawing->setName('Paid');$objDrawing->setDescription('Paid');$objDrawing->setPath('./image/sample1.png');$objDrawing->setCoordinates('B15');$objDrawing->setOffsetX(110);//旋转$objDrawing->setRotation(25);$objDrawing->getShadow()->setVisible(true);$objDrawing->getShadow()->setDirection(45);$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());//多sheet表格设置//在默认sheet后，创建一个worksheet$objPHPExcel->createSheet();//设置新的的sheet$objPHPExcel->setActiveSheetIndex(1);//设置sheet的name$objPHPExcel->getActiveSheet()->setTitle('test12');//设置单元格的值（字段值）$objPHPExcel->getActiveSheet()->setCellValue('A1', 'numben12');$objPHPExcel->getActiveSheet()->setCellValue('B1', 'numben22');$objPHPExcel->getActiveSheet()->setCellValue('C1', 'numben32');$objPHPExcel->getActiveSheet()->setCellValue('D1', 'numben42');$objPHPExcel->getActiveSheet()->setCellValue('E1', 'numben52');$objPHPExcel->getActiveSheet()->setCellValue('F1', 'numben52');$objPHPExcel->getActiveSheet()->setCellValue('G1', 'numben52');$objPHPExcel->getActiveSheet()->setCellValue('H1', 'numben52');for ($i = 2; $i <= 14; $i++) {    $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, convertGBK('0012'));    $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, convertGBK('0022'));    $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, convertGBK('0032'));    $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, convertGBK('0042'));    $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, convertGBK('0052'));    $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, convertGBK('0062'));    $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, convertGBK('0072'));    $objPHPExcel->getActiveSheet()->setCellValue('H' . $i, convertGBK('0082'));}//直接输出到浏览器//保存excel—2007格式$objWriter = new PHPExcel_Writer_Excel2007($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.xlsx"');header("Content-Transfer-Encoding:binary");$objWriter->save('php://output');//可选择性对导出数据进行转码function convertGBK($str){    if(empty($str)) return '';    //return  iconv('utf-8', 'GBK', $str);    //return  iconv('GBK', 'utf-8', $str);    return $str;}

excel中数据读取：

<?php/** * 读取excel文件，并进行相应处理 */$fileName = "./excel/2016test.xlsx";if (!file_exists($fileName)) {    exit("文件".$fileName."不存在");}$startTime = time();//方法一：/* include './PHPExcel/Writer/Excel2007.php';$objReader = new PHPExcel_Reader_Excel2007;$objPHPExcel = $objReader->load($fileName); *///方法二：require_once './PHPExcel/IOFactory.php';$objPHPExcel = PHPExcel_IOFactory::load($fileName);//获取sheet表数目$sheetCount = $objPHPExcel->getSheetCount();//默认选中sheet0表$sheetSelected = 0;$objPHPExcel->setActiveSheetIndex($sheetSelected);//获取表格行数$rowCount = $objPHPExcel->getActiveSheet()->getHighestRow();//获取表格列数$columnCount = $objPHPExcel->getActiveSheet()->getHighestColumn();echo "<div>Sheet Count : ".$sheetCount."　　行数： ".$rowCount."　　列数：".$columnCount."</div>"; $dataArr = array();/** 循环读取每个单元格的数据 *///行数循环for ($row = 1; $row <= $rowCount; $row++){    //列数循环 , 列数是以A列开始    for ($column = 'A'; $column <= $columnCount; $column++) {        $dataArr[] = $objPHPExcel->getActiveSheet()->getCell($column.$row)->getValue();        echo $column.$row.":".$objPHPExcel->getActiveSheet()->getCell($column.$row)->getValue()."<br />";    }}echo "<br/>消耗的内存为：".(memory_get_peak_usage(true) / 1024 / 1024)."M";$endTime = time();echo "<div>解析完后，当前的时间为：".date("Y-m-d H:i:s")."　　　总共消耗的时间为：".(($endTime - $startTime))."秒</div>";var_dump($dataArr);$dataArr = NULL;

注意：
PHPExcel不仅可以读取excel；
还可以读写以下格式文件：

ReadingBIFF 5-8 (.xls) Excel 95 and aboveOffice Open XML (.xlsx) Excel 2007 and aboveSpreadsheetML (.xml) Excel 2003Open Document Format/OASIS (.ods)GnumericHTMLSYLKCSVWritingBIFF 8 (.xls) Excel 95 and aboveOffice Open XML (.xlsx) Excel 2007 and aboveHTMLCSVPDF (using either the tcPDF, DomPDF or mPDF libraries, which need to be installed separately)

若仅仅是通过浏览器将数据导入excel表格中，没有复杂操作，可采用以下方法：
以下方法比较简单，不需要用其他类库：

<?php$host="127.0.0.1";$db_user="";$db_pass="";$db_name="";$timezone="Asia/Shanghai";$link=mysql_connect($host,$db_user,$db_pass);mysql_select_db($db_name,$link);mysql_query("SET names UTF8");header("Content-Type: text/html; charset=utf-8");//需要统计的天数$day_number = 4;$date_now = date("Ymd",time());// 多天汇总表格$table_all = 'keywords_alltoone_' . $date_now;//导出表格$result = mysql_query("select * from $table_all");//第一行数据，这个要根据查询天数改变$str = "关键词\t昨天\t前天\t大前天\t\n";$str = iconv('utf-8','GBK',$str);while($row=mysql_fetch_array($result)){    $name = iconv('utf-8','GBK',$row['name']);    $before1 = iconv('utf-8','GBK',$row['before1']);    $before2 = iconv('utf-8','GBK',$row['before2']);    $before3 = iconv('utf-8','GBK',$row['before3']);    $str .= $name."\t".$before1."\t".$before2."\t".$before3."\t\n";}$filename = date('Ymd').'.xls';exportExcel($filename,$str);function exportExcel($filename,$content){    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");    header("Content-Type: application/vnd.ms-execl");    header("Content-Type: application/force-download");    header("Content-Type: application/download");    header("Content-Disposition: attachment; filename=".$filename);    header("Content-Transfer-Encoding: binary");    header("Pragma: no-cache");    header("Expires: 0");    echo $content;}