我们知道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');
下载地址: 蓝邹云下载