PHPExcel 导入导出-php手册-php.cn

Home

php教程

php手册

PHPExcel 导入导出

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 am 11:36 AM

大家帮忙优化下吧

getletter方法是把数组的索引转成excel的表头
// 数字转字母 function getLetter($num) { $str = "$num"; $num = intval($num); if ($num $ret = chr(ord('A') + intval($str) - 1); } else { $first_str = chr(ord('A') + intval(floor($num / 26)) - 1); $second_str = chr(ord('A') + intval($num % 26) - 1); if ($num % 26 == 0){ $first_str = chr(ord('A') + intval(floor($num / 26)) - 2); $second_str = chr(ord('A') + intval($num % 26) + 25); } $ret = $first_str.$second_str; } return $ret; } // excel 导入 /** * 导入excel到数据库 * @param string $db 数据库表名 * @param path string 文件名（路径） * @return boolean */ function excelImport($db, $file) { import("Org.Util.PHPExcel"); $PHPExcel = new PHPExcel(); $PHPReader = new \PHPExcel_Reader_Excel2007(); if (!$PHPReader->canRead($file)) { $PHPReader = new \PHPExcel_Reader_Excel5(); if (!$PHPReader->canRead($file)){ return false; } } $E = $PHPReader->load($file); $cur = $E->getSheet(0); // 读取第一个表 $end = $cur->getHighestColumn(); // 获得最大的列数 $line = $cur->getHighestRow(); // 获得最大总行数 // 获取数据数组 $info = array(); for ($row = 1; $row for ($column = 'A'; $column $val = $cur->getCellByColumnAndRow(ord($column) - 65, $row)->getValue(); $info[$row][] = $val; } } $DB = M($db); $data = array(); for ($i = 2; $i for ($j = 0; $j for ($k = 0; $k $data[$i][$info[1][$k]] = $info[$i][$k]; } } } $datalist = array_values($data); $result = $DB->addAll($datalist); // echo $DB->getLastSql();exit; if ($result) { return true; } return false; } // 导出excel /** * 导出excel方法 * @param array $data 需要导出的数据 * @param array $title excel表头 * @param string $name 导出后的文件名 */ function excelExport ($data, $title=null, $name=null) { import("Org.Util.PHPExcel"); $PHPExcel = new PHPExcel(); if(!is_null($title)){ array_unshift($data, $title); } if(is_null($name)){ $name = time(); } foreach ($data as $k => $v) { for ($i = 1; $i $tr = getLetter($i).($k+1); if ($value == null) { $value = ''; } $buffer[$tr]=array_values($v)[$i-1]; $PHPExcel->getActiveSheet()->setCellValue($tr, array_values($v)[$i-1]); } } $PHPExcel->setActiveSheetIndex(0); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="' . $name . '.xls"'); //文件名称 header('Cache-Control: max-age=0'); $result = PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel2007'); $result->save('php://output'); }

AD：真正免费，域名+虚机+企业邮箱=0元

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn