?ㄧ?绔??朵?涓??缁?甯镐????扮?????藉氨???版????瀵煎?ュ??瀵煎?猴?PHP瀵煎?ュ??哄???界??瀹??颁?绠?寰?澶???锛???涓烘??涓?涓??杈?濂界????浠?HPEXCEL??
??杩????颁?涓????锛???hpExcel 瀵煎?烘?版?????跺?????版?ラ?? Invalid cell coordinate 涓??村?ㄧ?ㄨ?涓???浠舵病?????拌?杩??风?????锛?缃?涓??句?涓?涓????ユ????版??ぇ浜?26?????跺?? 26涓??姣??ㄥ?浜???瑕?瀵瑰???拌?琛?澶??? 澶т?26搴?璇?A,AB,AC,AD…????涓??汇??PHPExcel?版????烘?瑰????琛?涓?琛???寰??????
姣?濡?锛?绗??琛?????棰?锛?杈??虹???跺??浠?1,B1,C1,D1…AA1,BB1,CC1椤哄?寰?????
**
* PHPExcel浣跨?ㄧ被
* +------------------------------------浣跨??-----------------------------------+
* 瀵煎??xcel琛?br />
* vendor('PHPExcel.PHPExcelUser');
* $file='./cs.xlsx';
* $excel=new \PHPExcelUser($file);
* $data=$excel->excelImport(1);//浠?绗??琛?璇诲??,濡???????瀛?绗︿覆???芥?扮??璇???浼?杩???涓?涓???璇????扮?
* 瀵煎??xcel琛?br />
* vendor('PHPExcel.PHPExcelUser');
* $file='./cs.xlsx';
* $excel=new \PHPExcelUser($file);
* $excel->excelExport($data=array(),$title=array());
* +------------------------------------浣跨??-----------------------------------+
* Class FileTemp
*/
class PHPExcelUser {
public $excelFilename; //excel瀵煎?ユ??瀵煎?虹????浠剁????绉?br />
public $strCheck; //???﹁?琛?瀵煎?ュ?ㄩ?ㄥ?绗︿覆楠?璇?
public $funCheck; //???﹁?琛?瀵煎?ュ?芥?伴?璇?
public $errorArr = array(); //褰???瑕?妫??ュ苟涓?????璇??惰???????璇??璁℃?扮?
/**
* ?????芥??br />
* @param $excelFilename ??浠跺??绉?br />
* @param bool $strCheck ???﹁?琛?瀛?绗︿覆楠?璇?妫?娴?
* @param bool $funCheck ???﹁?琛??芥?伴?璇?妫?娴?
*/
public function __construct($excelFilename, $strCheck = false, $funCheck = false) {
if (!$excelFilename) {
try {
$error = '璇疯???xcel??浠跺??绉?#39;;
throw new Exception($error);
} catch (Exception $e) {
echo $e->getMessage();
}
}
$this->excelFilename = $excelFilename;;
$this->strCheck = $strCheck;
$this->funCheck = $funCheck;
}
/**
* 瀵煎?烘??Excel??浠?br />
* @param array $data 涓?涓??缁存?扮?,缁???濡???浠??版????ュ?烘?ョ???扮?
* @param array $title excel??绗??琛???棰?,涓?涓??扮?,濡???涓虹┖??娌℃????棰?
* @exapme
* $arr = $Model -> select();
* excelExport($arr,array('id','璐︽??#39;,'瀵???','?电О'),'??浠跺??');
*/
function excelExport($data = array(), $title = array()) {
require_once('PHPExcel.php');
error_reporting(E_ALL);
date_default_timezone_set('Europe/London');
$objPHPExcel = new PHPExcel();
/*浠ヤ????浜?璁剧疆 锛?浠?涔?浣??? ??棰???涔?绫荤??*/
$objPHPExcel->getProperties()->setCreator($this->excelFilename)
->setLastModifiedBy($this->excelFilename)
->setTitle("?版??XCEL瀵煎??quot;)
->setSubject("?版??XCEL瀵煎??quot;)
->setDescription("澶?浠芥?版??quot;)
->setKeywords("excel")
->setCategory("result file");
/*浠ヤ?灏辨???澶???Excel?????版??? 妯??????版???涓昏????涓?姝ワ??朵??烘???戒?瑕???/
$model = $objPHPExcel->setActiveSheetIndex(0);
$key = 65; //????A-Z
$key2 = ord("@");//@--64
foreach ($title as $k => $v) {
if($key>ord("Z")){
$key2 += 1;
$key = ord("A");
$colum = chr($key2).chr($key);//瓒?杩?26涓??姣??舵??浼????
}else{
if($key2>=ord("A")){
$colum = chr($key2).chr($key);//瓒?杩?26涓??姣??舵??浼????
}else{
$colum = chr($key);
}
}
$model->setCellValue($colum . '1', $v);
$key++;
}
$column = 2; //浠?绗??琛?寮?濮?
foreach($data as $key => $rows){ //琛????
$span = ord("A");
$span2 = ord("@");
foreach ($rows as $m => $n) {
foreach($title as $k=>$v){
if($span>ord("Z")){
$span2 += 1;
$span = ord("A");
$j = chr($span2).chr($span);//瓒?杩?26涓??姣??舵??浼????
}else{
if($span2>=ord("A")){
$j = chr($span2).chr($span);
}else{
$j = chr($span);
}
}
}
//dump($j.$column.'|'.$n);
$model->setCellValue($j . $column, $n);
//?版???绗??琛?寮?濮? A2,B2,C2,D2… 寮?濮?寰???br />
$span++;
}
$column++;
}
//exit;
$objPHPExcel->getActiveSheet()->setTitle('siteape');
$objPHPExcel->setActiveSheetIndex(0);
header('Content-Type: applicationnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $this->excelFilename . '.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
}
/**
* Excel璇诲??
* @param $begin 寮?濮?璇诲????琛???br />
* @return array|string
*/
function excelImport($begin) {
require_once('PHPExcel.php');
$filename = $this->getRealFile($this->excelFilename);
//寤虹?reader瀵硅薄
$PHPReader = new PHPExcel_Reader_Excel2007();
if (!$PHPReader->canRead($filename)) {
$PHPReader = new PHPExcel_Reader_Excel5();
if (!$PHPReader->canRead($filename)) {
return array();
}
}
//寤虹?excel瀵硅薄锛?姝ゆ?朵??冲??互??杩?excel瀵硅薄璇诲????浠讹?涔???互??杩?瀹????ユ??浠?br />
$PHPExcel = $PHPReader->load($filename);
/*璇诲??excel??浠朵腑??绗??涓?伐浣?琛?/
$currentSheet = $PHPExcel->getSheet(0);
/*??寰???澶х??????/
$allColumn = $currentSheet->getHighestColumn();
/*??寰?涓??辨??澶?灏?琛?*/
$allRow = $currentSheet->getHighestRow();
$returnCell = '';
//寰??????姣?涓??????肩????瀹广??娉ㄦ??琛?浠?1寮?濮?锛???浠?A寮?濮?
for ($rowIndex = $begin; $rowIndex <= $allRow; $rowIndex++) {
for ($colIndex = 'A'; $colIndex <= $allColumn; $colIndex++) {
$addr = $colIndex . $rowIndex;
$cell = $currentSheet->getCell($addr)->getCalculatedValue();
if ($cell instanceof PHPExcel_RichText) {
//瀵?????浆?㈠?绗︿覆
$returnCell[$rowIndex][$colIndex] = $cell->__toString();
} else {
$returnCell[$rowIndex][$colIndex] = $cell;
}
}
}
return $returnCell;
}
/**
* ?峰??姝ｇ‘??璺??
* @param $file
* @return string
*/
public function getRealFile($file) {
$file = $file ? $file : $this->excelFilename;
$error = '';
if (substr($file, 0, 4) == 'http') {
$realfile = $file;
} elseif (!$file) {
$error = '璇锋??瀹???浠惰矾寰?';
} elseif (file_exists($file)) {
$realfile = $file;
} elseif (file_exists('.' . $file)) {
$realfile = '.' . $file;
} elseif (file_exists('..' . $file)) {
$realfile = '..' . $file;
} else {
$error = '?ㄨ??ョ????浠朵?瀛???#39;;
}
if ($error != '') {
try {
throw new Exception($error);
} catch (Exception $e) {
echo $e->getMessage();
}
} else {
return $realfile;
}
}
/**
* ?规???版???瀛?娈靛?归????瀹圭??????????琛ㄥご
* @param $titleArr 浜?缁存?扮?锛?field=>title
* @param $data
* @return array
*/
public function getArrayTitle($titleArr, $data) {
$reData = array();
$firstData = $data[0]; //????瑕???绗???℃?版???冲??br />
if ($firstData) {
foreach ($firstData as $k => $v) {
$title = $titleArr[$k];
$reData[] = $title;
}
} else {
//?存?ヨ??ラ?璁ら『搴???title
foreach ($titleArr as $k => $v) {
$reData[] = $v;
}
}
return $reData;
}
}
濡?娌＄?规?娉ㄦ??锛???绔???涓烘?圭淮缃?缁?????,杞?浇璇锋敞???ヨ??ttps:/news/5437.html
