php判断上传的Excel文件中是否有图片及PHPExcel库认识_php技巧_脚本之家

需要借助PHPExcel这个库复制代码
代码如下:$excelPath = ‘Test.xls’; $objReader =
PHPExcel_IOFactory::createReader; $objReader->setReadDataOnly;
$objPHPExcel = $objReader->load; $currentSheet =
$objPHPExcel->getActiveSheet(); $AllImages=
$currentSheet->getDrawingCollection(); if > 0) { //处理 }

$objPHPExcel->getActiveSheet();//以二维数组形式返回该表格的数据$sheetData

$curSheet->toArray;var_dump;如果在读取文件之前,文件格式未知,你可以通过IOFactory

identify()方法得到文件类型,然后通过createReader()方法去穿件阅读器。复制代码 代码如下:$inputFileName =
‘./sampleData/example1.xls’;/** 确定输入文件的格式 **/$inputFileType
= PHPExcel_IOFactory::identify;/** 穿件相对应的阅读器 **/$objReader
= PHPExcel_IOFactory::createReader;/** 加载要读取的文件
**/$objPHPExcel =
$objReader->load;2.设置Excel的读取选项在使用load()方法加载文件之前,可以设置读取选项来控制load的行为.2.1.ReadingOnly
Data from a Spreadsheet
FilesetReadDataOnly()方法,配置阅读器不关注表格数据的数据类型,都以string格式返回复制代码 代码如下:$inputFileType =
‘Excel5’;$inputFileName = ‘./sampleData/example1.xls’;/** Create a new
Reader of the type defined in $inputFileType **/$objReader =
PHPExcel_IOFactory::createReader;/** 配置单元格数据都以字符串返回
**/$objReader->setReadDataOnly;/** Load $inputFileName to a
PHPExcel Object **/$objPHPExcel = $objReader->load;$sheetData
=$objPHPExcel->getActiveSheet()->toArray;var_dump;返回数据:复制代码 代码如下:array { [“A”]=> string
“Integer Numbers” [“B”]=> string”123″ [“C”]=> string”234″
[“D”]=> string”-345″ [“E”]=> string”456″ [“F”]=> NULL }
[2]=> array { [“A”]=> string “Floating PointNumbers”
[“B”]=> string “1.23” [“C”]=> string “23.45” [“D”]=>
string “0.00E+0.00” [“E”]=> string “-45.68” [“F”]=> string
“£56.78” } [3]=> array { [“A”]=> string “Strings” [“B”]=>
string “Hello” [“C”]=> string “World” [“D”]=> NULL
[“E”]=> string “PHPExcel” [“F”]=> NULL } [4]=> array {
[“A”]=> string “Booleans” [“B”]=> bool [“C”]=> bool
[“D”]=> NULL [“E”]=> NULL [“F”]=> NULL } [5]=> array
{ [“A”]=> string “Dates” [“B”]=> string “19 December 1960”
[“C”]=> string “10 October 2010” [“D”]=> NULL [“E”]=>
NULL [“F”]=> NULL } [6]=> array { [“A”]=> string “Times”
[“B”]=> string “9:30” [“C”]=> string “23:59” [“D”]=> NULL
[“E”]=> NULL [“F”]=> NULL } [7]=> array { [“A”]=>
string “Formulae” [“B”]=> string “468” [“C”]=> string
“-20.998” [“D”]=> NULL [“E”]=> NULL [“F”]=> NULL }
[8]=> array { [“A”]=> string “Errors” [“B”]=> string
“#N/A” [“C”]=> string “#DIV/0!” [“D”]=> NULL [“E”]=>
NULL [“F”]=> NULL }}如果不设置则返回:array { [“A”]=>
string “Integer Numbers” [“B”]=> float [“C”]=> float
[“D”]=> float [“E”]=> float [“F”]=> NULL } [2]=>
array { [“A”]=> string “Floating Point Numbers” [“B”]=> float
[“C”]=> float [“D”]=> float [“E”]=> float [“F”]=>
float } [3]=> array { [“A”]=> string “Strings” [“B”]=>
string “Hello” [“C”]=> string “World” [“D”]=> NULL
[“E”]=> string “PHPExcel” [“F”]=> NULL } [4]=> array {
[“A”]=> string “Booleans” [“B”]=> bool [“C”]=> bool
[“D”]=> NULL [“E”]=> NULL [“F”]=> NULL } [5]=> array
{ [“A”]=> string “Dates” [“B”]=> float [“C”]=> float
[“D”]=> NULL [“E”]=> NULL [“F”]=> NULL } [6]=> array
{ [“A”]=> string “Times” [“B”]=> float [“C”]=> float
[“D”]=> NULL [“E”]=> NULL [“F”]=> NULL } [7]=> array
{ [“A”]=> string “Formulae” [“B”]=> float [“C”]=> float
[“D”]=> NULL [“E”]=> NULL [“F”]=> NULL } [8]=> array
{ [“A”]=> string “Errors” [“B”]=> string “#N/A” [“C”]=>
string “#DIV/0!” [“D”]=> NULL [“E”]=> NULL [“F”]=> NULL
}}Reading Only Data from a SpreadsheetFile applies to Readers: Excel2007
YES Excel5 YES Excel2003XML YES OOCalc YES SYLK NO Gnumeric YES CSV NO

