<? require_once ('inc/PHPExcel/PHPExcel/IOFactory.php');
/**
* @author lgl
* 使用实例
* $fieldMap=['昵称'=>'username','头像'=>'photo','签名'=>'sign',"性别"=>'sex'];//header和字段映射
* $fieldAttr=['性别'=>'time'];//对应列的字段值转化类型
* $readFile= new ReadExcelBiz(dirname(__FILE__).'/3-26上传.xls',$fieldMap,$fieldAttr,3);
* $rowsNum=$readFile->getRowsNum();
* for($i=1;$i<=$rowsNum;$i++){
* print_r($readFile->getNextRow());
* }
*/
namespace PublicService\Biz;
class ReadExcelBiz
{
private $_objExcel = null;
private $_objReader = null;
private $_objCurSheet = null;
private $_charset = 'utf-8';
private $_head = array();
private $_fieldMap = array();
private $_fieldAttr = array();
private $_cols = 0;
private $_rows = 0;
private $_rowCursor = 0;
/**
* ReadExcelBiz constructor.
* @param $filepath excel文件路径
* @param array $fieldMap 字段映射
* @param array $fieldAttr 字段类型转化
* @param int $sheetindex sheet下标
* 例子
* $fieldMap=['昵称'=>'username','头像'=>'photo','签名'=>'sign',"性别"=>'sex'];
* $fieldAttr=['性别'=>'date']; 设置某列值转化的类型 目前只支持 'date' ,'datetime','time'
*/
public function __construct($filepath, $fieldMap = array(), $fieldAttr = array(), $sheetindex = 0)
{
try{
$this->_objReader = \PHPExcel_IOFactory::createReaderForFile($filepath);
$this->_objReader->setReadDataOnly(true);
$this->_objExcel = $this->_objReader->load($filepath);
$this->_objCurSheet = $this->_objExcel->getSheet($sheetindex);
$this->_rows = $this->_objCurSheet->getHighestRow();
$this->_cols = $this->char2num($this->_objCurSheet->getHighestColumn());
$this->_fieldAttr = $fieldAttr;
if(count($fieldMap) > 0 || count($fieldAttr) > 0)
{
$this->getHead();
for($i=0; $i< count($this->_head); $i++)
{
if($fieldMap[$this->_head[$i]])
$this->_fieldMap[$i] = $fieldMap[$this->_head[$i]];
if($fieldAttr[$this->_head[$i]])
$this->_fieldAttr[$i] = $fieldAttr[$this->_head[$i]];
}
}
}
catch(\Exception $e){
echo $e->getMessage();
exit;
}
}
/**
* @desc 获取excel头
* @return array|bool
*/
public function getHead()
{
$this->_rowCursor = 1;
$this->_head = $this->getRow($this->_rowCursor, false);
return $this->_head;
}
/**
* @desc 获取第一行数据
* @return array|bool
*/
public function getFirstRow()
{
return $this->getRow(1);
}
/**
* @desc 获取下一行数据
* @return array|bool
*/
public function getNextRow()
{
$this->_rowCursor++;
return $this->getRow($this->_rowCursor);
}
/**
* @desc 获取前一行数据
* @return array|bool
*/
public function getPrevRow()
{
$this->_rowCursor--;
return $this->getRow($this->_rowCursor);
}
/**
* @desc 获取行数据
* @param $index 第几行
* @param bool $useFieldMap 是否使用字段映射
* @return array|bool
*/
public function getRow($index, $useFieldMap = true)
{
if($index > $this->_rows)
return FALSE;
$row = array();
for($col = 0; $col < $this->_cols; $col++)
{
$objCell = $this->_objCurSheet->getCellByColumnAndRow($col, $index);
if($objCell->getDataType() == \PHPExcel_Cell_DataType::TYPE_FORMULA)
$value = $objCell->getCalculatedValue();
else
$value = $objCell->getFormattedValue();
$value = trim($this->decode($this->transforValue(trim($value), $col)));
if($useFieldMap && $this->_fieldMap[$col])
$row[$this->_fieldMap[$col]] = $value;
else if(!$useFieldMap || count($this->_fieldMap) == 0)
$row[$col] = $value;
}
return $row;
}
/**
* @desc 获取总列数
* @return int
*/
public function getColsNum()
{
return $this->_cols;
}
/**
* @desc 获取总行数
* @return int
*/
public function getRowsNum()
{
return $this->_rows;
}
private function char2num($pColumn)
{
$strlen = strlen($pColumn);
$columnNum = 0;
for($i = 0;$i < $strlen; $i++)
{
$str = substr($pColumn, $i, 1);
$columnNum = $columnNum * 26 + (ord($str) - ord('A') + 1);
}
return $columnNum;
}
/**
* @desc 转码
* @param $str
* @return mixed|string
*/
private function decode($str)
{
if(function_exists('mb_convert_encoding'))
return mb_convert_encoding($str, $this->_charset, "utf-8");
else
return iconv("utf-8", $this->_charset, $str);
}
/**
* @desc 单元格值转化
* @param $value
* @param $col
* @return false|string
*/
private function transforValue($value, $col)
{
$formatCode = array('date' => 'Y-m-d', 'datetime' => 'Y-m-d H:i:s', 'time' => 'H:i:s');
$dataType = $this->_fieldAttr[$col];
if($formatCode[$dataType])
{
if(is_numeric($value))
$value = gmdate($formatCode[$dataType], \PHPExcel_Shared_Date::ExcelToPHP($value));
else if($value != "")
$value = date($formatCode[$dataType], strtotime($value));
}
return $value;
}
}
<? require_once ('inc/PHPExcel/PHPExcel/IOFactory.php'); class ExcelWriter { private $_objExcel = null; private $_objActSheet = null; private $_charset = ''; private $_head = array(); private $_rows = 0; private $_excelClass = 'Excel5'; private $_excelPostfix = '.xls'; public function __construct($config = array()) { $this->_objExcel = new PHPExcel(); $this->_objActSheet = $this->setActiveSheetIndex(0); $this->_charset = isset($config['charset']) ? $config['charset'] : MYOA_CHARSET; } public function setActiveSheetIndex($index) { return $this->_objExcel->setActiveSheetIndex($index); } public function setFileName($filename) { $this->_filename = $this->encode($filename); $this->_objActSheet->setTitle($this->encode($filename)); } public function setTitle($title) { $this->_objActSheet->setTitle($this->encode($title)); } public function addHead($row) { $this->_rows++; $row = $this->row2array($row); $this->_head = $row; for($col = 0; $col < count($row); $col++) { $data = $this->encode($row[$col]); $width = ceil(strlen($data)*1.2); $style = $this->_objActSheet->getStyleByColumnAndRow($col, '1'); $align = $style->getAlignment(); $align->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER); $align->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $this->_objActSheet->getColumnDimensionByColumn($col)->setWidth($width); $this->_objActSheet->setCellValueExplicitByColumnAndRow($col, $this->_rows, $data, PHPExcel_Cell_DataType::TYPE_STRING); } } public function addRow($row) { $this->_rows++; $row = $this->row2array($row); for($col = 0; $col < count($row); $col++) { $data = $row[$col]; if(substr($data, 0, 1) == '"' && substr($data, -1) == '"') $data = substr($data, 1, -1); $data = $this->encode($data); $this->_objActSheet->setCellValueExplicitByColumnAndRow($col, $this->_rows, $data, PHPExcel_Cell_DataType::TYPE_STRING); } } public function Save() { ob_end_clean(); Header("Cache-control: private"); Header("Content-type: application/vnd.ms-excel"); Header("Accept-Ranges: bytes"); Header("Accept-Length: ".ob_get_length()); Header("Content-Disposition: attachment; ".$this->get_filename($this->_filename.$this->_excelPostfix)); $objWriter = PHPExcel_IOFactory::createWriter($this->_objExcel, $this->_excelClass); $objWriter->save("php://output"); } private function encode($str) { if(function_exists('mb_convert_encoding')) return mb_convert_encoding($str, "utf-8", $this->_charset); else return iconv($this->_charset, "utf-8", $str); } private function row2array($row) { if(is_array($row)) return $row; $delimiter = ','; $enclosure = '"'; $expr_field = '/'.$delimiter.'(?=(?:[^'.$enclosure.']*'.$enclosure.'[^'.$enclosure.']*'.$enclosure.')*(?![^'.$enclosure.']*'.$enclosure.'))/'; $row = preg_split($expr_field, trim($row)); $row = preg_replace(array('/"(.*)"$/s','/""/s',"/\<\?/s"), array('$1','"',"<?_("), $row); return $row; } private function get_filename($filename) { $filename_encoded = str_replace("+", "%20", urlencode($filename)); if (preg_match("/msie|trident/i", $_SERVER['HTTP_USER_AGENT'])) return 'filename="' . $filename_encoded . '"'; else if (preg_match("/Firefox/", $_SERVER['HTTP_USER_AGENT'])) return 'filename*="utf8\'\'' . $filename . '"'; else return 'filename="' . $filename . '"'; } } ?>