phpexcel类是php一个excel表格处理插件，最近由于工作需要用到了这个插件，完成了一个excel导入导入的功能。现在将主要逻辑整理一下和大家分享，有需要的可以参考一下。
       下面的事例只是完成了一个excel的上传下载的基本功能，还有一些设置表格样式、合并单元格等操作没有体现在这里，大家可以参考phpexcel的手册，事例后面是我摘出来的phpexcel的常见的一些操作，供大家参考：
       将phpexcel提供的api根据需要封装成一个供上传和下载使用的类，其实就是两个函数，一个用于上传一个用于下载，这里我就直接将代码贴出来了。
canread($file)) { /* 如果phpexcel_reader_excel2007无法读取excel，则销毁刚才创建的对象，使用phpexcel_reader_excel5来读 */ unset($phpreader); $phpreader = new phpexcel_reader_excel5(); } if(!$phpreader->canread($file)) { /* 文件无法读取，返回空的数组 */ return array(); } $phpexcel = $phpreader->load($file); /* 目前的实现只读取第一个工作表 */ $currentsheet = $phpexcel->getsheet(0); /* 拿到工作表的行数和列数 */ $allrows = $currentsheet->gethighestrow(); $allcolumns = $currentsheet->gethighestcolumn(); $allcolumns++; $currentcolumn = 'a'; /* 解析第一个行，记录$fields中要读取的行 */ while($currentcolumn != $allcolumns) { $title = $currentsheet->getcell($currentcolumn . '1')->getvalue(); $field = array_search($title, $fields); $columnkey[$currentcolumn] = $field ? $field : ''; $currentcolumn++; } $datalist = array(); /* 跳过标题行（第一行）开始读取数据 */ for($currentrow = 2; $currentrow getcell($currentcolumn . $currentrow)->getvalue()); if(empty($columnkey[$currentcolumn])) { $currentcolumn++; continue; } $field = $columnkey[$currentcolumn]; $currentcolumn++; if (empty($cellvalue)) { $data->$field = ''; } else { $data->$field = $cellvalue; $ignore = false; } } if ($ignore == true) { continue; } /* 设置没有从excel中读到的数据 */ foreach(array_keys($fields) as $key) { if(!isset($data->$key)) { $data->$key = ''; } } $datalist[] = $data; } return $datalist; } public function setexcelfiled($count) { $letter = 'a'; for($i = 1; $i phpexcel = new phpexcel(); $this->rawexceldata = $data; $this->fields = $this->rawexceldata->fields; $this->rows = $this->rawexceldata->rows; $this->fieldskey = array_keys($this->fields); if(!$this->rawexceldata->filename) $this->rawexceldata->filename = $this->rawexceldata->kind; $this->excelkey = array(); for($i = 0; $i fieldskey); $i++) $this->excelkey[$this->fieldskey[$i]] = $this->setexcelfiled($i); /* set file base property */ $excelprops = $this->phpexcel->getproperties(); $excelprops->setcreator('ricky'); $excelprops->setlastmodifiedby('ricky'); $excelprops->settitle('office xls document'); $excelprops->setsubject('office xls document'); $excelprops->setdescription('document generated by phpexcel.'); $excelprops->setkeywords('office excel phpexcel'); $excelprops->setcategory('result file'); /* 处理第一个页签 */ $this->phpexcel->setactivesheetindex(0); $sheettitle = $this->rawexceldata->kind; $excelsheet = $this->phpexcel->getactivesheet(); /* 设置页签名称 */ if($sheettitle) $excelsheet->settitle($sheettitle); foreach($this->fields as $key => $field) $excelsheet->setcellvalueexplicit($this->excelkey[$key] . '1', $field, phpexcel_cell_datatype::type_string); $i = 1; foreach($this->rows as $num => $row) { $i++; foreach($row as $key => $value) { if(isset($this->excelkey[$key])) { $excelsheet->setcellvalueexplicit($this->excelkey[$key] . $i, $value, phpexcel_cell_datatype::type_string); } } } /* urlencode the filename for ie. */ $filename = $this->rawexceldata->filename; if(strpos($_server['http_user_agent'], 'msie') !== false || strpos($_server['http_user_agent'], 'trident') !== false) $filename = urlencode($filename); $excelwriter = phpexcel_iofactory::createwriter($this->phpexcel, 'excel5'); $excelwriter->setprecalculateformulas(false); if($savepath == '') { header('content-type: application/vnd.ms-excel'); header(content-disposition: attachment;filename=\{$filename}.xls\); header('cache-control: max-age=0'); $excelwriter->save('php://output'); } else { $excelwriter->save($savepath); } }}
测试代码：
测试代码中完成了excel文件的上传和下载：从本客户端择一个excel文件导入，然后完成解析后，再下载到客户端。
html代码很简单，没有css、js，只有一个file空间和一个提交按钮。如下所示：
filename:

