最近在做一个项目，有大量的execl表单导入导出，模板格式也比较简单，都是一维格式。但是抵不住量多，每次写导入导出的时候都要单独写。就想着怎么偷个懒。所以就写了一下几个工具方法。
字母对应的数字(顺序A对应1)
1 /** 2 ?????* 字母对应的数字(顺序A对应1) 3 ?????* @param $char 4 ?????* @return int 5 ?????*/ 6 ????function charToNum($char) 7 ????{ 8 ????????$char = strtolower($char); 9 ????????$array = array(‘a‘, ‘b‘, ‘c‘, ‘d‘, ‘e‘, ‘f‘, ‘g‘, ‘h‘, ‘i‘, ‘j‘, ‘k‘, ‘l‘, ‘m‘, ‘n‘, ‘o‘, ‘p‘, ‘q‘, ‘r‘, ‘s‘, ‘t‘, ‘u‘, ‘v‘, ‘w‘, ‘x‘, ‘y‘, ‘z‘);10 ????????$len = strlen($char);11 ????????$sum = 0;12 ????????for ($i = 0; $i < $len; $i++) {13 ????????????$index = array_search($char[$i], $array);14 ????????????$sum += ($index + 1) * pow(26, $len - $i - 1);15 ????????}16 ????????return $sum;17 ????}数字转字母Excel列标
/** ????* 数字转字母Excel列标 ????* @param $index ????* @param int $start 是否小写 ????* @return int ????*/ ???function numToChar($index, $start = 64) ???{ ???????!$start && $start = 64; ???????$str = ‘‘; ???????$num = floor($index / 26); ???????$mod = $index % 26; ???????if (($mod > 0 && $num > 0) || $num > 1) { ???????????//$str = chr(($mod == 0 ? $num - 1 : $num) + $start); ???????????$str = numToChar(($mod == 0 ? $num - 1 : $num), $start); ???????????$index = $index - (26 * $num); ???????????$index || $index = 26; ???????????return $str . numToChar($index, $start); ???????} else { ???????????return $str . chr($index % 27 + $start); ???????} ???}测试方法：
echo charToNum("A") . " " . numToChar(charToNum("A")); ???????echo "<br/>"; ???????echo charToNum("Z"). " " . numToChar(charToNum("Z"));; ???????echo "<br/>"; ???????echo charToNum("BA"). " " . numToChar(charToNum("BA"));; ???????echo "<br/>"; ???????echo charToNum("BZ"). " " . numToChar(charToNum("BZ"));; ???????echo "<br/>"; ???????echo charToNum("ZA"). " " . numToChar(charToNum("ZA"));; ???????echo "<br/>"; ???????echo charToNum("ZZ"). " " . numToChar(charToNum("ZZ"));; ???????echo "<br/>"; ???????echo charToNum("AAA"). " " . numToChar(charToNum("AAA"));; ???????echo "<br/>"; ???????echo charToNum("AAZ"). " " . numToChar(charToNum("AAZ"));; ???????echo "<br/>"; ???????echo charToNum("ZZA"). " " . numToChar(charToNum("ZZA"));; ???????echo "<br/>"; ???????echo charToNum("ZZZ"). " " . numToChar(charToNum("ZZZ"));;结果：
1 ??A26 ??Z53 ??BA78 ??BZ677 ??ZA702 ??ZZ703 ??AAA728 ??AAZ18253 ??ZZA18278 ??ZZZ
针对上面的两个方法，如果有更好的方法，欢迎提供意见。
将表转换成数组，其中map是表头字段和要转换成的数组key的映射关系。比如：
$map = [ ??"字段1"=>"key1", ??"字段2"=>"key2",];
字段1和字段2是execl表头名称
/** ????* 表数据转化为数组 excel 低版本excel，不包括excel2007 ????* @param $file ????* @param array $map 字段映射关系 ????* @return array ????*/ ???function excelToArray($file, $map = []) ???{ ???????/*创建对象,针对Excel2003*/ ???????$objReader = \PHPExcel_IOFactory::createReader(‘Excel2007‘); ???????/*此属性不明，貌似设置为flase也可以*/ ???????$objReader->setReadDataOnly(true); ???????/*加载对象路径*/ ???????$objPHPExcel = $objReader->load($file); ???????/*获取工作表*/ ???????$objWorksheet = $objPHPExcel->getActiveSheet(); ???????//获得当前活动的工作表，即打开默认显示的那张表 ???????//$objWorksheet=$objPHPExcel->getSheet(0); ???????//也可以这样获取，读取第一个表,参数0 ???????/*得到总行数*/ ???????$highestRow = $objWorksheet->getHighestRow(); ???????/*得到总列数*/ ???????$highestColumn = $objWorksheet->getHighestColumn(); ???????$highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn); ???????/*取单元数据进数组*/ ???????$keys = []; ???????for ($col = 0; $col < $highestColumnIndex; ++$col) { ???????????$keys[] = $objWorksheet->getCellByColumnAndRow($col, 1)->getValue(); ???????} ???????for ($row = 2; $row <= $highestRow; ++$row) { ???????????for ($col = 0; $col < $highestColumnIndex; ++$col) { ???????????????$key = $keys[$col]; ???????????????if ($map && isset($map[$key])) { ???????????????????$key_map = $key = $map[$key]; ???????????????} else { ???????????????????$key_map = $key; ???????????????} ???????????????while (isset($excelData[$row][$key])) { ???????????????????$count = intval(str_replace("{$key_map}_", "", $key)) + 1; ???????????????????$key = "{$key_map}_{$count}"; ???????????????} ???????????????$value = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue(); ???????????????$excelData[$row][$key] = $value ? "{$value}" : $value; ???????????} ???????} ???????return $excelData; ???}导出execl，其中map是数组key和表头字段的映射关系，比如：
$map = [ ??"key1"=>"字段1", ??"key2"=>"字段2",];
key1和key2 必须在$rows参数中存在
???/** ????* 导出Execl ????* @param $title ????* @param $rows ????* @param $map ????* @return \PHPExcel ????*/ ???function exportExcel($title, $rows, $map) ???{ ???????$objPHPExcel = new \PHPExcel(); //实例化phpExcel类 ???????$objPHPExcel->setActiveSheetIndex(0);//设置第一个工作表为活动工作表 ???????$objActSheet = $objPHPExcel->getActiveSheet();//获取活动工作表对象 ???????//设置属性 - 标题 ???????$objPHPExcel->getProperties()->setTitle($title); ???????/** ????????* 设置每个列的值一列为A ?一行为1 则 第一行第一列为A1 ????????* 以此类推,如果列不固定就用内置函数把数字转换成字母; $col是列 $row是行 $value是值. ????????*/ ???????// 表头 ???????$header = false; ???????$row_index = 2; ???????foreach ($rows as $row) { ???????????$col_index = 1; ???????????foreach ($row as $key => $value) { ???????????????if (key_exists($key, $map)) { ???????????????????$col_letter = $this->getLetter($col_index); ???????????????????if (!$header || !$objActSheet->getCell("{$col_letter}1")->getValue()) { ???????????????????????$objActSheet->setCellValue("{$col_letter}1", $map[$key]); ???????????????????} ???????????????????$objActSheet->setCellValue("{$col_letter}{$row_index}", $value === null ? "" : $value); ???????????????????$col_index++; ???????????????} ???????????} ???????????$row_index++; ???????????$header = true; ???????} ???????$objPHPExcel->getActiveSheet()->getPageSetup()->setHorizontalCentered(true); ???????$objPHPExcel->getActiveSheet()->getPageSetup()->setVerticalCentered(false); ???????ob_end_clean(); ???????ob_start(); ???????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-excel‘); ???????header(‘Content-Type:application/octet-stream‘); ???????header(‘Content-Type:application/download‘); ???????header(‘Content-Transfer-Encoding:binary‘); ???????$date = date("YmdHis"); ???????header("Content-Disposition:attachment;filename=\"{$title}-{$date}.xls\""); ???????$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel5‘); ???????$objWriter->save(‘php://output‘); ???????ob_end_flush(); ???}PHP 导入execl的工具类方法
原文地址：https://www.cnblogs.com/yangcoder/p/10122892.html