PHPEXCEL精简版之导出导入与图片读取

我们知道PHPEXCEL原版非常大。classes文件大小达到20M之多，当然里面的功能也是非常丰富的，但是当我只需要导出与导入功能时 就显得非常大了

精简后的PHPexcel大小2M都不到，并且能够完全满足导出与导入，当然里面还是有一些冗余用不到的 但是已经很小了

文件下载我会在后面给出链接

案例说明：

1.目录结构

|--PHPExcel       phpexcel核心文件夹

|--PHPExcel.php         phpexcel核心文件

|--read.php    excel导入案例

|--write.php    excel导出案例

|--xabc.xlsx xabcd.xls write.xlsx案例中使用或生成的excel文件

2.文件详细说明

2.1读取文件 read.php读取xabc.xlsx,xabcd.xls

里面分别介绍了如何导入xls xlsx两种格式文件的导入 并将其转换为数组 ，或者直接另存为新的excel文件

转换为数组之后我们可以进行导入到数据库或者直接展示等操作

<?php
require_once './PHPExcel/IOFactory.php';

$file = 'xabc.xlsx';
//$file = 'xabcd.xls';
$fileArr = explode('.',$file);
$exts = $fileArr[1];
if (!file_exists($file)) {
	exit("no file.\n");
}
if ($exts == 'xls') {
	$type = 'Excel5';
} else if ($exts == 'xlsx') {
	$type = 'Excel2007';	
} 
$objReader = PHPExcel_IOFactory::createReader($type); 
try{
	$objPHPExcel = $objReader->load($file);
}catch(Exception $e){}
$allWorksheets = $objPHPExcel->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<$allColumn;$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] = (string)$value; 
                } 
                $arr[$currentRow] = $row; 
            } 
            $array[$i]["Content"] = $arr; 
            $i++;
        }
print_r($array); //直接打印数组 根据需求展示或存入数据库
/*另存未新的EXCEL
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
*/

图片读取

$sheet = $objPHPExcel->getSheet(0);
$data=$sheet->toArray();//转数组 但是获取不到图片
foreach($sheet->getDrawingCollection() $imgk => $img) {//获取所有资源后遍历
    $filename = $img->getIndexedFilename();  //文件名
    $type = $img->getExtension();//图片扩展
    $path = $img->getPath();//图片路径
    $source = imagecreatefrompng($path);//根据扩展 读取图片
    $res = imagepng($source, $filename);//存储图片
    list($startColumn,$startRow)= PHPExcel_Cell::coordinateFromString($img->getCoordinates());//获取图片所在行和列
    echo $startColumn,'^^',$startRow,'^^^',$type,'^^^',$path,'^^',$filename,"<br/>";
    $imageFileName = $startRow.'_'.$startColumn.'_'.$imgk;
    switch ($type) {
                case 'jpg':
                case 'jpeg':
                    $imageFileName .= '.jpg';
                    $source = imagecreatefromjpeg($path);
                    $res = imagejpeg($source, $imageFilePath . $imageFileName);
                    break;
                case 'gif':
                    $imageFileName .= '.gif';
                    $source = imagecreatefromgif($path);
                    imagegif($source, $imageFilePath . $imageFileName);
                    break;
                case 'png':
                    $imageFileName .= '.png';
                    $source = imagecreatefrompng($path);
                    $res = imagepng($source, $imageFilePath.$imageFileName);
                    break;
    }
    exit;
}

2.2 导出文件write.php生成write.xlsx

导出文件我是直接使用的官方的代码 修改不大

增加了导出另存为xlsx和xls

<?php
	require_once './PHPExcel.php';
	// Create new PHPExcel object
	$objPHPExcel = new PHPExcel();

	// Set document properties
	$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
							 ->setLastModifiedBy("Maarten Balliauw")
							 ->setTitle("Office 2007 XLSX Test Document")
							 ->setSubject("Office 2007 XLSX Test Document")
							 ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
							 ->setKeywords("office 2007 openxml php")
							 ->setCategory("Test result file");


	// Add some data
	$objPHPExcel->setActiveSheetIndex(0);
	$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Firstname:')
											->setCellValue('A2', 'Lastname:')
											->setCellValue('A3', 'Fullname:')
											->setCellValue('B1', 'Maarten')
											->setCellValue('B2', 'Balliauw')
											->setCellValue('B3', '=B1 & " " & B2');

	// Define named ranges
	$objPHPExcel->addNamedRange( new PHPExcel_NamedRange('PersonName', $objPHPExcel->getActiveSheet(), 'B1') );
	$objPHPExcel->addNamedRange( new PHPExcel_NamedRange('PersonLN', $objPHPExcel->getActiveSheet(), 'B2') );

	// Rename named ranges
	$objPHPExcel->getNamedRange('PersonName')->setName('PersonFN');

	// Rename worksheet
	$objPHPExcel->getActiveSheet()->setTitle('Person');


	// Create a new worksheet, after the default sheet
	$objPHPExcel->createSheet();

	// Add some data to the second sheet, resembling some different data types
	$objPHPExcel->setActiveSheetIndex(1);
	$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Firstname:')
											->setCellValue('A2', 'Lastname:')
											->setCellValue('A3', 'Fullname:')
											->setCellValue('B1', '=PersonFN')
											->setCellValue('B2', '=PersonLN')
											->setCellValue('B3', '=PersonFN & " " & PersonLN');

	// Rename worksheet
	$objPHPExcel->getActiveSheet()->setTitle('Person (cloned)');

	// Set active sheet index to the first sheet, so Excel opens this as the first sheet
	$objPHPExcel->setActiveSheetIndex(0);

	//直接保存文件
	// Save Excel 2007 file
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	$objWriter->save(str_replace('.php', '.xlsx', __FILE__));

	/*
	//导出另存为-Excel2007
	// Redirect output to a client’s web browser (Excel2007)

	header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	header('Content-Disposition: attachment;filename="test.xlsx"');
	header('Cache-Control: max-age=0');

	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	$objWriter->save('php://output');
	*/
	//导出另存为-Excel5
	// Redirect output to a client’s web browser (Excel5)
	header('Content-Type: application/vnd.ms-excel');
	header('Content-Disposition: attachment;filename="test.xls"');
	header('Cache-Control: max-age=0');

	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
	$objWriter->save('php://output');

下载地址： 蓝邹云下载