对应的后台的代码如下：
0){ echo error: . $_files[file][error] .
; exit;}move_uploaded_file($_files[file][tmp_name], $_files[file][name]);/** * excel格式: * * 姓名 | 性别 * ----------------- * ricky | 男 * xxxxx | xxx *//* 定义要读取的列，数组的值需要和excel的每一行的标题一致或子集 */$fileds = array( 'name' => '姓名', 'sex' => '性别',);$parse = new parseexcel();/* 从上传的文件中解析出数据 */$rows = $parse->excel2array($_files[file][name], $fileds);/* 注意: 测试导入的时候，打开这个注释行，测试下载的时候需要关闭该注释行 *///output($rows);exit;/* 将数据原封不动在写入一个新的文件，供用户下载 */$data = new stdclass();/* excel的文件名 */$data->filename = 'ceshi';/* 页签的名字 */$data->kind = 'ceshi';/* excel的标题 */$data->fields = $fileds;/* 要写入的数据 */$data->rows = $rows;$parse->export2excel($data);
上述事例只是完成了一个excel的上传下载的基本功能，还有一些设置表格样式、合并单元格等没有体现在这里，大家可以参考phpexcel的手册，下面是我摘出来的phpexcel的常见的一些操作，供大家参考：
创建excel$objphpexcel = new phpexcel();创建一个worksheet$objphpexcel->createsheet();$objwriter = phpexcel_iofactory::createwriter($objexcel, 'excel5');$objwriter-save('php://output');保存excel（2007）$objwriter = new phpexcel_writer_excel2007($objphpexcel);非2007格式：$objwriter = new phpexcel_writer_excel5($objphpexcel); $objwriter->save(xxx.xlsx);直接输出到浏览器供下载header('content-type: application/vnd.ms-excel');header(content-disposition: attachment;filename=\{$filename}.xls\);header('cache-control: max-age=0');$excelwriter->save('php://output');设置excel的属性：创建人$objphpexcel->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('a1', 12);$objphpexcel->getactivesheet()->setcellvalue('a1', true);$objphpexcel->getactivesheet()->setcellvalue('a1', '=sum(c2:c4)');$objphpexcel->getactivesheet()->setcellvalue('a1', '=min(b2:c5)');合并单元格$objphpexcel->getactivesheet()->mergecells('a18:e22');分离单元格$objphpexcel->getactivesheet()->unmergecells('a28:b28');设置宽度$objphpexcel->getactivesheet()->getcolumndimension('b')->setautosize(true);$objphpexcel->getactivesheet()->getcolumndimension('d')->setwidth(12);设置字体$objphpexcel->getactivesheet()->getstyle('b1')->getfont()->setname('candara');$objphpexcel->getactivesheet()->getstyle('b1')->getfont()->setsize(20);$objphpexcel->getactivesheet()->getstyle('b1')->getfont()->setbold(true);$objphpexcel->getactivesheet()->getstyle('b1')->getfont()->setunderline(phpexcel_style_font::underline_single);$objphpexcel->getactivesheet()->getstyle('b1')->getfont()->getcolor()->setargb(phpexcel_style_color::color_white);$objphpexcel->getactivesheet()->getstyle('b1')->getfont()->setbold(true);设置对齐方式$objphpexcel->getactivesheet()->getstyle('c1')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_right);$objphpexcel->getactivesheet()->getstyle('c1')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_justify);$objphpexcel->getactivesheet()->getstyle('c1')->getalignment()->setvertical(phpexcel_style_alignment::vertical_center);设置单元格border$objphpexcel->getactivesheet()->getstyle('d1')->getborders()->gettop()->setborderstyle(phpexcel_style_border::border_thin);设置border的color$objphpexcel->getactivesheet()->getstyle('e1')->getborders()->getleft()->getcolor()->setargb('ff993300');;$objphpexcel->getactivesheet()->getstyle('e1')->getborders()->getright()->getcolor()->setargb('ff993300');$objphpexcel->getactivesheet()->getstyle('e1')->getborders()->gettop()->getcolor()->setargb('ff993300');$objphpexcel->getactivesheet()->getstyle('e1')->getborders()->getbottom()->getcolor()->setargb('ff993300');设置填充颜色$objphpexcel->getactivesheet()->getstyle('f1')->getfill()->setfilltype(phpexcel_style_fill::fill_solid);$objphpexcel->getactivesheet()->getstyle('f1')->getfill()->getstartcolor()->setargb('ff808080');加载图片$objdrawing = new phpexcel_worksheet_drawing();$objdrawing->setname('logo');$objdrawing->setdescription('logo');$objdrawing->setpath('./images/officelogo.jpg');$objdrawing->setheight(36);$objdrawing->setworksheet($objphpexcel->getactivesheet());$objdrawing = new phpexcel_worksheet_drawing();