2.2.ReadingOnly Named WorkSheets from a
FilesetLoadSheetsOnly(),设置要读取的worksheet,接受worksheet的名称作为参数。复制代码 代码如下:/** PHPExcel_IOFactory
*/include’PHPExcel/IOFactory.php’;$inputFileType = ‘Excel5’;//
$inputFileType = ‘Excel2007’;// $inputFileType = ‘Excel2003XML’;//
$inputFileType = ‘OOCalc’;// $inputFileType = ‘Gnumeric’;$inputFileName
=’./sampleData/example1.xls’;$sheetname = ‘Data Sheet #2’;echo ‘Loading
file’,pathinfo($inputFileName,PATHINFO_BASENAME),’ using IOFactory with
a definedreader type of ‘,$inputFileType,’
‘;$objReader = PHPExcel_IOFactory::createReader;echo ‘Loading
Sheet”‘,$sheetname,'” only
‘;$objReader->setLoadSheetsOnly;$objPHPExcel
=$objReader->load;echo ‘


‘;echo$objPHPExcel->getSheetCount(),’
worksheet’,(($objPHPExcel->getSheetCount,’ loaded

‘;$loadedSheetNames
=$objPHPExcel->getSheetNames();foreach($loadedSheetNames
as$sheetIndex => $loadedSheetName) { echo $sheetIndex,’ ->
‘,$loadedSheetName,’
‘;}如果想读取多个worksheet,可以传递一个数组复制代码 代码如下:$inputFileType = ‘Excel5’;
$inputFileName = ‘./sampleData/example1.xls’; $sheetnames = array(‘Data
Sheet #1′,’Data Sheet #3’); /** Create a new Reader of the type
defined in $inputFileType **/$objReader =
PHPExcel_IOFactory::createReader; /** Advise the Reader of which
WorkSheets we want to load **/$objReader->setLoadSheetsOnly; /**
Load $inputFileName to a PHPExcel Object **/$objPHPExcel =
$objReader->load;如果想读取所有worksheet,可以调用setLoadAllSheets()。

很早之前就知道有一个叫做PHPExcel的类可以用来操作Excel,一直没有机会尝试,今天试用发现无比强大,下载后的源码包里有详细文档,几乎能实现手工操作Excel能实现的一切功能。
一个简单的读取Excel的例子如下: 复制代码
代码如下: $inputFileType = ‘Excel2007’; $inputFileName =
‘./public/files/import_user_template.xlsx’; $sheetname = ‘Sheet1’;
//指定Excel类型,创建一个reader $objReader =
PHPExcel_IOFactory::createReader; //设置只读取数据,不包括公式和格式
$objReader->setReadDataOnly; //只读取指定的sheet
$objReader->setLoadSheetsOnly; $objPHPExcel = $objReader->load;
$curSheet = $objPHPExcel->getSheet; //包含数据的最大列 $allColumn =
$curSheet->getHighestColumn(); //包含数据的最大行 $allRow =
$curSheet->getHighestRow(); for($currentRow = 1; $currentRow <=
$allRow; $currentRow++){ for($currentCol = ‘A’; $currentCol <=
$allColumn; $currentCol++){ echo
$curSheet->getCell($currentCol.$currentRow)->getValue().”\t”; }
echo “\r\n”; }
要在ThinkPHP中使用,把源码包中的Classes目录复制到ThinkPHP的Vendor目录下,改名为PHPExcel,然后调用Vendor方法载入
复制代码 代码如下: vendor;
可是这样一来发现读取Excel以后再调用M或者D方法实例化模型类时报找不到Model类的错误,经过研究发现是自动装载机制冲突,要解决冲突,需要在M或者D方法调用之前使用spl_autoload_register函数重新注册autoloader类
复制代码 代码如下:
spl_autoload_register(array;
在ThinkPHP中调用PHPExcel的问题解决方案
在ThinkPHP中调用PHPExcel时,数据可以完全读出来,但是下一步D,M或调用模板的时候会出错。
经过研究,终于找到了解决方法。和大家分享一下。呵呵!
1,首先下载PHPExcel的包,放在 ThinkPHP/Vendor/下。 2,调用函数。 复制代码 代码如下: protected function
Import_Execl{ if{ return array; } Vendor; $PHPExcel = new PHPExcel();
$PHPReader = new PHPExcel_Reader_Excel2007();
if(!$PHPReader->canRead{ $PHPReader = new PHPExcel_Reader_Excel5();
if(!$PHPReader->canRead{ return array; } } $PHPExcel =
$PHPReader->load; $SheetCount = $PHPExcel->getSheetCount();
for($i=0;$igetSheet; $allColumn =
$this->ExcelChange($currentSheet->getHighestColumn; $allRow =
$currentSheet->getHighestRow(); $array[$i][“Title”] =
$currentSheet->getTitle(); $array[$i][“Cols”] = $allColumn;
$array[$i][“Rows”] = $allRow; $arr = array(); for($currentRow = 1
;$currentRow<=$allRow;$currentRow++){ $row = array();
for($currentColumn=0;$currentColumngetCellByColumnAndRow($currentColumn,$currentRow)->getValue();
} $arr[$currentRow] = $row; } $array[$i][“Content”] = $arr; }
spl_autoload_register(array;//必须的,不然ThinkPHP和PHPExcel会冲突
unset; unset; unset; unlink; return
array(“error”=>0,”data”=>$array); } protected function
ExcelChange{//配合Execl批量导入的函数 $len = strlen-1; $num = 0; for{
$num += *pow; } return $num; } 3,调用。 复制代码 代码如下: public function import(){
if(isset && ($_FILES[“import”][“error”] == 0)){ $result =
$this->Import_Execl($_FILES[“import”][“tmp_name”]);
if($this->Execl_Error[$result[“error”]] == 0){ $execl_data =
$result[“data”][0][“Content”]; unset; $data = D;
foreach($execl_data as $k=>$v){ $d[“serial_no”] = $v[0];
$d[“check_no”] = $v[1]; $d[“work_no”] = $v[2];
$d[“class_name”] = $v[3]; $d[“user_name”] = $v[4];
$d[“new_class”] = $v[5]; $d[“error_level”] = $v[6];
$data->data; }
$this->success($this->Execl_Error[$result[“error”]]); }else{
$this->error($this->Execl_Error[$result[“error”]]); } }else{
$this->error; } } 4,错误数据: 复制代码 代码如下: protected $Execl_Error =
array(“数据导入成功”,”找不到文件”,”Execl文件格式不正确”);

=$objPHPExcel->getActiveSheet();//以二维数组形式返回该表格的数据$sheetData

$curSheet->toArray;var_dump;也可以用PHPExcel_IOFactory的createReader方法去得到一个Reader对象,无需知道要读取文件的格式。复制代码 代码如下:$inputFileType = ‘Excel5’;//
$inputFileType = ‘Excel2007’;// $inputFileType = ‘Excel2003XML’;//
$inputFileType = ‘OOCalc’;// $inputFileType = ‘SYLK’;// $inputFileType =
‘Gnumeric’;// $inputFileType = ‘CSV’;$inputFileName =
‘./sampleData/example1.xls’;/** Create a new Reader of the type
defined in $inputFileType **/$objReader =
PHPExcel_IOFactory::createReader;/** Load $inputFileName to a
PHPExcel Object **/$objPHPExcel =
$objReader->load;//得到当前活动sheet$curSheet =

excel的写入与生成操作:复制代码
代码如下:include ‘PHPExcel.php’;include
‘PHPExcel/Writer/Excel2007.php’;//或者include
‘PHPExcel/Writer/Excel5.php’; 用于输出.xls的include
‘PHPExcel/IOFactory.php’;//phpexcel工厂类//创建一个excel$objPHPExcel =
new PHPExcel();//保存excel—2007格式$objWriter = new
PHPExcel_Writer_Excel2007;//也可以使用//$objWriter =
PHPExcel_IOFactory::createWriter($objPHPExcel,
“Excel2007”);//或者$objWriter = new PHPExcel_Writer_Excel5;
非2007格式$objWriter->save;//直接输出到浏览器$objWriter = new
PHPExcel_Writer_Excel5;header;header;header(“Cache-Control:must-revalidate,
post-check=0,
pre-check=0″);header(“Content-Type:application/force-download”);header(“Content-Type:application/vnd.ms-execl”);header(“Content-Type:application/octet-stream”);header(“Content-Type:application/download”);;header(‘Content-Disposition:attachment;filename=”resume.xls”‘);header(“Content-Transfer-Encoding:binary”);$objWriter->save;//直接生成文件$objWriterr->save;//设置excel的属性://创建人$objPHPExcel->getProperties()->setCreator;//最后修改人$objPHPExcel->getProperties()->setLastModifiedBy;//标题$objPHPExcel->getProperties()->setTitle(“Office
2007 XLSX Test
Document”);//题目$objPHPExcel->getProperties()->setSubject(“Office
2007 XLSX Test
Document”);//描述$objPHPExcel->getProperties()->setDescription(“Test
document for Office 2007 XLSX, generated using PHP
classes.”);//关键字$objPHPExcel->getProperties()->setKeywords(“office
2007 openxml
php”);//种类$objPHPExcel->getProperties()->setCategory;//设置当前的sheet$objPHPExcel->setActiveSheetIndex;//设置sheet的name$objPHPExcel->getActiveSheet;//设置单元格的值$objPHPExcel->getActiveSheet()->setCellValue;$objPHPExcel->getActiveSheet()->setCellValue;$objPHPExcel->getActiveSheet()->setCellValue;$objPHPExcel->getActiveSheet()->setCellValue;$objPHPExcel->getActiveSheet()->setCellValue;//合并单元格$objPHPExcel->getActiveSheet()->mergeCells;//分离单元格$objPHPExcel->getActiveSheet()->unmergeCells;//保护cell$objPHPExcel->getActiveSheet->setSheet;
// Needs to be set to true in order to enable any worksheet
protection!$objPHPExcel->getActiveSheet()->protectCells;//设置格式//
Set cell number formatsecho date . ” Set cell number
formats\n”;$objPHPExcel->getActiveSheet->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);$objPHPExcel->getActiveSheet()->duplicateStyle(
$objPHPExcel->getActiveSheet, ‘E5:E13’ );//设置宽width// Set column
widths$objPHPExcel->getActiveSheet()->getColumnDimension->setAutoSize;$objPHPExcel->getActiveSheet()->getColumnDimension;//设置font$objPHPExcel->getActiveSheet->getFont;$objPHPExcel->getActiveSheet->getFont;$objPHPExcel->getActiveSheet->getFont;$objPHPExcel->getActiveSheet->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);$objPHPExcel->getActiveSheet->getFont->setARGB(PHPExcel_Style_Color::COLOR_WHITE);$objPHPExcel->getActiveSheet->getFont->setARGB(PHPExcel_Style_Color::COLOR_WHITE);$objPHPExcel->getActiveSheet->getFont;$objPHPExcel->getActiveSheet->getFont;//设置align$objPHPExcel->getActiveSheet->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);$objPHPExcel->getActiveSheet->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);$objPHPExcel->getActiveSheet->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);$objPHPExcel->getActiveSheet->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);//垂直居中$objPHPExcel->getActiveSheet->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);设置column的border$objPHPExcel->getActiveSheet->getBorders->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);$objPHPExcel->getActiveSheet->getBorders->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);$objPHPExcel->getActiveSheet->getBorders->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);$objPHPExcel->getActiveSheet->getBorders->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);$objPHPExcel->getActiveSheet->getBorders->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);设置border的color$objPHPExcel->getActiveSheet->getBorders->getColor()->setARGB;$objPHPExcel->getActiveSheet->getBorders->getColor()->setARGB;$objPHPExcel->getActiveSheet->getBorders->getColor()->setARGB;$objPHPExcel->getActiveSheet->getBorders->getColor()->setARGB;$objPHPExcel->getActiveSheet->getBorders->getColor()->setARGB;$objPHPExcel->getActiveSheet->getBorders->getColor()->setARGB;设置填充颜色$objPHPExcel->getActiveSheet->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);$objPHPExcel->getActiveSheet->getFill->setARGB;$objPHPExcel->getActiveSheet->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);$objPHPExcel->getActiveSheet->getFill->setARGB;//加图片$objDrawing
= new
PHPExcel_Worksheet_Drawing();$objDrawing->setName;$objDrawing->setDescription;$objDrawing->setPath(‘./images/officelogo.jpg’);$objDrawing->setHeight;$objDrawing->setWorksheet($objPHPExcel->getActiveSheet;$objDrawing
= new
PHPExcel_Worksheet_Drawing();$objDrawing->setName;$objDrawing->setDescription;$objDrawing->setPath;$objDrawing->setCoordinates;$objDrawing->setOffsetX;$objDrawing->setRotation;$objDrawing->getShadow;$objDrawing->getShadow;$objDrawing->setWorksheet($objPHPExcel->getActiveSheet;//处理中文输出问题//需要将字符串转化为UTF-8编码,才能正常输出,否则中文字符将输出为空白,如下处理:$str
= iconv(‘gb2312’, ‘utf-8’,
$str);或者你可以写一个函数专门处理中文字符串:function convertUTF8)
return ”; return iconv(‘gb2312’, ‘utf-8’,
$str);}读取excel1.导入一个Excel最简单的方法是使用PHPExel的IO
Factory,调用PHPExcel_IOFactory类的静态法load,它可以自动识别文档格式,包括Excel2007、Excel2003XML、OOCalcSYLK、Gnumeric、CSV。返回一个PHPExcel的实例。复制代码
代码如下://加载工厂类include’PHPExcel/IOFactory.php’;//要读取的xls文件路径$inputFileName
= ‘./sampleData/example1.xls’;/**
用PHPExcel_IOFactory的load方法得到excel操作对象 **/$objPHPExcel =
PHPExcel_IOFactory::load;//得到当前活动表格,调用toArray方法,得到表格的二维数组$sheetData
=$objPHPExcel->getActiveSheet()->toArray;var_dump;1.创建一个ExcelReader去加载一个Excel文档如果你知道这个Excel文档的格式,可以建立一个相应的Reader去加载要读取的Excel文档。但是如果你加载了错误的文档类型,可会产生不可预知的错误。复制代码 代码如下:$inputFileName =
‘./sampleData/example1.xls’;/** Create a new Excel5 Reader
**/$objReader = new PHPExcel_Reader_Excel5();// $objReader = new
PHPExcel_Reader_Excel2007();// $objReader = new
PHPExcel_Reader_Excel2003XML();// $objReader = new
PHPExcel_Reader_OOCalc();// $objReader = new
PHPExcel_Reader_SYLK();// $objReader = new
PHPExcel_Reader_Gnumeric();// $objReader = new
PHPExcel_Reader_CSV();/** Load $inputFileName to a PHPExcel Object
**/$objPHPExcel = $objReader->load;//得到当前活动sheet$curSheet