我们知道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__)); */
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'); 下载地址：http://download.csdn.net/detail/slyjit/9834441