后端开发|php教程
基于PHPExcel库的EXCEL导入导出
后端开发-php教程
PHPExcel类是php一个excel表格处理插件，最近由于工作需要用到了这个插件，完成了一个excel导入导入的功能。现在将主要逻辑整理一下和大家分享，有需要的可以参考一下。
ecshop酒店预定源码,ubuntu炫酷屏保,img相对路径tomcat,爬虫好养活吗,php中如何上传图片的类型,红域名seolzw
下面的事例只是完成了一个excel的上传下载的基本功能，还有一些设置表格样式、合并单元格等操作没有体现在这里，大家可以参考PHPExcel的手册，事例后面是我摘出来的PHPExcel的常见的一些操作，供大家参考：
dll文件源码,vscode怎么弄出一个太极,ubuntu 重新下载,项目添加tomcat下运行,golang爬虫监控,php除了做网页还,一星期能学会seo吗lzw
将PHPExcel提供的API根据需要封装成一个供上传和下载使用的类，其实就是两个函数，一个用于上传一个用于下载，这里我就直接将代码贴出来了。
qq教程网站整站源码,vscode设置不了中文,ubuntu 编译sdl,宝塔启动tomcat项目,手机查询sqlite数据库,电动爬虫机器人平面图,简单php博客,亚马逊seo是什么排名,建站网站模板,一屏展示网站模板下载lzw
canRead($file)) { /* 如果PHPExcel_Reader_Excel无法读取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( icky); $excelProps->setLastModifiedBy( icky); $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空间和一个提交按钮。如下所示：
对应的后台的代码如下：
<?php function output($var){ echo ""; print_r($var); echo "";}include_once parseexcel.class.php;/* 处理上传的文件 */if ($_FILES["file"]["error"] > 0){ echo "Error: " . $_FILES["file"]["error"] . "
"; exit;}move_uploaded_file($_FILES["file"]["tmp_name"], $_FILES["file"]["name"]);/** * excel格式: * * 姓名 | 性别 * ----------------- * ricky | 男 * xxxxx | xxx *//* 定义要读取的列，数组的值需要和excel的每一行的标题一致或子集 */$fileds = array( ame => 姓名, 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（）$objWriter = new PHPExcel_Writer_Excel($objPHPExcel);非格式：$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 XLSX Test Document");题目$objPHPExcel->getProperties()->setSubject("Office XLSX Test Document");描述$objPHPExcel->getProperties()->setDescription("Test document for Office XLSX, generated using PHP classes.");关键字$objPHPExcel->getProperties()->setKeywords("office 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();
