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