php如何读取excel合并单元格格式化,phpexcel导入合并表格excel的操作

处理合并单元格的excel的操作，搜到一篇文章做参考

/**

+----------------------------------------------------------

* Import Excel | 2013.08.23

* Author:HongPing

+----------------------------------------------------------

* @param $file upload file $_FILES

+----------------------------------------------------------

* @return array array("error","message")

+----------------------------------------------------------

*/

public function importExecl($file){

if(!file_exists($file)){

return array("error"=>0,'message'=>'file not found!');

}

Vendor("PHPExcel.PHPExcel.IOFactory");

$objReader = PHPExcel_IOFactory::createReader('Excel5');

try{

$PHPReader = $objReader->load($file);

}catch(Exception $e){}

if(!isset($PHPReader)) return array("error"=>0,'message'=>'read error!');

$allWorksheets = $PHPReader->getAllSheets();

$i = 0;

foreach($allWorksheets as $objWorksheet){

$sheetname=$objWorksheet->getTitle();

$allRow = $objWorksheet->getHighestRow();//how many rows

$highestColumn = $objWorksheet->getHighestColumn();//how many columns

$allColumn = PHPExcel_Cell::columnIndexFromString($highestColumn);

$array[$i]["Title"] = $sheetname;

$array[$i]["Cols"] = $allColumn;

$array[$i]["Rows"] = $allRow;

$arr = array();

$isMergeCell = array();

foreach ($objWorksheet->getMergeCells() as $cells) {//merge cells

foreach (PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) {

$isMergeCell[$cellReference] = true;

}

}

for($currentRow = 1 ;$currentRow<=$allRow;$currentRow++){

$row = array();

for($currentColumn=0;$currentColumn

$cell =$objWorksheet->getCellByColumnAndRow($currentColumn, $currentRow);

$afCol = PHPExcel_Cell::stringFromColumnIndex($currentColumn+1);

$bfCol = PHPExcel_Cell::stringFromColumnIndex($currentColumn-1);

$col = PHPExcel_Cell::stringFromColumnIndex($currentColumn);

$address = $col.$currentRow;

$value = $objWorksheet->getCell($address)->getValue();

if(substr($value,0,1)=='='){

return array("error"=>0,'message'=>'can not use the formula!');

exit;

}

if($cell->getDataType()==PHPExcel_Cell_DataType::TYPE_NUMERIC){

$cellstyleformat=$cell->getParent()->getStyle( $cell->getCoordinate() )->getNumberFormat();

$formatcode=$cellstyleformat->getFormatCode();

if (preg_match('/^([$[A-Z]*-[0-9A-F]*])*[hmsdy]/i', $formatcode)) {

$value=gmdate("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($value));

}else{

$value=PHPExcel_Style_NumberFormat::toFormattedString($value,$formatcode);

}

}

if($isMergeCell[$col.$currentRow]&&$isMergeCell[$afCol.$currentRow]&&!empty($value)){

$temp = $value;

}elseif($isMergeCell[$col.$currentRow]&&$isMergeCell[$col.($currentRow-1)]&&empty($value)){

$value=$arr[$currentRow-1][$currentColumn];

}elseif($isMergeCell[$col.$currentRow]&&$isMergeCell[$bfCol.$currentRow]&&empty($value)){

$value=$temp;

}

$row[$currentColumn] = $value;

}

$arr[$currentRow] = $row;

}

$array[$i]["Content"] = $arr;

$i++;

}

spl_autoload_register(array('Think','autoload'));//must, resolve ThinkPHP and PHPExcel conflicts

unset($objWorksheet);

unset($PHPReader);

unset($PHPExcel);

unlink($file);

return array("error"=>1,"data"=>$array);

}

2018.10.17 / 热度：1070 / 分类： php

发表评论：

昵称

邮件地址 (选填)

个人主页 (选填)