phpexcel读写excel表格详解

代码实例：
数据导入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；
还可以读写以下格式文件：

Reading

BIFF 5-8 (.xls) Excel 95 and above
Office Open XML (.xlsx) Excel 2007 and above
SpreadsheetML (.xml) Excel 2003
Open Document Format/OASIS (.ods)
Gnumeric
HTML
SYLK
CSV

Writing

BIFF 8 (.xls) Excel 95 and above
Office Open XML (.xlsx) Excel 2007 and above
HTML
CSV
PDF (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;
}